Views:

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.

Did you know?

North52's Decision Suite works across the Power Platform

The ultimate no-code decision suite for your Power Platform projects.

North52 Turbocharges Microsoft Dynamics 365 CDS, Dynamics Portals, Power Apps and Power Automate to help you deliver your projects faster and easier.