Adding records in a related table
I'm new with FileMaker and I'm trying to migrate an MS Access Database to Filemaker Pro 12 (I'm using Pro 12 Advanced).
The data I have has several tables linked with relationships however, to keep the problem simple lets assume I have a typical Products, Invoices and Invoice Lines tables situation with the below shown relationships.
I would like to have a Master Details layout to create records in the Invoices table as well as the related Invoice Lines table. My problem is that I would like to be able to look-up the Invoice Line from the Products table using a Drop-down list control which would provide Auto-complete function based on the PRODUCT_PN column in the Products table and NOT based on the PRODUCT_ID field which is part of the relationship.
In other words, the Invoice Lines Items are related to the Products through the Line_Product = PRODUCT_ID relation where the PRODUCT_ID is the PK of the Products table and is a Auto Serial Number type of field and the Line_Product is the FK that links the Invoice Lines table to the Products table while I would like to do the look-up on the PRODUCTS_PN field.
Is this possible in FileMaker?
Thanks in advance for any help.