Unanswered

Write back a value in a SP-list depending on the match of two parameters (where one parameter has multiple values)

(0) Share

Report

Posted on by Lulm1992

I have two SP-lists:

"Maßnahmen" (Master Data) which has columns named "Control ID" and "RiskNumber"

The other List "Matrix" references to "Maßnahmen" through (lookup) the column "Controlactivity" (multiple values possible). For each process there is a risk number which also have control activities.

I need to create a script which matches _MatrixList.Controlactivity with _Maßnahmen.Control ID and returns the MatrixList.RiskNumber to the list Maßnahmen.RiskNumber

Description of the example below: Process 1 bears the Risk "1" and Process 14 bears Risk "3", both can be controlled with activity "M01"; hence in the "Maßnahmen"-List should appear "1;3" in the column "RiskNr"

I appreciate every kind of help

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

v-jefferni on at

Like (0)

Report
Re: Write back a value in a SP-list depending on the match of two parameters (where one parameter has multiple values)

Hi @Lulm1992 ,

The master list includes unique control IDs, is it? So, the approach would be:

1. Get items of the master list

2. Initialize two array variables.

3. Apply to each Master list item.

4. Within apply to each control, Get items of Matrix list.

5. Apply to each 2: Matrix item

6. Apply to each 3: Controlactivity value

7. Condition: items('Apply_to_each_3')?['value'] equals to items('Apply_to_each')?['ControlID'], true branch append to variable1:

{ "ControlID": items('Apply_to_each_3')?['Value'], "RiskNr":items('Apply_to_each_2')?['Risik-No.'] }

false branch leaves blank.

Now the flow has generated an array with two columns, ControlID and RiskNr, so next step is updating this variable with unique control IDs.

8. Out of and underneath Apply to each 2 Control, Filter array on variable1, criteria is item()?['ControlID'] equals to items('Apply_to_each')?['Control ID']

9. "Select" from Filter array, map RiskNr to item()?['RiskNr']

10. Append to variable2

{ "Control ID": items('Apply_to_each')?['Control ID'], "RiskNr":body('Select') }

This is the complex array with unique Control IDs and corresponding RiskNr values.

Best regards,

Was this reply helpful? Yes No
ruben_noah on at

Like (0)

Report

Re: Write back a value in a SP-list depending on the match of two parameters (where one parameter has multiple values)

Dear @v-jefferni ,

Thank you for your solution. I am working together with @Lulm1992. I still have some basic questions about your solution.

- before step 1, what is the trigger event of the Flow? We choose "when an item is created of modified" with List Name "Matrix"
- at what point do we actually update the RiskNr in the list "Maßnahmen/Master Data"? For my understanding that point is missing in the solution.

I hope you could follow my request. Thank you in advance and best regards,

Ruben

-

Was this reply helpful? Yes No
v-jefferni on at

Like (0)

Report

Re: Write back a value in a SP-list depending on the match of two parameters (where one parameter has multiple values)

Hi @ruben_noah ,

Actually, in my solution I was assuming it is an instant button flow. To update RiskNr, after the last step you can use update item action and get the values from variable2.

Best regards,

Was this reply helpful? Yes No
ruben_noah on at

Like (0)

Report

Re: Write back a value in a SP-list depending on the match of two parameters (where one parameter has multiple values)

Hi @v-jefferni ,

Thank you. Maybe a silly question, but how does the "update item" knows what specific item to update?, the "update Item" action requires as identificator the ID, but how do I know the specific ID of the one Item i want to update?

Thank you and best regards,

Ruben

Was this reply helpful? Yes No