12 Replies Latest reply on Dec 2, 2015 7:12 AM by techt

    Working with tables in FMP 11

    Scraps

      New to all this but am trying. I have 2 related tables in one file.I also have 5 fields in the first table that I need to pull information from the second table. What I want to accomplish is when I place a part number in the new record I would like the information in the related table to auto fill the 5 fields in the first table. Can someone point me in the right direction.

        • 1. Re: Working with tables in FMP 11
          mikebeargie

          If you're new to this why are you starting in version 11, and not the current version 14?

           

          The behavior you're specifying is called a lookup. Where a value is "looked up" from another table and copied to your main table.

           

          Here's the documentation about lookups:

          About lookups

           

          And here's a 2 part tutorial for using lookups:

          FileMaker Tutorial - 089 - Lookups pt 1 - YouTube

          FileMaker Tutorial - 090 - Lookups pt 2 - YouTube

           

          Let us know if you have additional questions after going through those resources.

          • 2. Re: Working with tables in FMP 11
            techt

            Are the two tables related by the part number? As long as the part number is unique and the relationship between the two tables is based on the part number (many to one), the information will be displayed without having to autofill the data into the second table.

             

            If you want to post a copy of your solution, or a screen shot of the relationship, that would help to better understand what you're trying to accomplish.

            • 3. Re: Working with tables in FMP 11
              mikebeargie

              A lot of the time lookups are required because if you just show related data, and the related data changes, you may lose "point in time" context.

               

              Example: Bob adds a quantity of 12 "product 101" to an invoice and ships the 12 items to the customer for payment on delivery. Bob used a related field to product::price to show the line item cost on his invoice. Bob sends the invoice to his customer along with the items. One week later Bob gets a call from the customer to re-send the invoice which got lost, however the price of "product 101" has now changed. Bob therefore doesn't have an accurate record of the invoice.

               

              In the above scenario, using a lookup allows you to save that "point in time" from the looked up table, for a value that you don't want to change once it's looked up.

              • 4. Re: Working with tables in FMP 11
                Scraps

                Thanks for the reply. Attached is a copy of what I am trying to do

                • 5. Re: Working with tables in FMP 11
                  techt

                  Is the goal to copy the information from one table to the other (so you have historical data), or just see the information from the other table (so you always have the most up to date information)?

                  • 6. Re: Working with tables in FMP 11
                    Scraps

                    So we have repair history. Will need to maintain the repair records. The table (Base inventory) is the Inventory of all the parts and equipment we cary in stock. Other than Qty. this will not change. Table (inventory) is to track repairs and will need to maintain the record for history of repairs.

                     

                    Thank you

                    John Lyons

                    Workshop Supervisor

                    Bram Offshore Transportes Maritimos LTDA

                    Cell: +55 22 99928 4574

                    • 7. Re: Working with tables in FMP 11
                      techt

                      Couple of problems that I saw with your database. You don't have a unique identifier for either table and the relationship you built would cause more problems than it would solve. I added a primary key identifier to your base table and a foreign key identifier to your inventory table. Since we really didn't dive into your processes and goals, I took a number of things as a guess. I added the fields to the layout and included a dropdown to select the Description. Once selected, the ID fields are updated and the relationship allows you to see the related data (top field set) or copy the data into the layout (via a script). I would either add multiple dropdown fields to narrow the final inventory items as options, or devise a value that would only link to one unique base inventory item. I would also strongly recommend that you update your copy of FMP so you can run on current hardware and OS. Hopefully this helps get you in the right direction.

                      • 8. Re: Working with tables in FMP 11
                        Scraps

                        Thanks for the help TechT it is for sure the right direction. I will continue to work on it . Again thank you.

                        • 9. Re: Working with tables in FMP 11
                          Scraps

                          The main problem I see is that in the Drop down filed you created does a list of the description of the item. I would like to change that to the actual part number (item). I tried to change in the values list you added but it still goes direct to the description. How do I get it to item in the drop down menu?

                          • 10. Re: Working with tables in FMP 11
                            techt

                            Glad it's working out for you. The dropdown, ideally, needs to be a unique value such that when selected, a match can be found in the base table and the correct pkID updated to the fkID in Inventory. In Layout mode, select the last tab of the Inspector, the value list that you see is set there. You can also adjust the value list from the File>Manage menu.

                            • 11. Re: Working with tables in FMP 11
                              Scraps

                              I made the changes as you suggested and all is working great. Again thanks for the Support

                              • 12. Re: Working with tables in FMP 11
                                techt

                                You're very welcome; have a great day!