3 Replies Latest reply on Mar 4, 2015 7:51 AM by philmodjunk

    Alternative parts in Filemaker Pro



      Alternative parts in Filemaker Pro



      I have a table of products (about 3500 records). Table has key fields: part number, description, quantity.

      Not all of the 3500 products are unique, but many of them are alternatives to other parts in the same selection. Could you suggest how can I manage this: On one record, choose 2+ different records to show as alternatives?

      E.g. I have 5 O-rings: O1, O2, O3, O4, O5 in my catalogue. 3 of them are alternatives to each other (O1, O3, O4). So after I make my selection (via a portal?!) on record O1, that O3 and O4 are alternative parts to this, then record O3 shows that its alternatives are O1 and O4, and similarly O4 shows O1 and O3 as its alternatives. O2 and O5 would be left alone.

      This way I could calculate total quantity that includes also alternative part's quantities.

      At the moment I have a separate text field into which I manually write each alternative part numbers. This is little help only for searching but not at all perfect.

      If someone could direct me on the right course I would really appreciate it!

      P.S. Googleing this problem didn't help, it showd me alternative products for Filemaker instead.


        • 1. Re: Alternative parts in Filemaker Pro

          What you are describing is a "many to many self join". Your text field that lists part numbers can actually be used for this as a self join relationship like this:

          catalogTable::AlternatesList = CatalogTable|Alt::PartNumber

          Will allow a portal to list all the alternate parts numbers

          Another option is to set up a Join table for this:

          CatalogTable ----<Part_Alternates>-----CatalogTable|Alt

          CatalogTable::PartNumber = Part_Alternates::PartNumber
          CatalogTable|Alt::PartNumber = Part_Alternates::AltPartNumber

          CatalogTable|Alt is a second table occurrence of your existing catalog table. The key trick here is that if you link Part A to Part B and also want Part B to Link Back to Part A, you need two records in Part_Alternates, one with the Values in the PartNumber fields swapped. Creating the second number can be scripted so that when you link A to B, it also links B to A.

          • 2. Re: Alternative parts in Filemaker Pro

            Hi again!

            I finally managed to get my hands on it and tried the second option. This works but only with two parts (one original and one alternative). When I have three then this doesn't work anymore.

            I believe I should try the first option now? Or is there another solution?


            • 3. Re: Alternative parts in Filemaker Pro

              If you list each part in that field separated by a return, it will match to more than one related record, but feel free to set up a join table instead as that's generally a more flexible option for this kind of thing.