If one of the data types (Text, Numeric, Currency, Date, or DateTime) hasn’t been assigned a value, it is considered to be null. The only exception is in the Text data type, where you can assign a value of “null” (string Text = null), which is different from an empty string with no assigned value.
Note: the following tests are usually used as the test argument in an If -Then -Else function described in the previous section. When used by itself, the test must be set to the appropriate formula type and will return either “true or “false”.
Null Test for Text –These formulas test a string of text for the specified condition. Returns either “True” or “False”.
Method | Example | Description |
Test for null | [text1]=null | Returns true if the string is a "null" string |
Test for empty | [text1].Equals("") | Returns true if the string is empty (no assigned value) |
In this application, we consider a Text data type to be null if it is in any of these three states: it has a value of “null”, is empty (no assigned value), or consists exclusively of white-space characters. Therefore, we’ll be using the following formula as the Null Test for Text:
Test for null, empty, or white-space | IsNullOrWhiteSpace([text1]) | Returns true if the string is either null, empty, or consists exclusively of white- space characters. |
Null Test for Numeric Values
If a numeric value has been declared, but hasn’t been assigned a value, it will be automatically assigned a default value of 0. But in certain cases where a null value is possible, the same method that was used for text should be used (IsNullOrWhiteSpace) to test for null. This formula will first convert the numeric value to a string ([num1].ToString()), and then use the null test method: (IsNullOrWhiteSpace):
Method | Example | Description |
Test for null | IsNullOrWhiteSpace([num1].ToString()) | Returns true if the numeric value hasn’t been assigned a value. |
If a number has a value of null, and is used in a formula, it will generate an error. The following formula will convert a null value to zero, so it can be used in the formula. If the number isn’t null, the same number is returned:
If(IsNullOrWhiteSpace([num1].ToString()),0,num1)
Null Test for Dates – If a Field Type set to “Date” isn’t set to a value, it will be stored as a null date in the database as 00-00-0001. Therefore, if the date string contains “001”, it is considered null.
Method | Example | Description |
If | If([date_of_response]].ToString(). Contains("001"), "Closed","Open") | Returns “Closed” if the given date is null. Otherwise “Open” is returned. Must be set as Formula–Text. |
If a date has a value of null, and is used in a date method it will generate an error.
Null Test for Documents – If a Field Type is set to “File Attachment”, this formula can be used to determine if a file is attached or not:
Method | Example | Description |
If | if(IsNullOrWhiteSpace([Doc1]), "Empty","Uploaded") | Returns “Empty” if a file hasn’t been uploaded yet for Doc1. Otherwise “Uploaded” is returned. Must be set as Formula–Text. |
Note that there are no formulas for documents other than a null test.