Sending a follow-up email after set time

[Anonymous]

Hello, I am a beginner.

I have built a working manual trigger flow that does three things:

1. Sends a welcome email to anyone who is not a manager (Yes-branch) and in the No-branch a manager-specific welcome email
2. Their hire date must be utcnow() or greater (the Excel report only shows w/c hiring dates and the previous week)
3. Their respective managers also receive an email in both branches
4. This is reading from an Excel online spreadsheet table that I must download from Workday directly
5. I get weekly reports of new starters and I am planning to just paste in the required details for each week, overwriting the last week.

My questions:

1. Is there any way to make it so that after 3 weeks, it sends a follow-up email?
2. If it's possible, could I remove their details in the Excel table I've made and it'd still know who to email in those intervals?
3. If 2 is not possible, is there a way to ensure it only sends the follow-up email to the same cohort who received it at the right time, even if there are other dates within the table?
4. Currently, my dates must be formatted text in Excel with yyyy-MM-dd, is there any way to make so that it will allow me to follow the Workday format of dd/MM/yyyy?
5. My current expression is - formatDateTime(items('Apply_to_each')?['Hire Date'],'yyyy-MM-dd') is greater than or equal to formatDateTime(utcNow(),'yyyy-MM-dd') - I tried to just change it within there but it threw back an error I didn't understand.
6. Is there a better way for this entire process as a whole?

I want to automate this process as much as possible as it's a task with up to 5 separate emails going out as it cascades welcome, reminder, final reminder every week - the 5th-week email also requires me to run a separate report to decide who receives that email, so I want to cut out as much manual stuff as possible.

I appreciate any help, I do realise it's a pretty long request. I have no issues with explanations being written as if speaking to a 5-year-old, I prefer that rather than back-and-forths because there are assumptions of my basic knowledge of flow.

[v-duann-msft]

Hi @Anonymous 

Thank you very much for posting.

Based on your description, here are the answers for your reference:

Here is my excel :

Is there any way to make it so that after 3 weeks, it sends a follow-up email?

Yes, we can combine ‘addDays’ and ‘formatDateTime’ fx with condition action to configure as below :

This is my flow to filter hire date for 3weeks:

Related fx:

• formatDateTime(addDays('1908-12-31',int(items('Apply_to_each_2')?['HireDate'])),'yyyy-MM-dd')
• formatDateTime(addDays(utcNow('yyyy-MM-dd'), -21),'yyyy-MM-dd')

If it's possible, could I remove their details in the Excel table I've made and it'd still know who to email in those intervals?

Unfortunately, that’s impossible right now. If we remove data from excel, flow won’t be able to read the past data any more.

If 2 is not possible, is there a way to ensure it only sends the follow-up email to the same cohort who received it at the right time, even if there are other dates within the table?

Yes, that’s should be possible as we always use dynamic content to fill in information. It will detect data according to column property.

Currently, my dates must be formatted text in Excel with yyyy-MM-dd, is there any way to make so that it will allow me to follow the Workday format of dd/MM/yyyy?

Yes, we can change date format to dd/MM/yyyy in excel as below. Just need to select the whole date column except title and click data, scroll down and find it.

My current expression is - formatDateTime(items('Apply_to_each')?['Hire Date'],'yyyy-MM-dd') is greater than or equal to formatDateTime(utcNow(),'yyyy-MM-dd') - I tried to just change it within there but it threw back an error I didn't understand.

Please kindly refer my flow in query 1.

Is there a better way for this entire process as a whole?

Actually, it’s tough to configure all process as a whole. As you known, the more complex you set up a flow, the easier it causes issue.

I believe it will be better we can separate the whole process into two or three flows as there are several recurring tasks(e.g. 3 weeks reminders). We’d better using scheduled flow.

The whole process would be when new hire comes, we add record in excel.

The first welcome email, we can use automated flow to detect new item created in excel and use whether he is manager or not as condition to send different types of welcome emails.

The 3weeks follow up email, we can use scheduled flow to run every day as each new hire join in in different time period. If hire data equals 21days, we send follow up emails.

The 5weeks follow up we can depend on excel ‘EmailSent’ column. Once we send follow up email, we can use ‘update a row in excel’ to switch email delivery status. After that, manually export that excel and finish.

If yo still have any further confusion, please don't hesitate to let me know.

Best regards,

Anna

[Anonymous]

Hi Anna,

thanks so much for your detailed answer, it has made me think:

