Back

The Sales Trac database in New Jersey, like a horseshoe crab, remained as it had been long ago, superficially connected to other files in the DCC Solution systems but within its own parameters still a flat-file database of the sort you could have built in FileMaker 1 back when Macs had little 9-inch screens. What finally precipitated the update was the proposal: the sales staff that sent the proposals out to the customers wanted a single form to display all the different printing and binding and paper and size options, and even multiple product descriptions, on a single proposal, with different prices for different quantities for all the different variations the customer was potentially interested in.

Building the new Sales Trac 3-file related architecture was only the start, though. The admin assistant wanted the order of the categories to change depending on the number of variables per each category, so that if there was only one binding option it would be listed at the top:

BINDING: Saddle stitch

But if there were multiple binding options to be priced out, to list other categories first and then put BINDING OPTION #1 down lower, listing what that option was, and then the prices and quantities there. And if, in addition to different options for binding, there were different options for types of paper, the paper options should be listed first if there were more binding options than paper options (i.e., list each paper option, then, within it, each binding option and the prices and quantities for each); whereas, if the paper options outnumbered the binding options, to list them in the other order.

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 FileMaker does not have an intrinsic routine to sort *a list of fields* according to how many unique entries are in each field.

(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. 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.

It's not that there was 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. This was another of the more interesting challenges I've faced while working on this solution.

Note the reversal of order between binding and papers parameters on the second described product of the proposal on the right: