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.

Marketing automation tips
Marketing Automation

TIP | Sending SMS with Unicode Characters

1 minute read#marketing automation #sms

Are you planning to send SMS messages with Unicode characters? If so, you may be wondering why your messages are split into multiple messages even though they don’t exceed the character limit. Here’s what you need to know about sending SMS messages with Unicode characters. What are Unicode Characters? Unicode is a character encoding standard […]

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

SFMC TIP | The Power of Send Logging

1 minute read#send logging

Send logging in Salesforce Marketing Cloud is a feature that allows you to track the delivery, open, click, and other engagement metrics for the emails that you send from your account. This feature helps you to gain insights into the performance of your email campaigns and optimize them for better results. When you enable send […]

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
Wordpress CMS
Building the blog, Wordpress

WP | SEO plugin and google search console

less than a minute read

I have found one plugin that meets all my requirements and those are All three were satisfied with The SEO Framework. But also with this plugin I had some struggles, later I found same problems one may have with all other SEO plugins. The problem I had was that I was not able to read […]

Continue reading
SFMC tips and tricks
Marketing Automation, Salesforce Marketing Cloud, SFMC Tips & Tricks

SFMC TIP | How to Link Journey to Delivery

1 minute read#data views #SQL

In Salesforce Marketing Cloud, you can link the customer journey to delivery using data views. Custom views that contain e.g. all tracking information. Here are some steps you can follow to link the journey to delivery using data views: This SQL view will return data such as the subscriber key, event date, journey and activity […]

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