Use ID to compile data from two SharePoint lists

[aah]

I have two SharePoint lists, 'People' and 'Medicals'.

People list:

Medicals list:

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

I need a flow that will:

1. Look up what medicals are due from the 'Medicals' list.

2. Get the ID number of the medical due from the 'Medicals' list.

3. Compare that ID number to the 'People' list.

4. Get the name of the person related to that ID number.

5. Return it in an email with the person's name and medical due date together.

Any help on this would be greatly appreciated!

[SimnaJ]

1. Set up a Recurrence trigger to run the flow daily or weekly, depending on how often you want the reminder emails to be sent.
2. Use the "Get items" action to retrieve all items from the "Medicals" list where the "Due Date" column is less than or equal to today's date.
3. Add a "Filter array" action to filter out any items where the "Completed" column is set to "Yes" (assuming that there is a column in the "Medicals" list that indicates whether the medical has already been completed).
4. Use a "Select" action to select the columns you need from the filtered array (e.g. "ID", "Person ID", and "Due Date").
5. Use the "Apply to each" action to iterate over the selected items.
6. Inside the "Apply to each" loop, use the "Get item" action to retrieve the corresponding item from the "People" list based on the "Person ID" column from the "Medicals" list.
7. Use a "Select" action to select the person's name from the retrieved item.
8. Use a "Compose" action to create the email body using the person's name and the medical due date.
9. Use the "Send an email" action to send the email to the appropriate recipient(s).
10. Use an "Update item" action to update the "Completed" column for each processed item in the "Medicals" list to "Yes".

Note: This is just an outline and may need to be customized based on your specific requirements and data structures.

If you feel that my response has helped you, please mark it as the solution and please consider giving it a thumbs up. This will help other community members find the solution quickly if they have a similar issue in the future.

[aah]

Hi @SimnaJ,

Thanks for the reply, I just have a couple of questions.

So my list doesn't have a completed column, the date in the 'Last Medical Assessment' column gets updated to show it has been completed so should I just ignore the steps that mention a completed column?

I don't quite understand the 'Apply to Each' action, what should be inside it and how do I link the two lists using it?

Thanks for your help!

[SimnaJ]

If you don't have a "Completed" column, then yes, you can ignore the steps that mention it.

Regarding the "Apply to each" action, this action allows you to loop through each item in the array returned by the previous action. In this case, the previous action would be the "Filter array" action that filters the medicals that are due.

Inside the "Apply to each" action, you'll use the "Get item" action to retrieve the corresponding item from the "People" list based on the "Person ID" column from the "Medicals" list. The "Person ID" column is used to link the two lists together. You'll need to configure the "Get item" action to use the "Person ID" value from the current item in the loop (i.e., the item from the "Medicals" list) to retrieve the corresponding item from the "People" list.

Once you have the item from the "People" list, you'll use the "Select" action to select the person's name from the retrieved item. You'll then use a "Compose" action to create the email body using the person's name and the medical due date from the current item in the loop. Finally, you'll use the "Send an email" action to send the email to the appropriate recipient(s).

I hope this clarifies things for you. Let me know if you have any further questions!

[aah]

Hi @SimnaJ,

Thanks again for the reply.

I'm not going to lie I'm very lost... the image below is everything I have so far but I don't think it's correct.

I'm new to power automate and I don't really understand what order the 'filter array' / 'apply to each' steps should go and what should go in them.

Thanks again for all your help on this.