2 Replies Latest reply on Aug 2, 2015 11:47 PM by SaadAbbasi

    Looking up a foreign key in Filemaker



      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?

        • 1. Re: Looking up a foreign key in Filemaker

          Set up a script to perform a find on your Stock Table. This script can copy the Stock record's Primary key to a variable and then create a new record in the transaction table and set the record's foreign key to the value of this field.

          You can also add a new occurrence of the Stock table to your relationship graph and link it by Part Number to your transactions table. You can set up a Looked Up Value auto enter option on the foreign key field to copy over the Part ID when the part number matches.

          • 2. Re: Looking up a foreign key in Filemaker

            Can you elaborate a bit more on the Looked Up Value Auto Enter option? FM only allows me to copy over the field that has a match. For instance, if PartID matches, i can copy that but not Part Number?