I have a few Zen principles I try and adhere to when building FM solutions, including keeping my tables as skinny as possible and avoiding data tables that look like Swiss cheese when shown in table view. Accordingly, I try to only define a dedicated “attribute” field (such as DOB for a contact entry) in one of my data tables if I know that there will always (or very nearly always) be a value stored in that field for every record.
To help manage the need to add new fields and also avoid Swiss cheese in my tables, I’ve been using “Attribute Tables” (which are also inherently skinny) to store things that previously would have required a table-fattening dedicated field, and sometimes (like a “Notes” attribute) in multiple tables, regardless of how consistently it was populated.
The Attribute Table is dead simple, having just a primary key field, a foreign key field which can then be linked to one or more data tables, the usual record creation/modification time stamped meta data fields, and then a field for the name of the Attribute and another field to hold the value for that Attribute Name. The valid Attribute Names are shown to the user in a drop-down which are controlled by a account with administrator privileges, so that a regular user can’t just enter anything willy-nilly into the Attribute Name field.
So far, so good! Using an attribute table like this in a “typical” CRM type solution has saved me from having to create over 200 individual fields, and making a real difference in performance as solutions have grown over time. A problem, though, is that unless I control the process further, there is a risk that a user could inadvertently enter a value for the same attribute for a given entity record if they’re just selecting from a regular pop-up or drop-down menu or portal. In other words, if a user entered a birthdate for the Birthday attribute, I didn’t want the interface to show the ability to enter a birthday value a second time for that given record. I know I can control for duplicates using field validation, and there are times I do that (although field validation can have its own challenges in FM). But I didn’t want to confuse the user in the first place.
What I needed was a “dynamically dwindling value list” that only ever allowed a user to select from an individual item in a dynamic value list one time. FileMaker has actually made this possible going back to at least version 7 by using a combination of a calculation field and an additional relationship using a “<>” operator to exclude records where a related attribute table. But it always felt a little kludgy to me and had issues with scale, and when FM 12 came out the ExecuteSQL function made it possible to instead control the dwindling value list using a global field and a an eSQL call that uses the “not in” clause to omit records that had already been entered into the attribute table.
I’ve already discovered that one has to be very careful using FM’s eSQL “not in” functionality. I now break up my eSQL calls to avoid having to dynamically define the “not in” values and that does make a performance difference. But – and here is my problem – despite the benefit of being skinny, Attribute Tables have the potential to get really big in terms of record counts. With more than 10,000 or so attribute records, doing a “not in”, however indexed and well-optimized, will slow things down when a user tries to enter a new attribute value. The older pre-v12 “FileMaker” way runs into similar performance roadblocks at scale.
I worry I’ve hit a dead end with trying this “dwindling value list” technique. I’ve tried to make things more scalable by having multiple Attribute Tables (i.e. one for “People” tables, another for “Organizations” tables, etc.), and this helps even if it requires more tables to be defined, but if a “People” table, for example, has more than 10,000 records, it’s going to have multiple times that amount in related values in an Attribute Table, so even if I “atomize” my Attribute Tables, the performance bottleneck is never really that far away.
Has anyone else confronted this? My searching so far hasn’t turned up much that’s helpful as it relates to scaling this technique.
Sorry for the length of my post. Any insight would be greatly appreciated!