Checking the status field of all items in a sharepoint list

%3CLINGO-SUB%20id%3D%22lingo-sub-1714431%22%20slang%3D%22en-US%22%3EChecking%20the%20status%20field%20of%20all%20items%20in%20a%20sharepoint%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1714431%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20sharepoint%20list%20that%20is%20maintained%20throughout%20the%20day%20by%20each%20of%20our%20bank%20branches.%26nbsp%3B%20Each%20branch%20has%20their%20own%20item%20in%20the%20list.%26nbsp%3B%20When%20they%20are%20done%20for%20the%20day%2C%20they%20change%20their%20status%20to%20%22verified%22.%26nbsp%3B%20I%20want%20to%20build%20a%20flow%20that%20will%20check%20the%20status%20of%20all%20items%20in%20the%20list%20and%20send%20an%20email%20when%20they%20are%20all%20in%20the%20%22verified%22%20status.%26nbsp%3B%20As%20of%20now%2C%20it%20sends%20an%20email%20when%20each%20status%20is%20changed%20to%20verified%2C%20but%20only%20want%201%20email%20for%20all.%26nbsp%3B%20Any%20ideas%20out%20there%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1714431%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1714857%22%20slang%3D%22en-US%22%3ERe%3A%20Checking%20the%20status%20field%20of%20all%20items%20in%20a%20sharepoint%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1714857%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F634702%22%20target%3D%22_blank%22%3E%40rbutgins21%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EMy%20understanding%20of%20your%20desired%20solution%3A%3CBR%20%2F%3EEach%20branch%20has%20their%20own%20item%20in%20a%20list%20(bank%20branch%20list)%20and%20every%20morning%2C%20the%20status%20is%20supposed%20to%20be%20%22unverified%22.%20So%20that%20when%20they%20are%20finished%2C%20they%20update%20their%20status%20to%20verified.%20When%20every%20status%20is%20verified%2C%20an%20notification%20should%20be%20sent%20to%20inform%20that%20everyone%20is%20finished.%20When%20that%20has%20happen%2C%20the%20status%20of%20every%20item%20should%20go%20back%20to%20be%20unverified%20(to%20be%20set%20for%20the%20next%20day).%3CBR%20%2F%3E%3CBR%20%2F%3ESuggestion%20for%20solution%3A%3CBR%20%2F%3EHave%20two%20lists%2C%20one%20for%20the%20bank%20branch%2C%20and%20another%20one%20for%20Power%20Automate%20counter%20(one%20object%20in%20the%20list%20with%20number%20column).%20Whenever%20an%20item%20is%20updated%20in%20the%20bank%20branch%20and%20the%20field%20has%20gone%20from%20unverified%20to%20verified%2C%20update%20the%20other%20object%20in%20the%20Power%20Automate%20counter%20list%20with%20%2B1.%3CBR%20%2F%3EWhen%20the%20number%20reaches%20the%20same%20amount%20as%20all%20of%20the%20branches%2C%20then%20trigger%20an%20notification.%20After%20the%20notification%20has%20occured%2C%20either%20have%20a%20button%20triggered%20reset%20for%20the%20verified%2Funverified%20status%2C%20or%20make%20it%20automatic.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20hope%20this%20makes%20sence%2C%20if%20anything%20is%20unclear%2C%20please%20tell%20and%20I%20will%20try%20to%20explain%20further.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1715647%22%20slang%3D%22en-US%22%3ERe%3A%20Checking%20the%20status%20field%20of%20all%20items%20in%20a%20sharepoint%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1715647%22%20slang%3D%22en-US%22%3EThat%20could%20work.%20But%20I%20would%20use%20a%20get%20list%20items%20on%20a%20scheduled%20run%20every%205%2C10%2C15%20minutes%20or%20so.%20Whatever%20is%20acceptable.%20%3CBR%20%2F%3E%3CBR%20%2F%3EThen%20get%20items%20action%20on%20the%20list.%20%3CBR%20%2F%3E%3CBR%20%2F%3ENext%20out%20a%20condition%20and%20out%20the%20get%20items%20status%20field%20in%20the%20check%20and%20this%20will%20add%20an%20apply%20to%20each%20loop.%20For%20the%20condition%20put%20in%20%3D%20verified.%20%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20the%20no%20branch%20when%20false%20out%20an%20end%20workflow%20(might%20be%20terminate)%20action.%20%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20will%20make%20it%20not%20continue%20if%20all%20the%20items%20aren%E2%80%99t%20true.%20After%20the%20loop%20then%20do%20what%20you%20want%20when%20all%20items%20are%20verified.%20You%20can%20then%20also%20do%20another%20update%20item%20and%20use%20the%20get%20items%20status%20column%20update%20in%20it%20and%20it%20will%20loop%20through%20each%20item%20and%20you%20can%20reset%20it%20automatically%20And%20also%20plug%20your%20email%20action%20before%20or%20after%20this%20loop%20through%20the%20items.%20If%20you%20don%E2%80%99t%20want%20to%20reset%20then%20obviously%20skip%20this%20step.%20%3CBR%20%2F%3E%3CBR%20%2F%3EAnyway%20the%20other%20suggested%20option%20will%20work%20too%20but%20this%20way%20you%20can%20do%20without%20an%20additional%20list%20but%20a%20little%20more%20complicated.%20%3CBR%20%2F%3E%3CBR%20%2F%3EHope%20it%20helps%20%3B).%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1721413%22%20slang%3D%22en-US%22%3ERe%3A%20Checking%20the%20status%20field%20of%20all%20items%20in%20a%20sharepoint%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1721413%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F284426%22%20target%3D%22_blank%22%3E%40ArefHalmstrand%3C%2FA%3E%26nbsp%3B%20What%20is%20the%20syntax%20for%20adding%201%20to%20a%20number%20field%20in%20Power%20Automate%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22rbutgins21_0-1601315098546.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F222628i5157D6826AC24703%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22rbutgins21_0-1601315098546.png%22%20alt%3D%22rbutgins21_0-1601315098546.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1721877%22%20slang%3D%22en-US%22%3ERe%3A%20Checking%20the%20status%20field%20of%20all%20items%20in%20a%20sharepoint%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1721877%22%20slang%3D%22en-US%22%3ETry%20this%3A%20add(triggerBody()%3F%5B'number'%5D%2C1)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1721915%22%20slang%3D%22en-US%22%3ERe%3A%20Checking%20the%20status%20field%20of%20all%20items%20in%20a%20sharepoint%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1721915%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F284426%22%20target%3D%22_blank%22%3E%40ArefHalmstrand%3C%2FA%3E%26nbsp%3B%20Having%20issues%20with%20my%20condition%20statement%20-%20can't%20get%20it%20to%20be%20true%20-%20the%20status%20has%20been%20changed%20to%20verified%2C%20but%20that%20step%20keeps%20failing.%26nbsp%3B%20I%20am%20missing%20something%20simple%20as%20this%20is%20not%20a%20complicated%20flow....%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22rbutgins21_0-1601322191607.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F222644i17C6BEB924F78BF9%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22rbutgins21_0-1601322191607.png%22%20alt%3D%22rbutgins21_0-1601322191607.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1721976%22%20slang%3D%22en-US%22%3ERe%3A%20Checking%20the%20status%20field%20of%20all%20items%20in%20a%20sharepoint%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1721976%22%20slang%3D%22en-US%22%3EI%20will%20try%20build%20similar%20scenario%20and%20test%20it%20%3A)%3C%2Fimg%3E%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20your%20%22verified%22%20field%20a%20choice%20field%20or%20free%20text%20%3F%3C%2FLINGO-BODY%3E
Highlighted
Contributor

