Assuming that you have this set of tables/relationships (names may be different):
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.
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.
Thank you! The List Function worked! VERY helpful...
"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.
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...