IF

The IF function is used to perform a logical test and return a specified value based on whether the test evaluates to true or false. The function can be used with various data types, including strings, numbers, dates, and booleans.

Syntax

object    IF(bool? condition, object trueValue, object falseValue)
bool      IF(bool? condition, bool trueValue, bool falseValue)
string    IF(bool? condition, string trueValue, string falseValue)
string[]  IF(bool? condition, string[] trueValue, string[] falseValue)
byte?     IF(bool? condition, byte? trueValue, byte? falseValue)
byte[]    IF(bool? condition, byte[] trueValue, byte[] falseValue)
short?    IF(bool? condition, short? trueValue, short? falseValue)
int?      IF(bool? condition, int? trueValue, int? falseValue)
int[]     IF(bool? condition, int[] trueValue, int[] falseValue)
long?     IF(bool? condition, long? trueValue, long? falseValue)
double?   IF(bool? condition, double? trueValue, double? falseValue)
decimal?  IF(bool? condition, decimal? trueValue, decimal? falseValue)
Single?   IF(bool? condition, Single? trueValue, Single? falseValue)
DateTime? IF(bool? condition, DateTime? trueValue, DateTime? falseValue)
Guid?     IF(bool? condition, Guid? trueValue, Guid? falseValue)

Inputs

Object	Description
condition	The logical test.
trueValue	The value if true.
falseValue	The value if false.

Examples

To use the IF function, you need to specify the logical test to perform and the values to return if the test evaluates to true or false. You can use other functions such as ISNULLOREMPTY and EQUALS to perform additional tests within the IF function.

The IF function is often used in conjunction with lookup operations, where a value is retrieved from a data source based on a specific condition, and a default value is returned if no value is found. In this scenario, the IF function is used to check if the retrieved value is null and return a default value if it is.

A few examples of IF being used are:

If MyColumn is NULL or an empty string return Value2, otherwise return Value1. You can set the value to return if the statement is false to the original column value by entering the column name e.g. IF(ISNULLOREMPTY(MyColumn), "Value2", MyColumn).

IF(ISNULLOREMPTY(MyColumn), "Value2", "Value1")

Another example rather than using ISNULLOREMPTY is that you can use the EQUALS function to return either true or false. If the value in Column1 is equal to MyValue then return Value1 otherwise return Value2.

IF(EQUALS(Column1, "MyValue"), "Value1", "Value2")

Alternatively you can also use string literals for the boolean expression rather than equals, but note you must use == as these are C# functions. This will only work if your column is a string data type, it can be better to sue the EQUALS function instead. If the value in Column1 is equal to MyValue then return Value1 otherwise return Value2.

IF(Column1=="MyValue", "Value1", "Value2")

For an example of using IF with Lookups, we can take the example of looking up the user account control value in AD. If that is null return a default value otherwise return the value.

IF(ISNULL(LOOKUPB("UserAccountControl", "", WHEN("Logon Name", LogonName))), 512, LOOKUPB("UserAccountControl", "", WHEN("Logon Name", LogonName)))