Question

Calculated Value field using IF statements, based on 5 choice fields

  • 13 December 2022
  • 8 replies
  • 933 views
J
Badge +4

I have 5 Choice fields on my form with Yes/No values.  I need to generate a score using a Calculated Value field based on what is selected in those Choice fields.  The Yes/No values equal numbers so depending on which ones are selected determines the score.  There are 4 possible scores and I can do the calculated values in 4 separate fields using these IF statements below. 

Is it possible to combine these into one Calculated Value field?  Or many there is a better way to do this?

 if(RAIHC_ADL=='No' && RAIHCStairs=='No' && RAIHCSelfReliance=='Self-Reliant' && RAIHCSupervision=='No', 2,3)
 if(RAIHC_ADL=='Yes' && RAIHCStairs=='No' && RAIHCSelfReliance=='Self-Reliant', 2,3)
 if(RAIHC_ADL=='Yes' && RAIHCStairs=='Yes' && RAIHCPhysicalAssistance=='Yes', 5,4)
 if(RAIHC_ADL=='No' && RAIHCStairs=='Yes' && RAIHCPhysicalAssistance=='Yes', 5,4)
 
I tried using this but it doesn’t work:
if(RAIHC_ADL=='No' && RAIHCStairs=='No' && RAIHCSelfReliance=='Self-Reliant' && RAIHCSupervision=='No', 2,3, if(RAIHC_ADL=='Yes' && RAIHCStairs=='No' && RAIHCSelfReliance=='Self-Reliant', 2,3, if(RAIHC_ADL=='Yes' && RAIHCStairs=='Yes' && RAIHCPhysicalAssistance=='Yes', 5,4,  if(RAIHC_ADL=='No' && RAIHCStairs=='Yes' && RAIHCPhysicalAssistance=='Yes', 5,4))))

8 replies

Garrett
Userlevel 6
Badge +16

Hi @jambou 

 

Just enclose each if function with parentheses () - example:
 ( if(<condition1>, 2, 3 ) ) + ( if(<condition2>, 4, 3 ) ) + ( if(<condition3>, 6, 3 ) ) 

This resolves to  - assuming all the conditions are true
(2) + (4) + (6)

= 12

 

Hope that helps
 

 

J
Badge +4

Thanks Garrett, I was able to combine my four statements into one using your suggestion and it gives me a total number, however, my problem is that I don’t want the total number of all 5 statements,, I want it to only calculate one of the four statements at a time, based on what choice fields are selected etc., so the number calculated would have to be 2, 3, 4 or 5.

Does that make sense?  I’m probably not explaining this very well. 

bamaeric
Userlevel 5
Badge +13

Give this a try:

 

(If(or(ADL=='No' && Stairs=='No' && SelfReliance=='Self-Reliant' && Supervision=='No', ADL=='Yes' && Stairs=='No' && SelfReliance=='Self-Reliant'),2,3)) + (If(or(ADL=='Yes' && Stairs=='Yes' && PhysicalAssistance=='Yes', ADL=='No' && Stairs=='Yes' && PhysicalAssistance=='Yes'),5,4))

J
Badge +4

Thanks bamaeric,

I tried it and it adds the two together, so always results in 7 or 8.  The result has to be 2, 3, 4 or 5 only.

Does it have to have the + in it?

bamaeric
Userlevel 5
Badge +13

Hi @jambou. Thanks for clarifying. The result returned is only one value that is 2, 3, 4, or 5? If this is true, I’m missing what conditions must be met for the value to be 3 or 4.

Right now, your If statements contradict because the formula false statement values (3 and 4) cannot both be returned, One of those needs a condition. One formula can be constructed to return a value, but I think we need to know what conditions return a value of 3 or 4.

Hopefully that makes sense.

J
Badge +4

Hmm ok, so none of my statements would result in a 3 or 4?

This one gives me a score of 3 and I attached the screen shot of the form:

 if(RAIHC_ADL=='No' && RAIHCStairs=='No' && RAIHCSelfReliance=='Self-Reliant' && RAIHCSupervision=='No', 2,3)

 
Garrett
Userlevel 6
Badge +16

Hi @jambou 

Here is a Excel sheet. Perhaps you can fill out the Truth Table to clearly define the logic for your scoring. 

This truth table will make it easier to define the logic and also helps to verify or test the logic

 

 

1 Attachment
J
Badge +4

Thanks Garrett, that really helped to define the logic.  I attached the filled in Excel file.  I removed some rows because sometimes some of the fields get hidden and are not needed in the calculation.  I’m not sure if that makes it more complicated or simpler lol.

I’m trying to re-created an InfoPath form that someone else created but I can't figure out the logic to make it work like it does in the infoPath form.

1 Attachment

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings