8 Replies Latest reply on Nov 1, 2014 4:38 PM by FredH

    Link tables question

    FredH

      Title

      Link tables question

      Post

      Hi Experts,

      I have a very basic - even maybe stupid :-p - question.
      I have 2 tables : customer & invoices.

      For both tables I have a Primary Key and in the invoices table, I have a Foreign Key called fk_CustomerID.  Both tables are linked via the pk_CustomerID & fk_CustomerID.  No other links are created between those two other tables and I do not have any other common field.
      Now I would like to know how I can set up my forms in order that the tables work together : from my customer form, I would like to link to the invoices making sure that the customer information is correctly added to the new invoice.

      What is the best way to achieve this? 
      Also, is my basic setup ok or should I add something in/between the tables?

      Thanks in advance for your input!
      Kind regards,
      _Fred_

        • 1. Re: Link tables question
          philmodjunk

          You want the customer info data to appear on each invoice.

          You have two choices and both start from the relationship you have already set up. You can copy the data from customer info to each new invoice or you can simply link to it. Either method works and which is best is a user based decision not a database designer decision. The difference is that if you copy the customer info over, each invoice record will record the customer info that was current at the time the invoice was created. If you link to it, any update of your customer info will automatically update all invoices connected to that customer record.

          Consider what might happen when a customer changes their shipping address and then calls to complain that they did not receive a recent order. If each invoice copies the shipping address, you can check the invoice record and tell whether that invoice was shipped to the new or the old address. If it's a dynamic link, you have the most up to date info, but can't tell from the database whether that was the address to which you shipped a particular order.

          To copy over data from customer info, you define fields in invoices for the customer data and define looked up value or auto-enter field options that copy over the data from a corresponding field in the customer table.

          To link to the data, simply enter layout mode on your invoice layout and use the field tool to add the fields from the customer table (Occurrence) to your invoice layout.

          • 2. Re: Link tables question
            FredH

            Hi PhilModJunk,

            Thanks for your input!  I think that it would be best to choose for the "link to the data" but I do not quite understand what to do exactly.
            When I enter a new record in my Customer form, everything works fine but when I go over to my invoices form, I can also create a new record but I cannot link it to an existing customer.  When I add the customer field from the customer table, this field seems to be uneditable.

            Any Idea what I am doing wrong?
            Thanks in advance for your input!
            Kind regards,

            _Fred_

            • 3. Re: Link tables question
              philmodjunk

              Each new invoice record must be linked to a customer record. Until it is linked, data from the customer table is not accessible as FileMaker has no way to determine which record in customer to access for that data.

              There are many different ways to do the linking. Here's the most common "beginner level" approach:

              Format your fk_CustomerID field from your invoices table with a value list set up as a "use values from field" value list. Specify the pk_customerID field from customers as the first field and a name field (may need to define a calculation field to combine values from several fields to use for this) as field two.

              When you select a customer in this field, the value list enters the ID number and then your new invoice record is linked to a customer record and fields from the customer table should now show data from that table.

              For a detailed description of how to set up a "use values from field" value list (with pictures), see the exploration layout for that found in Adventures in FileMaking #1.

              • 4. Re: Link tables question
                FredH

                Hi PhilModJunk,

                That was the link I was missing! :-)
                Thanks a lot because also example file was very helpful in order to understand the different setup options

                Kind regards,

                _Fred_

                • 5. Re: Link tables question
                  philmodjunk

                  You'll find that the method I outlined here tends to become a fairly irritating way to select customers as your table of customer records becomes large. The conditional value list methods illustrated in the Aif #1 file are one way to make such a list shorter and thus easier to work with. On the other hand, the second adventure file: Adventures in FileMaking #2 - Enhanced Value Selection explores a number of other methods that make the process of selecting a "Name" from a value list much more user friendly.

                  • 6. Re: Link tables question
                    FredH

                    Hi PhilModJunk,

                    Thanks a lot for the additional details!
                    I have now another related question: 

                    I have added the Invoices fields on my Customer layout page but when I create a new Customer record, I cannot edit the Invoices part unless I first go to the Invoices layout and link both using the foreign key.
                    Is it thus possible that this link is created automatically when I create a new customer record?  For example when I create a new customer record using a script?

                    Thanks in advance for your input!
                    Kind regards,

                    _Fred_

                    • 7. Re: Link tables question
                      philmodjunk

                      Careful, Putting fields from Customer on your Invoices layout makes sense, but not the other way around. You have one customer record to many invoice records, so any invoice record for the same customer can all link back to the same customer record to show info on that customer. But if you do the reverse, you could have many invoice records linked to that customer record. If you put fields from invoices directly on the customer layout, you will only see data from the first such related invoice record. To show data from the many related invoices, you'd need a portal so that you can get a list of multiple related records.

                      And to answer your question, you'd need to enable "allow creation of records via this relationship"  for Invoices in your relationship before those fields would let you add data to them and create the new related record to receive that data in the same action.

                      • 8. Re: Link tables question
                        FredH

                        Thanks PhilModJunk!
                        There seems to be lots of options available via the Relationship tab that I had overseen ;-)