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

Question

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.

Consolidated File.xlsx

Yoshiro_Fujimori · Accepted Answer

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%20in%20Designer

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")

Yoshiro_Fujimori · Accepted Answer

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 Answer

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 Answer

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