1

I am using a SharePoint list to track compliance with annual training requirements for a few hundred users. I would like to set up an automation that notifies individual users via Outlook email when they are 60 days from being due to complete any of the individual training requirements again.

Example:

The conditions: Today's date is 9/19/2024. User D. Parton comes due to complete CPR Training in 60 days.

The goal: D. Parton is notified via Outlook email that she is due to complete CPR Training in 60 days.

I would like for this to happen for each user on the list for each training requirement as soon as the conditions are met.

Is there a way to do this? Thanks in advance!

Improve this question

1 Answer 1

Reset to default
3

Short answer, Yes there is a way to do this.

Without knowing how your SharePoint list has been designed, I have made the assumption that you have a row per person and multiple date columns; one for each training course. If that's not the case, you'll need to change the flow accordingly. I've only provided steps for one course but you should be able to duplicate that logic for additional courses.

I'm sure there are plenty of other ways to do this also.

Example list:

enter image description here

Flow Overview:

enter image description here

1. Trigger

Recurrence - Set to run once per day

2. Initialize Variable - varDateToday

Convert utcnow() to local timezone (or use UTC if that is preferrable. Timezone Reference)

startOfDay(convertFromUtc(utcNow(),'*localTimeZone*'))

enter image description here

3. Get SharePoint List Items

Filter list to items where the training course due date equals today.

Course1DueDate eq 'variables('varDateToday')' enter image description here

4. Send an email to each person from the previous step

Improve this answer
3
  • Thank you for this! I've got a good start here. I'm having trouble with getting the "Get items' step to return anything. I have the SIte Address and List Name pointing to the correct location, but I'm not sure that the 'Filter Query' input is correct. Here's what I have CES Intermediate Phase 1 eq 'varDateToday' (The varDateToday is the dynamic content as you have shown in your example with the purple box. The other thing is that this needs to fire 60 days before the expiration date entered in the cell. Is there a way to adjust the "Initialize variable" step to make that happen? Thanks!
    – Michael
    Commented Sep 20 at 17:40
  • You will need to ensure that you are using the system name of the 'CES Intermediate Phase 1' column, not the display name. You might have to go to List Settings, click on the column and look at the address bar. The very end will be like: Field=FieldName or possibly Field=Field_x0020_Name. Once you have that, you could use addDays(Field_x0020_Name,-60) in your filter query
    – user79408
    Commented Sep 22 at 21:53
  • Confirmed that the system name is reflected in the variable. Still not working. For the -60 part; that goes in the varDateTorday variable?
    – Michael
    Commented Sep 26 at 21:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.