4 Replies Latest reply on Oct 9, 2013 7:16 AM by philmodjunk

    Adding records in a related table

    CorneliuMusat

      Title

      Adding records in a related table

      Post

           Hi,

           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.

            

           Regards,

           Corneliu.

      Relations.jpg

        • 1. Re: Adding records in a related table
          CorneliuMusat

               Hi Again,

                

               To further clarify my post, I managed to achieve what I needed by making the Drop-Down List taking the values from a Value List that Shows only the Second Column (PRODUCTS_PN) but takes the value from the PRODUCT_ID (First Column). This works, except that the Auto-complete function works only if you type the value quickly without delays, e.g. if I type the first few characters of the Part Number (PRODUCT_PN) and stop to read the rest of the characters before typing them, the auto completion is reset which is an inconvenience. Also, another inconvenience is that, although the Drop-down List only shows the second column for the look-up, after the final value is entered, the field actually shows the PRODUCT_ID value and not the PRODUCT_PN.

                

               Thanks in advance for any comments or suggestions.

               Regards,

               Corneliu.

                

          • 2. Re: Adding records in a related table
            philmodjunk

                 See this demo file:

                 FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
                 Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7

                 It uses an auto complete value list on a text field to look up the ID number. Scripting is included to handle the issue of possible duplicate names.

                  

            • 3. Re: Adding records in a related table
              CorneliuMusat

                   Thanks PhilModJunk for your help with this. 

                   I actually ended up re-linking the tables by the PRODUCTS_PN column as it made my life way easier and the PN turns out to be unique across the T_Products Table.

                   Thanks again,

                   Corneliu.

              • 4. Re: Adding records in a related table
                philmodjunk

                     Just because a field always contains unique values does not mean that it is the best choice for use as a primary key in relationships. Such a "name" or "description" field is vulnerable to users deciding that the text in the field should be changed. And such a change will create issues for your database when that change then "breaks" the link to other related records where the match field still contains the original name or description.

                     Keeping your records linked by an auto-entered serial number that is never ever changed avoids this issue. That's the point behind the demo file. It allows you to use a name or description for a search with an auto-complete enabled drop down list and yet a serial number field remains the primary key.