How can you check for null and or if the field exists in a UI only formula field

  • 1
  • Question
  • Updated 2 years ago
  • Answered
I have a ui only field that has a formula that returns true false. One of my checks is looking to see if the field is not null. When I inspect the model on the page the null check is not working because actually Salesforce did not return a field to check with to begin with. So how can I check if the field even exists?

IF({{RPS_External_Transfer_Amount__c}} >= 20000000,true,IF({{RPS_Expected_Number_of_Participants__c}}>=1000,true,IF({{RPS_Expected_Number_of_Participants__c}} < 10 && {{RPS_External_Transfer_Amount__c}} >= 40000 && ({{RPS_Deposit_Based_Commission_First_Year__c != null }} && {{RPS_Deposit_Based_Commission_Renewal_Yrs__c != null }} && {{RPS_Deposit_Based_Commission_Transfer__c != null }}),true,IF({{RPS_MVE_Amount__c}}>=100000 || ({{RPS_MVE_Amount__c }} > {{RPS_External_Transfer_Amount__c}} * 0.05) ,true,false))))
Photo of Jaime

Jaime

  • 1,220 Points 1k badge 2x thumb

Posted 2 years ago

  • 1
Photo of Zach McElrath

Zach McElrath, Employee

  • 49,056 Points 20k badge 2x thumb
I see several problems with your current formula field syntax --- the following are invalid:

{{RPS_Deposit_Based_Commission_First_Year__c != null }}
{{RPS_Deposit_Based_Commission_Renewal_Yrs__c != null }}
{{RPS_Deposit_Based_Commission_Transfer__c != null }}

should be

{{RPS_Deposit_Based_Commission_First_Year__c}} != null
{{RPS_Deposit_Based_Commission_Renewal_Yrs__c}} != null
{{RPS_Deposit_Based_Commission_Transfer__c}} != null

See if that changes anything.
Alternatively you could check for something like > -1, which would be false if the field is either null or not present but true if the field has a 0 value.
Photo of Jaime

Jaime

  • 1,220 Points 1k badge 2x thumb
thank you Zach...that was it!
Photo of Jaime

Jaime

  • 1,220 Points 1k badge 2x thumb
actually I take it back...that was not it.

Here is the updated formula...

IF({{RPS_External_Transfer_Amount__c}} >= 20000000,true,
IF({{RPS_Expected_Number_of_Participants__c}}>=1000,true,
IF({{RPS_Expected_Number_of_Participants__c}} < 10 && {{RPS_External_Transfer_Amount__c}} >= 40000 && ({{RPS_Deposit_Based_Commission_First_Year__c }} >-1 || {{RPS_Deposit_Based_Commission_Renewal_Yrs__c }} > -1 || {{RPS_Deposit_Based_Commission_Transfer__c}} >-1),true,
IF({{RPS_MVE_Amount__c}}>=100000 || ({{RPS_MVE_Amount__c }} > {{RPS_External_Transfer_Amount__c}} * 0.05) ,true,false))))

When any of these fields are empty or not in the model the formula still evaluates to true which it should not.
RPS_Deposit_Based_Commission_First_Year__c
RPS_Deposit_Based_Commission_Renewal_Yrs__c
RPS_Deposit_Based_Commission_Transfer__c
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
I believe your formula is written so that if any of those fields return true, the formula will return true. That's what the OR statements mean.

If you want to return true only if all of the three fields are present, use AND statements.
Photo of Jaime

Jaime

  • 1,220 Points 1k badge 2x thumb
thanks Matt....I actually want do want it to return true if any even one of those fields is null
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Try testing just this part of your formula, and see if you're getting the results you want:

IF(({{RPS_Deposit_Based_Commission_First_Year__c }} >-1 || {{RPS_Deposit_Based_Commission_Renewal_Yrs__c }} > -1 || {{RPS_Deposit_Based_Commission_Transfer__c}} >-1),true,false)

In words, this formula says:

If ((Field1 is NOT null) OR (field2 is NOT null) OR (field3 is NOT null)) return true. otherwise, return false.

If that's not what you want, you need to change your formula.