Overview
These scenarios will walk you through the various ways to handle null or empty values within a formula.
Scenario 1 - [entity.fieldname.0]
You have a formula set to execute on the Opportunity entity and you need to reference the Estimated Revenue field. However you cannot be sure that the field will have an actual value so you need to set a default of zero if a null or empty value is encountered. You can do this by adding the default as follows:
[opportunity.estimatedrevenue.0]
Scenario 2 - Default string in Find function
You have a formula that is using the FindValue() function to retrieve the job title of the current user. But if no job title has been set then we need to set the default text to the following 'No job title set':
FindValue('systemuser', 'systemuserid', WhoAmI(), 'jobtitle', 'No job title set')
Function signature: FindValue('entitylogicalname', 'inputfieldname', 'inputfieldvalue', 'outputfieldname', 'defaultvalue', 'nolock', 'cacheenabled')
The same procedure can be used for other functions such as FindSum(), FindMax(), FindAvg(), etc.
Scenario 3 - Default number value in Find function
You have a formula that is using the Avg() function to retrieve the value of all opportunities but there may be no opportunities so instead of returning null you would like to return zero. This can be achieved as follows,
FindAvgFD('GetOpportunityAverage', 'estimatedrevenue', '0')
Function signature: FindAvgFD('formuladetailname', 'fieldname', 'defaultvalue')
The same procedure can be used for other functions related to Sum, Max, Count, etc.
Scenario 4 - Default null value in Find Function
Internally Nortth52 uses the '?' symbol to identify a null value and you can use this in your FindValue / FindRecord functions as the default value, thereby returning a null value.
FindValue('account','parentaccountid', 'addc3395-5d15-45a1-8d6b-2a2a12c31c40', 'accountnumber','?')
Function signature: FindValue('entitylogicalname', 'inputfieldname', 'inputfieldvalue', 'outputfieldname', 'defaultvalue', 'nolock', 'cacheenabled')
Scenario 5 - Blank strings
You are using a new field called nickname in an email that displays a '?' result when not populated.
[new_car.new_nickname.#]
This will insert a blank string into the email if the nickname field is blank.
Scenario 6 - Manually check for null values
You can use the ContainsData() function in your N52 formulas to manually check for blank values.
ContainsData([account.description]) would return False if the description field was blank.