3 Replies Latest reply on Apr 8, 2014 11:29 AM by philmodjunk

    "Smart" Table Occurrences



      "Smart" Table Occurrences


           Hi, from googling around I'm pretty sure this isn't possible but I thought I'd check to see if anyone has some latest tricks.  I'm looking for something like Bento's smart collections, where a filtered table can be a source for a layout.

           I can get most of the way there by doing things like using find and found sets, but I'm particularly interested in something where a layout is used specifically for a subset of what is in a table.  For instance a "Songs" table has a "Recording Type" column, where one of the values is "Original", and I want a layout that is just for the Originals.  I could script it by making it always "Find" originals when I enter the layout, but that just seems kind of clunky, plus the "Show All" button could confuse things.  Are there any ways to, say, make a table occurrence always dynamically represent a subset of what would normally be in the table?


        • 1. Re: "Smart" Table Occurrences

               What you describe is a very common way to set this up. If you have FileMaker Advanced, you can remove the Show All and Show Omitted only script steps or set those menu options up to run your own scripts--which then can show only those records that are "original" or only those omitted records that are omitted original records.

               And a script Performed by the OnModeEnter trigger can constrain the found set to only those records that are Original.

               Another option is to put a portal on the layout with a relationship and/or portal filter that only lists Original records from the Recordings table.

               And if this is a Form View layout, you can set the OnRecordLoad trigger on a set of layouts so that the system automatically changes layout to the layout designed for that recording type as you step through the records in the found set, perform finds or Show all records....

          • 2. Re: "Smart" Table Occurrences

                 One thing I've realized after some more thought about this.  The reason I felt I needed this behavior is because I'm moving from Bento, and Bento forced me to have some denormalized schema in a couple of ways.

                 For instance, I had a "Recordings" table, where some columns were only relevant if it were a "Live Recording", and other columns were only relevant if it were a "Mixdown".  I had several columns that were relevant for either case (like "title" and "duration").  And then I had a column that indicated whether the record was a "Live Recording" or a "Mixdown".

                 In Filemaker, I was able to split this into three tables - a parent "Recording" table that had the common elements, a "Live Recording" table, and a "Mixdown" table.  The two child tables have a "recording_id" column to refer to the appropriate Recording record.

                 So now I can have layouts that are truly limited to just Live Recordings or Mixdowns.  The only downside is that if I want to add a new Mixdown record, I have to script it to add a new parent Recording record too, but that's not a big deal.

                 So the takeaway is that if someone feels they need a persistent layout against a subset of a table occurrence, it might just be an indication that they need to split their table.


            • 3. Re: "Smart" Table Occurrences

                   It's a classic data modeling challenge: You have a set of similar but not identical data. Do you cram it all in one table with a lot of fields and just leave some fields blank when they are not relevant for a particular record or do you link in "detail" tables with data specific to a particular type of item.

                   Neither approach is "right" or "wrong" arguments can be made in favor of each and the final call usually comes down to what the developer needs to do with this data and how dissimilar are the different types of items listed.

                   In both cases, you nearly always want one table where you have one record for every item of every type.

                   I once managed a database that documented purchase and manufacturing specifications for both bottle closure products and the materials used to produce them. One record might be for a metal screw cap, another for an aluminum foil "capsule" folded over the top of a wine or juice bottle and a third might be for the coil of material used to make it. We needed that unified table for inventory and production management purposes, but used detail tables to manage the specification details unique to a particular type of product or material.