Tip #1349: Calculated field from related record

When Marius Agur Hagelund Lind poked me with this tip I was like, duuuude, who doesn’t know that? Turns out, not a single person in my family knew that so here it is.

All yours, Marius

Here’s an oldie but goodie.

Did you know that you can use related records to calculate a field value?Well you can! Simply type in the relationship name followed by a period, then the field name of the related record.

Tada!

Nick Doelman pointed out that you can reference a calculated field on the parent record, and that could reference a calculated field on it’s parent. So you could cascade it however far you wish.

Which means you can build a “top level value” if it’s a self-referencing field.

Finale

Ever wanted to use “set value” in a business rule to get a value from a parent record? Well here’s how:

  1. Create a calculated field, name it something silly like: _internal_calculated_reference_fieldname
  2. Reference the parent record
  3. Create a business rule, set the actual field value to the calculated value
  4. Optional: set the condition to be “state = active”. This way it won’t update when the record has been deactivated.

Tîpp Jäår $0.02 + VAT

This is something I use quite often to bubble up the values, e.g. show up a parent company’s phone number and email on a contact record without the need for a quick view form.

One caveat if you start including calculated fields that use related records, into the views. They cannot be used to sort the results.

Cover photo by unsplash-logoJosh Durham

2 thoughts on “Tip #1349: Calculated field from related record

  1. Jaan says:

    There are a few limitations to consider, calculated fields can have a maximum chain length of 5, and can only span up to 2 entities (can’t reference a field on another entity that references a field on another entity), according to the documentation.

    https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/define-calculated-fields#considerations

    In practice, Ive definitely been able to define one that spans 3 entities before, but the source calculated field became uneditable until I removed the dependency on it temporarily

    • Absolutely, Jaan, those considerations are a good bedtime read 😉 – we’re getting into the territory of deterministic vs non-deterministic functions (like NOW()).

Leave a Reply

Your email address will not be published. Required fields are marked *