Expand my Community achievements bar.

SOLVED

0 results on my querydef on a temporary scheme

Avatar

Level 2

Hello,

 

I'm trying to set up a monitoring system that will calculate all my leads receiving duplicate emails. Regarding the workflow activities, I think everything is correct. However, when I perform a querydef on a temporary schema, namely temp:changeAxis2, I get no results. If I use the temporary schema temp:query, though, I manage to get results.

 

Here is my workflow:

Ragsthenos_0-1715866556686.png

Let me explain. I select all the leads from my database (I will change the targeting once I have resolved the issue). Then, I switch dimensions to the broadlogs, deduplicate the duplicate lines I have, then deduplicate the duplicate leads, and switch dimensions (temp:changeAxis2) to the recipient dimension. Finally, I send an email to the relevant operators containing the following JavaScript code:

 

var query = xtk.queryDef.create(
<queryDef schema='temp:changeAxis2' operation="select">
<select>
<node expr="[target/@mongoId]"/>
</select>
<orderBy>
<node expr="[target/@mongoId]"/>
</orderBy>
</queryDef>
);

var res = query.ExecuteQuery();

var wkfCount = res.query.length();

var _error = '';
for each (var line in res.query){

_error += '- ID : ' + line.target.@mongoId + '<br>'
}


delivery.mailParameters.subject = "Notification Christin Preprod - Leads ayant reçu plusieurs fois le même email";
delivery.content.html.source = new XML('<![CDATA[<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><HTML><HEAD></HEAD><BODY><P>' + wkfCount + ' leads ont reçu un ou plusieurs email(s) qui avaient déjà été reçu(s). Voici leurs mongoId :<br><br>' + _error +'</P></BODY></HTML>]]>')

 

à la réception de l'email ça me dit que j'ai 0 leads qui a reçu un email en double alors que j'en ai 46 comme le montre mon workflow. je sais que normalement le code fonctionne car quand je met schema='temp:query' au lieu de schema='temp:changeAxis2' ça m'affiche bien 1414 leads dans mon email

 

Ragsthenos_1-1715867229676.png

 

 

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

1 Accepted Solution

Avatar

Correct answer by
Level 4

Hello! 

In a situation like this, I'd probably just use the vars.recCount variable - it's declared any time an activity passes any number of records out.

 

Just try logInfo(vars.recCount) in a code activity after any query/split etc and you'll see what I mean.

When building the queryDef part, I also like accessing the temporary schema values via the variable

vars.targetSchema so that I don't have to update any values if I reuse code somewhere else. You have to wrap it in curly braces {} as it is a variable to be inserted.

 

 

 

 

<queryDef schema={vars.targetSchema} operation="select">

 

 


I'm pretty sure you can cut  the [target/@mongoId] down to just @mongoId as the [target/] aspect is implied when the value is a field directly on the record for the given schema.

 

 

<node expr="@mongoId"/>

 

 

 

When looking at your for each loop, I believe you can drop the .query part so it's just (var line in res) - I think this is a small difference between the NLWS.xtkQueryDef and xtk.queryDef querying methods. (EDIT: Sorry, actually, I think this would be because we're querying a temporary schema as opposed to directly from the actual table? Regardless, doesn't seem to be needed)

 

 

for each (var line in res){}

 

 


Looking at your code, I'd try something like

 

 

var wkfCount = vars.recCount

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

var res = query.ExecuteQuery();

var _error = '';
for each (var line in res){

_error += '- ID : ' + line.@mongoId + '<br>'
}

---extra code down here for delivery building---

 

 

View solution in original post

4 Replies

Avatar

Correct answer by
Level 4

Hello! 

In a situation like this, I'd probably just use the vars.recCount variable - it's declared any time an activity passes any number of records out.

 

Just try logInfo(vars.recCount) in a code activity after any query/split etc and you'll see what I mean.

When building the queryDef part, I also like accessing the temporary schema values via the variable

vars.targetSchema so that I don't have to update any values if I reuse code somewhere else. You have to wrap it in curly braces {} as it is a variable to be inserted.

 

 

 

 

<queryDef schema={vars.targetSchema} operation="select">

 

 


I'm pretty sure you can cut  the [target/@mongoId] down to just @mongoId as the [target/] aspect is implied when the value is a field directly on the record for the given schema.

 

 

<node expr="@mongoId"/>

 

 

 

When looking at your for each loop, I believe you can drop the .query part so it's just (var line in res) - I think this is a small difference between the NLWS.xtkQueryDef and xtk.queryDef querying methods. (EDIT: Sorry, actually, I think this would be because we're querying a temporary schema as opposed to directly from the actual table? Regardless, doesn't seem to be needed)

 

 

for each (var line in res){}

 

 


Looking at your code, I'd try something like

 

 

var wkfCount = vars.recCount

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

var res = query.ExecuteQuery();

var _error = '';
for each (var line in res){

_error += '- ID : ' + line.@mongoId + '<br>'
}

---extra code down here for delivery building---

 

 

Avatar

Level 2

Hello @ALangridge 

Sorry for the late response, as this was not the priority subject for me I was not able to deal with it directly but your solution works perfectly thank you very much

Avatar

Community Advisor

Hi @Ragsthenos 

 

Could you let us know the error you're seeing after running the JS activity. It could be some access rights which you need to give while accessing the ffda schemas.

 

For your reference

 

Regards

Akshay