cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
EricDeferm
Level: Powered On

Iterate using Do Until through a number of fields of an item in a SP list

Here's the story. Starting from SP list with 10 approver fields (Approver1 > ... > Approver10). Each of the approvers should be requested for approval using the action 'Start An approval'. On response (approve/reject) we want to update the corresponding response field (Response1 > ... > Response10). We could build 10 nested condition each with 'Start an approval', followed by an Approve or Reject  update action of the Response field. Technically this is possible, however it's daunting & time consuming task (and in our case we're facing 15 approvers or more), so I'm looking to implement some dynamic code to retrieve & update fields in a dynamic way, in other words to use a counter in combination with a field name. Unfortunately I can't figure out the right syntax. I'm working with Variables and want to retrieve the approver & response fields as follows.

Q1) If the variable is called 'VarApprover', how can I set the value of this variable in the Do Until loop using an action 'Set Variable', to assign the value of ('Approver' + X), where x is iterating from 1 to 10 to the variable? Is there an Expression to achieve this?

Q2) Suppose there's a solution for Q1, we can then Start 10 sequantial approvals (one for each approver), but how (expression or other syntax?) do we update the corresponding ResponseX field of the List item after Approval/Reject by the approver?

Tx already for reading the story :-)

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Iterate using Do Until through a number of fields of an item in a SP list

Hi @EricDeferm,

 

Do you want to achieve your needs using a flow (Microsoft Flow)?

Could you please share a bit more about the Approver field? Is it a Person or Group type column?

 

For your first question, if you want to assign the value of ("Approver"+X) to the VarApprover variable, please take a try with the following workaround:

  • Add a Flow Button trigger.
  • Add a Variables-> "Initialize variable" action, Name set to VarApprover, Type set to String and Value set to empty.
  • Add a Variables-> "Initialize variable 2" action, Name set to Counter, Type set to Integer and Value set to 1.
  • Add a Variables-> "Initialize variable 3" action, Name set to NumberOfApprover, Type set to Integer and Value set to 10.
  • Add a "Do Until" action, click "Edit in advanced mode", type the following formula:
@equals(variables('NumberOfApprover'), 0)

Click "Change limits", Timeout property set to P30D.

 

Within "Do Until" action, add a "Set Variable" action, Name choose VarApprover, Value set to following formula:

concat('Approver',variables('Counter'))

Add a "Increment variable" action, Name choose Counter, Value set to 1.

Add a "Decrement variable" action, Name choose NumberOfApprover, Value set to 1.

 

Image reference:10.JPG

 

11.JPG

The flow works successfully as below:12.JPG

 

More details about using expression in flow actions, please check the following article:

Use expression in flow actions

 

 

For your second question, I have created a SP list on my side, the data structure as below:13.JPG

 

Note: I have created 3 Approver fields and corresponding Response fields on my side for testing. The Approver fields are all Person or Group type column in my SP list.

 

I have made a test on my side, please take a try with the following workaround:14.JPG

 

15.JPG

 

16.JPG

 

Within Do Until action, add a "Set Variable" action, Name choose VarApprover, Value set to following formula:

concat('Approver',variables('Counter'))

Within "Start an approval" action, type VarApprover (Variable) dynamic content within the Title field. The Assigned to field set to following formula:

triggerBody()?[variables('VarApprover')]?['Email']

Within "Update item" action, Response1 field set to following formula:

if(contains(body('Start_an_approval')?['title'],'Approver1'),body('Start_an_approval')?['response'],null)

The Response2 field set to following formula:

if(contains(body('Start_an_approval')?['title'],'Approver2'),body('Start_an_approval')?['response'],null)

The Response3 field set to following formula:

if(contains(body('Start_an_approval')?['title'],'Approver3'),body('Start_an_approval')?['response'],null)

 

On your side, the Response10 field should set to following formula:

if(contains(body('Start_an_approval')?['title'],'Approver10'),body('Start_an_approval')?['response'],null)

 

 

Best regards,

Kris

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 REPLIES 8
EricDeferm
Level: Powered On

