cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Flow to create update parent list from multiple child lists

Hey all, 

 

Newbie to the Flow world but hoping for some help along the way here.   So I am setting up a Project Management Office (PMO) dashboard using SharePoint Online.  

 

What I would like to happen is for a "Parent" Project Dashboard List to be auto-populated based on the latest status reports from a number of "Child" projects. Each Project has its own site with its own list of status updates.   Status updates are produced by the various PM's using PowerApps that feed into the List. 

 

The parent project dashboard list should only show the latest status report from the respective child list. 

 

hope this makes sense? 

 

  

2 ACCEPTED SOLUTIONS

Accepted Solutions

Ah so the Project Dashboard list doesn't have anything in it yet? Then that's the problem. You need to have list items in your dashboard for each project, i.e ProjectA, ProjectB, ProjectC in the Title column and other columns as appropriate. If the dashboard list is empty the flow won't work as there's nothing to update in the list because the list items haven't been created. So whenever a new project is created you need to add it to the list on the Dashboard. Sorry, I should have mentioned that rather than assuming you'd added the project names to the dashboard.

If you don't want to do it that way then you'll need to use a SharePoint Create item action initially but that then adds a bit more complexity when you need to update it.

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

View solution in original post

Yes this can be adapted to get the most recent status report on each project (even within the last 24 hours - don't forget I set the recurrence to run every day at 9am), but I'm tied up on a support call for my company at the moment so it might be a while before I post a possible solution.

Rob
Los Gallardos

View solution in original post

15 REPLIES 15
AKRogers
Helper V
Helper V

Hi @Anonymous ,

 

As I see it, there are two parts to your request:

  1. Feed data from multiple SharePoint sites into one "parent" list in a separate site
  2. Retrieve only the most recent data from each site and keep the parent list updated appropriately

 

Handling the first part is easy as long as the account running the SharePoint actions in your flow has access to all the sites involved in the process.  The second part isn't difficult, but it does require some advanced options and a scheduled flow.

 

To get the most recent item from each "child" list, do a SharePoint Get Items action against each list.  You'll need to add a value to the "Order by" field in the action.  You'll order the results by "Created desc,"  which will give you the results from the SharePoint list ordered by most recently created.  Then add a "1" to the "Top Count" field to restrict your results to just the very last item that was created.  You can repeat this for each list, either handling it in one large flow or in multiple smaller flows depending on how many sites you're dealing with.  Here's an example action:

 

GetitemsOrderByTopCount-123019.png

 

To keep the parent list updated, simply delete the items from the list before creating new items from the data gathered in your Get Items actions.  I suggest using a scheduled flow on a daily recurrence--or whatever recurrence suits your needs-- to set the expectation that the data will be updated at a certain time every day.

 

Let me know how it goes!

Ashley

 

If this response provided an answer to your question please mark it as a solution so that others can find it.

If you found this helpful, consider giving it a thumbs up!

Anonymous
Not applicable

Hi -

 

Thanks for the reply. 

 

You are spot on with the request but I couldnt see how to do the two parts in your reply: 

 

  1. Feed data from multiple SharePoint sites into one "parent" list in a separate site
  2. Retrieve only the most recent data from each site and keep the parent list updated appropriately

 

 

So basically: 

Parent Site needs to have the following Columns in their status dashboard: 

 

Project Name, Project Manager, Business Area, Status, Progress, Comments etc. 

 

Site 1 would have then have a list which has a number of columns but would include:  There would be a separate item entry for each status report.  

Project Name, Status, Progress, Comments

 

Site 2 would have then have a list which has a number of columns but would include. There would be a separate item entry for each status report. : 

Project Name, Status, Progress, Comments

 

Site 3, 4, 5 and so on would have the same lists - each one controlled by different project managers in different business areas.  

 

Main dashboard (Parent Page) would have the consolidated latest version. 

 

If you are able to spell it out for me (think newbie!) then I would appreciate it. 

 

Thanks

Steve

RobElliott
Super User
Super User

Hi @Anonymous this can be done via parallel branching. If I've understood correctly then you are wanting something like the following:
0-WantedResult.png

Ok in my simple example they are lists within the same site, but they could just as easily be different sites as you've said about your projects.

You could either have a trigger to manually start the flow or you could do it as I have with a recurrence schedule which I've set to run at 9am every day.

1-Trigger.png

We then add a SharePoint Get items action and select the ProjectA list. Once we've done everything we need to do for ProjectA we'll then do the same for the other projects.
2-GetItemsA.png

 We then add another Get items but this time we select the Dashboard list, but we are only interested in ProjectA so we add a filter query in the advanced actions. That will be Title eq '{Title}' where{Title} is selected from the Get items ProjectA section of the dynamic content box. Make sure you have it inside '  ' apostrophes. You will then see that it automatically wraps the action in an apply to each. That's expected and of course there is only 1 item.

3-GetItems-Dashboard.png

 

3a-filterQuery.png

 The final action is to a SharePoint Update item action to update the dashboard. The ID and Title are selected from the Get items Dashboard ProjectA section of the dynamic content. The RAG field is selected from the Get items ProjectA section (it can be easy to select from the wrong section). Again you'll see that the action gets wrapped automatically in an Apply to each control.

4-UpdateDashboard.png

 That's ProjectA done. Now go back up to the recurrence trigger and underneath it click the + and select Add a parallel branch.

5-ParallelBranch.png

Then you'll need to do the same actions for ProjectB and ProjectC as you did for ProjectA. You can copy the actions to your clipboard to make it quicker. Click the 3 ... at the top right of the actions an select copy to clipboard. Then when you add an action select My Clipboard.

When one of the PMs updates their list, the next time the flow runs it will update the dashboard:

6-UpdatedList.png

As I said earlier, my example is very simple but I hope it gives you an idea of how you can do this. Come back with any questions.

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

Anonymous
Not applicable

Hi Rob, 

 

Many thanks for the description - I tried to follow it just for one of the lists for now (before I do the parallel branching) but although the flow runs successfully, it is not updating the dashboard list when I run the test? 

 

I have created 3 lists on one site (List_1, List_2, List_3) - all identical lists with the same two columns (for now) 

 

I created the dashboard list also with the identical columns - all on the same for ease for now - I can easily recreate and add in additional columns later and set up the parallel branching as well. 

 

Did the Update Items within the Get Items 2 so that it allowed me to select the ID, Title, RAG etc. (mine shows up as RAG Value) Tried a number of combinations which eventually allowed the flow to run successfully. 

 

The only thing is that the Project Dashboard is not updating.  This sounds very thick but this body will not let me attach photos. Recurrence and Get Items 1Recurrence and Get Items 1

 

Screenshot above shows the initial get items etc. 

 

 

Image_2.jpg

 

Screenshot 2 above shows the Get Items 2 and then the Update Item as a follow on action. 

 

Any thoughts why the project dashboard isnt updating as per your instructions? 

 

Thanks

Steve

 
 

 

 

@Anonymous the most obvious thing I can think of at the moment is that the RAG Value in the update item action is being selected from the wrong section of the dynamic content. Just check that you're selecting it from the Get items section not the Get items 2 section, then we'll have another look.

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

Anonymous
Not applicable

Hi Rob, 

 

Still nothing. Recreated from scratch and nothing appears in the Project Dashboard List.  Just to confirm the following therefore: 

 

1) Get Items with the site address and List 1 in the first part (no filters)

