can formula field be used in lookup filter salesforce

Guri Bee

3 min read

·

17-10-2024

1

0

Share

Can Formula Fields Be Used in Lookup Filters in Salesforce?

Salesforce's lookup filters are incredibly powerful, allowing you to refine your data views based on specific criteria. One common question that arises is whether you can use formula fields within these filters. While the answer isn't straightforward, it depends on the type of formula field you're using.

Let's break down the scenarios:

1. Formula Fields Based on Standard Fields: YES

When your formula field is based solely on standard fields from the related object, you can absolutely use it in a lookup filter. This works because Salesforce can directly evaluate the formula based on the field values of the related record.

Example:

• Object: Contact
• Lookup Field: Account
• Formula Field (on Account): "Is Active" (Formula: IF(IsActive, TRUE, FALSE))

You can create a filter on the Contact object that filters for Contacts where the related Account's "Is Active" formula field is "TRUE".

Why this works:

Salesforce can readily evaluate the formula "Is Active" based on the standard field "IsActive" on the Account object.

Code Example from Github:

// Example from a Salesforce Apex trigger:
Account acc = new Account(Name = 'Example Account', IsActive = true);
insert acc; 

Contact con = new Contact(FirstName = 'John', LastName = 'Doe', AccountId = acc.Id);
insert con;

// Now you can filter for contacts where the Account is "Active":
List<Contact> activeContacts = [SELECT Id FROM Contact WHERE Account.Is_Active = true];

Original Source: Github - Salesforce Apex Trigger

2. Formula Fields Based on Other Formula Fields: NO

This is where things get tricky. If your formula field relies on another formula field within the related object, you cannot directly use it in a lookup filter. Salesforce doesn't currently support the evaluation of nested formula fields within filters.

Example:

• Object: Account
• Lookup Field: Opportunity
• Formula Field (on Opportunity): "Is Won" (Formula: IF(StageName = 'Closed Won', TRUE, FALSE))
• Formula Field (on Account): "Won Opportunities" (Formula: COUNT(Opportunities.Is_Won))

You cannot create a filter on Account where "Won Opportunities" equals 1 because Salesforce can't directly evaluate the "Is Won" formula field within the "Won Opportunities" formula.

Solution:

You'll need to explore alternative approaches:

• Create a workflow rule/process builder: Trigger an update on the related object (Account in this case) to populate a standard field based on the "Is Won" formula field. You can then filter on this newly created standard field.
• Use a custom field: Create a custom field on the related object (Account) to manually track "Won Opportunities" and update it accordingly.
• Utilize SOQL queries: While not directly a filter, you can use SOQL queries with the WHERE clause to filter for data based on your specific formula field logic. However, this will require more coding.

3. Formula Fields Based on Relationship Fields: NO

If your formula field uses a relationship field from the related object, it cannot be used directly in a lookup filter. This is because the relationship field itself is a lookup field, making it a nested dependency.

Example:

• Object: Account
• Lookup Field: Contact
• Formula Field (on Account): "Contact's City" (Formula: Contact.City)

You cannot filter for Accounts where "Contact's City" is "New York" because Salesforce can't evaluate the "Contact.City" field directly within the lookup filter.

Solution:

Similar to scenario 2, you can explore workflow rules, custom fields, or SOQL queries to achieve your filtering requirements.

Key Takeaway:

While Salesforce provides a lot of flexibility with formula fields, their use within lookup filters is limited. Understanding these limitations can save you time and effort. You can find alternative solutions by incorporating workflow rules, custom fields, or SOQL queries to achieve the desired filtering outcomes.