AnsweredAssumed Answered

Complex join: show all rows in a results table

Question asked by jtu on Aug 4, 2016

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!           

Outcomes