Looking up a foreign key in Filemaker
I am trying to make a modern Inventory system for my business. I have a main "Stock" Table that lists the parts that I have and their current stock. To get stuff in or out of the system, the user must make a "Transaction". The Transactions table basically logs anything that gets in or out of the Inventory table and automatically subtracts or adds from the Stock table.
The two tables are related by PartID (not a part number, but a meaningless unique number). Here is my problem: the parts that I stock do not have PartID on their barcodes (as it is meaningless and has no relevance to the business). Whenever I scan a barcode, the scanner only outputs the Part Number. But this means that I cannot relate back to the Stock table because my relationship is based on PartID and not a Part Number. I must enter a PartID manually to make a relationship - without PartID the Transaction table is completely useless.
How can I 'lookup' the PartID in the "Stock" table from the Part Number field?