From time to time you need a simple hierarchical representation of data that is also sortable.
Usually I build such solutions with HTML and/or jQuery, because you can get great looking
and flexible solutions.
Unfortunately there are no layout elements in Filemaker that even come close to such a result.
But recently I needed a pure Filemaker solution that could map a single level hierarchy with as
few fields and relationships and scripts as possible.
So what do you need to create a simple hierarchy in Filemaker?
We use (like everyone else) first of all 2 tables (a main table and a subtable for the data) and
a portal in which the records are displayed hierarchically.
The approach I want to describe now is based on the fact that the subrecords can be groups or entries.
To distinguish between these two types I split the sorting into 2 individual fields.
This makes it easier for us to search and adjust entries and whole groups later when re-sorting,
as well as to apply different graphical elements or formatting to distinguish them.
In the main table ‘Main’ there is actually only a field ‘id’ (indexed, auto-enter serial) to create
the header record for the data, ‘g_closed’ to hold the ids of the closed groups and
‘g_sort’ (number, global) which we need for the sort script.
The subtable has the following fields:
‘id’ (indexed, auto-enter serial)
‘idMain’ (indexed)
‘sort1’
‘sort2’
‘title’
The field ‘idMain’ connects main and sub table.
‘title’ field is the displayed row in the portal.
Field ‘sort1’ contains the group number and is incremented by one for each group.
‘sort2’ is the sorting within a group and is incremented from 0 (which is the group record itself).
So we can easily recognize the group record because it always contains a 0 in ‘sort2’.
We now connect the tables with only 2 relations to be able to include all functions.
The first relation is used for reordering by a script.
The second relation is used for display in the portal and is then sorted by ‘sort1’ (sorting of groups)
and ‘sort2’ (sorting within groups).
If we now design an interface for the Hierachy it might look like this.
If individual groups are now closed, the IDs are written in the ‘g_closed’.
By a simple filtering of the portal now the entries records of the respective group are hidden.
We use here an extra field ‘idParent’ so that the filtering of the portal remains also after a re-sorting of a group.
We show or hide the graphic elements and colors depending on whether the field ‘sort2’
contains a 0 or a number >0.
So we can design the functions with some short scripts:
Sort
Set Variable [ $Sort1 ; Value: Sub Filter::sort1 ]
Set Variable [ $Sort ; Value: If ( Sub Filter::sort2 > 0 ; Sub Filter::sort2 ; Sub Filter::sort1 ) ]
Show Custom Dialog [ "Einfügen an Position" ; $Sort ]
If [ Get ( LastMessageChoice ) = 2 ]
Exit Script [ Text Result: ]
End If
Freeze Window
If [ $Sort1 < $Sort ]
Set Variable [ $Sort ; Value: $Sort + ,2 ]
End If
If [ Sub Filter::sort2 > 0 ]
Set Field [ Sub Filter::sort2 ; If ( $Sort > Sub Filter::sort2 ; $Sort + ,1 ; $Sort - ,1 ) ]
Set Field [ Main::g_sort ; $Sort1 ]
Commit Records/Requests [ With dialog: Off ]
Go to Related Record [ Show only related records ; From table: “Main~Sub#g_sort” ;
Using layout: “Sub Filter” (Sub Filter) ; New window ]
Sort Records [ Restore ; With dialog: Off ]
Replace Field Contents [ With dialog: Off ; Sub Filter::sort2 ; Serial numbers ]
Close Window [ Current Window ]
Else
Set Variable [ $Sort1New ; Value: $Sort ]
Set Field [ Main::g_sort ; $Sort1 ]
Commit Records/Requests [ With dialog: Off ]
Replace Field Contents [ With dialog: Off ; Main~Sub#g_sort::sort1 ; $Sort1New - ,1 ]
Go to Related Record [ Show only related records ; From table: “Sub Filter” ;
Using layout: “Sub Filter” (Sub Filter) ; New window ]
Sort Records [ Restore ; With dialog: Off ]
Set Variable [ $sort1 ; Value: 0 ]
Replace Field Contents [ With dialog: Off ; Sub Filter::sort1 ;
Let ( [ $sort1 = If ( Sub Filter::sort2 > 0 ; If ( $sort1 = 0 ; 1 ; $sort1 ) ; $sort1 + 1 ) ] ; $sort1 ) ]
Close Window [ Current Window ]
End If
Refresh Portal [ Object Name: "Portal" ]
Add Child
Set Variable [ $mainId ; Value: Main::id ]
Set Variable [ $id ; Value: Sub Filter::id ]
Set Variable [ $sort1 ; Value: Sub Filter::sort1 ]
Set Field [ Main::g_sort ; $sort1 ]
Commit Records/Requests [ With dialog: Off ]
Set Variable [ $sort2 ; Value: Max ( Main~Sub#g_sort::sort2 ) + 1 ]
New Window [ Style: Card ; Name: "Temp" ; Using layout: ; Top: -5000 ]
Go to Layout [ “Sub Filter” (Sub Filter) ; Animation: None ]
New Record/Request
Set Field [ Sub Filter::idMain ; $mainId ]
Set Field [ Sub Filter::idParent ; $id ]
Set Field [ Sub Filter::sort1 ; $sort1 ]
Set Field [ Sub Filter::sort2 ; $sort2 ]
Commit Records/Requests [ With dialog: Off ]
Close Window [ Current Window ]
Refresh Portal [ Object Name: "Portal" ]
Add Parent
Set Variable [ $mainId ; Value: Main::id ]
Set Variable [ $sort1 ; Value: Max ( Sub Filter::sort1 ) + 1 ]
New Window [ Style: Card ; Name: "Temp" ;
Using layout: ; Top: -5000 ]
Go to Layout [ “Sub Filter” (Sub Filter) ; Animation: None ]
New Record/Request Set Field [ Sub Filter::idMain ; $mainId ]
Set Field [ Sub Filter::sort1 ; $sort1 ]
Commit Records/Requests [ With dialog: Off ]
Close Window [ Current Window ]
Refresh Portal [ Object Name: "Portal" ]
Delete
Show Custom Dialog [ "Alert" ; "Delete ?" ]
If [ Get ( LastMessageChoice ) = 2 ]
Exit Script [ Text Result: ]
End If
If [ Sub Filter::sort2 = 0 ]
Set Field [ Main::g_sort ; Sub Filter::sort1 ]
Commit Records/Requests [ With dialog: Off ]
Go to Related Record [ Show only related records ; From table: “Main~Sub#g_sort” ;
Using layout: “Sub Filter” (Sub Filter) ; New window ]
Delete All Records [ With dialog: Off ]
Close Window [ Current Window ]
Else
Delete Portal Row [ With dialog: Off ]
End If
Refresh Portal [ Object Name: "Portal" ]
Toggle
Set Field [ Main::g_closed ; If ( FilterValues ( Main::g_closed ; Sub Filter::id ) = "" ;
List ( Main::g_closed ; Sub Filter::id ) ; ListAminusListB ( Main::g_closed ; Sub Filter::id ) ) ]
Refresh Portal [ Object Name: "Portal" ]
Toggle All
If [ Main::g_closed ≠ "" ]
Set Field [ Main::g_closed ; "" ]
Else
Set Field [ Main::g_closed ; UniqueValues ( List ( Sub Filter::idParent ) ; 2 ) ]
End If
Refresh Portal [ Object Name: "Portal" ]
So we can create a completely sortable hierarchy with Filemaker instruments with very few fields, scripts and relationships.
This small database is nowhere near as flexible (and also only single-level) as an HTML-supported solution,
but for simple purposes it can certainly be implemented without much effort.
If you like to have an open version of the file, just send me an email.
You have any questions? Feel free to send me an email, I’ll try to answer as soon as possible.