Expand my Community achievements bar.

SOLVED

Filtering a query based on an instance variable

Avatar

Level 4

In my workflow I am populating an instance variable with the last run date:

instance.vars.last_run_date = new Date()

Each time this workflow runs it populates the variable. 

My question is - how do I use this variable in a query node target selection - I want to only pull back records where the expression modification date > instance.vars.last_run_date is true.

I cannot find examples of how to do this. Any pointers would be super helpful.

Cheers,

Matt

1 Accepted Solution

Avatar

Correct answer by
Level 4

Thanks for input.  We did try the incremental query but in Adobe Campaign 7 it is not possible to get new and modified records via an incremental query.

In the end I created a workflow with a javascript node that read a last_run_date from a file and captured the current date-time - both values were written to instance vars to be used in later steps in the workflow:

// Get last_run_date from file

var f = new File("/path/last_run_date.txt");

f.open("r");

var line = f.readln();

f.close();

instance.vars.last_run_date = line;

// Store time process started

instance.vars.current_run_date = formatDate(new Date(), "%4Y-%2M-%2D %2H:%2N:%2S");

logInfo("Read last_run_date from file = " + instance.vars.last_run_date)

In the query node I could add a targetting rule to filter the table for records with a modification date on or after $(instance/vars/@last_run_date).

Finally after the query had executed I wrote the last_run_date back to disk in another javascript node:

var f = new File("/path/last_run_date.txt");

f.open("w");

f.write(instance.vars.current_run_date);

f.close();

logInfo("Saved current run date = " + instance.vars.current_run_date);

Seems a bit clunky but works!  I imagine there is a better way to avoid the use of an external file to store the last run date between runs of the workflow.

View solution in original post

4 Replies

Avatar

Level 10

Hi Matt,

I'm not sure this can easily be done that way. Have you considered using the Incremental query? Targeting activities

This type of query can be used to find only new entries since its last execution. Which seems to me to match what you are trying to achieve.

Let me know if you have more specific requirements.

Florent.

Avatar

Community Advisor

In a webApp, I usually do it like the following (https://db.tt/HDj12iCF1B )

Never tried it in a workflow:

$([/instance/vars/last_run_date])

Also try formatting the date i.e.

instance.vars.last_run_date = formatDate(new Date(), '%2D/%2M/%4Y %2H:%2N:%2S');

logInfo(instance.vars.last_run_date);

Avatar

Correct answer by
Level 4

Thanks for input.  We did try the incremental query but in Adobe Campaign 7 it is not possible to get new and modified records via an incremental query.

In the end I created a workflow with a javascript node that read a last_run_date from a file and captured the current date-time - both values were written to instance vars to be used in later steps in the workflow:

// Get last_run_date from file

var f = new File("/path/last_run_date.txt");

f.open("r");

var line = f.readln();

f.close();

instance.vars.last_run_date = line;

// Store time process started

instance.vars.current_run_date = formatDate(new Date(), "%4Y-%2M-%2D %2H:%2N:%2S");

logInfo("Read last_run_date from file = " + instance.vars.last_run_date)

In the query node I could add a targetting rule to filter the table for records with a modification date on or after $(instance/vars/@last_run_date).

Finally after the query had executed I wrote the last_run_date back to disk in another javascript node:

var f = new File("/path/last_run_date.txt");

f.open("w");

f.write(instance.vars.current_run_date);

f.close();

logInfo("Saved current run date = " + instance.vars.current_run_date);

Seems a bit clunky but works!  I imagine there is a better way to avoid the use of an external file to store the last run date between runs of the workflow.

Avatar

Level 3

Hi Matt,

Have you consider options for this? I think it would a much cleaner way.

Regards,

Ankur A.