I have a sharepoint list that is maintained throughout the day by each of our bank branches.  Each branch has their own item in the list.  When they are done for the day, they change their status to "verified".  I want to build a flow that will check the status of all items in the list and send an email when they are all in the "verified" status.  As of now, it sends an email when each status is changed to verified, but only want 1 email for all.  Any ideas out there?

 

Thanks

17 Replies
Highlighted

@rbutgins21 

My understanding of your desired solution:
Each branch has their own item in a list (bank branch list) and every morning, the status is supposed to be "unverified". So that when they are finished, they update their status to verified. When every status is verified, an notification should be sent to inform that everyone is finished. When that has happen, the status of every item should go back to be unverified (to be set for the next day).

Suggestion for solution:
Have two lists, one for the bank branch, and another one for Power Automate counter (one object in the list with number column). Whenever an item is updated in the bank branch and the field has gone from unverified to verified, update the other object in the Power Automate counter list with +1.
When the number reaches the same amount as all of the branches, then trigger an notification. After the notification has occured, either have a button triggered reset for the verified/unverified status, or make it automatic.

I hope this makes sence, if anything is unclear, please tell and I will try to explain further.

Highlighted
That could work. But I would use a get list items on a scheduled run every 5,10,15 minutes or so. Whatever is acceptable.

