Get Creative with Cross Object Formulas

When working with records of an object, that are linked to another object using either a Lookup or Master-Detail relationship, accessing information from one linked record to another may be needed based on certain business requirements.

Luckily, Salesforce provides a few options for that, both from a child record to parent and parent record to child, using OOTB (out of the box) features such as below:

In this article, we will focus primarily on Cross Object Formula fields.

What are Cross Object Formulas?

A read-only field that derives its value from a formula expression you define. The formula field is updated when any of the source fields change.

Formula Return Type:

• Checkbox
• Currency
• Date
• Date/Time
• Number
• Percent
• Text
• Time

How do they work?

Cross Object formulas pull information from the Parent Record of the Child Record the field is created on. A special syntax needs to be followed for Standard Parent or Custom Parent Objects that allow the formula to pull detailed values on Child Record to display as read-only.

Cross Object Formulas are useful to avoid writing custom trigger logic to fetch information from related records and updating on text/number fields etc.

Standard Syntax: Write Object name followed by ‘.’ and the field name.

Account.AccountNumber

 

Custom Syntax: Write Object name, replace ‘__c’ with ‘__r’, followed by ‘.’ and the field name.

Client_Account__r.AccountNumber

 

How to Create a Cross Object Formula

Cross Object formulas are regular formula fields and are create the same way. The only difference is that information is fetched from the related parent record.

1. From Setup, click Object Manager, then select the Object you want to create formula on.
2. Click Fields & Relationships.
3. Click New.
4. Select Formula as the Data Type.
5. Click Next, and fill in the details.
   • Add Field Label
   • Select Formula Return Type
6. Click Next.
7. Select the Advanced Formula tab, and add the formula in the box.
8. Click Check Syntax to verify there are no errors.
9. Click Next.
10. Ensure the Visible checkbox is checked so all profiles are selected, and click Next.
11. Click Save & New.

Creative Ways to Use Cross Object Formulas

1. Go Mutli Level Deep

Cross Object formulas allows you to go 10 objects deep. This becomes very useful when you want to reference grand parent or great grand parent information or even information from owner records.

2. Access System and Organization Information

Access Organization or System Information to quickly calculate important information without having to fetch information by custom means.

3.  Utilize User, UserRole or Profile for help with criteria

Information such as logged in User or role/profile can assist in using these in your formula criteria to meet certain conditions. For Example:

4. Fetch Custom Metadata Values and Custom Settings

Another amazing feature of cross object formula is that all your custom metadata and settings become available to use as criteria for your formula or exact values even.

Limitations of Cross Object Formulas

• Cross-object formulas that reference currency fields convert the value to the currency of the record that contains the formula. If the referenced currency field is from a custom setting, the field value isn’t converted to the record’s currency.
• Salesforce allows a maximum of 10 unique relationships per object in cross-object formulas. The limit is cumulative across all formula fields, rules, and lookup filters. For example, if two different formulas on opportunities reference two different fields of an associated account, only one unique relationship exists (from opportunities to accounts).
• You can’t reference cross-object formulas in roll-up summary fields.
• In cross-object formulas, you can’t reference merge fields for objects related to activities. For example, merge fields for contacts and accounts aren’t available in task and event formulas.
• In cross-object formulas, you can’t reference fields from contacts through person accounts. For more information, see Using custom formula fields with person accounts.

Conclusion

Cross Object formulas are very handy for quick calculations and leveraging information from other records. There are certain limits on number of spanning relationships that need to be taken account when creating cross object formulas. So make sure you are aware of them.