Multiple references to the same record in a DB

I'm working on an "internal parts database." We have an internal company part number and would like to cross reference / relate this to several different manufacturer's part numbers. As an example, we may have a black widget with internal part number WIDG001. We buy this widget from Widgetco as part number WIDGIWIDGI001 and the same item can be purchased from Wally's Widget World as part number WWW101A.

What's the best way to reference an internal part number field to multiple other fields in a single Filemaker DB?

Since a given part can be linked to many vendors and a vendor can be linked to many parts, you need a join table:

Parts----<Vendor_Part>----Vendors (----< means "one to many" )

Parts::__pkPartID = Vendor_Part::_fkPartID

Vendors::__pkVendorID = Vendor_Part::_fkVendorID

None of the fields shown thus far should be your internal company part number nor should they be the Vendor supplied part number. Make the __pk fields either auto-entered serial numbers of text fields that auto-enter Get ( UUID ).

Use a text field in Parts for the internal company part number and use a text field in Vendor_Part for the Vendor Supplied part number. Note that a portal to Vendor_Part placed on a Parts layout will be able to list all vendors that supply that part and the part numbers used by each vendor for that part. Conversely, a portal to Vendor_Part placed on the Vendors layout could list all parts supplied by that vendor along with both internal part number and the part number supplied for it by that vendor.

The Vendor_Part table occurrence can also serve as the basis for summary reports listing multiple parts with a break down listing each vendor and their part number.

I guess what I should have asked is, "how to hold all the aliases," as you put it. I understand that it WOULD do this, but I'm not clear on HOW to do this. I don't know how to get a single item to relate to multiple.