8 Replies Latest reply on Sep 21, 2016 11:26 PM by jorber

Calculate hierarchy in a self-related table

Hi

I have a self-related table in FileMaker called Store used for keeping track of stored objects as described below. I have tried, but failed, to create a dynamic calculation field, Hierarchy, that shows all parent positions for each object in the table and are updated when  Name is changed. Does anyone have a description how to solv this?

 __kf_StroreID _ParentStore_fk Name Hierarchy 10 0 House 1 Hoiuse 1 20 10 Room 1 Hoiuse 1 / Room 1 30 20 Cabinet 1 Hoiuse 1 / Room 1 / Cabinet 1 40 30 Shelf 1 Hoiuse 1 / Room 1 / Cabinet 1 / Shelf 1 50 30 Shelf 2 Hoiuse 1 / Room 1 / Cabinet 1 / Shelf 2 101 40 Item 1 Hoiuse 1 / Room 1 / Cabinet 1 / Shelf 1 / Item 1 102 40 Item 2 Hoiuse 1 / Room 1 / Cabinet 1 / Shelf 1 / Item 2 103 40 Item 3 Hoiuse 1 / Room 1 / Cabinet 1 / Shelf 1 / Item 3 201 50 Item 21 Hoiuse 1 / Room 1 / Cabinet 1 / Shelf 2 / Item 21 202 50 Item 22 Hoiuse 1 / Room 1 / Cabinet 1 / Shelf 2 / Item 22

• 1. Re: Calculate hierarchy in a self-related table

please post the calculation. is it stored/unstored?

beverly

• 2. Re: Calculate hierarchy in a self-related table

Hi,

it looks like you need a recursive custom function that terminates when _ParentStore_fk = 0.

Should be fun.

• 3. Re: Calculate hierarchy in a self-related table

Check that ...

I think that you can do it something like this ...

Hierarchy = If ( _ParentStore_fk = 0 ; Name ; SelfByParent::Hierarchy & "/" & Name )

You can't create recursion in a field definition.

• 4. Re: Calculate hierarchy in a self-related table

Actually, it IS possible to use recursion in a calculation field. It requires defining the field first as a data field and then changing it to a calculation so the field is available for the needed self reference when you set up the calculation, but it can be done. I prefer not to as I prefer to use a CF for the recursion.

In addition to using recursion to map out this hierarchy, a script could be used to build this information by looping or recursing through the relationship to build the list.

1 of 1 people found this helpful
• 6. Re: Calculate hierarchy in a self-related table

moyerdave wrote:

Check that ...

I think that you can do it something like this ...

Hierarchy = If ( _ParentStore_fk = 0 ; Name ; SelfByParent::Hierarchy & "/" & Name )

You can't create recursion in a field definition.

Using Phil's suggestion, and throwing caution to the wind, I tested the following:

Hierarchy = If ( _ParentStore_fk = 0 ; Name ; SelfBy_kf_store::Hierarchy & " / " & Name )

You can use recursion in a field definition.

• 7. Re: Calculate hierarchy in a self-related table

It occurred to me that you need a "top-of-hierarchy" flag.  That would be a number field, 0 or 1, with 1 indicating that this record is at the top of the hierarchy.

Instead of parent key = 0, it would look like If ( flag_TopOfHierarchy; Name; ...

1 of 1 people found this helpful
• 8. Re: Calculate hierarchy in a self-related table

Thank you David

I finally got it to work using

If ( not _ParentStore_fk ; Name ; SelfByParent::CalculatedHierarchy & " / " & Name ) and the right direction of relation:

Storage::_ParentStore_fk    =    SelfByParent::__kf_StoreID