Dynamic Calculation Evaluation in Form

[Anonymous]

Is it possible to have a label that can dynamically resolve a value given a set of conditions that are visible in the form as different labels?

For example,

If [Score] [Green Operator] [Green Operand] = true, "Green", 

If [Score] [Yellow Min Operator] [Yellow Operand] && [Score] [Yellow Max Operator] [Yellow Max Operand] = true, "Yellow", 

If [Score] [Red Operator] [Red Operand] = true, "Red", else "No result"

The operands and operators are unique to distinct keys. We currently have about 15 keys and they all have different operands and operators that are specific to the key. So this would resolve hardcoding the 15 conditions (1 for each key) with a dynamic measure/variable. 

I appreciate your time reviewing this without being paid. Any suggestions are appreciated. Thanks!

[v-jefferni]

Hi @Anonymous ,

Could you please share more details about your scenario? What is the data source you are working with? What is the "key" you referred?

As per your screenshot, I suspect that some of the conditions are included in other conditions, so the key point is how to write the if statement: the range of the operand need to be larger and larger. For instance, in your example, if scores are integers, you could set a collection that includes numerical values which covering the whole range of scores, then set variables that each one includes numbers meet one condition, in Screen's OnVisible:

ClearCollect(colInt,Sequence(100));
Set(var1,Filter(colInt,Value < 8));Set(var2,Filter(colInt,Value >= 8));Set(var3,Filter(colInt,Value >= 5 && Value<=6));

Then on the Text of the Label, set below formula:

If(TextInput1.Text in var3, "Yellow", TextInput1.Text in var1, "Green",TextInput1.Text in var2,"Red")

So if you type in 6 in TextInput box, the Label returns Yellow, type in 4, returns Green, and so on.

You could see that Yellow range is included by Green range, so Yellow range condition need to be in front of Green range, and Red range is not intersected with others so place that condition at last.

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[Anonymous]

Thank you @v-jefferni. Datasource is SharePoint Form Integration.

Key = Category + Subcategory. If I have 10 categories, with 100 subcategories, there will be 1,000 distinct keys. 

Looks like this:

Category A

Subcategory 1

Subcategory 2

Subcategory 3

The keys for the selection so far would be A1, A2, A3.. etc

The keys can have associated thresholds that is text such as "Yes" = Green and "No" =Red. So solution needs to be able to handle comparison operators that are text only as well as numeric.

The challenge with using variables is that when the user edits the record the variable will default to previous record. All of the text controls you see in screenshot are referencing dependent drop-downs.

Thank you!!