0 results on my querydef on a temporary scheme | Community
Skip to main content
R
Ragsthenos
Level 2
May 16, 2024
Solved

0 results on my querydef on a temporary scheme

  • May 16, 2024
  • 2 replies
  • 1524 views

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:

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

 

 

 

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 ALangridge

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

 

 

2 replies

ALangridge
ALangridgeAccepted solution
Level 4
May 17, 2024

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

 

 

    R
    RagsthenosAuthor
    Level 2
    May 22, 2024

    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 🙂

    ALangridge
    ALangridge
    Level 4
    May 22, 2024

    No stress mate, glad this worked! 

    AkshayAnand
    Community Advisor
    AkshayAnandCommunity Advisor
    Community Advisor
    May 22, 2024

    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

    Terms & ConditionsAccessibility statement

    Loading footer...