*Hierarchical Portal Filtering within FileMaker Pro
<https://www.excelisys.com/fm-tips/filemaker-pro-hierarchical-portal-filtering-2-2/>*By
Andy Persons

This is the second of a three-part series on FileMaker Pro hierarchical
portal filtering. You can find part one here
<https://www.excelisys.com/fm-tips/filemaker-pro-hierarchical-portal-filtering-2-1/>
.

This FileMaker Pro hierarchical portal filtering file demonstrates how to
provide high-performance sorting of a hierarchical portal by various
fields, while maintaining the hierarchy. **Been tested in FileMaker Pro
versions 14-16.

*Download the FileMaker Pro hierarchical portal filtering demo file.
<https://www.excelisys.com/wp-content/uploads/2017/05/HierarchyRevisited-part-2.fmp12.zip>*

[image: filemaker pro hierarchical portal filtering part 2 screenshot]
<https://www.excelisys.com/wp-content/uploads/2017/05/HierarchyRevisited-part-2.fmp12.zip>
Static Approach

The most straightforward approach would require creating an index “path”
calculation for every field that you might want to sort by. For example, if
you wanted to sort by name, you’d have to create a sort_name field that
contained “Plants Vegetables Roots Carrots”. Each additional field that you
wanted users to be able to sort by would need a similar field. Also,
whenever a record was edited, all descendant records would need to have
their sort fields updated.
Dynamic Approach

For very small data sets, you could make the path field an unstored
calculation and have each record calculate up the path chain dynamically.
However, this would very quickly become so slow as to be unusable.
Dynamic Approach with High-Performance

This FileMaker Pro hierarchical portal filtering file demonstrates an
approach that combines the light footprint of a dynamic approach with
performance closer to that of a static approach.

Here’s the overview:

   - A dictionary field (sort_dictionary) of record id’s and sort field
   values is created in the data table.
   - Before sorting, all related values of sort_dictionary are copied into
   a global variable named $$sort_dictionary using the List() function.
   - A sort key field (sort_key) is calculated on the fly by replacing IDs
   in the record’s id_path with the corresponding sort value from
   $$sort_dictionary and padding it with zeros if it’s a number field.
   - For example, an id_path with “000001 000034 000052 000076” would
   become “Plants Vegetables Roots Carrots” on the fly, using the values from
   $$sort_dictionary
   - The portal is then set to sort by sort_key.
   - Because the data from both id_path and $$sort_dictionary have already
   been downloaded to the local machine, sort_key can calculate very
   quickly.

Next Installment: Hierarchy Advanced 2.3

Drag-and-drop arbitrary sorting and reassignment
*Stay Tuned!*

- Christo
Founder/President
eXcelisys, Inc.
www.eXcelisys.com <http://www.excelisys.com>
574-273-5805