How do I create a calculated field using JavaScript in an Enrichment activity and use it in subsequent workflow steps?

Question

Hi,I am working in Adobe Campaign Classic and want to calculate a new field in an Enrichment activity using JavaScript (Initialization script), and then use this field in later workflow activities (such as Query, Split, or Export).Specifically, I want to calculate the maximum value from a set of fields, using some logic, and store the result as a new field (for example, maxScore). I tried setting the value in the Initialization script like this:targetData.maxScore = Math.max( (targetData.Real_Score_Personbil >= targetData.phs ? -1.0 : targetData.Real_Score_Personbil), (targetData.Real_Score_Pension >= targetData.phs ? -1.0 : targetData.Real_Score_Pension), (targetData.Real_Score_Sjukvard >= targetData.phs ? -1.0 : targetData.Real_Score_Sjukvard), (targetData.Real_Score_Olycksfall >= targetData.phs ? -1.0 : targetData.Real_Score_Olycksfall), (targetData.Real_Score_OvrigaFordon>= targetData.phs ? -1.0 : targetData.Real_Score_OvrigaFordon), (targetData.Real_Score_Liv >= targetData.phs ? -1.0 : targetData.Real_Score_Liv), (targetData.Real_Score_Hem >= targetData.phs ? -1.0 : targetData.Real_Score_Hem), (targetData.Real_Score_Bat >= targetData.phs ? -1.0 : targetData.Real_Score_Bat), (targetData.Real_Score_BoKvar >= targetData.phs ? -1.0 : targetData.Real_Score_BoKvar), (targetData.Real_Score_BankKonto >= targetData.phs ? -1.0 : targetData.Real_Score_BankKonto), (targetData.Real_Score_BankSpara >= targetData.phs ? -1.0 : targetData.Real_Score_BankSpara), (targetData.Real_Score_BankBolan >= targetData.phs ? -1.0 : targetData.Real_Score_BankBolan) ); However, when I try to add this field in the "Add data" (or "Output columns") tab of the Enrichment, I am not sure what to put in the "Expression" field.If I write maxScore or targetData.maxScore, I get an error saying the element is unknown. If I use vars.maxScore, I get an error that vars is unknown.What is the correct way to:Calculate a new field using JavaScript in the Initialization script of an Enrichment activity?Make this field available as an output column (so I can use it in later workflow activities)?If possible, please provide a step-by-step example.Thank you!

SorenDP · Accepted Answer

Sure!

So in pseudo configuration I would do it sort of like this.

Query Activity with empty additional data field:

JS Activity in the next step:

var query = xtk.queryDef.create(
  <queryDef schema="temp:query2" operation="select">
  <select>
    <node expr="newField"/>
    <node expr="@id"/>
    <node expr="@email"/>
  </select>
  
  </queryDef>);
  
var result = query.ExecuteQuery();
var index = 0;
for each (var record in result){

var newField = "false";
  if (record.@email == "XYZ@XYZ")
  {
    newField = "true";
  }
  
  var sql = "UPDATE "+vars.tableName+" SET sNewField = '"+newField+"' WHERE iId = "+record.@id;
  sqlExec(sql);
  
}

This will set the "newField" in the temporary workflow table to a specific value after JS calculations. However, it is important to note that this will probably not scale very well since this is single updates per iteration. So I would consider a different approach if you are beyond more than say 3-5k rows.

If you are scaling this solution, I would personally suggest to store the calculated values in a seperate dedicated schema/table and join that table into the sql query in the JS activity to ensure that you can update based on the join (eg. a batch update) rather than making single SQL query requests.

SorenDP · Answer

To my knowledge if the values you want to calculate are per recipient, then you would need to use a JS activity to query the temporary table in the workflow and then loop through each item instance and do the calculations on each iteration. Lastly you would have to write the results back to the elements from the table, to ensure that these are in fact saved as targetData variables.

You could do the read using "var query = xtk.queryDef.Create" syntax. In some of my examples, I have been writing the results back to the temporary working table using SQL statements. At least that is how we've done stuff similar to this. >

So simply put. I would:
1. Create the initial query and add an empty field as additional data "maxScore"
2. Create a JS activity afterwards to query the tempTable -> do calculations per record -> write result back to temporary working table
3. Now you should see the results in targetData in your workflow, per record in question.

Potentially someone has a better way around this?

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded