I’m a FileMaker geek by profession. FileMaker, in case anyone doesn’t already know, is a commercial database environment, one in which the same software that lets you design the database also runs the resultant database for everyday use. Its strong points are ease of use, flexibility, rapid application development, and gentle learning curve.
On the weak points side, It’s considered “middle iron” — a good environment for multi-user groupware for a few dozen concurrent users and hundreds of thousands of records, and relational (if atypically so in design) but not “big iron” like Oracle or SQL systems with hundreds of billions of records and several hundred thousand concurrent users. It’s not more than sufficiently fast in ordinary use and just doesn’t scale up to the really big levels.
FileMaker, like any database, is very good about sorting records in a table based on the values in one or more fields (# of widgets, date of birth, etc). It’s actually also quite good about ascertaining the number of related entries through a given relationship (# of Purchase Order Line Items for a given Purchase Order, for instance), or, by setting up and referencing its own value list items, the number of UNIQUE related values in a given field through a given relationship (# of unique vendors on Purchase Order LIne Items for a given Purchase Order, for instance).
But how does one sort a list of fields according to how many unique entries are in each field?
We have an admin assistant, call her Betty, who wants the sales database to generate proposals to clients listing prices for all the variations on possible print-shop orders that they’ve asked to have priced out for them. Betty wants the proposal to contain a grid with quantities in columns and along the left the rows broken down into categories and sub-categories — kinds of paper stock, number of press colors, flat & final sizes, types of binding, and so on.
She wants the order of the categories to change depending on the number of variables per each category, though.
To do Betty’s incredible flexible proposal, I need to know for any given job-estimate writeup record if there are more kinds of paper than there are kinds of binding being estimated for the customer — if there are more different types of binding, the proposal would break prices down by papers first, and then within each paper by the more options available for binding. (And there are FOUR such categories: size, paper, binding, and press colors !!!).
Getting the one with the minimum (or maximum) unique values was easy thanks to FileMaker’s “min” (or “max”) function — see how I got Param A in the script, that wasn’t too bad.
The fun started when I went on to ask FileMaker to tell me which category was “next to minimum”, or first runner-up in minimum-ness. Or, to put it a different way, how to tell FileMaker to set Param B to the category that has the minimum number of values out of all categories except for the category that has the minimum number of values — a category whose name was trapped in the field Param A previously in the script.
It’s not that there’s no way to do it, there are probably a couple hundred ways to do it, but one likes to avoid exhaustive “If w > x and w > y and w > z and the name of w is not already in Param A, then w but otherwise if x > w and w > y and x > z and the name of x is not already in Param A…” formulations.
Check out how I pinned down Param B (and Param C and Param D)… hee hee hee!
I know it’s just FileMaker and not real coding with pointers and declared variables and stuff like you have with C++ or even VB, and yeah FileMaker is easy to use, but dang it sometimes I do earn my salary.