Updating a record from within a Javascript Step | Community
Skip to main content
E
eriku40433679
Level 3
February 8, 2018
Solved

Updating a record from within a Javascript Step

  • February 8, 2018
  • 4 replies
  • 6949 views

Hi guys,

I'm trying to perform some string manipulation on the result set of a previous query. I'm using an intermediary Javascript step to contain the logic.

var schemaName = vars.targetSchema.substr(vars.targetSchema.indexOf(":") + 1);  
logInfo(schemaName);
  
var query = xtk.queryDef.create(  
  <queryDef schema={vars.targetSchema} operation="select">  
    <select>  
      <node expr="@id"/>  
      <node expr="@description"/>  
    </select>  
  </queryDef>  
);  
result = query.ExecuteQuery();  
  
  
for each (var e in result) {  
     logInfo(e.@description + e.@id);
     
     // manipulation and assignment logic goes here
     // db update logic goes here
}
// better if the db update goes here and is batched

I'm able to retrieve the fields from the previous step and print the columns out. I'm also able to perform the manipulation required on the description field but I can't, for the life of me, figure out how to SET and UPDATE the field in question.

Should I use a separate sqlExec call using an update sql query as it iterates over each item? Feels a bit inefficient and 'baking' in the string values directly to the sql query seems wrong and dangerous...

Many thanks.

Cheers!

--Erik

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by Amit_Kumar

    Hi Erik,

    Use this:

     var schemaName = vars.targetSchema.substr(vars.targetSchema.indexOf(":") + 1);    
    logInfo(schemaName);  
        
    var query = xtk.queryDef.create(    
      <queryDef schema={vars.targetSchema} operation="select">    
        <select>    
          <node expr="@id"/>    
          <node expr="@description"/>    
        </select>    
      </queryDef>    
    );    
    var resultSet = query.ExecuteQuery();    
    // update the name of schemaName from your query    
    var collection = <{schemaName}-collection _operation="update" xtkschema={vars.targetSchema}/> ;
    for each (var row in resultSet) {    
    var updatedDescription = String(row .@description);
    // manipulation and assignment logic goes here 
    // update the name of schemaName from your query 
    var schemaNameProgram = <{schemaName} id={row.@id} description={updatedDescription} _key = '@id' xtkschema={vars.targetSchema}/>;
    collection.appendChild(schemaNameProgram);
    }
      xtk.session.WriteCollection(collection);

    Regards,

    Amit

    4 replies

    Amit_Kumar
    Amit_KumarAccepted solution
    Level 10
    February 8, 2018

    Hi Erik,

    Use this:

     var schemaName = vars.targetSchema.substr(vars.targetSchema.indexOf(":") + 1);    
    logInfo(schemaName);  
        
    var query = xtk.queryDef.create(    
      <queryDef schema={vars.targetSchema} operation="select">    
        <select>    
          <node expr="@id"/>    
          <node expr="@description"/>    
        </select>    
      </queryDef>    
    );    
    var resultSet = query.ExecuteQuery();    
    // update the name of schemaName from your query    
    var collection = <{schemaName}-collection _operation="update" xtkschema={vars.targetSchema}/> ;
    for each (var row in resultSet) {    
    var updatedDescription = String(row .@description);
    // manipulation and assignment logic goes here 
    // update the name of schemaName from your query 
    var schemaNameProgram = <{schemaName} id={row.@id} description={updatedDescription} _key = '@id' xtkschema={vars.targetSchema}/>;
    collection.appendChild(schemaNameProgram);
    }
      xtk.session.WriteCollection(collection);

    Regards,

    Amit

      E
      eriku40433679Author
      Level 3
      February 9, 2018

      Excellent. Thanks Amit! Works like a charm. Hard coded the schema name as per your inline instructions and it works just fine for this specific workflow.

      I'd like to know if there's a way for me to use the schemaName variable I've already extracted on the first line to make the collection variable a bit more flexible?

      Cheers!

      robayzma
      robayzma
      Level 3
      March 23, 2018

      Hi Amit,

      when I run the code above in a Javascript Code step I get this error:

      23/03/2018 10:11:40 js JST-310000 Error while compiling script 'WKF151/js' line 4: invalid XML tag syntax (line='var collection = <{schemaName}-collection _operation="update" xtkschema={vars.targetSchema}/> ; ' token='-collection _operation="update" xtkschema={vars.targetSchem

      23/03/2018 10:11:40 js a}/> ; ').

      My resultset comes from a dedupe operation that again comes from an intersect. the schemaname is temp:Intersect

      What am I missing?

      Kind regards

      Robert

        poorvab56881543
        poorvab56881543
        Level 2
        September 17, 2019

        Hi Amit,

        Thanks for sharing the above snippet to update collection in a DB schema.

        I am working over a project where I need to sort customer rankings available under multiple product columns and build Campaign according to product ranking. I am able to sort the product rankings for each customer using querydef and setting it into event variable. However when I am trying to read the rankings for each customer through an Enrichment Activity, I am getting ranking of the last customer corresponding to each customer record instead of their own respective rankings. I believe this is due to the last record I am updating in the for loop.

        Can you please suggest a way out to read modified data of each customer specifically within a Campaign workflow.

        Thanks,

        Poorva

        Terms & ConditionsAccessibility statement

        Loading footer...