How can I get my variable value into quote " "?

[assiba_johnson]

Hi everyone!

I have the following js script to pass the value gridversion to delivery.  

The result is OK but the value is not passed to the delivery with double quote " "

---

var query = xtk.queryDef.create(
<queryDef schema={vars.targetSchema} operation="select">
<select>
<node expr="@mongoId"/>
<node expr="@email"/>
<node expr="@scoring"/>
<node expr="@profil"/>
<node expr="@gridVersion"/>
</select>
</queryDef>
);

result = query.ExecuteQuery();

for each (var e in result) {
logInfo(e.@scoring +";"+ e.@profil +";"+ e.@mongoId);

var recipientScoring = e.@scoring;
var mongoId = e.@mongoId;
var recipientProfil = e.@profil;
var gridVersion = e.@gridVersion;

//for statement

if (recipientScoring == "RFM10" && recipientProfil == 1111) {
gridVersion = 1
logInfo("gridVersion:" + gridVersion)}

if (recipientScoring == 'RFM10' && recipientProfil == 1000) {
gridVersion = 2
logInfo("gridVersion:" + gridVersion)}

else {
logInfo("gridVersion:" + 'DEFAULT')}

sqlExec("UPDATE " + vars.tableName + " SET iGridVersion="+ gridVersion +" where sMongoId= '" + mongoId + "'");

// Declaration de variable

instance.vars.tableName = "gridVersion"

--

In the delivery tabs/advanced settings, I have: 

And under Delivery Tabs/variables, I have:

My result is:

Expected result should be:

Anyone knows how I can fix this? Thanks in advance!

[ParthaSarathy]

Hi @assiba_johnson ,

Add below line in top of for loop,

var gridVersion = e.@gridVersion;

var gridVersionString = gridVersion.toString();

And pass gridVersionString variable to your delivery.

[assibaj68720939]

Hello @_Manoj_Kumar_  & @ParthaSarathy ,

I have the js script working like this now:

Result OK:

However, even that the gridVersion variable is updated for each Lead under the delivery, they all picked up the value 1. NOK.

How can I make it work so?

    Lead A will get the value A via the Delivery variable

     Lead B ----> Value B

     Lead C ----> Value C

Thanks in advance for your help!

[_Manoj_Kumar_]

Hello @assibaj68720939 

The value of the instance variable is static and will be the same for all records.

Here is what you can do.

Add an enrichment activity before this javascript code and add a blank field gridVersion in it.

Then in your JS code do this:

if(recipeintScoring=='RFM10' && recipientProfile ==1111){
var gridVersion=2;
}else if(recipeintScoring=='RFM10' && recipientProfile ==1010){
var gridVersion=1;
else{
var gridVersion=0;
}

var mySQL="UPDATE "+vars.tableName+" set sGridVersion="+gridVersion+" WHERE sEmail="+w.@email+";

sqlExec(mySQL);

I am have used the email address as Primary key to update field you can use mongoId or whatever is the unique valure

Then in your delivery, you can just use targetData.gridVersion to personalize content.

[assiba_johnson]

Hi @_Manoj_Kumar_ ,

Many thanks for the above.

I mentioned earlier as targetdata value in the email, this is not working  as the value should be passed as email variable to get the result I am expecting. Now, I am trying to use the js (in red) to prepare the delivery and update the variable value during the email preparation but not updating either:

var query = xtk.queryDef.create(
<queryDef schema={vars.targetSchema} operation="select">
<select>
<node expr="@mongoId"/>
<node expr="@email"/>
<node expr="@scoring"/>
<node expr="@profil"/>
<node expr="@griddVersion"/>
</select>
</queryDef>
);

var result = query.ExecuteQuery();

for each (var w in result){
logInfo(w.@scoring +";"+ w.@profil+";"+ w.@mongoId);

// Conditions for gridVersion Value

var recipientScoring = w.@scoring;
var mongoId = w.@mongoId;
var recipientProfil = w.@profil;
var gridVersion = w.@griddVersion;
instance.vars.gridVersion = vars.gridVersion;

if(recipientScoring=='RFM10' && recipientProfil ==1111){
vars.gridVersion=2;}
else if(recipientScoring=='RFM10' && recipientProfil ==1010){
vars.gridVersion=1;}
else{
vars.gridVersion=0;}

sqlExec("UPDATE " + vars.tableName + " SET sGriddVersion="+ vars.gridVersion +" where sMongoId= '" + mongoId + "'");

logInfo("instance.vars.gridVersion : " + vars.gridVersion);

}

loadLibrary('xtk:shared/nl.js');
loadLibrary('xtk:shared/xtk.js');

var deliveryCode = '202203_cha125_en_ps'
var label = "*"
var newDeliveryCode = "202203_cha125_en_ps_c"

var query = xtk.queryDef.create(
<queryDef schema="nms:delivery" operation="select">
<select>
<node expr="@id"/>
<node expr="@label"/>
<node expr="@isModel"/>
<node expr="[@operation-id]"/>
<node expr="[@workflow-id]"/>
<node expr="[@folderProcess-id]"/>
<node expr="[@folder-id]"/>
</select>
<where>
<condition expr={"@FCP=0 and @deliveryCode = '" + deliveryCode +"' and @isModel=1"}/>
</where>
</queryDef>
);

var execQuery = query.ExecuteQuery();
var results = execQuery.delivery;
var current = results[0];
// Get the destination folder based on if we have a model or not
var folder_id = String(current["@folder-id"])

if (NL.XTK.parseInt(current.@isModel)) {
folder_id = String(current["@folderProcess-id"])
logInfo("IS MODEL: " + folder_id)
} else {
logInfo("IS NOT A MODEL: " + folder_id)
}

var delivery = nms.delivery.create()
delivery.DuplicateTo("nms:delivery|" + current.@id, "xtk:folder|" + folder_id)

if (String(current.@label).indexOf(label) < 0) {
delivery.label = current.@label + " | " + label;
} else {
delivery.label = current.@label;
}

delivery.deliveryCode = newDeliveryCode;

// link the delivery to the operation to make sure it will be displayed in
// the campaign dashboard. This attribute needs to be set manually here since
// the Duplicate() method has reset it to its default value => 0
delivery.operation_id = instance.operation.id;
delivery.workflow_id = instance.id;

// update delivery variable

var variables = delivery.variables._var

for( var index=0; index < variables.length; index++)
{
if(delivery.variables._var[index].name.toString() == ""){
delivery.variables._var[index].stringValue = instance.vars.gridVersion
break;}
}

// adjust some delivery parameters to make it compatible with the
// "Prepare and start" option selected in the Delivery tab of this activity
delivery.scheduling.validationMode = "manual";
delivery.scheduling.delayed = 0;

// save the delivery in database
delivery.save()
// store the new delivery Id in event variables
vars.clientsdeliveryId = delivery.id

Any idea?

Thanks in advance !

[_Manoj_Kumar_]

@assiba_johnson 

Replace this

instance.vars.tableName = "gridVersion"

with this

instance.vars.tableName = '"'+gridVersion+'"';

[assibaj68720939]

Hi @ParthaSarathy , many thanks for your suggestion. It didn't work out for me. I will try @_Manoj_Kumar_ approach and provide a feedback here. Thank you all!!!

[assibaj68720939]

Hi @_Manoj_Kumar_ , not working. Do I have to add something to the delivery property variables? Thanks!

[assibaj68720939]

@_Manoj_Kumar_ & @ParthaSarathy, maybe I am missing something. Actually, this value is a  variable under Email and doesn't exist under any record in DB. I would like to manipulate it and pass value to delivery variable. I tried all I know without success. When I set it to static value (eg: 1) under  the delivery variable tab it works OK. But when I try to manipulate it as a variable coming from the temp table it doesn't work. Thanks.

[_Manoj_Kumar_]

Hello @assibaj68720939 @assiba_johnson 

Replace this code ;

var variables = delivery.variables._var

for( var index=0; index < variables.length; index++)
{
if(delivery.variables._var[index].name.toString() == ""){
delivery.variables._var[index].stringValue = instance.vars.gridVersion
break;}
}

with this:

delivery.variables._var[0].stringValue = instance.vars.gridVersion

Then define a variable in your delivery with the name gridVersion and the datatype should be a string

Then use this variable in delivery like this:

<%= variables.gridVersion %>

[Sukrity_Wadhwa]

Hi @assiba_johnson,

Were you able to resolve this query on your own or do you still need help here? Do let us know.

Thanks!

[assiba_johnson]

Hi @Sukrity_Wadhwa I am still stuck with this. Will really appreciate it if someone has the solution for this. Seeing product limitation here maybe. Thanks.

[Sukrity_Wadhwa]

Thanks for sharing that @assiba_johnson! I will escalate this among our internal SMEs and see if they can help you with this issue.

[assiba_johnson]

Thanks very much @Sukrity_Wadhwa