2) Get Items Project Dashboard looks at Project Dashboard list and filter query by Title eq '{Title}'

3) Add action to number 2 as Update Item to Project Dashboard

4) ID and Title are from the dynamic content "Get Project Dashboard)

5) RAG Value is from Get List  1 Items list

 

Tried that and ran - it runs successfully but still nothing is populated in the Project Dashboard List.  I am SharePoint admin and have full admin rights on all sites so it isnt a permissions thing. 

 

Not sure what I am missing here? 

On the main screen of your flow which shows the history of flow runs, click on the most recent one so you can see the result of each step in the flow. Click on the update item action. It should show an input and output. In this case I also got rid of the branching and am just using ProjectA and I changed the RAG status to Amber. Are you getting the same or is there something different, for example the output section is blank?

7-UpdateResult.png


Rob
Los Gallardos

Anonymous
Not applicable

Hi Rob, 

 

Strangely, I am not getting anything in the outputs from the first section and then it will not let me open the output from the Update Dashboard section?

 

Output Image.jpg

That's a bit strange @Anonymous , it's not finding your dashboard. Just as a test, if you create a simple flow with  a manual trigger and just add the action to get items from the dashboard with the Filter Query of Title eq 'ProjectA' and nothing else do you get the output then?

7c-SimpleFlow.png

 

