If Expression Evaluate Letters

[cbohlman]

Hello,

I have a flow that is updating one list (Record List) based on the entries made in another list (Entry List). I need to write an expression to evaluate if a value in the list should be updated or not. I want the record list to always show the 'highest' entry and only update it if the new value is higher than what was previously entered. (i.e. the Record List has a value of 3 and the Entry list has a new value of 4, the Record list should be updated. If the new value is 2 or 3 it should not be updated)

However, where this gets tricky is the values being entered are letters where their value is equivalent to numbers B=2, C=3, D=4 etc. Is there a way to write an If expression that evaluates if D is greater than B etc. So if the Record List shows an entry of B and the Entry list shows an entry of D the Record List will be updated with 'D'. If the Record List shows and entry of D and the Entry list has an entry of B the Record List will not be updated.

[ekarim2020]

Hi @cbohlman ,

May be you can use a "table" (JSON object) that maps the value of each letter to its value:

You can copy-and-paste the following: 

{
  "A": 1,
  "B": 2,
  "C": 3,
  "D": 4,
  "E": 5,
  "F": 6,
  "G": 7,
  "H": 8,
  "I": 9,
  "J": 10,
  "K": 11,
  "L": 12,
  "M": 13,
  "N": 14,
  "O": 15,
  "P": 16,
  "Q": 17,
  "R": 18,
  "S": 19,
  "T": 20,
  "U": 21,
  "V": 22,
  "W": 23,
  "X": 24,
  "Y": 25,
  "Z": 26
}

You can then use the "table" to get the values represented by each letter, for example:

variables('varAlphaValues')?['A']
1

variables('varAlphaValues')?['M']
13

variables('varAlphaValues')?['Z']
26

Here is another example using variables to represent a Record List item and an Entry List item:

We can use the following expressions in a condition action to compare the values of Record List item and Entry List item:

The expressions used in the condition are:

variables('varAlphaValues')?[variables('varRecListItem')]
variables('varAlphaValues')?[variables('varEntryListItem')]

Hope this helps.

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[cbohlman]

@ekarim2020 ,

Thank you for your response. I have been trying to use your suggestion in my flow. However it is a fairly large flow and this is just one piece I am trying to fit in. Since the Record list item and Entry List item will vary (depending on entries) I assumed (maybe incorrectly) that the second half of the expressions in the condition statement would need to pull from the 'Get Items' (for Record List) and the 'modified/new item' (for Entry List). Except the flow kept failing when i tried that with the error message below

[ekarim2020]

The error message mentions that the following property item()?['PainClass_x0028_Highest_x0029_'] cannot be selected. This property can't be selected ['PainClass_x0028_Highest_x0029_'] with the expression you currently have. Can you share the expressions used in the condition and some sample data of item()

The property needs to result in a character:

variables('varAlphaValues')?['A']

Ellis

[cbohlman]

Hello,

Here are the expressions in my condition currently:

variables('varAlphaValues')?[item()?['PainClass_x0028_Highest_x0029_'] is less than 

variables('varAlphaValues')?[triggerOutputs()?['body/PainClass/Value']]

Where ''PainClass_x0028_Highest_x0029_' is the column name of the Record List and triggerOutputs()?['body/PainClass/Value' is the value of the 'created/modified item' column that triggers the flow (Entry List). 

The Entry List is a SharePoint list where the 'Pain Class' column is a choice column with options B,C,D, and E. 

The Record List is also a SharePoint list with a column of 'Pain Class (Highest)' which is also a Choice column with options B,C,D, and E. 

And based on these choices, B = 2, C= 3, D= 4 and E= 5

My flow is updating the Record List based off the entries in the Entry list. What I am hoping to accomplish here is to have an expression evaluate the current value for the item in the Record List compared to the new value in the Entry List. If the new value (B) is less than the current value (D) then the Record List would NOT be updated. But if the new value (D) is higher than the current value (B) then the Record List should be updated with the new value (D).