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

[A_B_SE]

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]

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? 
 

[A_B_SE]

Hello @SorenDP and thanks for your answer.
Could you explain in details how I can set up the JS activity and how I should write the JS code?

[SorenDP]

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.  

[RCh2_]

Hi @A_B_SE,

Just a quick note — the Initialization script in the Enrichment activity doesn’t retain custom fields like `targetData.maxScore` for use in later workflow steps unless you explicitly define them in the Add data tab. Also, variables like `targetData` or `vars` can't be referenced directly in the expression fields.

Instead, you can achieve your goal entirely using built-in expressions. If you want to get the maximum value below `phs` from multiple fields, try this:

Greatest(
Iif(Real_Score_Personbil >= phs, -1.0, Real_Score_Personbil),
Iif(Real_Score_Pension >= phs, -1.0, Real_Score_Pension),
Iif(Real_Score_Sjukvard >= phs, -1.0, Real_Score_Sjukvard)
-- add more fields as needed
)

Just set an alias like `maxScore`, and it will be available in downstream activities like Split or Export.

Sharing snip for reference:

1. Enrichment

2. Result

Hope this helps! 

Thanks,

[A_B_SE]

Hello @RCh2_ 

I'm doing it this way but since it is a quite complex formula I get a message saying:
ODB-240000 ODBC error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them. SQLState: 37000

That's why I want to do it in a JS activity. Your method works but I need to do it 3 times and it takes like 30 min per enrichment.