ACC | How to Leverage queryDef in Adobe Campaign

Adobe Campaign, Marketing Automation
7 minutes read#jsapi #programming #SQL #workflow

When it comes to programatically selecting records from the database in Adobe Campaign Classic, queryDef emerges as a crucial tool. As a static SOAP method extensively employed in JavaScript, particularly within workflows or web apps, queryDef offers unparalleled capabilities. However, what many may not realize is that there are three distinct implementations of this function. Join us as we embark on a journey to explore and compare all three implementations, shedding light on their respective strengths and identifying the ultimate choice for maximizing your Adobe Campaign experience.

xtk.queryDef.create()

The older version uses XML for creating the query definitions. You can take a look at the official data oriented adobe campaign API documentation.

var query = xtk.queryDef.create(
  <queryDef schema="xtk:workflow" operation="select">
    <select>
      <node expr="@internalName"/>
    </select>
  </queryDef>
)

var res = query.ExecuteQuery()

for each (var w in res.workflow)
  logInfo(w.@internalName)

Options that can be used with queryDef are listed below:

<queryDef schema="schema_key" operation="operation_type">
  <select>
    <node expr="expression1"/>
    <node expr="expression2"/>
  </select>
  <where>
    <condition expr="expression1"/>
    <condition expr="expression2"/>
  </where>
  <orderBy>
    <node expr="expression1"/>
    <node expr="expression2"/>
   
  </orderBy>
  <groupBy>
    <node expr="expression1"/>
    <node expr="expression2"/>
    
  </groupBy>
  <having>
    <condition expr="expression1"/>
    <condition expr="expression2"/>
 
  </having>
</queryDef>

Additionally you can use subqueries in the where conditions:

<condition setOperator="AND">
  <subQuery schema="xtk:recipient">
    <select>
      <node expr="[@recipient-id]"/>
    </select>
    <where>
      <condition setOperator="OR">
        <condition expr="[@status]='active'"/>
        <condition expr="[@status]='pending'"/>
      </condition>
    </where>
  </subQuery>
  <condition setOperator="AND">
    <condition expr="[@age]&gt;=18"/>
    <condition expr="[@country]='USA'"/>
  </condition>
</condition>

The function offers a high level of versatility, allowing you to define and execute queries similar to those created in the query editor. A useful tip is to first create your query in the query editor and then extract the XML source code of the query section. This XML code can be directly copied into the queryDef XML structure, enabling you to effortlessly create and test complex queries. This streamlined approach empowers you to leverage the full potential of queryDef and efficiently handle various query scenarios.

var query = xtk.queryDef.create(
  <queryDef schema="nms:deliveryLog" operation="select">
    <select>
      <node expr="@id"/>
      <node expr="@eventName"/>
      <node expr="@eventDate"/>
      <node expr="[@messageId]"/>
    </select>
    <where>
      <condition expr="[eventDate]&gt;=date('2022-01-01')" />
    </where>
    <orderBy>
      <node expr="@eventDate" sortDesc="true" />
    </orderBy>
  </queryDef>
);

var res = query.ExecuteQuery();

