Passing a Distinct Workflow Variable to Alert

[jhoyer_sd]

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!

[david--garcia]

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.

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

[jhoyer_sd]

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.

[david--garcia · Accepted solution]

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

Records	Project
25	SDPROJ 4567
12	SDPROJ 4567
154	SDPROJ 4856
53	SDPROJ 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();

[jhoyer_sd]

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