Hello, I would like to store the location of electronic components in my workshop. As I have mixed furniture, boxes in boxes, drawer cabinets, etc, I thought a simple “Location” table with parent row would suffice:
ID Parent ID Name
----------------------------------------
1 Red Cabinet
2 1 Drawer A1
3 2 Front Compartment
Now I can record that a component is stored in location ID 3. However showing only “Front Compartment” is meaningless without parent names. How can I concatenate an unlimited number of parent’s names into a string for each row? I would like a field storing the string “Red Cabinet, Drawer A1, Front Compartment” for ID 3, for example.
I have tried using a formula to create a Display Name field concatenating parent Display Name fields however it throws a circular reference error. This sounds like a common problem but I can’t think of the keywords to find the right solution, please can someone point me in the right direction?