Blog

Formula - What to do when the If statement doesn't work?

A question that arises on occasions is - why doesn't my If statement work? This post walks through one of the most common causes and highlights the possible workarounds.

When we call the If function to apply conditional statements, there is an issue that often catches out app builders who are unfamiliar with the syntax. This can affect numerous things in apps, notably search screens that call the Filter function and apply multiple conditions with the If function, validation logic that checks multiple controls, and screen/UI related functionality.

This post describes this behaviour and may provide some guidance in cases where the If function doesn't behave as expected.

Demonstration - Typical case of the If statement not working as expected

To give an example of what typically happens, let's take an example of a screen for capturing details that relate to the financial application. There are 3 controls - a status dropdown, a checkbox to indicate the completion of a reference check, and another checkbox to indicate the completion of a credit check.

The requirement is to add a label that describes the overall status. The table beneath shows the intended label text for records in the 'In progress' status. For all other statuses, the label text should show 'Not started'.

Status
Combobox
Reference
Checkbox
Check Credit
Checkbox
Label Text
Required
In progress
True
TrueAll checks complete
In progress
True
FalseCredit check outstanding
In progress
False
FalseReference outstanding
In progress
False
FalseNot started

To implement this logic, we add a label and set the label text to the following formula. This syntax appears to make logical sense for most people.

If(cboStatus.Selected.Value = "In progress",
   If(chkReference.Value = true && chkCredit.Value = false, 
      "Credit check Outstanding",
      If(chkReference.Value = false && chkCredit.Value = true, 
         "Reference Outstanding",
         "All checks completed"
      )
   ,"Not started")
)
The initial test of this formula seems to work. When we set the status to 'In progress' and check the reference check box, the label displays 'Credit check outstanding'. This is expected behaviour.



We now check the 'credit check' checkbox and we expect to see the text 'All checks completed'. However, the label displays a blank. What's wrong with this formula? Why doesn't it work?


Beware of nested If statements

The nested call to the If function is the cause of this problem. When we nest If statements, each nested branch must return either true or false. With this formula, the inner If returns the text "credit check outstanding", "Reference check outstanding", or "All checks" completed rather than true/false, and this is the cause of the unexpected behaviour. 

The fix for this problem is to avoid the use of nested If statements and there are several patterns that we can adopt. The If function enables us to specify multiple conditions and results without nesting calls to If. The usage looks like this.

If( Condition1, ThenResult1, 
    Condition2, ThenResult2,  
    Condition3, ThenResult3,  
    Condition4, ThenResult4,  
    etc...  
    DefaultResult   
)

In this example, we can modify our formula as shown beneath and the logic will now work as expected (as highlighted in the screenshot beneath).

If(cboStatus.Selected.Value = "In Progress" && 
        chkReference.Value = true && chkCredit.Value = false,
   "Credit check Outstanding",
   cboStatus.Selected.Value = "In Progress" && 
        chkReference.Value = false && chkCredit.Value = true,
   "Reference Outstanding",
   cboStatus.Selected.Value = "In Progress" && 
        chkReference.Value = true && chkCredit.Value = true,
   "All checks complete",
   "Not Started"
)


Another technique is to call the Switch function in conjunction with the If function. The example formula would look like this.

Switch(cboStatus.Selected.Value, 
       "In progress", 
       If(chkReference.Value = true && chkCredit.Value = false,
          "Credit check Outstanding",
          chkReference.Value = false && chkCredit.Value = true,
          "Reference Outstanding",   
          chkReference.Value = true && chkCredit.Value = true,
          "All checks complete",
          "Not Started"
        )
       "Not Started/Other status selected..."
)

Conclusion

When the if function doesn't work as expected, a common cause is nested calls to If. This post highlighted an example and showed how to work around this issue by removing the nested calls.
Previous
Next
Related posts