9

The Knowledge Article: 000006007 - Custom indexes for an organization to help improve performance lists the following in relations to indexes on formula fields:

Note:
Formula field can also be indexed since Summer 12. The following rules have to be true in order to index a formula field:

  • The formula contains fields from a single object only (not relationship fields).
  • The formula field doesn't reference any non-deterministic functions (e.g. SYSDATE).
  • The formula field doesn't reference any non-supported fields for including in indexes. This list isn't documented anywhere specifically (there are lots of special cases), but in Spring 12(176), createdById was non-supported, but in Summer 12 (178), it is supported. Same story for CreatedDate.
  • The formula field contains references to Primary Keys (e.g Id)

If one of the condition above is false, the "Add index" does not display on the BT of the field.

Referencing a date function that returns a different result each day is a clear example of something that would be non-deterministic.

What else would make a formula non-deterministic and therefore not available for a custom index?

Improve this question

1 Answer 1

Reset to default
5

I found the following in Force.com SOQL Best Practices: Nulls and Formula Fields:

Here are examples of common non-deterministic formulas. Force.com cannot index 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, TotalOpportunityQuantity, ExpectedRevenue, IsClosed, IsWon
    • Case: ClosedDate, IsClosed
    • Product: Product Family, IsActive, IsArchived
    • Solution: Status
    • Lead: Status
    • Activity: Subject, TaskStatus, TaskPriority

Also in Working with Very Large SOQL Queries - Custom Index Considerations for Selective SOQL Queries

  • A custom index can't be created on these types of fields: multi-select picklists, currency fields in a multicurrency organization, long text fields, some formula fields, and binary fields (fields of type blob, file, or encrypted text.) New data types, typically complex ones, are periodically added to Salesforce, and fields of these types don’t always allow custom indexing.
  • You can’t create custom indexes on formula fields that include invocations of the TEXT function on picklist fields.
Improve this answer
2
  • 1
    I assume "other entities" would include system variables like $Profile or $UserRole.
    – Jeremy Nottingham
    May 10, 2015 at 23:00
  • @JeremyNottingham I'm not sure where they fall under the current documentation. They do seem non-deterministic to me. A users profile or role could easily be changed outside of the underlying record.
    – Daniel Ballinger
    May 11, 2015 at 1:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .