7 Replies Latest reply on May 14, 2012 2:22 PM by GuyStevens

    Entering Data fro m One Layout to Multiple Tables

    JackLogan

      Title

      Entering Data fro m One Layout to Multiple Tables

      Post

      Hi, I am very new to filemaker pro 11 and am having trouble figuring this out. Im sure its something simple and am hoping someone can help me out.

      I basically have an inventory db with 4 tables as can be seen in the picture.

      The relationship between the 4 tables is this

      For Each Store(customer) I can have many Store SV Numbers(Invoice).

      Each Store SV Number(Invoice) can have one or more Part NUmbers(Product). Which is why i made the Store SV Line Table(Join Table) as a Join table.

      Instead of having multiple layouts for entering very little information. How do i add all the relevant data from the first 3 tables as a form for entering new records on the Inventory Layout screen.

      Also, How do i get the foreign key of a child table to autopopulate with the primary key ID of a parent table based on a field common to both tables. For example,

      If i have a product with a part number of 001554 and primary key of 1 assigned. How do i get the Join Table to automatically populate the primary key in the foreign key field by simply typing in the part number 001554 in the Child table.

      PLease Help!

      Relationship.JPG

        • 1. Re: Entering Data fro m One Layout to Multiple Tables
          GuyStevens

          You should be able to make a layout based on your "Store" table.

          Then you can just add fields from the other tables on your layout.

          Just make sure that you double click the "Equal" sign in the relationship graph to enable "Allow creation of records via this relationship" on the side of the child table (right side) for every relationship.

          You can also choose wether or not to select "delete records in this table when a record is deleted in the other table".

          But that depends on whether you need it or not.

          The idea is that when you delete a store, do the records in the StoreSv need to be deleted as well or not.

          The primary keys are filled in automatically in the Foreign key field when there is a relationship. So no worries there.

          • 2. Re: Entering Data fro m One Layout to Multiple Tables
            JackLogan

            Thank you so much! that worked perfectly. I can now get the foreign key to auto populate. However i am still having the problem of having the InventoryID foreign key auto populate.

            My Join Table "StoreSVLIne" has PArt NUmber and qty Used.  If i simply type the part number in, how do i get the "InventoryIDfk" field of the chlild table to search the Parent Inventory Table  for the same part number and then copy the "InventoryIDpk" primary Key in the Foreign Key field of the child table(StoreSVLine).

            I know of the pop up menu and value list way to do it, but with over a 1000 parts in inventory that might not be an option.

            • 3. Re: Entering Data fro m One Layout to Multiple Tables
              GuyStevens

              You have multiple options.

              I think the simplest is to make a dropdown list of your InventoryIdFk field in your StoreSvLine table.

              In the dropdown list settings you choose InvertoryIdPk as value in the first row. In the second row you choose Part Number. (You can select show second row only if you want)

              Then when you click the dropdown list you can just start typing. If you type fast enough your dropdown list will select the partnumber you typed. As soon as you select it the InventoryId gets placed into the InventoryIdFk field.

              Should work wonders.

              But it's true that a super large list of articles can be a bit of a bother. You could use categories and first select a categorie and then get a second list that contains only items from that category.

              Or you could go to a different layout to select a product where you have the ability to search etc.

              • 4. Re: Entering Data fro m One Layout to Multiple Tables
                GuyStevens

                P.S. Also give your StoreSVLine it's own Unique Id field that's set up as an Auto enter Serial number field.

                Just in case you ever need it.

                I always give every table it's own unique Id.

                • 5. Re: Entering Data fro m One Layout to Multiple Tables
                  JackLogan

                  Ok, So i did this, I created a drop down list and it does the job. But still does not auto populate the foreign key field based on The inventory table. Is it because the Inventory Table is a parent table?

                  • 6. Re: Entering Data fro m One Layout to Multiple Tables
                    GuyStevens

                    Are you sure you have set the Valuelist on the InventoryIdFk field of the StoreSvLine table?

                    You don't need the PartNumber field in that table.