I have two lists in sharepoint. One with titles and the other list is connected to it with sub-items for each title. List 2 has another column to mark weather it is completed or not. I want to automatically mark A, B, and C completed when the sub-items associated with them are all marked completed (example: check list 2 - if A -1, A-2, and A-3 are marked Y in the completed column, then mark A as Y in the completed column in List 1)
List 1 List 2
A A- 1
Is there a column in List 2 that is a lookup column and points back to list 1? If not, how can you identify items in list 2 taht are realted to items in list 1?
I will provide a solution in a few hours - it invloves more steps than you would think......
Hi @victory1234 ,
I'm sure there are multiple ways to do it but here is my humble suggestion:
I set up two lists similar to how I think you have them:
I set up a flow with the trigger of "When an item is created or modified" looking at the list with the subtasks in it (Test list 2 in my case) and also set up a variable that will keep track of whether or not all tasks have been complete. We set the initial value for our variable as true, as we will change it to false later in the flow if needed:
I then used the Get items task on my main list, filtering it so it only returns items which have the same title (A,B,C,etc...) and also aren't already complete. If they're already complete we don't need to worry about them!
I then use an Apply to each, using the value from our Get items from main list, to get the items from our subtask list which have the same title.
I then have another apply to each that goes through each item we just got from our sublist and checks whether its status is "Y". If any of them don't have a "Y" in status, we then set our tracking variable to false:
Finally, just outside of our Apply to each for the subtasks and inside the apply to each for our main tasks we then have a condition which checks if our variable is still true, if so we update our main task item to have Y in the status column:
All my tests came back working like you described you wanted, so let me know if it helps! If not I will see what else may be lurking going wrong.
You could certainly do without putting all the flow in the Apply to each for the main task list, but I decided to do this just to try and keep run times down if you end up getting a very large list as the first filtering will just cause the flow to complete if nothing meets the criteria.
Full flow in spoiler:
Three Super User rank tiers have been launched!
Features releasing from October 2020 through March 2021
We've updated and improved the layout and uploading format of the Power Automate Cookbook!
Fill out a quick form to claim your user group badge now!