Activity: update data with javascript calculated field

[maurizio_coro]

Hi,

I'd like to use the update data activity but the content of a field should be the result of a javascript function.

Something like that:

any idea on who to do it?

thanks

[Florian_Courgey · Accepted solution]

Hi,

We use an Enrichment, followed by a Javascript activity to perfom JS operations on fields:

Arrows order:

1. Create an enrichment with the expression field to 0 for an int, 0.0 for a double, '' for a string

2. Start the WF and Double click on the transition arrow to display the Workflow Schema

>> for the JS code, see below

3. Check your results with Right click > display the target (Be sure to check Keep intermediate population in the WF properties)

4. Use your newly created fields in your update activity

In this example, the JS code sets @myInt to 99, @myDouble to a random value (via Math.random()) and @myString to a md5 of the recipient email. Note: each field is prefixed by i for an Int, d for a Double, s for a String and ts for a Timestamp/DateTime (see Database mapping)

// select all records from last Transition
var sql = "SELECT iId, sEmail, dMyDouble FROM " + vars.tableName; // vars.tableName is the name of the Workflow Schema
var recipients = sqlSelect("collection,@id:int,@email:string", sql); // see sqlSelect 
// iterate over each row
for each(var recipient in recipients.collection){
  // call your methods
  var newInt = 99;
  var newDouble = Math.random();
  var newString = digestStrMd5(recipient.@email.toString());
  // update the row
  var sql = "UPDATE "+ vars.tableName + " SET iMyInt="+newInt+", dMyDouble="+newDouble+", sMyString='"+newString+"' WHERE iId="+recipient.@id.toString();
  logInfo('Executing '+sql);
  sqlExec(sql);
}

[rasmusp39956127]

Hey all, this is an additional question about enrichment of the data.

Can the enrichment process be executed within JavaScript(JS) function instead of using "Enrichment" -tool in order to make a single dynamically adjustable block for the end user?

I am adding additional data to vars.tableName using JS but after the activity block that runs my JS I'm not able to retrieve the data. This is in Workflow -> Display the Target, at the end of the workflow, where the data should update.

Shouldn't I be able to reference the current temp table using vars.tableName in the location? (e.g. Beginning being temp:stage1, step one being temp:stage2 and so on.. vars.tableName should be working to recognise different stages i'm in, right?)

Thanks in advance and happy New Year!