Wednesday, June 26, 2019

Create a Customer Saved Search to Show All Customers where Multiple Custom Checkboxes are Unchecked

Scenario: In the customer record, there are three checkboxes. What the customer wants is to pull up any customer record with all these three checkboxes unchecked.

The challenge is that a checkbox can have three values; true, false and a null value (not true or not false).

To resolve the issue, the following formula can be used in the Results tab of the custom search:

Field = Formula (Numeric)
Summary Type = SUM
Formula = CASE WHEN ({customfield1}='F' OR NVL({customfield1},'1')='1') AND ({customfield2}='F' OR NVL({customfield2},'1')='1') AND ({customfield3}='F' OR NVL({customfield3},'1')='1') then 1 else 0 end

Note:  Replace 'customfield1', 'customfield2' and 'customfield3' with the internal IDs of the custom checkbox fields of the account being used.

No comments:

Post a Comment