Flow to create update parent list from multiple child lists

[Anonymous]

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? 

[AKRogers]

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:

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]

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]

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

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.

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.

 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.

 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.

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

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:

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]

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 1

Screenshot above shows the initial get items etc. 

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

[RobElliott]

@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]

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? 

[RobElliott]

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?

Rob
Los Gallardos

[Anonymous]

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?

[RobElliott]

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?

Rob
Los Gallardos

[RobElliott]

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

Rob
Los Gallardos

[Anonymous]

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? 

[RobElliott · Accepted solution]

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]

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.  

[RobElliott · Accepted solution]

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]

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