Infinite Hierarchy

March 29, 2011

Last week Hal Gumbert mentioned on Twitter that he was “working on a FileMaker quote to display and edit a BOM ( Build of Materials ) that can go 9 levels deep.”

Probably the most efficient and user friendly way to implement this is using a tree view with collapsible/expandable items. This is nothing new and very nice techniques have been already published by several FileMaker Developers, including SeedCode’s Hierarchy Template.

What captured my eyes (and mind) was Hal’s mention that he was “Planning for infinite…

This looked like the kind of challenge I like.

OK, as far as displaying the tree view is concerned, it’s also nothing new. An implementation based on recursive custom function (well, so it’s actually limited by the maximum recursion depth, but that’s high enough to be reasonably “infinite”) was published by Matt Petrowsky on the ISO FileMaker Magazine’s website. In my implementation, it looks like this:

But what I wanted to achieve was a bit more intuitive user experience. I wanted to be able to click on the triangle to expand or collapse the specific item and to click on the item name to edit it. Just like you can control the List view in Mac OS X’s Finder. That was the real challenge.

Since the triangle’s horizontal position is changing depending on the item’s depth, you cannot place the collapse/expand button on a fixed place. If you can limit the hierarchy depth, you can create buttons for every depth’s triangle position. But you cannot create unlimited number of buttons

So we discussed this with Hal briefly over Skype and interestingly got the same idea at the same time (actually Hal got the idea a few seconds sooner). The solution is what I like the most. Simple and clean.

You probably know that FileMaker Pro lets you choose between two different behaviors for a text field when you click on it. You can

  • select the entire contents
  • or place the cursor in the middle of the text right where you have clicked

The second option is the default behavior. Now think. FileMaker Pro has a function to return the current selection start and length – Get(ActiveSelectionStart). Since the tree display is actually generated as calculated text, including the triangle, you can allow entry into the text field and then check the cursor position. Then you can use an OnObjectEntry script trigger to do this right after clicking on the field. And that’s it. Simple and clean…

Now I can easily recognize whether the user clicked on the triangle, on the item name, or in the empty space on the left side of the triangle.

Do you want to try it out yourself? Just click here to download the sample file and open it using FileMaker Pro 10 or newer.

Unfortunately, this technique does not work with FileMaker Go. But that’s not so big deal because even iPad has quite limited screen space, so you will want to use another approach anyway.

Now imagine you have a text field 100 pixels wide and 100 pixels high. It contains 10 lines of text with 10 tabs on each line. Line spacing is set to 10 pixels and tabs are placed on every 10 pixels. How would you utilize this? :-)

Enjoy, and feel free to leave your comments or questions below.

 

{ 29 comments… read them below or add one }

Agnes Riley March 29, 2011 at 10:49 pm

HOnza, this is very cool. I like that you can name the items right there. I know people hate hearing this, but you know what else would be cool? If you could drag and drop to rearrange them to add some sort of priority or sort them alphabetically if you wish. I’ve always been fascinated by hierarchy display in FileMaker since I saw the first demo years ago.

Reply

Paul Spafford March 30, 2011 at 1:38 am

Definitely my favourite implementation, HOnza. Very clean, with a minimum of fields and table occurrences.

Reply

Chiyoko Yoshida March 30, 2011 at 2:07 am

Nice work. Enjoying the demo! :) Thanks!

Reply

Matt Petrowsky March 30, 2011 at 6:38 am

NICE! This is what I would call the ultimate in “WorkAround Pro” techniques. ;) As a suggestion for the UI obsessed – who might not like the field entry flash. I would opt for a single-click/double-click solution. A single click would simply determine the collapsed state and then take action only if the second click of a double-click was not detected.The double-click would, of course, head into the field to edit the value. Since navigating a hierarchy is not commonly tasked with editing field values, this seems like a cleaner UI solution to me. But hey, at least we can come up with stuff like this!Nice job!

Reply

Audrey Akhavan March 30, 2011 at 7:38 am

