2084132 - Manage Data - Calculation not working for field using custom calculations and IF statements - Compensation

• A company’s compensation form contains a custom field that contains a formula with nested “if” conditional statements that is not populating. This Article describes what to check when you have a custom field built from a custom formula that contains “if” statements that will not populate when a form is opened.

• Compensation

• A company has a form that contains fields built from custom formulas. In some of these fields they are making use of conditional “If” statements to determine the output of the fields in the form. When they open the form, they observe that these fields are showing up as blank or not computing correctly.

• Issue Example:
    
   
  The Below formula was taken from a real world example for a computation for the minimum field.
   
   
  The formula below was for the field called meritMinPct
  
   

   if(roundedRating=0,0,

   if(roundedRating=3,

   if(ratioRange="0_92",1.25,

   if(ratioRange="92_108",1,

   if(ratioRange="108",0.75))),

   if(roundedRating=4,

   if(ratioRange="0_92",1.5,

    if(ratioRange="92_108",1.25,

   if(ratioRange="108",1))),

   if(roundedRating=5,

   if(ratioRange="0_92",1.75,

   if(ratioRange="92_108",1.5,

   if(ratioRange="108",1.25))),0)))) * (prorating/10000)
 
 

• The result of this was that was the minimum was showing up as 0 all the time.
   

• Root Cause:
   
   
  For issues such as this, there is likely a bug in the  ”if” statements themselves. The “ if” condition needs to have 2 branches, one to output the true value, while the other to output the false value. This can get complex when using nested if statements. In example above, there is  a condition for if(ratioRange="108",1) which only has one branch and as such does not meet the requirement. This causes the entire formula to behave incorrectly.

• Various Causes.
   
• See example with missing pairs.

• The formula will need to be rewritten so that each if statement has two branches. The above formula can be rewritten as

   Sample is below:
 
   if(roundedRating=0,0, 
   if(roundedRating=3, 
   if(ratioRange="0_92",1.25,
   if(ratioRange="92_108",1,0.75)),
   if(roundedRating=4,
   if(ratioRange="0_92",1.5,
    if(ratioRange="92_108",1.25,1)),
   if(roundedRating=5,
   if(ratioRange="0_92",1.75,
   if(ratioRange="92_108",1.5, 1.25)),0)))) * (prorating/10000).

KBA , sf compensation manage data , LOD-SF-CMP , Compensation Management , Problem