for each (var log in res.deliveryLog) {
  var logId = log.@id;
  var eventName = log.@eventName;
  var eventDate = log.@eventDate;
  var messageId = log[@messageId];
  
  logInfo("Log ID: " + logId);
  logInfo("Event Name: " + eventName);
  logInfo("Event Date: " + eventDate);
  log

In the provided code snippet, you may observe the usage of a special function called ‘for each’ which is specific to Adobe Campaign. This function allows you to iterate and traverse over data structures, such as result sets, in a convenient manner.

NLWS.xtkQueryDef()

As the programming world gradually shifts away from XML and embraces JSON, it’s worth noting that the new implementation of xtkQueryDef in Adobe Campaign Classic allows you to work with JXON (JSON/XML Object Notation). This means you can leverage the flexibility and simplicity of JSON while still utilizing the power of xtkQueryDef. Embracing JXON provides a modern approach to querying data, aligning with the industry trend and offering enhanced capabilities for data manipulation.

JXON xtkQueryDef function, being based on the same features as XML one, empowers you to create virtually any query you would with the XML queryDef.

var query = NLWS.xtkQueryDef.create({
  queryDef: {
    schema: "nms:recipient",
    operation: "select",
    select: {
      node: [
        { expr: "@id" },
        { expr: "@firstName" },
        { expr: "@lastName" }
      ]
    },
    where: {
      condition: [
        { expr: "[@country]='USA'" },
        { expr: "[@age]>=18" }
      ]
    },
    orderBy: {
      node: { expr: "@lastName", sortDesc: "false" }
    }
  }
});

var res = query.ExecuteQuery();
var profiles = res.getElementsByTagName("recipient");

for each (var profile in profiles) {
  var firstName = profile.getAttribute("firstName");
  var lastName = profile.getAttribute("lastName");
  logInfo("Profile: " + firstName + " " + lastName);
}
xtk.queryDef VS NLWS.xtkQueryDef

While the XML version of query def is currently available, it is considered the older approach for querying data. It’s recommended to focus on the JXON version since XML support may be phased out in the future. However, it’s worth noting that this change is expected to happen in the distant future. Ultimately, the choice between XML and JXON depends on your personal preference and the simplicity of usage. Personally, I find the ‘NLWS.xtkQueryDef’ function simpler to use with XML queries rather than JXON. But again, the decision is yours to make based on your specific requirements and preferences.

It’s also important to note that both versions ultimately return XML data. In Adobe Campaign Classic, specific functions are available to handle XML payloads, enabling you to extract, manipulate, and process the returned XML data efficiently.

NL.QueryDef()

Last but certainly not least, there is another variant of the queryDef function that I find particularly fascinating. While delving into the core JavaScript libraries of Adobe Campaign, I came across an undocumented function that instantly captured my interest. What sets this humble function apart is its ability to return the query results in JSON format, allowing you to effortlessly process and manipulate the data using native JavaScript functions. Despite the absence of official documentation, this variant offers the same powerful features as the previously mentioned queryDef functions, while providing the added advantage of JSON compatibility.

You can find the entire implementation under JavaScirpt libraries

  • xtk:queryDef.js
Getting Started

To begin using the queryDef.js library, you need to include it in your project and ensure that its dependencies are also included. The library requires the following dependencies:

  loadLibrary('xtk:shared/nl.js');
  NL.require('xtk:queryDef.js');
Query Definition

To create a query, you need to instantiate the NL.QueryDef object. The constructor takes several parameters that define the query:

  • schema: The schema on which the query will be executed.
  • settings: Additional settings for the query (optional).
  • selectExpr: An array of select expressions.
  • whereExpr: An array of where conditions.
  • orderByExpr: An array of order by expressions.
  • conditionLinkExpr: An array of condition links.

Here’s an simple example of how to create a query definition and process results:

var selectExpr = [{expr: "@status"}, {expr:"[operation/@label]", alias:"@campaignLabel"}],
    whereExpr  = {expr: "@internalName = 'DM150'"},
    nlQueryDef = new NL.QueryDef("nms:delivery", selectExpr, whereExpr),
    result = nlQueryDef.create().execute().data;
if (result.length>0){
	result.forEach(function(e,i){//you can also use good old for(;;)
    	logInfo(e.status)
        logInfo(e.campaignLabel)
        
    });
}

The constructor takes several parameters that define the query:

  • 'schema‘: The schema on which the query will be executed.
  • settings‘: Additional settings for the query (optional).
    var _defaults = {
      lineCount:    200,                          // The number of lines to retrieve
      operation:    NL.QueryDef.OPERATION_SELECT, // Operation type
      expandParam:  true,                         // True to handle enabledIf conditions in the query
      startLine:    0,                            // Start offset
      firstRows:    true                          // True to force indexes
    };
  • selectExpr‘: An array of select expressions.
  • whereExpr‘: An array of where conditions.
  • orderByExpr‘: An array of order by expressions.
  • conditionLinkExpr‘: An array of condition links.

When selecting linked records in certain scenarios, if we don’t use an ‘alias’, the attribute will be returned as an XML string. This means that instead of directly accessing the attribute value, we would need to parse and handle the XML structure to extract the desired information.

To avoid the complexity of dealing with XML, we can employ an ‘alias’. By using an ‘alias’, the attribute value will be returned directly as a regular data type (such as a string, number, or boolean), making it easier to work with and manipulate in our code.

Using an ‘alias’ simplifies the process of accessing and utilizing linked record attributes, as we can directly access the attribute value without having to handle XML parsing and manipulation.

On example below you can see the use of all arguments fot the function.

var nlQueryDef = new NL.QueryDef(
  "nms:delivery",
  {
    lineCount: 100,
    operation: NL.QueryDef.OPERATION_GET,
    expandParam: false,
    startLine: 50,
    firstRows: false
  },
  [
    { expr: "@status" },
    { expr: "@status", alias: "@toto" }
  ],
  { expr: "@internalName = 'DM150'" },
  { expr: "@columnName", sortDesc: true },
  [
    {
      alias: "link1",
      xpath: "/path/to/link1",
      schema: "nms:schema1",
      enabledIf: "@condition1"
    },
    {
      alias: "link2",
      xpath: "/path/to/link2",
      schema: "nms:schema2",
      enabledIf: "@condition2",
      whereExpr: [
        { expr: "@columnName = 'value1'" },
        { expr: "@columnName = 'value2'" }
      ]
    }
  ]
);

When going over this library you may notice it is using the NLWS.xtkQueryDef SOAP function. This means all the features from it can be used.

Tips

Use NL.QueryDef to create XML conditions from JSON by that can be used in changing of any XML object.

//var confitions = [{expr: "@internalName = 'DM600500'"}];
//var schema = "nms:seedAddress"
var getWhereConditionsFromJson = function(conditions, schema){
    var nlQueryDef = new NL.QueryDef(schema, [{expr:"@id"}], []); // does not really matter what is put here, as we only want to use its createWhereCondition method
          var root = <condition/>;
          //another good find, deep inside the adobe campaign JS libraries, NL.Utils.each function. 
          //It does exaclty the same as native JS Array.forEach() 
          //I did not know that time the ES5 has it 
          //also for oldtimers for(;;) can be always used :)
          NL.Utils.each(conditions, function(idx, condition) {
            root.appendChild(nlQueryDef.createWhereCondition(condition));
          });
    logInfo(root.toXMLString());
    return root;
  },

Now that I have shown you three different ways to use the same SOAP function, it’s up to you to decide which one you prefer. Personally, I would recommend using the last option, ‘NL.QueryDef’. This method utilizes JSON as the input format and also transforms the XML result into JSON. This can make the process more streamlined and consistent, as both the input and output formats are in JSON, allowing for easier data handling and integration with other systems.

Spawn workflows programatically
Adobe Campaign, Marketing Automation

ACC | Spawn workflows and automate more

3 minutes read#JavaScript #programming #workflow

Adobe Campaign is a powerful tool for creating and managing marketing campaigns. One of its most useful features is the ability to create automated workflows. In this post, we will walk you through the process of “spawning” workflows in Adobe Campaign. What is spawning a workflow? And why and where we can it be useful […]

Continue reading
Adobe Campaign on Debian linux
Adobe Campaign, Marketing Automation

ACC | Installing Adobe Campaign on Debian

15 minutes read#administration #apache #certbot #debian #ssl

Psst! Listen up, fellow campaigners! Did you know that you can actually install Adobe Campaign Classic locally and it won’t cost you a penny? Of course, you won’t be able to run actual marketing campaigns with it, but you can use it as your personal development playground. It’s like having a sandbox, but for grown-ups. […]

Continue reading
Adobe campaign tips and tricks
ACC Tips & Tricks, Adobe Campaign

ACC TIP | How To Convert Base64 to PDF attachment

1 minute read#delivery #JavaScript #jsapi #programming

In this article, I will provide a simple trick for converting Base64 encoded data to PDF using JSAPI in Adobe Campaign Classic. Due to AC’s inability to attach Base64-encoded files directly to emails, this method can prove to be highly useful. Base64 data format that would be easy to use when supperted by Adobe Campaign […]

Continue reading
Adobe Campaign post
Adobe Campaign, Marketing Automation

ACC | Add action button to the form view

2 minutes read#programming #schemas

Have you ever wondered how to add new button with custom functionality to form view. I will show you step by step how to do it. In my example I will create signatures used in email campaigns, which will be dependent on the recipient’s profile information such as language, country etc. The way how I […]

Continue reading
Adobe Campaign post
Adobe Campaign, Marketing Automation

ACC | Track dynamic links

3 minutes read#dynamic links tracking #email template #JavaSript

Some email campaign send outs can have links dynamically rendered in the content. Such links are not visible by Adobe Campaign personalization engine and thus will not be tracked. The good thing is that we can use a workaround to make dynamic links trackable and also on top of all workarounds Adobe itself has provided […]

Continue reading
Adobe Campaign post
Adobe Campaign, Marketing Automation

ACC | All about variables in Adobe Campaign

2 minutes read#JSSP #webapp #workflows

In this article I will explain all sorts of variables you can come across when working with the tool. Also I will discuss different less known approach of storing variables. Workflow variables In adobe campaign we have way how to store variables between consecutive activities. Normally you would store variables as instance or context variables. […]

Continue reading
Adobe campaign tips and tricks
ACC Tips & Tricks, Adobe Campaign, Marketing Automation

ACC TIP | Escalate user rights

1 minute read#JavaScript #programming #webapp

Normally web apps run under the web app user (which is anonymous and has very little to zero rights by default) and for certain operations you would need to require to grant additional access, or even grant full admin. To grant full admin for web app user is not solution to go with. Instead I […]

Continue reading
Adobe campaign tips and tricks
ACC Tips & Tricks, Adobe Campaign, Marketing Automation

ACC TIP | Export connection strings

less than a minute read#configuration #xml

Whether you change your laptop or want to save actual configuration for your colleagues. You can easily achieve that by taking all connection strings as XML. ims – connection over adobe id SSO Also similar approach is to directly change the connection string file nlclient_cnx.xml that is located under your local AppData folder. To quickly […]

Continue reading
Adobe Campaign post
Adobe Campaign, Marketing Automation

ACC | Complete guide to schemas

4 minutes read

I want to gather as much information I have and have it at one place for my future reference and for others to learn what can be possible with the custom schemas in the Adobe Campaign Classic – ACC If you work with adobe campaign for some time you might figured out that almost any […]

Continue reading