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 conditionBut 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.
