I'm beginning to add more logic to my InBizness package to handle found set a bit better. This type of granular control would likely be "a touch" too much for consulting projects (that bill out at standard rates of $100 + per hour). However for open framework applications such as InBizness or in house projects, these tweaks have a better return on the time invested to build them.
In this particular point, I want scripts that go to found sets to have the logic to ...
- show an error message if there are no records in the found set to go to and exit gracefully
- go to the form view layout if there is only one record in the found set
- go to the list view layout if there are more than one record in the found set
Now InBizness has over 50 tables and many of them have both form and list views (there are a few join tables that I can ignore for these purposes). Manually coding each decision branch would be a bit overwhelming. On the surface, using the feature to go to a layout by a calculated value seems the way to go. I could choose to go to the layout by its name or by its position in the layout order. Now going by the layout order is not an option in a database that is under constant construction and InBizness is definitely one of those. Manually typing in the layout name again and again and again for each script step that will use this found set option is also not an option because of the time involved to create and maintain.
Here are some posts about the Go To Layout script step that you mind find interesting, if you are unfamiliar with the various aspects of it.
Any Go To Layout script step that uses the layout name in the calculation has an element of danger involved. If you ever change the name of the layout, the script step will be broken. This is one reason why I have stayed away from this method in my open framework products. In an open framework, I sell the package open to modification by the owner. So it is quite possible they might get themselves into a problem by doing something as seemingly innocent as changing a layout or table name. I wanted to have one place were we can synch things up if these names are changed.
So I decided to create a custom function that will look at the found count of records, then look at the base table and then return the layout needed. Here is a small sample of the custom function I wrote ...
Get ( FoundCount ) = 1 and Get ( LayoutTableName ) = "Campaign"; "FORM_Campaign";
Get ( FoundCount ) > 1 and Get ( LayoutTableName ) = "Campaign"; "LIST_Campaign";
Get ( FoundCount ) = 1 and Get ( LayoutTableName ) = "Client"; "FORM_clients";
Get ( FoundCount ) > 1 and Get ( LayoutTableName ) = "Client"; "LIST_clients";
The actual calculation had over 50 lines and will likely grow when I integrate a planned manufacturing module to InBizness. I should also note that this function has even one more element of danger because it looks at the name of the table occurrence (not the table name itself) that is assigned to the layout. I wouldn't have even tried to do this if I wasn't working with a solution that totally embraces the Anchor / Buoy relationship design method.
Now it is challenging to maintain a rock solid naming convention for a robust database that has been evolving for well over a decade. That being said, I would feel comfortable that my InBizness product would compete quite well against any similar robust CRM product. In my case, I had only one thing that would drive the anal television detective Adrian Monk crazy. Sometimes I named my tables singular and sometime I named them plural. Does this matter to any degree from a database performance aspect, certainly not. However, it did require an extra 30 minutes while I checked and tweaked my custom function.
Now you might wonder how I'm going to handle the advance notice of no records in the found set. Well, this is going to be something I address in the header (a name often given to script steps at the top of a script) of the overall script itself. I try to add that as a posting later on.
That being said, my calculated go to layout script step will be at the bottom of the script. This is because I will have to go to the appropriate layout first and then analyze what my base table occurrence is and what my found set count is.
So I'm going to wrap up this blog post for now but I'll try to update you on the overall progress of this implementation plays out.
More info about the author and FileMaker in general, contact me at firstname.lastname@example.org.
© 2009 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.
Looking for a robust FileMaker CRM template to manage your business? Check out the InBizness product line at /solutions.html.