Sending Individual E-,mails to different teams according to their data Everyday.

[Kaish]

Hi All, 

I have a very challenging automation to complete for my organization.  I will attach the supporting documents for your guidance.

The task - I have to automate a process where the data comes everyday from the team, the process will automate the task in which the data results will be grouped with their names and will be sent to them personally with only their data. Also the grouping is divided into two parts for some and the two parts will have separate visibility. In this task i have to automate the process where everyday when the data is uploaded the E-mail body will show the previous day's date i.e - If on 5th April the data is uploaded, the E-mail body will have the message with 4th April which will be highlighted, and so on according to T-1 dates, for File received on Monday will have Friday date.

Here in the Examples the mail will be sent to different teams with their grouped data. Example, Sam - sam@xyz.com, John - john@xyz.com, Ravi - ravi@xyz.com, Ken - ken@xyz.com , the data will be sent to these teams grouped by their name. Kindly reply if anyone can guide me through the process. 

[Yoshiro_Fujimori · Accepted solution]

Hi @Kaish ,

This is a typical use case of Reporting tools.

You may want to check the interactive lesson of "Reporting in Designer".

https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Reporting%...

Here is a sample workflow.

I do not test the output of Email tool as my Designer is not connected with SMTP.

Please configure Email tool based on your environment.

I hope it helps.

Workflow

Output

Formula Tool: Calculation of as_of_date

DateTimeFormat(
IF DateTimeFormat(DateTimeToday(),"%a") = "Mon"
THEN DateTimeAdd(DateTimeToday(),-3,"day")
ELSE DateTimeAdd(DateTimeToday(),-1,"day")
ENDIF
,"%d %B")

[Kaish]

Hi @Yoshiro_Fujimori , the sample was perfect for my data and it worked perfectly. Thanks a lot for the solution, I was able to solve my problem for major extent. I had one more thing to add in this thread and need few more solutions. Let me explain few major points to be added.

1) I want to add a dynamic subject to the mail in which the Subject head of the mail changes everyday with the Sum of Current amount for that day for both (IM+GM) that means if the data for above image has current value for IM  and GM for john is 10000000+10000000 = 20000000 which is 20 million, So the subject head would be "Hi team, the following is the Sum for date 17 May which amounts to 20 Million"

2) I have to add the Previous amount also in the mail body, I just need to know the formula for day before yesterday date, could you help me with this requirement.

Kindly help me to solve this problem and then I will be able to finish my project with your help and support.

[Yoshiro_Fujimori · Accepted solution]

Hi @Kaish , glad to know it worked.

For additional issue (1), you may;

calculate the sum of current amount (Summarize Tool),

join it back to the main flow (Join Tool),

convert it to String type (Formula Tool) and 

add the amount to the message text (Report Text Tool).

Workflow

For additional issue (2), I cannot find the data for "the day before yesterday" in your input data.

Please describe which data you want to add to the report.

[Kaish · Accepted solution]

Hi @Yoshiro_Fujimori , I will work on the solution that you processed for my 1st query, let me explain you the 2nd point as you wanted to know the details, as you can see I mentioned in my problem statement the point which stated the date as in "automate the process where everyday when the data is uploaded the E-mail body will show the previous day's date i.e - If on 5th April the data is uploaded, the E-mail body will have the message with 4th April which will be highlighted, and so on according to T-1 dates, for File received on Monday will have Friday date." In the same way as you can see the solution data provided by you, the current amount is for 17th May, and the previous amount is for 16th May, so here I want to display 16th May also. 

Example : Hi Team,

                 The following is the sum for date 17th May

                  and the previous amount for date 16th May 

Solution as per my understanding is, if I get the formula for 16th May date i.e day before yesterday date, I can add  a new data in formula just like 'as_of_date' and then I can add the data name in Report text, that can solve my problem. Kindly let me know if any doubts.

[Yoshiro_Fujimori · Accepted solution]

Hi @Kaish ,

Thank you for the clarification.

I modified to get the day before yesterday.

I hope it works.

Formula Tool

yesterday = 

  IF DateTimeFormat(DateTimeToday(),"%a") = "Mon"
  THEN DateTimeAdd(DateTimeToday(),-3,"day")
  ELSE DateTimeAdd(DateTimeToday(),-1,"day")
ENDIF

as_of_date = 

  DateTimeFormat([yesterday],"%d %B")

day_before_yesterday =

  DateTimeFormat(
    IF DateTimeFormat([yesterday],"%a") = "Mon"
    THEN DateTimeAdd([yesterday],-3,"day")
    ELSE DateTimeAdd([yesterday],-1,"day")
    ENDIF
  ,"%d %B")

Report Text Tool

Hi Team,
The following is the Sum for date [as_of_date:A] which amounts to [amounts to:A]
and the previous amount for date [day_before_yesterday:A].

Output Image

[Kaish]

Hi @Yoshiro_Fujimori , your workflow worked great for my data. Thanks for your support. Was great gaining knowledge from you. Thanks 

[Kaish]

Hi @Yoshiro_Fujimori I had one more issue with my workflow, I have one more requirement where I don't want to send the data to the respective E-mail Users according to their grouping, instead I want to send mail to a single person who will Validate the data and then send the data Manually to the teams. The person will get different mails for different groups, Ex. Sam's grouped data will come in a separate mail, John's data will come in a different mail, and so on. Every mail will have different subject heading according to their groupings, 2nd Query - In the subject head we have to add one more data as you can refer your outcome for "John" as you can see in the data output of John we have IM and GM value, so basically there are count of IM- 1 and count of GM- 2, so there are 3 data in total, So I want the head to display the count, for Example - "IM $ GM Validation for John - 3 Data"  can this be done ? If any queries do let me know, I can explain. Thanks   

[Kaish]

@DataNath Could you help me in finding a solution. I have done my solution, I am stuck in a condition where my data has 2 groups for a single user, I want my header to be 

"IM $ GM Validation for John - 3 Data" I am getting results for single output but if the table has 2 sources for a single user then I am facing difficulty, I am using If Else statement, could you guide me. Thanks.