Salesforce: Formula field best practice

Formula field in Salesforce is great feature for user, it simplify complex filter conditions for SOQL queries, list views, and reports.

Let's say we have CloseDateAge formula field, we can use it in report filter CloseDateAge range = Current FY.

Or in SOQL
SELECT Id, Name
  FROM Opportunity
 WHERE CloseDateAge__c >  14
   AND CloseDateAge__c <= 21

Everyone is happy because they can get the data they need with very little effort, right? Wait a minute.

Indexes
By default, formula fields don’t have indexes. So when you create and use a formula field such as CloseDateAge with very large objects (say, objects that have more than one million of records), the SOQL queries and reports using the formula field as a filter might perform slower because your queries and reports have to perform full scans to find target records.

You can workaround by create trigger or workflow to update a field. It's work, but it requires overhead and is not intuitive.

Since the Winter ’13 release, you have been able to contact salesforce.com Customer Support to create a custom index on a formula field, provided that the function that defines the formula field is deterministic. But, Salesforce cannot index formula fields that:

• Reference other entities (i.e., fields accessible through lookup fields)
• Include other formula fields that span over other entities
• Use dynamic date and time functions (e.g., TODAY, NOW)

A formula is also considered non-deterministic when it includes:

• Owner, autonumber, divisions, or audit fields (except for CreatedDate and CreatedByID fields)
• References to fields that Force.com cannot index:

• Multi-select picklists

• Currency fields in a multicurrency organization

• Long text area fields

• Binary fields (blob, file, or encrypted text)

• Standard fields with special functionalities:

• Opportunity: Amount, TotalOpportunity, Quantity, ExpectedRevenue, IsClosed, IsWon

• Case: ClosedDate, IsClosed

• Product: Product Family, IsActive, IsArchived

• Solution: Status

•    Lead: Status

•    Activity: Subject, TaskStatus, TaskPriority

The CloseDateAge field above is a perfect example of a non-deterministic formula field that Force.com cannot index. So what can you do when you can’t create an indexed formula field to hide complex filter logic? Go back to square one: Put your filter logic in your filters!

So, in the SOQL above, change it to
SELECT Id, Name
  FROM Opportunity
 WHERE ((CloseDate = LAST_N_DAYS:21)
   AND  (CloseDate < LAST_N_DAYS:14))