"Lookup" calculated field


I have a nest data structure for customer accounts which means we have a parent, child and grand child relationship all in the same table.


account id | parent id | name | finance id
1 | null | client A | 1234
2 | null | client B | 4567
3 | 1 | client A.1 |
4 | 2 | client A.2 |
5 | 4 | client A.2.1 |

In the example above

  • Client A has a total of 4 accounts
  • Client B has 1 account

What I want to do is to link the finance ID from Row 1 to rows 3,4,5. The relationship is based on the “parent id”.

What’s the best way to do this?

You can derive parent account name from name of your accounts using Split(). Then get corresponding finance id from parent.
Please see below screenshot for your reference.

I have created calculated field “parent_finance_id” using formula below.

I hope this helps!

Tejal Gohil