Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

Need Help with Looping in JS for a usecase

akashaj7696
Level 3
Level 3

Hi Everyone,

 

I have a usecase wherein i need to populate one value to other field. Please see below-

CCT_IDbestZip
ID1 
ID1 
ID1 
ID1 
ID112345
ID1 
ID1 
ID1 
ID1 
ID1 

 

I want to populate the zip - 12345 to all the ID1 rows, I tried a code, mentioned below but didn't get it to work yet.

 

var query=xtk.queryDef.create(
<queryDef schema="temp:union" operation="select" lineCount="10000">
<select>
<node expr="@id"/>
<node expr="@CCT_ID"/>
<node expr="@bestZip"/>
<node expr="@newZip"/>
</select>
</queryDef>);

var result = query.ExecuteQuery();

for each (var rec in result.union){

if (rec.@CCT_ID = rec.@CCT_ID){
if (rec.@bestZip != ""){
var value = rec.@bestZip;
logInfo("The value of zip is: " +value);
}
if (rec.@CCT_ID = rec.@CCT_ID){
if (rec.@bestZip = ""){
var newZip = value;
logInfo("The NewZip Value is:" +newZip);
var update=<union xtkschema="temp:union" _operation="update" _key="@id" id={rec.@id}/>
update.@newZip = newZip;

//Write the results to the session
xtk.session.Write(update);
}
}
}

5 Replies
Darren_Bowers
Community Advisor
Community Advisor

Hi @akashaj7696 - I have tried to do this and I found that its easier to write back to another schema than the one you are querying in the queryDef. You can then query the temporary table later in the workflow.

I could never get the xtk.session.Write() function to actually work correctly so I used sqlExec() instead. Be careful if you are using user-input values as its not database-safe doing direct DB writes, so you should sanitize the inputs.

Also, the write operation seems to affect the cursor in the query operation when you need to page the query (i.e. >10000 results). It works fine if you never need to page the query.

There is some more info here:  https://experienceleaguecommunities.adobe.com/t5/adobe-campaign-classic-questions/advanced-workflow-...

Cheers

Darren

akashaj7696
Level 3
Level 3
Thanks Darren. Is there any solution to this usecase without using JS?
Darren_Bowers
Community Advisor
Community Advisor
@akashaj7696 - you mention you only want to populat the bestZip (12345) for ID1. If the use case is this simple then just use an Enrichment activity that looks for CCT_ID = "ID1" then set the bestZip value to 12345. If its more complex than this, like you have more than one CCT_ID that you need to look for then you probabaly need to use JS to locate and populate the bestZip for the right CCT_ID. Cheers
akashaj7696
Level 3
Level 3

That was funny, if it was that simple i could have hardcoded it's obviously for 1000s of ID. Can you atleast hep with the way to run more than 10000 records in JS write method? i'm looking for 200K records atleast

denisb91363072
Employee
Employee

Hi there,

If the ask is to query the temporary table and update the temporary table in return, it's not doable outside JS and you would have to have the column you want to update into the temporary table prior the JS by adding it as Additional Data with an empty value.... From a temp table to itself within the workflow itself require a JS activity...

Hope this helps,

Denis.