Composing a reminder email using two SharePoint lists

(0) Share

Report

Posted on by aah

126

I'm looking to use Power Automate to send out a reminder when medicals are due.

I have two lists in SharePoint - 'Medicals' & 'People'

I need a flow that will:

1. Using the 'Medicals' list, look up the 'Next Medical Assessment Due' column and see what medicals are due.

2. Get the 'Employee ID' of the employees whose medicals are due from the 'Medicals' list.

3. Compare that 'Employee ID' to the 'Employee ID' column in the 'People' list.

4. Using the 'Employee ID' get the 'Employee Name' of the person from the 'People' list.

5. Return it in an email containing the 'Employee Name' and the 'Next Medical Assessment Due' date together.

I'm quite new to Power Automate so the more explanation the better, thanks!

Categories:

General topics

I have the same question (0)

All responses (4)

Answers (0)

Manish Solanki 15,099 Moderator on at

Like (1)

Report
Copy link

Link copied!

Hi @aah

Here is the flow:
1. I have designed schedule flow which will trigger at 10 am (IST). You may modify the schedule or design a manual trigger flow as per your need:

2. Next, using 'Get items' we will fetch the records based on the next due date. Here, we are fetching those records with future appointment date:

3. Now, we will iterate Medical records and find the employee name form the 'People' list using 'Get items' action:
4. Then, iterate on the people records and append the required data in the array variable:

5. Finally, create a html table with the array variable and send the output of 'Create html table' action in an email:

I hope these steps will help you in designing the required flow 🙂

If this helps, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

Was this reply helpful? Yes No
aah 126 on at

Like (0)

Report
Copy link

Link copied!

Hi @ManishSolanki,

Thank you for your reply, I only have one issue.

My 'Next Medical Assessment Due' column is a calculated column so it won't let me use it in the Filter Query box. Is there a way to work around this?

Was this reply helpful? Yes No
Manish Solanki 15,099 Moderator on at

Like (1)

Report
Copy link

Link copied!
Hi @aah

In that case, we will use filter array action to filter out the due records. Here is the updated flow:

Expression used is:
formatDateTime(items('Apply_to_each')?['Next_x0020_Medical_x0020_Assessm'],'yyyy-MM-dd')

If this helps, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

Was this reply helpful? Yes No
aah 126 on at

Like (0)

Report
Copy link

Link copied!

Hi @ManishSolanki,

Thanks for the reply!

I'm not sure what I've done wrong because everything looks the same but when I run the flow the email I receive is empty. Any idea what might be causing this?

Was this reply helpful? Yes No