Updating Lists in Adobe Campaign with JavaScript

Marcel Szimonisz
Dec 20, 2024
5 minutes read

Updating Lists in Adobe Campaign with JavaScript

Imagine a campaign composed of multiple workflows, each following a specific sequence guided by various logic paths that dictate how messages are sent. To effectively manage this, you need to track where your recipients currently are within the sequence to determine their next step. The most straightforward and logical approach is to save the recipient’s current status in a database.

Table of Contents

One of the key challenges we face on projects is the inability to create custom tables (schemas) and save records directly into them. This is because of strict restrictions requiring approvals, extensive paperwork, and administrative rights that most users don’t have. In many cases, saving directly to the database is either extremely difficult or completely impossible. Instead, we rely on lists, which function like user-defined tables and are accessible to most roles with full permissions.

Update Adobe Campaign List Using workflow activities

Lists are very simple to use, especially when it comes to adding data. However, problems can arise when you need to update specific items in a list. The ‘List Update’ activity, which is used to add records to a list, does not have a built-in update feature. To update specific records, you need to create a workflow that first purges the existing data, saves the updated records, and then re-saves the remaining records. In some cases, Adobe Campaign may automatically remove duplicates during this process, which might not align with the specific needs of your workflow.

Update list in adobe campaign drake meme

Another major issue is that every time you run a workflow, it purges the list data and saves it with a new structure. However, another workflow in your campaign might depend on a different list structure, leading to mismatches that can cause subsequent runs to fail. The core problem is that all lists across these workflows need to have the exact same structure for the workflows to function properly. To address these complexities, a more straightforward solution is to use JavaScript to execute an SQL query that directly updates the list, avoiding the need for repeated purging and restructuring.

Update Adobe Campaign List Using JavaScript Code

For the solution we’re going to use, it’s important to first understand how to query data from workflow targeting data. If you don’t have admin rights or the permissions needed to execute SQL queries, you should refer to an article or guide on how to escalate your rights to admin to proceed effectively. (Shh 🤫 don’t tell anybody)

Before diving into the actual code that will simplify our work, we first need to identify the table name where our group or list is saved in the database. To do this, simply open the list, navigate to the Content tab, and select Schema from the bottom panel. This will display the structure of the list and, most importantly, the table name we need.

The final and most important step is to create a JavaScript activity that reads the target data and updates our list. In this step, I update the timestamp to reflect when a recipient entered a specific part of the campaign.

loadLibrary("xtk:shared/nl.js");
NL.require("xtk:queryDef.js");

var selectExpr = [
    { expr: '@userId'},
    { expr: '@Campaign_Code'}
  ],
  nlQueryDef = new NL.QueryDef("temp:enrichData",{lineCount: 5000}, selectExpr, []),
  result = nlQueryDef.create().execute().data,  
  i,
  sql;






for(i=0;i<result.length;i++){
    sql = "UPDATE grp275631569 SET tsCampaign1=CURRENT_TIMESTAMP,sCampaign1Code='"+result[i].Campaign_Code+"' WHERE sMDMID='"+result[i].userId+"'";
    sqlExec(sql); 
}

Step 1: Load Libraries

The required libraries for using function nlQueryDef we need to load:
- xtk:shared/nl.js: Provides shared functions in Adobe Campaign.
- xtk:queryDef.js: Enables the creation and execution of queries.

Step 2: Define Fields to Retrieve

A list of fields (@userId and @Campaign_Code) is specified in the selectExpr array to define what data to fetch from the targeting schema.

Step 3: Create the Query

A query is created using the NL.QueryDef object:
- Target schema: temp:enrichData. Certain activities in a workflow create temporary schemas, one of which is the enrichment activity. I referenced the closest enrichment activity that leads to the JavaScript.
- Limit: Up to 5000 records at a time. You can set the limit to any value. If the campaign doesn’t have a complex flow, you can choose a smaller limit, or increase it for larger campaigns based on your needs.
- Fields: Defined in selectExpr to fetch userId and Campaign_Code. You can select anything that you need to update in your list or by which you set up your where condition.

Step 4: Execute the Query

The query is executed, and the retrieved data is stored in the result array.

Step 5: Loop Through Results

A loop iterates through each record in the result array.

Step 6: Construct SQL Query

For each record, an SQL UPDATE query is constructed:
- Updates the tsCampaign1 field with the current timestamp.
- Updates the sCampaign1Code field with the Campaign_Code from the record.
- Matches the row using the sMDMID field with the userId from the record.