Determine max value by comparing data in lookup columns

[Anonymous]

Hi all,

Please see below context.

I have an 3 lists as follows:

1. Incident Management - used to create any incidents.

2. Severity Assessment Matrix (People) - used to record the severity associated with each people related incidence (e.g. injury)

3. Severity Assessment Matrix (Asset) - used to record the severity associated with each asset related incidence (e.g. faulty)

What I am need to achieve:

The incident management list references SAM (People) and SAM (Asset) through lookup columns. E.g. an incident occurred where a person sustained a minor injury (Severity: 2) and the equipment on site is broken (Severity: 4). The severity of the incident should compare the severities of each of People and Asset and automatically reflect the highest number - in this case 4.

I am trying to implement a solution through Power Automate (Flow) as follows:

When an item is created or updated > Update Item

But I am running into an infinite trigger loop. Note that I do not have a service account that I could use (one of the solutions proposed).

Help is much appreciated - I am newbie to SharePoint, Flow etc.

[v-litu-msft]

Hi @Anonymous,

There is no need to use Flow to achieve it, you could set the highest number column to a calculated column to find the largest number and display them: Calculate-the-smallest-or-largest-number-in-a-range 

To calculate the smallest or largest number in two or more columns in a row, use the MIN and MAX functions.

Column1	Column2	Column3	Formula	Description (result)
10	7	9	=MIN([Column1], [Column2], [Column3])	Smallest number (7)
10	7	9	=MAX([Column1], [Column2], [Column3])	Largest number (10)

Here is a document about how to create a calculated column in SharePoint list:

calculate-data-in-lists-or-libraries

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Lin Tu

Your proposed solutions only work if the data already exists as part of the List itself - not when the columns are lookup ones. Lookup columns cannot be referenced in a Calculated column. That's the challenge and why I am using Flow. 

Unless I missed something, my understanding is that the only way to achieve the result is Flow but I am happy to stand corrected.

[Anonymous · Accepted solution]

I have successfully devised a work around for this as follows:

1. Created a new column 'Severity Status' in my Incident Management List as a Choice. Values: Calculating and Calculated.

2. Created a Flow in Power Automate as follows: When an item is created or edited > Condition > Update Item.

This is a great link demonstrating step by step: https://www.youtube.com/watch?v=foUUmHINKzk

What is however missing, and which I encountered, is that despite creating the flows as per the video, one may still encounter errors in the Flow Checker - particular the infinite loop trigger error. Ignore the error, save the flow and test your data. It will clear out (eventually).

As for the function, I parsed each value into an int (this is important) for the max to work as follows:

max(int(value),int(value))