The Gearbox Blog

We write so you can learn from us and we can learn from you.

Sep 15 2014

TAGS filemaker virtual list

Infinite Virtual Hierarchy Reporting Module

by Rohit Roy |

When one our clients wanted a collapsible hierarchy report, I was excited to implement a feature I knew could be done because I had seen it before on the Internet. Collapsing records is not a native feature in FileMaker so some workarounds are required. I had two examples to work with (that I knew about at the time). The first one is by Makah Encarnacao of Soliant Consulting. She uses a shadow table that has relationships to the parent and child tables. The parent records always show and you can toggle the parent records to insert or remove child records. She details this on her blog post, which can be found here.

The second is by Honza of 24U Software and can be found in the comments section of Makah’s blog post. He takes a different approach and uses collapsible subsummary parts. He has one subsummary part for departments, and, instead of a body part, he uses a subsummary part to display the records! 

The body-like subsummary part summarizes a calculation field that only populates when you want to show the records.

Both solutions have their pros and cons, but for me, the biggest con was how long it took to expand/collapse records. Every time I clicked the expand/collapse button it had to sort the records. I was working off a server and this report had a lot of fields it had to be sorted by. Also, ideally the list would be collapsible at four different levels, which was going to add a good deal of complexity. 

So I decided to build my own solution...

I built a virtual list by looping through the found set. In addition to every record getting it’s own line, each hierarchy gets its own line every time its value changes. I store this list in a global variable I named $$master. Then, I use a table with blank records to display a duplicate of $$master named $$virtual. When the expand/collapse button is clicked, lines in $$virtual are either removed or inserted from $$master. It works fast enough for my purposes, but if you have a need for it try it for yourself.

Pros

  • 1 Table and no new relationships
  • Just sort once before building the list, no sorting afterwards
  • Decent speed when collapsing/expanding
  • It doesn't use ExecuteSQL so it can be used in FileMaker 11 solutions without hiccups.
  • No use of custom functions

Cons

  • It can take a while to build the list. It could take much longer depending on the number of hierarchies, the number of records, and the amount of information you want it to store. And, of course, if you are working remotely.
  • When you expand a hierarchy, it shows all the levels below it expanded. It doesn’t remember the expand/collapse state of a level once it is removed from the second list.
  • There will be a bunch of blank records at the bottom once you start collapsing levels. It’s pretty easy to change the found set every time you expand/collapse, but then you lose your screen position, which for me is unacceptable. I have yet to find a way for FileMaker to remember your exact screen position, but I’m working on it.
  • All hierarchies are displayed as records. This inflates the found count and also doesn't allow the layout part formatting that comes with sub-summary parts.

comments powered by Disqus