Thank you for sharing the demo file and blog details, Mr. HOnza! Beautifully simple and well-done.

Reply

Lee Hoong March 30, 2011 at 12:45 pm

Nice! Thanks for sharing.

Reply

Bruce Herbach March 30, 2011 at 2:26 pm

Very nice!! I implemented a Hierarchy setup for an Estimating database based on Matt’s technique. Over time I updated it to meet operational requirements and ended up with an index scheme very similar to your.The big part of this for my implementation was the setup to add,delete and move line items in the hierarchy. It’s always the details that get you. ;) Again very nice/clean implementation. Thanks for sharing.

Reply

HOnza Koudelka March 30, 2011 at 6:08 pm

Thanks everyone for your nice comments! I am glad you like the example.Agi: Drag&drop would be definitely great addition, but it’s also another challenge… ;-) First thing that comes to my mind is using container field with OnObjectEnter triger together with Matt’s double-click suggestion. Container allows drag&drop but disallows Get(ActiveSelectionStart). Therefore the double-click recognition. Any better ideas?

Reply

Tim Cimbura March 31, 2011 at 5:48 pm

This is a very clean implementation. Of course it looks simple after it’s done…but there’s a mastery in the simplicity. Thanks for putting it together and sharing!

Reply

HOnza Koudelka March 31, 2011 at 7:59 pm

Thanks, Tim. That’s why I like simplicity so much…And I also like how FileMaker developers keep inspiring me to think out-of-the-box.I have not seen that in any other development environment. Just creating a new FileMaker database and switching to the layout mode somehow starts up my imagination and creativity. What a great feeling! :-)

Reply

Hal Gumbert April 1, 2011 at 5:48 pm

Drag and drop could be done by using an icon on the right. Maybe the same one that Apple uses to reorder your favorites in the iPhone Phone app? You would need to drop the image on another container…

Reply

Justin Higley April 13, 2011 at 1:44 pm

Thanks for sharing! This is almost exactly what I was looking for to implement a bill of materials solution. Any pointers on getting it to work in a portal?

Reply

HOnza Koudelka April 13, 2011 at 4:29 pm

Hal: Would be nicer if it could be done like Finder… ;-)
Justin: Just replace the Extend Found Set and Constrain Found Set with a filtered portal. Create a “VISIBLE” calc field, and filter the portal by this field. Storing/restoring current portal row can be done just like storing/restoring current record.
[edited on Feb 20, 2012] But beware of speed issues. If you’re working with large data sets or slow network connection, keep in mind that portal filtering happens on the client side and all related records need to be transferred from FileMaker Server to FileMaker Pro even if they are not displayed.

Reply

Tamas Lassu May 11, 2011 at 9:19 am

This sounds great, just downloaded the file. Thanks!

Tom Lassu

Reply

Tim Anderson June 15, 2011 at 9:21 am

HOnza,

This is great, and I have taken it a step further by adding controls for indenting/outdenting and moving items up and don in the list. No plugins, just a couple of standard Custom Functions and some scripts.

I will email this to you so that you can share it with anyone who is interested.

See you in San Diego!

Tim

Reply

HOnza July 18, 2011 at 11:34 pm

Hey, I am preparing an even further improved version that will go into a new article. But ok, it seems to take some more time, so lat me share Tim’s modification here now, so that nobody has to wait…

http://www.24uSoftware.com/download/InfiniteHierarchy-TimAnderson.fp7

Enjoy, and stay tuned, new stuff is coming out soon ;-)

Reply

mike January 17, 2012 at 5:43 am

hello evryone, i’m biginer to filemaker. i found that tips very perceptual, but yet i’m not enough familiar with the FM terminology, i’m interesting to perform the Justin Higley’s goal he mentioned 5 posts before – to get it work in a portal. Mr. HOnza has mentioned some pointers i have not fully understand. If it’s posible anyone to share an work example. thanks very much.

mike January 17, 2012 at 1:32 pm

question specification: exactly i do not understend what means “to folder” – to folder a portal by a field.

