Good Sir William’s Heirs

by Will M. Baker
Posted in All, FileMaker, Techniques

Demo file:  Sir_Williams_Heirs_fp7

That old Bard, good Sir William, is nearing the end of his life. Having already provided well for his five children, he now seeks to divide his literary empire among his eleven grandchildren:

FamilyTree 1

But Sir William is a mischevious fellow, and wants only to reward grandchildren who meet certain criteria:

  • Eldest (among all the grandchildren)
  • Youngest (among all the grandchildren)
  • Eldest (among the children of the eldest child)
  • Youngest (among the children of the eldest child)
  • Eldest (among the children of the youngest child)
  • Youngest (among the children of the youngest child)
  • Middle (among all the grandchildren)
  • Middle (among the children of the middle child)

How will he sort it out, and have some fun in the process? Luckily, Sir William has a copy of FileMaker Pro 11 Advanced on hand. But how should he go about it? Should he script it? No, he’s written enough scripts in his lifetime, and he’s grown weary of them. Should he construct a web of conditional relationships on the relationship graph? Also a tiresome prospect, as he’s woven together so many complex relationships in his time that it will take scholars centuries to unravel the mess.

Okay, then. How about a portal filter? Yes, a portal filter, that clever addition to FileMaker Pro 11 that moves all the idiosyncratic, one-off relationship logic up to the presentation layer. Within short order, Sir William has built a beautiful FileMaker layout to display which grandchildren shall be favored:

Problems 2

But how did he do it? First, he created a table structure to match his family tree:

Graph 3

Both the CHILD and GRANDCHILD tables are sorted ascending by birth year. That should take care of any issue with family being added to the database in an inconvenient order.

SortOrder 4

The primary problem to be overcome is that when traversing the relationship graph, FileMaker only respects the last sort order in its path. Thus when moving from PARENT directly to GRANDCHILD, FileMaker will respect the sort on GRANDCHILD, but ignore the sort on CHILD. So, it’s up to portal filters to identify the favored grandchild.

  1. Eldest Grandchild. This one is easy. Because of the sorted relationship, Sir William doesn’t even need a portal. He can put the GRANDCHILD::NAME field directly on the layout, and it will display the eldest grandchild.

  2. Youngest Grandchild. Sir William needs a portal for this, but not a portal filter. He uses a descending sort on the portal to override the relationship’s sort, and presto magico, the youngest grandchild floats to the top.

  3. Eldest Grandchild of the Eldest Child. Okay, enough easy stuff; if Sir William wanted easy, he would have stuck to writing tragedies (they practically write themselves!). Time for a portal filter. The calculation below leverages the solution to problem #1 by constraining the list to just those grandchildren who descend from the eldest child. From there, the relationship’s built-in sort order floats the eldest to the top.

GRANDCHILD::ID_CHILD = CHILD::ID

  1. Youngest Grandchild of the Eldest Child. Same as above, but we apply an overriding descending sort to the portal, as in problem #2.

  2. Eldest Grandchild of the Youngest Child. Things are getting a bit trickier. We can use the same logic as in problem #3, but only if we can identify the youngest child. Sir William has been playing around with repeating functions, though, and happens to know that Last ( repeatingField ) works on more than just repeating fields!

GRANDCHILD:ID_CHILD = Last (CHILD:ID )

  1. Youngest Grandchild of the Yougest Child. Same as above, but we apply an overriding descending sort to the portal, just like in problem #4, which is just like problem #2. What a breeze! Why didn’t Sir William write all his plays using FileMaker syntax?

  2. Middle Grandchild. Okay, finally, a problem worth solving. The pre-sorted relationship takes care of putting all of Sir William’s grandchildren into the correct order, so all that remains is to grab the middle value out of the list. The two nuances here are to recast the retrieved value as a number, since it was converted to text by List ( ); and to devise a method for dealing with odd/even numbers of grandchildren (more could be born before his will is executed, after all!).

GRANDCHILD::ID = GetAsNumber ( GetValue ( List ( GRANDCHILD::ID ) ; Ceiling ( ValueCount ( List ( GRANDCHILD::ID ) ) / 2 ) ) )

  1. Middle Grandchild of the Middle Child. Now Sir William is scratching his skull cap. It’s easy enough to identify the middle child, using the same technique as in problem #7. But then how does he constrain the list of grandchildren to just that child before performing an additional operation on the list? Sure he could create some fields to pass blobs of text around, but the whole idea is to use portal filters! What about one of those fancy SQL plug-ins, so he can just employ a schmancy Select statement? Oh, wait, this is the 1600s; plug-ins haven’t been invented yet; it’s going to need to be a purely FileMaker solution.

Finally Sir William settles on a custom function to help him massage the two lists of data (one of children, one of grandchildren). He’s disappointed that he couldn’t fit all the logic into the portal filter, but at least the solution exists solely in the calculation engine. First, his custom function: ParallelLists ( list1 ; list2 ; list1Value )

Case (

IsEmpty ( list1 ) ; “” ;

List (

Case ( GetValue ( list1 ; 1 ) = list1Value ; GetValue ( list2 ; 1 ) ; “” ) ;

ParallelLists ( RightValues ( list1 ; ValueCount ( list1 ) – 1 ) ; RightValues ( list2 ; ValueCount ( list2 ) – 1 ) ; list1Value )

)

)

Then his portal filter calculation:

Let ( [

childIDList = List ( CHILD::ID ) ;

childID = GetValue ( childIDList ; Ceiling ( ValueCount ( childIDList ) / 2 ) ) ;

grandchildIDList = ParallelLists ( List ( GRANDCHILD::ID_CHILD ) ; List ( GRANDCHILD::ID ) ; childID ) ;

grandchildID = GetValue ( grandchildIDList ; Ceiling ( ValueCount ( grandchildIDList ) / 2 ) )

] ;

GRANDCHILD::ID = grandchildID

)

And with that, Sir William may rest easier, knowing that his FileMaker portal filters are ready to adapt to any changes in his family tree. May his grandchildren grow up to be as nefarious as he!

Here’s a quick summary of the techniques employed for each problem:

Solution 5

Leave a Reply