cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JorgeT
Regular Visitor

How to compare a Sharepoint list with a Excel file in Automate

Hi everyone,
 
I new in automate and i've been trying to create a flow that compare a Sharepoint list with a Excel file, i have a Excel file with pending tasks ids and the user assigned to each of them, for example:
TasksExcel.png
And i have a sharepoint list with all users and emails into the work team, like:
UserList.PNG
I need to send the ids of their pending tasks to the email of each user in the excel file, I've been trying to do this flow for two weeks, which at first I thought was simple, but the flow always fails me for some reason, I think that the answer is in the use of filters but I don't know how to use them
Thanks in advance!
1 ACCEPTED SOLUTION

Accepted Solutions
ekarim2020
Resident Rockstar
Resident Rockstar

Here is one possible approach to your problem. The flow produces an email message text for each user in the SharePoint List that has a task in the Excel file:

ekarim2020_6-1631378772268.png

This is the high level flow:

ekarim2020_8-1631379081876.png

(1) Starting with Get Items and List rows present in a table actions:

ekarim2020_9-1631379170981.png

(2) We create an Apply to Each loop, and for each SharePoint item record, we will try to find a matching user record in the Excel data. We can use the Filter Array function find and select these user records. Get get all the Excel rows where the SharePoint column user name (TeamUser) matches the Excel column User:

ekarim2020_13-1631380550138.png

(3) We can use the Select action to select only the Tasks for that user from the Filter Array action. This way we can ignore any other user data we dont need or want to see:

ekarim2020_15-1631380837165.png

Example:

ekarim2020_20-1631382446071.png

(4) Next we check the output of the Select action to find out if any tasks were found:

ekarim2020_16-1631380876763.png

If Yes, we can construct the email text body including a task list in a Compose action:

ekarim2020_17-1631381193414.png

And if no tasks were found we construct a different email text in a Compose action:

ekarim2020_18-1631381212707.png

Here is a summary of the condition:

ekarim2020_19-1631381252663.png

 

Hope this helps.

Ellis

View solution in original post

6 REPLIES 6
ekarim2020
Resident Rockstar
Resident Rockstar

Here is one possible approach to your problem. The flow produces an email message text for each user in the SharePoint List that has a task in the Excel file:

ekarim2020_6-1631378772268.png

This is the high level flow:

ekarim2020_8-1631379081876.png

(1) Starting with Get Items and List rows present in a table actions:

ekarim2020_9-1631379170981.png

(2) We create an Apply to Each loop, and for each SharePoint item record, we will try to find a matching user record in the Excel data. We can use the Filter Array function find and select these user records. Get get all the Excel rows where the SharePoint column user name (TeamUser) matches the Excel column User:

ekarim2020_13-1631380550138.png

(3) We can use the Select action to select only the Tasks for that user from the Filter Array action. This way we can ignore any other user data we dont need or want to see:

ekarim2020_15-1631380837165.png

Example:

ekarim2020_20-1631382446071.png

(4) Next we check the output of the Select action to find out if any tasks were found:

ekarim2020_16-1631380876763.png

If Yes, we can construct the email text body including a task list in a Compose action:

ekarim2020_17-1631381193414.png

And if no tasks were found we construct a different email text in a Compose action:

ekarim2020_18-1631381212707.png

Here is a summary of the condition:

ekarim2020_19-1631381252663.png

 

Hope this helps.

Ellis

View solution in original post

Thanks a lot!
Just one question, I don't understand how the "Select" indicates that the map should take the value "Task", in the dynamic content of the map section i only see the options "Element" and "Body" and I don't know how to tell it to take the value "task" from the filter array

Thanks for the reply

ekarim2020
Resident Rockstar
Resident Rockstar

Please press the small icon to toggle the mode to text mode:

ekarim2020_0-1631548180661.png

Ellis

 

Thank you very much, I had not seen that little button, one last question, if also in excel I had a column called "priority" with values of (1, 2, 3) like:

 

TasksExcelP.png

 

How could I relate the "task" array to the "task priority" array returned by select operations?

So that in the email the tasks are listed by their priority, for example:

 

Dear Anthael

Your tasks are:

Priority 1: 6474

Priority 2: -

Priority 3: 3424

 

I think of making an apply to each to go through the tasks and a counter and relating them by the indexes, but I don't think this is the most optimal way to do it.

 

Anyway, thanks for your response!

ekarim2020
Resident Rockstar
Resident Rockstar

This will require a small change to the flow. We can join the text for Task Priority and Task together in the Select action to produce an email body text like this:

ekarim2020_1-1631556371567.png

The expression to enter is:

concat('Priority ', item()?['Task Priority'],': ',item()?['Task'])

ekarim2020_0-1631556312404.png

ekarim2020_4-1631557168338.png

Ellis


 

Thanks, more than the solution to my problem was what I learned from your answers!

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,374)