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
Fade article effect on wordpress post
Building the blog, Wordpress

WP | Fade article effect on wordpress post

3 minutes read#css #php #programming #wordpress

Adding visual interest and engaging design elements to your WordPress website is an essential part of creating a great user experience. One way to accomplish this is by adding a fade effect to your post articles, which can create an attractive and engaging visual transition for readers. In this blog post, we’ll show you how […]

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
SFMC tips and tricks
Marketing Automation, Salesforce Marketing Cloud, SFMC Tips & Tricks

SMFC TIP | NULL value comparison in IF statement

less than a minute read#AMPScript #cloud page #email template #programming

If you have field in DE that its value is not always populated for a particular row (customer) and you test field value against any value cloud page will throw an 500 – internal server error. To fix this you will need to add another AND-condition to test field value for NULL

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
SFMC tips and tricks
Marketing Automation, Salesforce Marketing Cloud, SFMC Tips & Tricks

SFMC TIP | Invalid links in HTML

1 minute read#AMPScript #programming

When the HTML template is used (loading your own HTML code) all the links that use query parameters (?a=b) will resolve to invalid links when they redirect upon user click. This happens when web analytics UTM tags are being attached to the delivery. To resolve this issue all the links with additional query parameters has […]

Continue reading
Link campaign to the delivery send in salesforce marketing cloud
Salesforce Marketing Cloud

SFMC | Link campaign to delivery send

3 minutes read#data views #programming #SQL #ssjs

Are you aware of Salesforce Marketing Cloud’s additional marketing feature known as Campaign? This tool allows you to group similar journeys together, providing greater organization for your marketing activities. However, one drawback is that the campaign information is not available in the data views. While waiting for a fix from Salesforce, you can establish a […]

Continue reading
Comment form with bootstrap
Building the blog, Wordpress

WP | Style WordPress comments in bootstrap

4 minutes read#bootstrap #programming #wordpress

With creating your own WordPress theme you will have to style comment section. For that purpose you will have to add a bit of own styling. In my case I used bootstrap CSS framework that makes life easier for programmers that are not that proficient with front end styling. This will make sure that your […]

Continue reading