Then get items action on the list.

Next out a condition and out the get items status field in the check and this will add an apply to each loop. For the condition put in = verified.

For the no branch when false out an end workflow (might be terminate) action.

This will make it not continue if all the items aren’t true. After the loop then do what you want when all items are verified. You can then also do another update item and use the get items status column update in it and it will loop through each item and you can reset it automatically And also plug your email action before or after this loop through the items. If you don’t want to reset then obviously skip this step.

Anyway the other suggested option will work too but this way you can do without an additional list but a little more complicated.

Hope it helps ;).
Highlighted

@ArefHalmstrand  What is the syntax for adding 1 to a number field in Power Automate? 

rbutgins21_0-1601315098546.png

 

Highlighted
Try this: add(triggerBody()?['number'],1)
Highlighted

@ArefHalmstrand  Having issues with my condition statement - can't get it to be true - the status has been changed to verified, but that step keeps failing.  I am missing something simple as this is not a complicated flow....

rbutgins21_0-1601322191607.png

 

Highlighted
I will try build similar scenario and test it :)

Is your "verified" field a choice field or free text ?
Highlighted
Highlighted

@rbutgins21  I rebuilt the flow, but changed the status to status value and then had it send an email when it was true - that worked.  Now, when I delete the email step and do an update item, it is telling me "could not find the list item"

 

rbutgins21_0-1601323472793.png

 

Highlighted
Great! Strange error, try looking further on what has happen (perhaps a condition has been changed). You could also try to reconnect the SharePoint list connection.

Highlighted

@ArefHalmstrand  The condition step seems to be working, but now it is failing when I'm trying to update the 2nd list that contains the count. 

rbutgins21_0-1601324394144.pngrbutgins21_1-1601324466072.png

 

Highlighted
Could there be an issue with the actual list? It is hard for me to give any solid advice.
Highlighted

@ArefHalmstrandI guess I should ask the question - is it possible to have a trigger where an item is created or modified from one list and check the status on that list and if it meets the criteria - process an update item for another list.  I'm basically trying to count the number of branches on the trigger list, that have been changed to verified, by updating the count on a separate list.  The update step is where it is failing  - it's telling me that the list item is not found.

Highlighted

@rbutgins21 That should really not be an issue, updating a second list based on a trigger on the first list is common. However the connection has to be configured. I will try to find time to create a similar solution to try it out and see if I can recreate the same error that you are receiving. 

Highlighted

@ArefHalmstrand  I'm thinking it has something to do with the *ID (unique identifier of item to be updated).  I was just choosing ID, but I don't think that uniquely identifies the item in the list. 

rbutgins21_0-1601388320090.png

 

Highlighted
Try to manually add the ID of the listitem (the ID can be shown if you edit the current view in the list).
Highlighted
If it is the first item in a list, the ID should be 0
Highlighted

@ArefHalmstrandIt's the first and only item in the list, but I am still getting the following error...

 

rbutgins21_0-1601392498397.png