Challenges with Hierarchy

[cannibalflea]

I’ve been struggling with more complex hierarchies in fibery for a long time and I haven’t found any good solutions yet. Last week, I hit another challenge and I am struggling to see how I can achieve the result I am looking for:

Use Case

I have a list of Sites, each of which have a “Site Type”. There are several primary Site Types as well as some sub/secondary Site Types. This is modeled through self relations where a Site Type can be a Sub Type of another:

When data is added, the structure looks like:

Challenges

While this approach works to show the sites nested in the site type structure. It doesn’t appear possible to create a view where you are able to see all the sites associated with the first level of the hierarchy.

So in this example, being able to see 3 sites under Type A, 2 sites under Type B and 1 site under Type C. You are able to filter out the sub-types from the list but then you are not able to see the individual sites.

Things I’ve Tried

Use two relation fields

I tried to setup two relations fields to the Site Type:

• Site Type: first level type (using Relation Filter to show only first level types)
• Sub Type: second level type (using Relation Filter to only show those sub types related to chosen site type)

While only applicable to cases where there are two levels (e.g. Parent Type and Sub Type), this addressed challenge 2 rather nicely.

However, when I try to generate a list view, fibery automatically choses the first level Site Type relation and nests the sites accordingly. It doesn’t seem like fibery allows users to choose which Relation field should be used in the list.

Use a lookup to get the sub type sites

I tried to get the sites from the Sub Types using a lookup:

This works for a 2 level structure. However, I am unable to use the lookup field to create a view to see all the sites under level 1.

Use two different databases

This is the option I want to avoid as it results in more databases and confusion. However, to work around fibery’s limitations, I even tried this option.

I created a Sub Type database and related it to the Site Type and Sites:

My Site Types are now just the top level:

While this works well for the instances where the sites have both Site Type and Sub Type, it creates issues where there is no Sub Type (e.g. Site Type C):

Am I missing a workaround to make this work? Or is this just impossible with fibery?

[Chr1sG]

Here’s my suggestion, assuming I have understood the need correctly:

Add a formula in the Site type db as shown, and save it:

Then edit this field and update it as shown:

Here’s the formula:

If(IsEmpty([Site Type]), [Public Id], [Site Type].[Top level ID])

FYI, it needs to be done in these two steps, since you can’t refer to a formula field until it has been created and saved for the first time.
This will now work recursively for any number of type levels to find the public ID of the top level type.

Now create a lookup in the Site db as shown below:

Finally, create an auto-relation to link a Site to the top level Site type based on matching this new lookup field to the public ID of the site which is the top level.

Make sure you give this relationship meaningful field names, so that you know whether you are looking at directly linked sites or indirectly linked sites:

You can now use this relation in all the normal ways (smart folders, list views etc.):

[cannibalflea]

This is ingenious !! I should have posted sooner

[ccollins]

Creating a blank formula first and then editing it so you can reference itself is genius. Definitely taking note of this

[cannibalflea]

@ChrisG needs to write a fibery for dummies book with all these neat tips and tricks