Expand my Community achievements bar.

Announcing the launch of new sub-community for Campaign Web UI to cater specifically to the needs of Campaign Web UI users!
SOLVED

Activity: update data with javascript calculated field

Avatar

Level 3

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

1 Accepted Solution

Avatar

Correct answer by
Level 6

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

}

View solution in original post

2 Replies

Avatar

Correct answer by
Level 6

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

}

Avatar

Level 1

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!