Rob
Los Gallardos

Is the Get items actions from your Project Dashboard inside an apply to each with the "Select an output..." field set to value correctly:

3bGetItems-Dashboard.png

 

 

Rob
Los Gallardos

Anonymous
Not applicable

Hi Rob, 

 

First image below shows the output from the first Get Items which is looking at the first List_1

The second image below shows the input and output from the second Get Items which is looking at the Dashboard with the Filter Title on it.  Although, I cannot help thinking that wouldnt the output be nil given that it is looking at an empty dashboard that hasnt had anything sent to it yet? Unless I am missing something? 

 

Output from first Get Items.JPGOutput from second Get Items with Filter.JPG

Ah so the Project Dashboard list doesn't have anything in it yet? Then that's the problem. You need to have list items in your dashboard for each project, i.e ProjectA, ProjectB, ProjectC in the Title column and other columns as appropriate. If the dashboard list is empty the flow won't work as there's nothing to update in the list because the list items haven't been created. So whenever a new project is created you need to add it to the list on the Dashboard. Sorry, I should have mentioned that rather than assuming you'd added the project names to the dashboard.

If you don't want to do it that way then you'll need to use a SharePoint Create item action initially but that then adds a bit more complexity when you need to update it.

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

Anonymous
Not applicable

That works - thank you for your help.  

 

I suppose the only downside to this solution is that you have to have the actual title from one list in the dashboard list so that it can update the RAG status.  

 

My OP was that I wanted the latest status report from each project to appear in the dashboard but I suppose that would be far too complicated? 

 

So Project A could have say 5 status reports each on different dates

Project B could have 3 status reports each on different dates

Project C could have 5 status reports each on different dates

 

Project Dashboard just had

Project A - latest status report

Project B - latest status report

Project C - latest status report

 

If this is complicated then I will have to bring in a consultant but just thought I would ask.  

 

 

Yes this can be adapted to get the most recent status report on each project (even within the last 24 hours - don't forget I set the recurrence to run every day at 9am), but I'm tied up on a support call for my company at the moment so it might be a while before I post a possible solution.

Rob
Los Gallardos

Anonymous
Not applicable

Hi, 

Managed to get it to return the most recent by putting a "Created desc" into Order By and then Top Count = 1 - this is in the Get Items Dashboard.  

 

Sorted the individual lists out so that they too have the created by sorted for testing purposes and the latest version of all three now appears. Whenever I create a new list item and then resubmit the flow, the most recent list item appears.  

 

Just need to add in extra columns and ensure that when a Project Manager creates a new status report via PowerApps into their respective List, that it defaults the Title as ProjectA, ProjectB, ProjectC etc.  (or whatever the names are) 

 

Thanks for your help in this @RobElliott 

 

Happy New Year to you. 

Steve

 

Dashboard_overview.JPG

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Top Kudoed Authors
Users online (2,544)