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

Exclusive LIVE Community Event: Power Apps Copilot Coffee Chat with Copilot Studio Product Team

It's time for the SECOND Power Apps Copilot Coffee Chat featuring the Copilot Studio product team, which will be held LIVE on April 3, 2024 at 9:30 AM Pacific Daylight Time (PDT).     This is an incredible opportunity to connect with members of the Copilot Studio product team and ask them anything about Copilot Studio. We'll share our special guests with you shortly--but we want to encourage to mark your calendars now because you will not want to miss the conversation.   This live event will give you the unique opportunity to learn more about Copilot Studio plans, where we’ll focus, and get insight into upcoming features. We’re looking forward to hearing from the community, so bring your questions!   TO GET ACCESS TO THIS EXCLUSIVE AMA: Kudo this post to reserve your spot! Reserve your spot now by kudoing this post.  Reservations will be prioritized on when your kudo for the post comes through, so don't wait! Click that "kudo button" today.   Invitations will be sent on April 2nd.Users posting Kudos after April 2nd at 9AM PDT may not receive an invitation but will be able to view the session online after conclusion of the event. Give your "kudo" today and mark your calendars for April 3, 2024 at 9:30 AM PDT and join us for an engaging and informative session!

Tuesday Tip: Unlocking Community Achievements and Earning Badges

TUESDAY TIPS are our way of communicating helpful things we've learned or shared that have helped members of the Community. Whether you're just getting started or you're a seasoned pro, Tuesday Tips will help you know where to go, what to look for, and navigate your way through the ever-growing--and ever-changing--world of the Power Platform Community! We cover basics about the Community, provide a few "insider tips" to make your experience even better, and share best practices gleaned from our most active community members and Super Users.   With so many new Community members joining us each week, we'll also review a few of our "best practices" so you know just "how" the Community works, so make sure to watch the News & Announcements each week for the latest and greatest Tuesday Tips!     THIS WEEK'S TIP: Unlocking Achievements and Earning BadgesAcross the Communities, you'll see badges on users profile that recognize and reward their engagement and contributions. These badges each signify a different achievement--and all of those achievements are available to any Community member! If you're a seasoned pro or just getting started, you too can earn badges for the great work you do. Check out some details on Community badges below--and find out more in the detailed link at the end of the article!       A Diverse Range of Badges to Collect The badges you can earn in the Community cover a wide array of activities, including: Kudos Received: Acknowledges the number of times a user’s post has been appreciated with a “Kudo.”Kudos Given: Highlights the user’s generosity in recognizing others’ contributions.Topics Created: Tracks the number of discussions initiated by a user.Solutions Provided: Celebrates the instances where a user’s response is marked as the correct solution.Reply: Counts the number of times a user has engaged with community discussions.Blog Contributor: Honors those who contribute valuable content and are invited to write for the community blog.       A Community Evolving Together Badges are not only a great way to recognize outstanding contributions of our amazing Community members--they are also a way to continue fostering a collaborative and supportive environment. As you continue to share your knowledge and assist each other these badges serve as a visual representation of your valuable contributions.   Find out more about badges in these Community Support pages in each Community: All About Community Badges - Power Apps CommunityAll About Community Badges - Power Automate CommunityAll About Community Badges - Copilot Studio CommunityAll About Community Badges - Power Pages Community

Tuesday Tips: Powering Up Your Community Profile

TUESDAY TIPS are our way of communicating helpful things we've learned or shared that have helped members of the Community. Whether you're just getting started or you're a seasoned pro, Tuesday Tips will help you know where to go, what to look for, and navigate your way through the ever-growing--and ever-changing--world of the Power Platform Community! We cover basics about the Community, provide a few "insider tips" to make your experience even better, and share best practices gleaned from our most active community members and Super Users.   With so many new Community members joining us each week, we'll also review a few of our "best practices" so you know just "how" the Community works, so make sure to watch the News & Announcements each week for the latest and greatest Tuesday Tips!   This Week's Tip: Power Up Your Profile!  🚀 It's where every Community member gets their start, and it's essential that you keep it updated! Your Community User Profile is how you're able to get messages, post solutions, ask questions--and as you rank up, it's where your badges will appear and how you'll be known when you start blogging in the Community Blog. Your Community User Profile is how the Community knows you--so it's essential that it works the way you need it to! From changing your username to updating contact information, this Knowledge Base Article is your best resource for powering up your profile.     Password Puzzles? No Problem! Find out how to sync your Azure AD password with your community account, ensuring a seamless sign-in. No separate passwords to remember! Job Jumps & Email Swaps Changed jobs? Got a new email? Fear not! You'll find out how to link your shiny new email to your existing community account, keeping your contributions and connections intact. Username Uncertainties Unraveled Picking the perfect username is crucial--and sometimes the original choice you signed up with doesn't fit as well as you may have thought. There's a quick way to request an update here--but remember, your username is your community identity, so choose wisely. "Need Admin Approval" Warning Window? If you see this error message while using the community, don't worry. A simple process will help you get where you need to go. If you still need assistance, find out how to contact your Community Support team. Whatever you're looking for, when it comes to your profile, the Community Account Support Knowledge Base article is your treasure trove of tips as you navigate the nuances of your Community Profile. It’s the ultimate resource for keeping your digital identity in tip-top shape while engaging with the Power Platform Community. So, dive in and power up your profile today!  💪🚀   Community Account Support | Power Apps Community Account Support | Power AutomateCommunity Account Support | Copilot Studio  Community Account Support | Power Pages

