How to make a Collapsible List in FileMaker List View

Yesterday, a client asked me to make a collapsible list in FileMaker. Essentially, he wants a subsummary list that only shows the “child” data upon request. In my head I initially thought “FileMaker can’t do that!” which turned into “there’s got to be some way” so I told him I would try.

I played around with a subsummary report to see if I could trick it into displaying what I wanted. The problem with the subsummary report is that the “child” data is in the body part definition. So once you omit all the child records, the parent goes away. I need a way to keep the parent record, and hide the children.

I was in the middle of compiling an email to my client telling him it’s not possible, and giving him a list of other work-arounds (popup windows, tooltip, ect) when it hit me: The only way to make this work is to have all the data in one table.

In this example list, I want to show dog breeds (parent), and then under each breed a list of all of its possible variations or mixes(children). So I made a shadow table that has foreign key to the breed table, a foreign key to the variation table, a sort field, and an action field.

Collapsible List TOs

My list layout is based on the shadow table, and all the fields on the layout are coming from either the breed or variation table. Therefore each record in the shadow table will have the _kf_Breed field populated or _kf_Variation field populated, but not both.

So here’s the kicker: when I press the disclosure triangle for the first time, I am actually importing the “child” variation records into the shadow table. When I press the disclosure triangle for the second time, I am deleting the “child” variation records from the shadow table. The sort field is to make sure that the children always sort with the parent. The action field is either “create” or “delete” and is toggled by the disclosure triangle script. The action field is only used in the parent shadow records.

Here’s how it looks:

List View – Parent Records

List View – Parent and Children Records

Collapsible List Screen Demo

Data Disclosure: The cost range for each dog mix is fictional. Although I really do think my yorkipoo is worth that much, if not more.

In a nutshell, here’s what the disclosure triangle script does:

• The catalog ID and the action are passed in as parameters, separated by a return character
• If action is empty because it’s the first time the parent’s arrow has been clicked, set it to “create”
• If the script parameter is empty, exit script
• Create a popup window to do the work
• If action is “create”, go to the Variation layout, find on the foreign key breed ID, import the Variation primary keys into the Shadow table
• If action is “delete”, go to the shadow table, find on the shadow records to delete, and delete them
• Update the original shadow parent record to toggle between “create” and “delete”
• Close the popup
• Select the original window
• Show all records
• Sort Records by the SortOrder field

Other tips to note:

• Use a clear button object (with no script attached) and add conditional formatting to the children rows
• Put the “go to detail” button behind the button object so it’s hidden for the child records
• The disclosure triangle itself is a calculation in the shadow table of type container

A few Cons:

• If you have a lot of children in your list, your importing may have performance issues, especially over the WAN
• You will have to refresh the parent list. I added a “refresh list” button to update the “parent” records, and I also update the list in my navigation script. If you have a lot of parent records that need to be updated, you can also update them in a nightly server script

Overall, pretty slick, Aye? The link to download the sample file is below. I’d be happy to answer any questions via comments or email:  mencarnacao@soliantconsulitng.com Thanks for reading! Makah Encarnacao

Download the Collapsible List Sample File – ZIP