0 Replies Latest reply on Aug 4, 2016 12:54 PM by jtu

    Complex join: show all rows in a results table

    jtu

      I’m working on a database to allow clients to select product attributes (via a filter) and see which products are available to meet their needs. After filtering, I want to show a table of products meeting those criteria with links in each row connecting to details about that specific combination.

       

      One difference from most databases is that instead of the storing information about available, existing products, this database comprises products that are made to order, so it needs to be able to reflect all possible products that are viable combinations of the various attributes. For simplicity, let’s say we have the following:

       

      Product Sizes:

      Small

      Medium

      Large

       

      Colors by Size:

      Small – Blue

      Medium – Blue

      Medium – Red

      Large – Red

       

      Shapes by Size:

      Small – Round

      Small – Square

      Small – Rectangle

      Medium – Round

      Large – Square

      Large – Rectangle

       

      Material by Size:

      Small – Bronze

      Small – Silver

      Medium – Bronze

      Medium – Silver

      Large – Bronze

      Large – Silver

      Large - Gold

       

      For simplicity, let’s assume that color, shape, and material options are independent of each other – e.g., any color that works for a given size will work with any shape and material that works for that size. (There are some rare exceptions to this, but I believe I can handle those via calculations in the table.) Given this, these are all the viable product options:

       

      Size

      Color

      Shape

      Material

      Small

      Blue

      Round

      Bronze

      Small

      Blue

      Square

      Bronze

      Small

      Blue

      Rectangle

      Bronze

      Small

      Blue

      Round

      Silver

      Small

      Blue

      Square

      Silver

      Small

      Blue

      Rectangle

      Silver

      Medium

      Blue

      Round

      Bronze

      Medium

      Blue

      Round

      Silver

      Medium

      Red

      Round

      Bronze

      Medium

      Red

      Round

      Silver

      Large

      Red

      Square

      Bronze

      Large

      Red

      Rectangle

      Bronze

      Large

      Red

      Square

      Silver

      Large

      Red

      Rectangle

      Silver

      Large

      Red

      Square

      Gold

      Large

      Red

      Rectangle

      Gold

       

      And if the client filtered to Size = Large and Shape = Rectangle, they should see this:

       

      Size

      Color

      Shape

      Material

      Large

      Red

      Rectangle

      Bronze

      Large

      Red

      Rectangle

      Silver

      Large

      Red

      Rectangle

      Gold

       

      It is of course super-easy in FileMaker to set up a layout based on Size with portals to the three attributes and to see the options available at each size. But if you view the results in Table View, because the layout is based on the Size, only three rows (corresponding to the three sizes) show, with only the first related child records showing for each column:

       

       

       

      Size

      Color::Color

      Shape::Shape

      Material::Material

      Small

      Blue

      Round

      Bronze

      Medium

      Blue

      Round

      Bronze

      Large

      Red

      Square

      Bronze

      Likewise, if I base the layout on one of the child tables, I will only see as many rows as there are children in that table. And the whole point is to show all relevant rows.

       

      Is it possible to easily generate the list I’m seeking? I’m new to FileMaker and feel like I must be missing something simple and fundamental, but I have read a lot of posts and some reference material and have not been able to find a solution.

       

      One recommendation I have heard is to replace the tables mentioned above with a table that is the full list of combinations. However, that would defeat the purpose of having a relational database. There will be many thousands of viable options, and because attributes will constantly be added and options changing, the only realistic way to keep this accurate and up-to-date will be keep it normalized and automated.

       

      I’ve gone as far as to write an ExecuteSQL function and some calculations to parse its result into respective columns. I thought that might be an acceptable kluge – I got it working to a point – but I couldn’t find a syntax that would work for all the joins. In reality, there are many more attributes than this, and all have associated tables with details about the attributes. So in the actual database, the tables I show here for Color, Material, and Shape would instead be linking tables to details about Color, Material, and Shape – meaning more (and more complex) joins. The query is easy to set up in MS Access, and I was also able to set it up in MySQL, but it looks like parenthetical joins may not be an option in FileMaker’s ExecuteSQL function(?). And in any case, I’d rather not go the ExecuteSQL route if there is a more natural way to get the full table in FileMaker.

       

      One option I have not yet explored would be to write a script that would just loop through all the attributes to build a table. Again, I'm hoping there's a simpler, more native option that I've missed.

       

      I would welcome any help – thanks!