HOnza February 20, 2012 at 12:49 am

Mike, the word “folder” in my comment was a typo that I have corrected now. I meant to filter the portal. Sorry for the confusion.

Dale Long February 19, 2012 at 5:59 pm

This is a really cool demo. Thanks for sharing! I hope to incorporate a similar technique in our solution. One question though…how does this technique fare with really large data sets, say 100,000+ records?

Reply

HOnza February 20, 2012 at 12:42 am

The total number of records in your table is not very important. The key is that you always only work with a small data set at a time. For example, when expanding one item requires extending your found set by just a few records, the whole operation is going to be very fast. Collapsing records is even better, because Constrain Found Set just hides the items you want to hide, leaving everything else intact. The key is to never replace the current found set with a completely new one only to expand or collapse items. Doing so is the most common mistake that leads to low performance.

Caps May 4, 2012 at 11:57 am

Hi,
It’s possible to have two hierarchies rules to the same records.
Example, if I’m creating an asset manager, I need to have a hierarchy to manage types and other hierarchy to manage localization, this is possible to do with this?

Thanks

Reply

HOnza May 12, 2012 at 2:11 pm

Sure. Any hierarchical view is simply a parent-child kind of relationship relating records in the same table. The techniques used to display the hierarchical data are the same regardless of how you decide which record is parent.

Jakson November 1, 2012 at 3:14 pm

Hey HOnza, love this technique – thanks for sharing – awesome.

Would I be right in saying that this is a single user method – i.e. as we’re setting a field (the “expanded” field) on the records we’re viewing to create the filtered view , 2 users cant use the same set of records at the same time and filter them how they want separately?

Thanks and best, Jak

Reply

HOnza November 1, 2012 at 9:44 pm

Yes, this example does not take multiple users into account.
There are different ways how to modify it for multiple users, which one is the right one for you depends on several factors.
The easiest is using variables and an unstored calculation, but that’s only good for small amount of records as it significantly slows down searches.

Vitalik December 6, 2013 at 11:51 am

will be wery well as finder

Reply

susan mcm tucker April 22, 2014 at 6:07 am

Have implemented this recently for a client that insists that they see their projects as an outline. Academics do LOVE their outlines, after all. I am using a UUID and found that I had to have a set of shadow serial numbers since the index broke with a long string of 36 characters. Not a problem.

I did make two changes which I thought I would share. To continue my general look/feel of the UI, I couldn’t use the font from the demo file, but the font I am using doesn’t have char(9). Changing the storage to unicode solved this problem, and didn’t break the functionality. [While I was there, I also made the default language English, since none of my users speak Czech.]

The other thing that disturbed me was the click_receiver UI. Even if there was no child, the arrows indicated that there was. I could easily see my users finding that a frustration, so I implemented a simple modification to this calculation:

RepeatText( Char( 9 );  x_out_depth ) &

Case(
x_out_expanded and not IsEmpty ( project_items__project_items_CHILDREN::x_out_pk_project_items_id ) ;
“▼”;

not IsEmpty ( project_items__project_items_CHILDREN::x_out_pk_project_items_id ) ;
“▶” ;

” ” )

&  

” ”

Hope this helps someone, two years after the fact.

*susan*

Reply

Al Delgado December 27, 2016 at 1:54 pm

On the response to Justin:
“Justin: Just replace the Extend Found Set and Constrain Found Set with a filtered portal. Create a “VISIBLE” calc field, and filter the portal by this field. ”

What is meant by a “VISIBLE” calc field? I would I create the calculation?
Thanks

Reply

chuy March 11, 2017 at 10:53 am

I downloaded latest file “http://www.24uSoftware.com/download/InfiniteHierarchy-TimAnderson.fp7
” with FM11 adv, and testing it, sometimes when i close a parent some children dont hide, , then open close stops working, even with a fresh download.
Is there a more current file to download?
Is there a way to import a Db with ID & Parent, and have it recalculate levels & All

Reply

Leave a Comment