Re: Iterate using Do Until through a number of fields of an item in a SP list

Using the Switch is not bringing much benefit as each "Update Item" action needs to be repeated 10 times (x2, 1approve & 1reject)

mr-dang
Level 10

Re: Iterate using Do Until through a number of fields of an item in a SP list

Hi Eric,

This sounds like a Flow question, but I can tackle it anyway.

 

It sounds like you want a more efficient way to structure your approval/response system than attempting to build 10 nested conditions.

 

Situations like this can be solved by best practices. Currently you have 10 approver fields and 10 response fields. That's a lot of fields. And mapping to each one will be painful as you've predicted. To solve this with a best practice, you can restructure your SP list to include a column for the nth approval/response:

 

 

columns: ID, n, Approver, Response

In this way, each process will have 10 records. Below is a sample of process 1:

 

IDnApproverResponse
11GudmundurApprove
12EllenApprove
13TerryApprove
14StephanReject
15ManojApprove
16NupurApprove
17DavidReject
18HatimApprove
19François 
110Kim 

 

Since you're only working with one column for approver and one column for response, you won't need any fancy nesting.

 

That said, you will need to learn how to filter to lookup the id and nth step to update the approver and response.

 

 

Mr. Dang

 

_____________________

Microsoft Employee
@8bitclassroom
EricDeferm
Level: Powered On

Re: Iterate using Do Until through a number of fields of an item in a SP list

Tx @mr-dang for the alternative thinking/approach. I'll consider your suggestion. The initiators of the approval process want to assign all approvers in one entry form and want to see all the responses in one line. As such we need to keep a main form to enter the starting item, from which we then create ten sub items in another list. The approval cycle will become a lot easier but additional effort is required to split approvers and to consolidate responses.

Community Support Team
Community Support Team

Re: Iterate using Do Until through a number of fields of an item in a SP list

Hi @EricDeferm,

 

Do you want to achieve your needs using a flow (Microsoft Flow)?

Could you please share a bit more about the Approver field? Is it a Person or Group type column?

 

For your first question, if you want to assign the value of ("Approver"+X) to the VarApprover variable, please take a try with the following workaround:

  • Add a Flow Button trigger.
  • Add a Variables-> "Initialize variable" action, Name set to VarApprover, Type set to String and Value set to empty.
  • Add a Variables-> "Initialize variable 2" action, Name set to Counter, Type set to Integer and Value set to 1.
  • Add a Variables-> "Initialize variable 3" action, Name set to NumberOfApprover, Type set to Integer and Value set to 10.
  • Add a "Do Until" action, click "Edit in advanced mode", type the following formula:
@equals(variables('NumberOfApprover'), 0)

Click "Change limits", Timeout property set to P30D.

 

Within "Do Until" action, add a "Set Variable" action, Name choose VarApprover, Value set to following formula:

concat('Approver',variables('Counter'))

Add a "Increment variable" action, Name choose Counter, Value set to 1.

Add a "Decrement variable" action, Name choose NumberOfApprover, Value set to 1.

 

Image reference:10.JPG

 

11.JPG

The flow works successfully as below:12.JPG

 

More details about using expression in flow actions, please check the following article:

Use expression in flow actions

 

 

For your second question, I have created a SP list on my side, the data structure as below:13.JPG

 

Note: I have created 3 Approver fields and corresponding Response fields on my side for testing. The Approver fields are all Person or Group type column in my SP list.

 

I have made a test on my side, please take a try with the following workaround:14.JPG

 

15.JPG

 

16.JPG

 

Within Do Until action, add a "Set Variable" action, Name choose VarApprover, Value set to following formula:

concat('Approver',variables('Counter'))

Within "Start an approval" action, type VarApprover (Variable) dynamic content within the Title field. The Assigned to field set to following formula:

triggerBody()?[variables('VarApprover')]?['Email']

Within "Update item" action, Response1 field set to following formula:

if(contains(body('Start_an_approval')?['title'],'Approver1'),body('Start_an_approval')?['response'],null)

The Response2 field set to following formula:

