Activity: update data with javascript calculated field

Avatar

Avatar

maurizio_coro

Avatar

maurizio_coro

maurizio_coro

12-02-2019

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:

1689757_pastedImage_0.png

any idea on who to do it?

thanks

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar

Florian_Courgey

MVP

Avatar

Florian_Courgey

MVP

Florian_Courgey
MVP

13-02-2019

Hi,

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

2019-02-13_175608_screen_17.jpg

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);

}

Answers (1)

Answers (1)

Avatar

Avatar

rasmusp39956127

Avatar

rasmusp39956127

rasmusp39956127

31-12-2019

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!