Hi all,
I am new to the automation designer.
What I try to achieve is to export excel and email it to different groups of people( the excel file can be the same) based on the region parameter in my query.
Is there a dynamic way to do this rather than setting up different sets of export dataset?
I also tried setting up the variant but no luck yet.
Thank you so much for your help!
Regards,
Irene
You can use a query iterator to set up your variables for email and the excel file like this:
Does that help?
Hi John,
Thank you for your fast reply!
I have one more question, then how can I send the email to the correct team? Since now the variable is %Query Iterator1.%, how can I assign the region to the corresponding user/email list?
Irene
where do the region and email user/list come from? Can you select that from the query?
There wasn’t. But I just created one table for the list.
Ok, so now you can use the variable for Query iterator in your Email action.
Hi John,
Thank you for your help.
I almost finish the setting.
However, do you have a detailed example of email sending action?
I tried to set it up as a dynamic email recipient list to avoid the hassle of future change, but not able to get it done.
The table I have is like following:
Region | email list
Region1 | a@gmail.com , b@gmail.com
Region2 | c@gmail.com , d@gmail.com
I want to use Region1 & Region 2 instead of exact email as recipient.
Try this. Add a Query Iterator action and an Email action nested beneath it as seen below. The query iterator needs to retrieve your list of emails. In my example the region is irrelevant, but in your case it sounds like you will be maintaining a table mapping a region to recipients so I include it. For the Automation Designer the actual region value is unused.
Query Iterator action props:
Email action props:
Adjust the email action as needed to specify the file you'd like to attach and set your SMTP server properties. If you right-click on the To: field in the email action there is a menu item for Variables that let's you choose the correct column from your Query Iterator action. All controls in Automation Designer that support variables will have this Variables menu item. I have tested this with our SMTP server and a few real email addresses.
Hi mstaszew,
Thank you for your detailed explanation.
I’m able to send the email with variables now.
Just one more thing, I want to filter on the region( only send report to the team members of that region if there’s result from their region).
But so far, even I set the parameter on each region, e-mail still being sent to the region which was not in the result.
Also, there’s a little glitch, not sure why but email always being sent twice to the same region.
Thank you so much for your help!
I was only seeing single emails sent in my example. I think both of your If..Then..Else actions may be firing. I cannot tell the logic of your app there by the screenshot alone.
You want to loop over a list of regions. Using the region name/code you want to generate a report for that region? How are you incorporating the region into the Export Dataset action - please share screenshot of its configuration.
What are the "Set Variable" actions there doing? It looks like you want to test the region variable value, not set something new. In this example the app shows a message with text "A" when the region has a value of "Region 1" and a message with text "B" otherwise.
Yes indeed, I only want to email the report to the team involved ( it’s the same report, but only the team mentioned in the report needs to receive it).
I used join in my query, not sure if it’s the correct way to do it.
I also changed the set variables to test variables, but EU team still received the report when I set the filter on Americas.
(The email list table and customers table are in different schema, not sure if this matters)
That join is fine. There is a disconnect though between your excel export query and the rest of the app. You're using a literal in your SQL and then later it looks like you're trying to set a variable based on that literal. I also don't believe you can use a variable from your Query Iterator action which would simply things, but % has meaning in SQL so the traditional variable support format doesn't fit. How many regions do you have?
I have six regions in total. Only set 2 for testing for now.
One option is to have files with your report SQL, one file per region. Use the region in the filename. Then you can use the region from your query iterator in the Export Dataset filename and so on.
The quick and dirty solution is also an option. Make an app that has two actions. One to export, another to email. You can then copy/paste that app to have 6 copies. Adjust the query and distribution list in each and name each like "Run EU Report" "Run APJ Report" etc.
Thank you for your suggestion. I tried the first setting, indeed the report split for each region, but didn’t filter out the region which is not included. I’ll use the second option for now. Still hoping there’s a neat way to set it up.
I do not understand what you mean here. You can exclude regions by altering your Query Iterator SQL to only include regions of interest.
Do you mean I can exclude region in the way you set it up in the first screen shot instead of the ‘ the quick and dirty solution’? When I used the first option, the report is split to different regions, but I’m not able to control which team to receive it ( for example, when EU team is not in the report, there’s still report generated for EU). Maybe I missed anything?
Either way you should be able to exclude undesired regions. If you use the Query Iterator approach just have the SQL within the Query Iterator only include the regions you care about. If you're looking at the quick and dirty approach you'd specify an app for each region and then just run the apps for the regions you wish to generate reports for.
I’m not sure if I misunderstand you, but it sounds like to exclude the region in literal.
But I will only know which region to exclude
in the email distribution list after the dataset being exported.
