If() and isNull() Statements

if... then... 

One of the most commonly used expressions, this allows if.. then… statements inside an expression.

 if(clause, value if true, value if false)

e.g. If we have a field called [Value] which is a number field, we can use an if() expression within a Status Field to set a Red Amber Green status field ([RAGField]) to get its colour depending on the value.

if([Value] > 100, 'Red', 'Green')

… we can go one further and "nest" if() statements together, so that the value we return is another if() statement

if([Value] > 100, 'Red', if([Value] > 50, 'Amber', 'Green'))

You can also use "and" and "or" within the if() statement by using & and || respectively: 

if([Value] > 100 & [Value] < 200, 'Red', IF([Value] > 50, 'Amber', 'Green'))

if([Value] = 'A' || [Value] = 'B' || Value ='C', 'Low Alphabet', 'Other')

isNull() Function

You should be careful when when using some functions if there is a chance that the value in the data you are passing could be empty if the field you are checking has no value. Using the isNull() function helps stop errors by setting a value if the value for a Field is null. It takes two parameters where the first is the Field to check if null and the second is the value to set if the value for the Field is null.

If you wanted to set the value of an image field in a child record based on the parent, you need to test that the parent record image field has a value first. The Child record image field would have the expression.

isNull([Parent.ImageField], 'Default') where 'Default' is the value of the ImageList if no selection has been made.

Note: Isnull() only checks to see if the value for a Field is null, it doesn't account for a blank value for a Field. For example if checking whether a text Field is null or empty string it would need something like if(isnull([TextField],'')='',... at the start of the expression or for a bit field being null or false if(isnull([BitField],false) = false,....