if(contains(body('Start_an_approval')?['title'],'Approver2'),body('Start_an_approval')?['response'],null)

The Response3 field set to following formula:

if(contains(body('Start_an_approval')?['title'],'Approver3'),body('Start_an_approval')?['response'],null)

 

On your side, the Response10 field should set to following formula:

if(contains(body('Start_an_approval')?['title'],'Approver10'),body('Start_an_approval')?['response'],null)

 

 

Best regards,

Kris

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
EricDeferm
Level: Powered On

Re: Iterate using Do Until through a number of fields of an item in a SP list

Hi @v-xida-msft , your solution is briljant, I got the stuff about the variables working and decided to use a Switch and made some simplifications to keep the nesting short. Your solution though is much better and shows exactly the syntax I was missing. Txxxxxxxxxxxxxxxx!!!!!

mr-dang
Level 10

Re: Iterate using Do Until through a number of fields of an item in a SP list

@v-xida-msft's solution is so thorough it should be its own blog post Smiley Happy

Microsoft Employee
@8bitclassroom
EricDeferm
Level: Powered On

Re: Iterate using Do Until through a number of fields of an item in a SP list

Hi @v-xida-msft I investigated your solution and are happy to learn and simplify things a bit. Unfortunately I encounter a few new issues. I was able to reduce the Flow and the syntax.

This is my current flow. It starts with the scheduled trigger, then initialize only 1 variable (a counter), get the items according some filter criteria, followed by an 'Apply to each' which is running a 'Do until' for each item according to the filter criteria.

Within the loop we start an Approval Cycle to request a notification for each 'assigned' approver, incrementing the counter and finally Updating the item with approver response answer & date.

Details:

  1. Run condition for the Do until is done on the Approvers email address (depending on the project criteria the number of approvers is varying) using the Items object in combination with the variable VarCounter, we can request notifications for all specified approvers by checking if they have an Email address > Items('Apply_to_each')[concat('Approver', variables('VarCounter'))]['Email'
  2. Same logic is applied for sending ApprovalCyle notifications to each assigned approver Assigned to > items('Apply_to_each')[concat('Approver', variables('VarCounter'))]['Email'] , Mail body text to compile a personal mail using the DisplayName > items('Apply_to_each')[concat('Approver', variables('VarCounter'))]['DisplayName']
  3. Update Items elaborates further on this way of working > correspnding Response field is updated based upon the Approver# entered in the mail title (as you suggested > great tip)
    Response1 > if(contains(body('ApprovalCycle')?['title'], 'Approver1'), body('ApprovalCycle')?['response'], items('Apply_to_each')?['Response1'])
    Response2 > if(contains(body('ApprovalCycle')?['title'], 'Approver2'), body('ApprovalCycle')?['response'], items('Apply_to_each')?['Response2'])
     
    Difference with your proposal > for the else action I replaced "Null" by current value in the field itself
     
    Remaining issues:
    1. The condition of the Do Until fails if the ApproverX field has no value => it seems that the object is not present/created when no person/group is selected for an Approver field in the list?
    2. The Update Items is stil not working as expected, the corresponding Response field is updated, but the previous Response fields are made empty again as if the Item itself is not refreshed/updated after each approval?
     
    Any idea what's still missing for both cases?

Approvers-Overview.pngApprovers-ApprovalCycle.pngApprovers-UpdateItem.png

EricDeferm
Level: Powered On

Re: Iterate using Do Until through a number of fields of an item in a SP list

Below the warning message (2nd image) that I receive when the Flow fails because the next approver (property 'Approver4') doesn't exist, though it's definetly available in the SP List > see 1st image

I tried different conditions with Null & Length, but they all fail for the same reason.

  • @equals(Items('Apply_to_each')[concat('Approver', variables('VarCounter'))], null)
  • @equals(length(Items('Apply_to_each')[concat('Approver', variables('VarCounter'))]?['Email']), 1)

What's the appropriate method to test if an object/field does not exist, is not available or is empty?

Any suggestion?

SPList-Approver4.pngCheck for empty approver - Invalid condition.png