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

SOLVED

Passing a Distinct Workflow Variable to Alert

Avatar

Level 2

We have been using the method outlined in this help article (Sending Personalized Alerts to Operators) to pass through workflow variables to alerts with great success. However, we now have a workflow where we want to pass through the Project Name from the temp:query table as the variable in the alert. Because several records will have the same project name, we would like to be able to make this a distinct list. For example, we have one data set where the same project name is on 430 records and we don't want this to show in the alert list 430 times. I am not able to find any resources that walk through how this would be possible. I appreciate any help, insights and/or resource recommendations.

 

Thank you!

1 Accepted Solution

Avatar

Correct answer by
Employee Advisor

You can count the records (count(@id) and group by project field on your querydef

 

<select>
<node expr="count(@id)"/> <node expr="@project" groupBy="true"/> </select>

The plan is to count the records and display the project those belong to

 

RecordsProject
25SDPROJ 4567
12SDPROJ 4567
154SDPROJ 4856
53SDPROJ 6598

 

Here are some supporting docs

  1. https://experienceleaguecommunities.adobe.com/t5/adobe-campaign-standard/distinct-opens-and-clicks/m...
  2. https://experienceleaguecommunities.adobe.com/t5/adobe-campaign-classic-questions/xtk-querydef-creat...
  3. https://blog.floriancourgey.com/2018/08/use-querydef-the-database-toolkit-in-adobe-campaign

 

Here is another example using distinct="true" in this case, try changing the expression in the @ID field to count(@id)

 

    ctx.students = xtk.queryDef.create(  
  <queryDef schema="my:schema" distinct="true" operation="select">  
    <select>  
      <node expr="@id"/>  
      <node expr="@projectName"/>  
    </select>  
    <orderBy>  
      <node expr="@projectName" sortDesc="false"/>  
    </orderBy>  
  </queryDef>).ExecuteQuery();

 

View solution in original post

1 Reply

Avatar

Employee Advisor

To prevent the project name being repeated on your delivery alert like below?? could you provide a screenshot highlighting the issue as well as depicting the wanted outcome.

 

IDProject
1A
2A
3A
4A
5A
6A
7A
8A
9A
10A
11B
12B
13B
14B
15C
16C
17C
18C
19C
20C

Avatar

Level 2

That is exactly it. In your example, we would only want A,B, and C to be listed once.

 

As a generic example:

 

Records from Workflow:

Customer Number

Project Name

1

SDPROJ 4567

2

SDPROJ 4567

3

SDPROJ 4856

4

SDPROJ 6598

5

SDPROJ 6598

6

SDPROJ 6598

 

Generic Alert Copy

Hello,

A new email is ready to send to customers in this workflow. There are an estimated 6 records.

Projects included in this send:

  • SDPROJ 4567
  • SDPROJ 4856
  • SDPROJ 6598

If you have any questions or concerns about these projects, please let us know.

 

 

This would let them know how many records total, but wouldn't list each project name multiple times.

 

Right now when the alert comes through it reads:

 

Hello,

A new email is ready to send to customers in this workflow. There are an estimated 6 records.

Projects included in this send:

  • SDPROJ 4567
  • SDPROJ 4567
  • SDPROJ 4856
  • SDPROJ 6598
  • SDPROJ 6598
  • SDPROJ 6598

If you have any questions or concerns about these projects, please let us know.

Avatar

Correct answer by
Employee Advisor

You can count the records (count(@id) and group by project field on your querydef

 

<select>
<node expr="count(@id)"/> <node expr="@project" groupBy="true"/> </select>

The plan is to count the records and display the project those belong to

 

RecordsProject
25SDPROJ 4567
12SDPROJ 4567
154SDPROJ 4856
53SDPROJ 6598

 

Here are some supporting docs

  1. https://experienceleaguecommunities.adobe.com/t5/adobe-campaign-standard/distinct-opens-and-clicks/m...
  2. https://experienceleaguecommunities.adobe.com/t5/adobe-campaign-classic-questions/xtk-querydef-creat...
  3. https://blog.floriancourgey.com/2018/08/use-querydef-the-database-toolkit-in-adobe-campaign

 

Here is another example using distinct="true" in this case, try changing the expression in the @ID field to count(@id)

 

    ctx.students = xtk.queryDef.create(  
  <queryDef schema="my:schema" distinct="true" operation="select">  
    <select>  
      <node expr="@id"/>  
      <node expr="@projectName"/>  
    </select>  
    <orderBy>  
      <node expr="@projectName" sortDesc="false"/>  
    </orderBy>  
  </queryDef>).ExecuteQuery();

 

Avatar

Level 2

@David__Garcia - This is exactly what I needed! I was able to get both ways to work in testing but love the idea of providing the record count for each distinct project name. Thanks for that additional suggestion!