2 Replies Latest reply on May 3, 2012 4:47 AM by darkenney

    Confused by simple Lookup



      Confused by simple Lookup


      Should be easy. My first table (products) has 7 fields: productID, colorID1, colorID2, colorID3, colorName1, colorName2, colorName3.

      Second table (color_list) has two fields: colorID, colorName. When I enter a color ID in the colorID1 field of the products table, I would like colorName1 field to be populated with the correct name from looking into the color_list table.

      I recall this being done with a straight forward Lookup in very old FileMaker. With the new relationship model, I am flummoxed.

        • 1. Re: Confused by simple Lookup

          That should be very straight forward to set up for colorName1, but what about the other fields? that won't be so straight forward and wouldn't have in the older versions of FileMaker either.

          Here's the set up for just the first field to get you started:

          Open Manage | Database | Relationships.

          If you haven't already done so, drag from table 1::ColorID1 to the colorTable's ColorID field.

          Now you have two options for using this relationship. You can copy the colorName from the color table into the colorName1 field using the looked up value field option for this field or you can remove this field from your database and just add the ColorName field from your color table. Both options have their uses so you will have to consider their differences and decide on which method you want. The looked up value option will not update if you edit the color name field in the related color table automatically. You are capturing a "snapshot" of the value that was in the field at the time you last selected a value in the ColorName1 field. If you add the actual field from the related table, it will automatically show the current value of that field.

          To get this to work for the other colorID/color name pairs of fields would require additional relationships between these two tables. In current versions, you select the colorTable's "box" in Manage | Database | Relationships and then click the duplicate button (two green plus signs) to make a new "occurrence" of the color table. This does not duplicate your actual table. It just gives you a new "box" to the same table for setting up an additional relationship. With this new occurrence, you can then drag to link ColorID in the new occurrence of your color table to the ColorID 2 field.

          I would not, however, set it up this way. A simpler, more logical structure is to use a "join" table of colors that links a given Product record to any number of color records and a given color record to any number of Product records. It would look like this in Manage | Database | Relationships:


          The match fields:

          Products::PRoductID = Product_Color::ProductID
          Colors::ColorID = Product_Color::ColorID

          With this set up, you add a portal to Product_Color on your Products layout with the color name field from colors added to the portal row and you can create a list of any number of colors for a given product.

          In similar fashion a portal to Product_Color on the Colors layout can list all products that list that color.

          • 2. Re: Confused by simple Lookup

            Thank you for the clear and through response. I was able to work through what you suggested with success. I am still surprised that this isn't easier as it's just an MS Excel vlookup. As this is part of a larger solution, I may go back to the source import data and bring in color name on the product records with the colorid. Thanks again for the support. Regards, David.