Searching on lookup and formula fields in Salesforce list views

List views are one of the most useful Salesforce features: they help to sort, prioritize, analyze and filter records.

List views are also searchable:

However, certain types of fields including lookup and formula fields are excluded from searching.

Even though both types of fields can still be used for filtering, search may be desirable in many cases as it is more efficient to use than configuring filters.

For example, for cases linked to addresses via a lookup, it is useful to search cases by typing an address.

Browser search is also not sufficient as new records are loaded on demand as as lists are scrolled.

Overview of lookup and formula fields

Before looking into possible workarounds, let’s take a look what makes lookup and formula fields different:

Lookup fields enable relationships between objects, similar to foreign keys in a relational database. Their values are stored in records as Salesforce object ids but they are displayed in list views using object names. They are also rendered as hyperlinks enabling quick navigation in Salesforce.

Formula fields are calculated based on other field values — they are ‘virtual’ and are not stored in the database. Formulas do not have to return numeric values — they can be string values, e.g., a concatenation of several text/numeric fields.

Enabling search for a single field

Let’s assume that there’s a subscription (Subscription_c) object with a lookup field to Product2 (Product__c) representing customer subscriptions (e.g., a 24 month contract for Internet + TV) and users want to be able to search for product names (e.g., ‘Internet + TV’) in subscription list views.

The solution proposed in this post relies on the following:

• All simple text fields are included in search
• The fields do not have to be displayed in a list view to be used in search

One of the possible solutions is to:

• Add Product_Name__c text field where product name is stored
• Add trigger on subscription to copy product name to the field
• Add trigger on product to update Product_Name__c when product name changes

Including more data

The approach above can be taken further to:

• Include more data, e.g., Product_Name__c could include product code, product family, etc.
• Combine multiple fields into a single search to simplify trigger code maintenance — e.g., in the subscription example above the search field could contain both customer and product names

At Nextian we typically combine all needed fields into a single Search_Text__c field on an object.

A trigger for updating its value for a subscription could look as follows:

trigger UpdateSearchText on Subscrption__c(before insert, before update) {

  // Search field length (255, the maximum length of a string field that can be used in search):
  //
  final Integer MAX_SEARCH_TEXT_LENGTH = 255;
  final Integer MAX_PRODUCT_CODE_CHARS = 15;

  // Build APEX maps for lookup objects (in this case accounts and products):
  //
  Set<Id> accountIds = new Set();
  Set<Id> productIds = new Set();

  for (Subscrption__c s: Trigger.New) {
    accountIds.add(s.Account__c);
    productIds.add(s.Product__c);
  }

  Map<Id, Account> accountMap = new Map<Id, Account> ([SELECT Id, Name FROM Account WHERE Id IN: accountIds]);
  Map<Id, Product2> productMap = new Map< Id, Product2> ([SELECT Id, Name, ProductCode FROM Product2 WHERE Id IN: productIds]);

  // Finally build a search field for each subscription:
  //
  for (Subscrption__c s: Trigger.New) {

    // When using formula fields in the search text, recalculate formulas for accurate values:
    //
    s.recalculateFormulas();

    // Build the search string:
    String accountName = accountMap.get(s.Account__c) != null ? accountMap.get(s.Account__c).Name : '';

    String productDetails = '';
    Product2 product = productMap.get(s.Product__c);
    if (product != null) {
      productDetails = product.Name + ' ' + product.ProductCode.left(MAX_PRODUCT_CODE_CHARS);
    }

    s.Search_Text__c = accountName + ' | ' + productDetails;

    // Ensure that the search text does not exceed max length:
    // (as an alternative, trimming can be applied to each of the name fields individually
    s.Search_Text__c = s.Search_Text__c.left(MAX_SEARCH_TEXT_LENGTH);
  }
}

The example above is simple but collecting data for search can involve more query levels, e.g., search on opportunity for users who prepared syncing quotes (opportunity → syncing quote → prepared by).

The limiting factors are governor limits and the maximum of 255 characters in the search fields (the latter can be addressed by creating multiple search fields).

Conclusions

For some use cases, out-of-the-box Salesforce search functionality may have limitations affecting user experience and/or productivity.

These limitations can be typically overcome with additional programming (APEX triggers, dedicated Lightning components, etc.).

Nextian is a vendor of Quote-to-Cash (QTC) software for cloud and communications helping providers accelerate growth and increase customer lifetime value.

Contact us today to find out how we can help you!