Hierarchical Portal Filtering within FileMaker Pro

By Andy Persons

This is the second of a three-part series on FileMaker Pro hierarchical portal filtering. You can find part one here.

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.

**This has been tested in FileMaker Pro versions 14-16

 ••Download the FileMaker Pro hierarchical portal filtering demo file.••

filemaker pro hierarchical portal filtering part 2 screenshot

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!


**This article is provided for free and as-is, use, enjoy, learn, share, leave awesome feedback, and experiment at your own risk – but have fun! eXcelisys does not offer any free support or free assistance with any of the contents of this blog post. If you would like help or assistance, please consider retaining eXcelisys’ FileMaker Pro consulting & development services.

About eXcelisys, Inc.: Founded in 2001, eXcelisys (www.excelisys.com) is an FBA Platinum Partner and FileMaker Certified developer organization. eXcelisys specializes in designing, developing, customizing, supporting, consulting, migrating, upgrading, fixing, and integrating of database solutions for Desktop, Mobile, and Web applications. Our core technology competencies are FileMaker Pro, FileMaker Go, and MySQL for database frameworks, along with FileMaker WebDirect, WordPress, MySQL, PHP, CodeIgniter, PostgreSQL, Joomla, Drupal, Magento, CSS, HTML5, and Javascript for web sites and web applications. Aside from providing eXcellent customer service, our goals are to use these technologies to intuitively automate your organization’s data solution needs seamlessly and flawlessly across the web, mobile, and desktop platforms. Contact eXcelisys today for a free estimate and consultation about making your business more efficient through intuitive and effective software automation. 866-592-9235 | [email protected]

eXcelisys, Inc. is an independent entity and this web site/information/blog post has not been authorized, sponsored, or otherwise affiliated with FileMaker, Inc. FileMaker is a trademark of FileMaker, Inc., registered in the U.S. and other countries.

About the Author:

7 Comments

  1. John May 10, 2017 at 5:48 pm - Reply

    Love it. Great Tip!

  2. Michael Ashley May 11, 2017 at 1:05 am - Reply

    Thank you, great timing!

  3. Dick Honing May 15, 2017 at 4:13 am - Reply

    Hi there,

    you may take it even one step further by combining your technique with the virtual lists technique. That way you do not have to add any logic to your data table.

    best regards – Dick Honing / separating data and interface since 1999 …

    • Andy Persons May 15, 2017 at 1:26 pm - Reply

      Hi Dick,

      Unfortunately, you couldn’t do that with this approach, since it requires stored, indexed fields. However, you could use it with Doug West’s Hierarchy Lite approach, which uses portal filtering.

  4. Liam Neville May 20, 2017 at 3:05 pm - Reply

    Hey Andy,

    This is great! I Had a couple of issues adapting to my filmmaker solution though.

    I was using Get(UUID), for the ID’s. And this can cause dashes – in the UUID. So WordCount considers a – as a space, so the word count is wrong. Wordcount being used in the arrows/level calculation.

    No biggie. Solution was changing the calculation of level to WordCount(Substitute(id_path_ancestors;”-“;””))

    Love you work by the way

    • Liam Neville May 20, 2017 at 3:14 pm - Reply

      Realised that I’d need to do the same with g_hierarchy. Might just be easier to change my Auto-Enter on the UUID to remove the dashes actually

      Cheers.

    • Andy Persons May 22, 2017 at 6:51 pm - Reply

      Thanks for the kind words, Liam!

      You could also use a calc such as PatternCount ( id_path_ancestors ; ” ” ) + 1

Leave A Comment

Website Content and Design

Website Content and Design

Copyright © eXcelisys, Inc., all rights reserved. No unauthorized use permitted; all derivative works must have prior approval. All content – including design and copy within the eXcelisys website is protected under international copyright laws. eXcelisys will prosecute ANYONE who borrows or steals any derivative or facsimile of the content and design of eXcelisys’ website without written approval or permission. Read More

Trademarks

Trademarks

FileMaker®, FileMaker Pro® and the referencing FileMaker® logos are trademarks of FileMaker, Inc., in the United States, the European Union and other countries. MySQL® and MySQL® Logos are registered trademarks of MySQL AB in the United States, the European Union and other countries. The PostgreSQL elephant logo “Slonik” is a registered trademark of the PostgreSQL Global Development Group. Read More

Privacy Policy

Privacy Policy

We will never give or sell your email address or contact information to anybody, for any reason. There are no eXceptions. We do not make our customer list available to anyone outside of eXcelisys at all, ever.

There is no way that submitting your information to eXcelisys will result in you receiving “spam” or other junk mail from eXcelisys if you have requested us not to do so. Read More