I believe it will be better we can separate the whole process into two or three flows as there are several recurring tasks(e.g. 3 weeks reminders). We’d better using scheduled flow.

I think separating into two flows would be really good. So the first Flow will no longer be manually triggered, it'll be a Scheduled Flow that checks daily if a hire date = today, then send an email, including my conditions of if they are a manager send x email, if they aren't send x email? - My issue is, I am not well versed on the function to schedule it to check the file every day. I'd really appreciate your help!

Then the second flow will check if it's been 21 days since their hire date, send the follow-up email.

Yes, we can change date format to dd/MM/yyyy in excel as below. Just need to select the whole date column except title and click data, scroll down and find it.

I did this before but Flow threw back an error to me that it didn't recognise it because my date was being translated to things like 43453 in Flow. I'll try again.

"Unable to process template language expressions for action 'Condition' at line '1' and column '16020': 'In function 'formatDateTime', the value provided for date time string '44222' was not valid. The datetime string must match ISO 8601 format.'." - this is the result, so my date has to be in text format. So I am using a formula that will transform my original report hire date format, e.g. =TEXT(A2, "yyyy-MM-dd") this works fine for me, thankfully!

'1908-12-31'

Why do you use this date? What does it mean?

Thanks again for your help. I'm going to try and create such a flow while I wait for your reply, just to at least try myself.

[Anonymous]

@v-duann-msft  I have managed to make the scheduled flow! I have tested it and it accurately sends only today's hire dates but I also want to see it working in action so I have it scheduled to 1pm with data in the spreadsheet for today, tomorrow and friday.

For the second Flow I get this error message:

Unable to process template language expressions for action 'Condition' at line '1' and column '16020': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

when using the expression to add more days. I don't understand it. I tried it with the column with regular date formatting and the one that was reformatted with a formula and neither worked.

[v-duann-msft]

Hi @Anonymous 

Thank you for your reply and apologize for the late response as we have plenty of requests every day. Glad to hear scheduled flow is almost ready for you. 😛

According to your latest reply, you currently got problem on filter hire date for 21days. It should be a date format conversion issue.

Since you mentioned you changed the date format in excel using formula before, what’s your date output of ‘list rows present in a table’ now?

 

For me, the date output is still 44201 in text format. So I use fx to convert it.

• formatDateTime(addDays('1908-12-31',int(items('Apply_to_each_2')?['HireDate'])),'yyyy-MM-dd')

Depend on output from excel, we need to change expression. I can help you to modify formula if you can share me your flow details and output of date.

By the way, the ‘1908-12-31’ is caused by my mistake when I add new record in excel. Normally it should be 1899/12/31 as the output number ‘44201’ is being delivered from excel online is count of days since 1899.12.31. I input 2012 in excel by mistake so there is 9 years difference while testing so I plus 9 to 1899 manually and get 1908. That’s why you saw this wired date. ಥ_ಥ

You can refer below articles for date conversion from excel :

https://powerusers.microsoft.com/t5/Building-Flows/Excel-Online-Date/td-p/134200

https://powerusers.microsoft.com/t5/Building-Flows/Excel-dates-turned-into-integers/td-p/130364

Hope to hear from you soon.

Thanks

Anna

[Anonymous]

Not at all Anna, it gave me time to learn. Yes, at the moment the only part I'm struggling with is the scheduled 3-week email. In my scheduled flow test, I'm not actually using the formula but I've just tried it now and the output is:

"Hire Date":"2021-01-27"

Hire Date is the text output from the formula that Flow is referencing.

So is my output like that causing the issue?

[v-duann-msft · Accepted solution]

Hi @Anonymous 

Thank you for your reply.

Yes, the format you are using in excel is the root cause of the failure.

If the output from excel looks like : 44201

We should convert it via formula:

• formatDateTime(addDays('1899-12-31',int(items('Apply_to_each_2')?['HireDate'])),'yyyy-MM-dd')
• formatDateTime(addDays(utcNow('yyyy-MM-dd'), -21),'yyyy-MM-dd')

If you get regular date format from excel as : 2021-01-27

We just need to use simpler expression :

• formatDateTime(items('Apply_to_each_2')?['HireDate'],'yyyy-MM-dd')
• formatDateTime(addDays(utcNow('yyyy-MM-dd'), -21),'yyyy-MM-dd')

Hope it may help you.

Thanks

Anna

[Anonymous]

Thank you so much Anna for your help, it's invaluable. I will accept the solution.

I had one last question - edit: this is why some basic maths skills are so important. 21 days will always fall on the same day of the week. 😂