Send email after 4 work days

[ppl]

Hello.

I need to send email after 4 work days (from Monday to Friday) after creating file in SharePoint document library.

Which way is better to do that?

[Anonymous · Accepted solution]

@ppl ,

I would suggest using an expression to calculate how many work days it has been between the created date of the SP file and today's date. You can then use a condition to evaluate if that number of days is equal to 4.

The logic behind excluding Saturday and Sunday from the calculation could be along the lines of subtracting two days from the the "days since" calculation if the date created was Wednesday through Friday (assuming you want the email to send on the 4th day). The dayOfWeek() expression is helpful here; it returns an integer between 1-7 (where Monday is 1 and Sunday is 7) representing the day of the week of the date passed into it.

So:

dayOfWeek('11-18-2020')

would return 3, since Wednesday is the third day of the week.

This is what I came up with (though I used a planner plan instead of a Sharepoint directory to get my "createdDate" values. My assumption in this solution is that the email is to be sent on the fourth day including the date the item was created. So if the item was created on Monday, the email will be sent on Thursday.

Technically the two expressions in the two Compose actions could be combined into one...but it would be a longer expression. Here are the two expressions and their explanations:

if(lessorequals(dayofweek(items('Apply_to_each')?['createdDateTime']),2),0,2)

this evaluates what day of the week the item was created on and creates an "adjustment" value to account for the two weekend days if the fourth day would be Saturday or Sunday. if the created day is a Monday or Tuesday, no adjustment is needed, hence zero. if the created day is Wednesday through Friday, we'll have to add two extra days to get past the weekend.

addDays(items('Apply_to_each')?['createdDateTime'], add(3,outputs('Compose_4')), 'MM-dd-yyy')

this adds three days (plus the "adjustment" value) to the file's dateCreated date to get the fourth work day after the file was created.

the Condition evaluates whether the date of the fourth workday after the file was created equals today or not. It is important to make sure that both dates are formatted the same, or it may always evaluate as false.

I hope this helps

thanks,

Kyle

[ppl]

@Anonymous 

Interesting solution. Thank you!

But I have an error in this step

if(lessorequals(dayofweek(items('Apply_to_each')?['Сreated']),2),0,2)

[Anonymous]

@ppl ,

According to the error, there is a problem with the output of "items('Apply_to_each')?['Сreated']" in that it is not returning a DateTime but rather is empty.

if you put a Compose into your loop and pass in the "body" of "Get Files" the output should allow you to see all of the available data.

Share a screenshot of that, if you would.

thanks,

Kyle

[ppl]

@Anonymous 

[Anonymous]

@ppl,

I am not able to re-create your error...and i'm using SP files now instead of Planner tasks in my test Flow.

instead of the whole body in the test "Compose" just pass in the "Created" field and see what it returns.

The error is suggesting that it's returning nothing, but your most recent screenshot clearly shows that there is data in the "Created" field. This leads me to believe that we're using the wrong "address" (the JSON expression that identifies the data we want). However, I'm using the same syntax and it seems to be working for me...

So, before running the flow, hover your cursor over the dynamic content in the Compose field and record what the actual JSON is to access that field.

If you would, share both of those.

We'll get to the bottom of this.

thanks,

Kyle

[ppl]

@Anonymous 

This is what Compose of Created returns

And JSON code of it

[Anonymous]

@ppl,

this is weird...are any of the 22 records returning nothing for that field?

That wouldn't make any sense, because every item would have a Created date...but that's the only thing I can think of...

the "if()" expression looks right. In fact, it matches mine exactly...

And, based on the compose that you inserted, it does look like you've got the right expression to pull the Created Date.

the only thing I can suggest at this point is to pare back the expression and troubleshoot it by building it step by step. add just the "dayofweek()" expression around "Created" in your test Compose and see what it returns.

I wish I had something more specific, but, like I said, I'm not able to recreate your issue.

thanks,
Kyle