Every 24 Hours Check Status and If One Day Has Passed...Send Email

[Phineas]

I'm looking for confirmation that my flow will work as I perceive it will.

Goal - Filter Query: Status eq string('Meeting Completed-Upload Minutes')
     If 'Status' eq 'Meeting Completed-Upload Minutes' and one day has passed since the Status change occurred, send email. 

[trice602]

Hi @Phineas ,

Good job, I like it.  On the surface I don't see any issues immediately, so I recommend you run a test and work through the expressions as needed.  Looks good on first pass. 😀

[wskinnermctc]

How you have this set up is not very intuitive to me, and I'm not reading the question correctly.

Could you explain more about what you are doing?

(The time check seems backwards and I'm having a hard time thinking how that works, sorry it's Friday. Just need a little more help understanding.)

[Phineas]

The flow is filtering for 'Meeting Completed-Upload Minutes' and the 'Final Meeting Date'.

There are two items on the SP List that have a 'Status' of "Meeting Completed-Upload Minutes".

One of the items has a date entered in the 'Final Meeting Date', the other has not content in 'Final Meeting Date'; However, I received and email for both items, rather than just the one with content in the date.  

[Phineas]

I have a list of meeting request.

The Flow is focusing on two content items, 'Status' and 'Final Meeting Date'.


Goal:
Flow runs every 24 hours and filters for 'Status' or "Meeting Completed-Upload Minutes".
All items returned that also have a 'Final Meeting Date' that was one day past (or yesterday, based on today) then send email.

[wskinnermctc · Accepted solution]

@Phineas what is the SharePoint List column type of "Final Meeting Date"?

• Single Line String/Text
• DateTime with Date Only
• DateTime with Date and including Time

You could put all of this in the filter query and don't need a condition step.

Here is an example, I was using a Date Time type column named "FinalMeetingDate_DT" that had a date and time.

Example SharePoint List - column "FinalMeetingDate_DT"Filter Query only returns matched Status and Final Meeting Date of Yesterday

The Filter Query uses this below:

Status eq 'string('Meeting Completed-Upload Minutes')' and (FinalMeetingDate_DT ge 'startOfDay(addDays(utcNow(),-1))' and FinalMeetingDate_DT lt 'startOfDay(utcNow())')

This will Get Items for only items with a Status eq 'Meeting Completed-Upload Minutes') 

And

(Final Meeting Date is greater than or equal midnight yesterday

And

Final Meeting Date is less than midnight today)

I did this so that it will get anything with the previous date. Because lets say you ran the flow at 3 pm, but a meeting date was yesterday at 9am. Your original flow would not get that 9am item because it is only comparing to yesterday at 3pm. (Today 3:00pm - 24 hours = yesterday 3:00 pm)

Let me know if this works for you,

[Phineas]

I'm getting the following error.

Final Meeting Date is Date only.

The expression "Status eq 'Meeting Completed-Upload Minutes' and (FinalMeetingDate_ DT ge '2023-11-02T00:00:00.0000000Z' and FinalMeetingDate_DT lt '2023-11-03T00:00:00.0000000Z')" is not valid.

[wskinnermctc]

You are using my column name "FinalMeetingDate_DT" which is not the same as yours.

You need to use your column name "FinalMeetingDate"

[Phineas]

Duh, silly me.

Thank you. It works.

As configured will this kick out an email everyday until the 'Status' changes (which would mean the minutes have been uploaded)?

Goal -
If the 'Status' remains 'Meeting Completed-Upload Minutes', and it has been at least one day since the 'Status' became 'Meeting Completed-Upload Minutes', an email should go out; And, the same email should go out everyday until the 'Status' is changed.

[wskinnermctc]

No - my example will only send one email for items that has a FinalMeetingDate of yesterday.

If the FinalMeetingDate is 2, 3, 4.... days in the past, they will not have an email sent.

I do not have a way to check when the Status was changed to Meeting Completed-Uploaded Minutes. How would I know when a Status was changed? Where is a date value to reference when the Status was changed?

I am lost again.

[Phineas]

The Filter Query is only returning 'Status' matching 'Meeting Complete-Upload Minutes'.

If the individual who receives that email access the app and uploads the minutes the 'Status' will change, meaning the next time the flow runs that item will not be pulled in.

As configured, if no action is taken on the item, after 24 hours at during the next run(s) the item will not be pulled in even though the 'Status' has not changed.

I need the emails to keep flying, starting one day after 'Status' is changed to 'Meeting Completed-Upload Minutes'

[wskinnermctc · Accepted solution]

Ok so lets say you have an item that has the Status changed to "Meeting Completed-Upload Minutes" on Monday November 6th at 2:00PM. So at 2:00PM someone has edited that item. 

Example 1: If you have this flow scheduled to run every day at 8:00am and use this in the Filter Query below:

Status eq 'string('Meeting Completed-Upload Minutes')' and Modified lt 'startOfDay(addDays(utcNow(),-1))'

• Monday the Nov-6 the item was created
• Tuesday 8am the flow runs - No - this filter query will not pull in the item
• Wednesday 8am the flow runs - Yes - this filter query does pull the item
• Thursday 8am the flow runs - Yes - this filter query does pull the item

Example 2: If you used this filter query below:

Status eq 'string('Meeting Completed-Upload Minutes')' and Modified lt 'startOfDay(utcNow())'

• Monday the Nov-6 the item was created
• Tuesday 8am the flow runs - Yes - this filter query does pull in the item
• Wednesday 8am the flow runs - Yes - this filter query does pull the item
• Thursday 8am the flow runs - Yes - this filter query does pull the item

I am using the "Modified" datetime value assuming the last time an item was modified was when that status was changed.

[Phineas]

Okay, the first formula is the option I need.

The Power Apps already has send email language that sends an initial email out to the individual the moment the 'Status' becomes 'Meeting Completed-Upload Minutes'.

If a day goes by and the minutes have not been uploaded then the first formula you provided when then begin to kick out an email every day until the 'Status' changes, yes?