ACC Tips & Tricks, Adobe Campaign, Marketing Automation

Use variables in query expression builder

Use variables in query expression builder

When the time comes, you will find yourself in a situation where you would like to set up query builder condition expressions as dynamic values. We can achieve this and more with Adobe Campaign Classic. But what are those situations?

For example, I had to build a reporting workflow for automated campaigns that was divided by countries and recurrence. I used the very same workflow for all scenarios but had to manually amend the conditions whenever I encountered different countries or recurrences. This led me to make the conditions in various queries dynamically, adding the values of country and recurrence in all necessary queries.

Simply put, use dynamic conditions whenever you feel that centralizing values to one place is beneficial for you in terms of maintenance or replicating functionality.

Simple operators

When employing simple operators such as equal to, not equal to, contains, and starts with, we can utilize our instance or event variables as the value for the query condition.

$(vars/@myVar)
$(instance/vars/@myVar)

When all is working fine and when leaving the value input you should see value of parameter 'vars/@myVar' anything else will cause issues.

Included in operators

Now that we understand the use of workflow variables in the query builder, challenges may arise when dealing with the ‘included in’ operator. When we use vars.incond = ‘1,2,’ Adobe Campaign’s query expression will be evaluated as ‘1,2’ instead of ‘1,2,’ thus giving us wrong or no results. Before I found an easier way on the forum, many others and I used the Initialization script of a query activity to access conditions and then create the ‘included in’ expression from the instance variables.

//expr="@firstName IN (1, 2)
vars.incond = "1,2";
activity.where[0].condition[0].condition.expr = "@firstName IN (" + vars.incond + ")";
//condition[0] means that its first condition

But there is a better and simpler way to do it. We only need to tell Adobe Campaign not to escape quotes by wrapping our variable in the query with the $noescaping function. We can use it directly in query builder and there is no need to access intialization script.

$noescaping(vars/@incond)

$noescaping is a built in function that is used to indicate that the content within the function should not have its quotes or special characters escaped. This is particularly useful when you want to include a variable or value in a query expression without having it modified or escaped. It ensures that the content is treated as-is, without any additional formatting or escaping.

  • $string(vars/@myVar) casts your variable as string
  • $long(vars/@myVar) casts your variable to be a long
  • $int64(vars/@myVar) casts your variable to be an int64
  • $date(vars/@myVar) casts your variable to be a date
  • $datetime(vars/@myVar) casts your varibale to be a datetime
  • $noescaping(vars/@myVar) content within the function should not have its quotes or special characters escaped.

Oh hi there 👋
I have a FREE e-book for you.

Sign up now to get an in-depth analysis of Adobe and Salesforce Marketing Clouds!

We don’t spam! Read our privacy policy for more info.

#adobe campaign classic #SQL #workflow
Marcel Szimonisz
Marcel Szimonisz
MarTech consultant As a marketing automation consultant, I specialize in problem-solving, process automation, and driving innovation for clients' marketing platforms.

I hold certifications in Adobe Campaign v6 (3x certified) and Salesforce Marketing Cloud (5x certified), as well as 1x Salesforce Associate certified.

Moreover, I serve as a community advisor for Adobe Campaign, providing expert insights and guidance.

Beyond my professional pursuits, I explore various programming languages, CMSs, and frameworks, enhancing my technical expertise and staying at the forefront of industry advancements.
Take a look at our subscription offering in case you are looking for a Marketing Automation Consultant.

Leave a comment

Your email address will not be published. Required fields are marked *

Similar posts that you may find useful

Adobe Campaign post
Adobe Campaign, Marketing Automation

Implementing DKIM in adobe campaign

2 minutes read

Have you ever wondered how to implement DKIM in Adobe Campaign Classic, look no further here is how you can do it. Implementing DKIM for Adobe Campaign Classic, gave many hard times as there is no official documentation you can follow step by step. But nothing is lost, it is easier than you may think. […]

Continue reading
Adobe Campaign Classic REST over SOAP
Adobe Campaign, Marketing Automation

Build REST over SOAP API in adobe campaign

4 minutes read

If you’re familiar with Adobe Campaign Classic, you may have noticed that it utilizes the SOAP (Simple Object Access Protocol) API. We live in an age where REST is taking over, and the good old XML SOAP is slowly being forgotten. To me, as an old-timer, I can confidently say that it doesn’t make any […]

Continue reading
Adobe Campaign post
Adobe Campaign, Marketing Automation

Complete guide to data schemas in ACC

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

How to query filtered data extension

less than a minute read

Easy way to segment your data is to use filtered data extension. Here we can use user interface to set up conditions as we like. When you want to add this newly filtered data extension to the SQL activity you will find that data extension cannot be found in the file browser. But it is […]

Continue reading
Salesforce Marketing Cloud Tips
Marketing Automation, Salesforce Marketing Cloud, SFMC Tips & Tricks

Power of AMPScript Functions in SSJS

3 minutes read

Have you ever wondered, while working with SSJS, if there was a function in SSJS similar to the one you use in AMPscript? What if I told you that you can bring native AMPscript functions to SSJS? Today, I will show you how to incorporate the best features from both scripting worlds into SSJS automation, […]

Continue reading