5 Replies Latest reply on Nov 20, 2014 2:45 PM by Jakedeg

    Relationship Structure for multi-instance lookups



      Relationship Structure for multi-instance lookups


      My database has a "setup" layout that writes to a "show info" table where I let the user specify "colors" for about 28 unique "frames". Each of these frames has a "color" text field and a "color swatch" field. Every time a user enters a color, I want it to lookup the color swatch from an separate external database of possible colors which I have built. All of these fields in the "Show Info" table are global, and there is really only one record being written to.

      Right now, I have it working on a few of the frames as a test, but I am wondering if one of you brilliant, attractive people knows of a better method than the one I am currently using. I have relationships setup so that there is a separate instance of the "show info" table for each "frame". I relate the color field in the Show Info table to the Color field in the external file and I make the swatch field a lookup field. As I said, this is currently working great.

      The problem is, if I follow this through for all the frames, I'll need 28 instances of the "show info" table in my relationship graph. This seems big and clumsy and cumbersome. Is it? Is there a better way? Is there a quicker, simpler, more efficient way to implement this sort of multi-instance lookup without needing a new table and new relationship for each version of the field? Or is this normal?

      I tried to do some googling (which is how I solve most FileMaker questions) but I wasn't even sure which language to use and what to look for...


        • 1. Re: Relationship Structure for multi-instance lookups

          Instead of 28 fields linked to 28 occurrences of the same look up table, add 28 related records, which then allows you to link that one related table to one occurrence of the look up table.


          You can set up a portal to Frames on your SetUp layout.

          • 2. Re: Relationship Structure for multi-instance lookups

            Thanks. This is probably a way better way to be thinking about this. So I made the new table for "Frames" but when I try to place a portal on my Setup layout, it asks me to pick from a related table. What should the relationship between "Frames" and "Show Info" be? Since there is only one record in Show Info and it's full of global data, I'm not sure how to link these two.

            • 3. Re: Relationship Structure for multi-instance lookups

              In some cases, a Cartesian Join operator (X) can be used as that allows you to match any record in table 1 to all records of table 2. But you can't enable "allow creation of records via this relationship" if you use that operator and I imagine that being able to create your frame records directly in the portal would be useful. (BTW, you will not be limited to exactly 28 frames in this approach. If circumstances change and you need more/less frames, you just add/delete records from your frames table.)

              So if you want to create Frame records directly in the portal, you simply need a field with nearly any value you care to use in it so long as it is not empty. So define a field named constOne as a calculation field with the number 1 as the sole calculation term. Match it to a plain number field in Frames and you have a working relationship where you can enable "allow creation..." and yet any record in Setup will link to all records in Frames as the match field values of all the records will be the numeric value 1. (This, BTW, is how we set up Cartesian joins in FileMaker before we had the option to use the X operator.)

              • 4. Re: Relationship Structure for multi-instance lookups

                Thanks! This is working great. A much better way to do this.

                I actually don't need to add Frames from within the portal, so the X operator is an ideal solution.

                • 5. Re: Relationship Structure for multi-instance lookups

                  Now for the next challenge: Conditional Formatting between portals.

                  This is going to be much easier referencing a screenshot for the layout in question. See attached image. 

                  At the top, you see a "Default Color" section. The set of 7 frames in there are defaults. Then, each vertical box down below has it's set of seven. So, rather than the 28 frames I was talking about earlier, there are now 35 frames in that separate table. As you can see, each Spot box has a button to set it to default. I made this work using a Find and a GetNthRecord script. But, once a color matches the default color, I want it's text label to turn gray. See how the "Frames" Drop-down is gray in all spots but spot 4? That's cuz the first three match the default. I want the same thing to happen for colors. So, in this screenshot, all the color labels would be grayed out except for F1 in Spot 1 which doesn't match the default.

                  I also want that "Use Default Colors" button to only be visible if something in that spot doesn't match the defaults.

                  How do I do this now that these are all portals and related records? With my previous methodology, I could just do a simple conditional format if ShowInfo::Spot 1 Fr1 = ShowInfo::Spot D Fr1. But now those are the same field in different records of another table and I'm lost. 

                  Thanks again!