Super User of the Month | Chris Piasecki

In our 2nd installment of this new ongoing feature in the Community, we're thrilled to announce that Chris Piasecki is our Super User of the Month for March 2024. If you've been in the Community for a while, we're sure you've seen a comment or marked one of Chris' helpful tips as a solution--he's been a Super User for SEVEN consecutive seasons!   Since authoring his first reply in April 2020 to his most recent achievement organizing the Canadian Power Platform Summit this month, Chris has helped countless Community members with his insights and expertise. In addition to being a Super User, Chris is also a User Group leader, Microsoft MVP, and a featured speaker at the Microsoft Power Platform Conference. His contributions to the new SUIT program, along with his joyous personality and willingness to jump in and help so many members has made Chris a fixture in the Power Platform Community.   When Chris isn't authoring solutions or organizing events, he's actively leading Piasecki Consulting, specializing in solution architecture, integration, DevOps, and more--helping clients discover how to strategize and implement Microsoft's technology platforms. We are grateful for Chris' insightful help in the Community and look forward to even more amazing milestones as he continues to assist so many with his great tips, solutions--always with a smile and a great sense of humor.You can find Chris in the Community and on LinkedIn. Thanks for being such a SUPER user, Chris! 💪 🌠  

Find Out What Makes Super Users So Super

We know many of you visit the Power Platform Communities to ask questions and receive answers. But do you know that many of our best answers and solutions come from Community members who are super active, helping anyone who needs a little help getting unstuck with Business Applications products? We call these dedicated Community members Super Users because they are the real heroes in the Community, willing to jump in whenever they can to help! Maybe you've encountered them yourself and they've solved some of your biggest questions. Have you ever wondered, "Why?"We interviewed several of our Super Users to understand what drives them to help in the Community--and discover the difference it has made in their lives as well! Take a look in our gallery today: What Motivates a Super User? - Power Platform Community (microsoft.com)

March User Group Update: New Groups and Upcoming Events!

  Welcome to this month’s celebration of our Community User Groups and exciting User Group events. We’re thrilled to introduce some brand-new user groups that have recently joined our vibrant community. Plus, we’ve got a lineup of engaging events you won’t want to miss. Let’s jump right in: New User Groups   Sacramento Power Platform GroupANZ Power Platform COE User GroupPower Platform MongoliaPower Platform User Group OmanPower Platform User Group Delta StateMid Michigan Power Platform Upcoming Events  DUG4MFG - Quarterly Meetup - Microsoft Demand PlanningDate: 19 Mar 2024 | 10:30 AM to 12:30 PM Central America Standard TimeDescription: Dive into the world of manufacturing with a focus on Demand Planning. Learn from industry experts and share your insights. Dynamics User Group HoustonDate: 07 Mar 2024 | 11:00 AM to 01:00 PM Central America Standard TimeDescription: Houston, get ready for an immersive session on Dynamics 365 and the Power Platform. Connect with fellow professionals and expand your knowledge. Reading Dynamics 365 & Power Platform User Group (Q1)Date: 05 Mar 2024 | 06:00 PM to 09:00 PM GMT Standard TimeDescription: Join our virtual meetup for insightful discussions, demos, and community updates. Let’s kick off Q1 with a bang! Leaders, Create Your Events!  Leaders of existing User Groups, don’t forget to create your events within the Community platform. By doing so, you’ll enable us to share them in future posts and newsletters. Let’s spread the word and make these gatherings even more impactful! Stay tuned for more updates, inspiring stories, and collaborative opportunities from and for our Community User Groups.   P.S. Have an event or success story to share? Reach out to us – we’d love to feature you!

Users online (3,732)