4 Replies Latest reply on May 23, 2012 10:39 AM by DavidMaffucci

    Lookup based on one field OR another...

    DavidMaffucci

      Title

      Lookup based on one field OR another...

      Post

      Hello FileMaker Wizards...

       

      Is there a way to lookup based on one field OR another? I've got an invoice database which looks up a description and a price based on a part number. Trouble is, we use both and internal part number and on occasions we use manufactor part numbers. Is there a way to perform a lookup based on one field OR another? Thank you folks!

        • 1. Re: Lookup based on one field OR another...
          philmodjunk

          Assuming that you have this set of tables/relationships (names may be different):

          Invoices---<LineItems>----Parts

          Invoices::InvoiceID = LineItems::InvoiceID
          Parts::PartID = LineItems::PartID

          In your Parts table, define two fields, InternalPartNumber and MfgPartNumber.

          Define a calculation field, cPartIDList, in the table as: List ( InternalPartNumber ; MfgPartNumber )

          Modify your relationship to be:

          LineItems::PartID = Parts::cPartIDList

          And you will be able to enter either internal or manufacturer's part numbers into LineItems::PartID and your look up fields will correclty Look up values.

          Note:

          There must not be any overlap between MfgPartNumber values and InternalPartNumber values or you might look up data from the wrong part record. You may need to add a "prefix" or something to one set of ID's or the other to make sure that no manufacturer's ID ever matches the internal part number for a different part.

          How/Why this works:

          The List function returns the values from the two fields separated by a return character. In FileMaker, when a list of values of this type is used in the "match" field of a relationship, Filemaker matches values from the other side of the relationship to any of the listed values. You can even have a list of values on both sides of a relationship and it still works.

          • 2. Re: Lookup based on one field OR another...
            DavidMaffucci

            Thank you! The List Function worked! VERY helpful...

            • 3. Re: Lookup based on one field OR another...
              Sorbsbuster

              "we use both and internal part number and on occasions we use manufactor part numbers" - so, if they are guaranteed to be unique, why not just put 'the part number you're using' into the 'PartNumber' field?

              And if all 3 lists cannot be guaranteed to have absolutely no duplicates anywhere, then you've got the problem Phil describes.

              • 4. Re: Lookup based on one field OR another...
                DavidMaffucci

                The MFG and our Part Numbers are always different. The issue is that sometimes you pick up and item and we used one of our pricetags with our PN on there but often all you see is the MFG PN. So far the List function is doing the trick...