A cross-object formula is a formula that spans at least two related records and references or merges fields on those records. These would be records in lookup or master-detail relationships.
Here are some good-to-know tips:
- You can reference fields from related records that are up to 10 relationships away.
- You can’t use this type of formula to create a default value for a field.
This is because a formula field does not contain data: Instead it carries out a calculation on values in other fields. In the process of creating a record – the relationships you wish to make use of in the formula won’t exist until the record is first saved.
- Cross-object formula fields are useful for exposing information from hidden pages or fields.
There may be information on records that the user does not have access to due to organisation wide defaults, profile object settings, sharing rules or field level security. A user without access to those records may need certain information from them.
For example, a donkey sanctuary may have a custom object for volunteer details which holds sensitive personal data. There may be staff who work with the volunteers who need to see contact details (but not the rest of the data available to a volunteer manager). To allow information on the volunteer detail object available to other users, create a further custom object that uses cross object formula fields to display the volunteer contact details.
- Tip: You can’t include an object as the lookup field in a formula. You have to reference a value on a record of that object. Use the object’s ID field or other field.
For example, Account.Owner is invalid because it references the User object directly. Account.Owner.Firstname or Account.OwnerId will be valid references for your formula.