2 Replies Latest reply on Jul 10, 2013 12:37 PM by ChristopherRaymond

    Customers > Invoices relationship question / troubles



      Customers > Invoices relationship question / troubles


           I have a customers table and an invoices table. When I create a new invoice, I enter the customer name and it looks up the customer's address info an populates the fields in the invoice. However if I change the customer's address in the customers table, all of the records in the invoices table get updated as well. This is undesirable because a customer may have moved and I don't want old invoices showing the new address, I want them showing the old address.

           Can someone help me understand how to make this work properly?


        • 1. Re: Customers > Invoices relationship question / troubles

               There is nothing wrong with your relationship in terms of why this happens for your addresses, but I suggest matching by a customer ID number instead of a name. Customer names are not unique, the same customer's name can take more than one form (Such as Phil or Phillip) and customers change their name.

               To solve the immediate problem, you need to add address fields to your invoices table and set up auto-enter field options that copy the current address from the selected customer. Then future changes to an address in the customer table will not alter addresses in the invoices table, but you'll need to think about options for updating customer addresses so that when a new address is specified for the user, that update occurs in the customer table, not just the current invoice. A button with an "update address in contacts" label can perform a script to copy the data in the opposite direction.

          • 2. Re: Customers > Invoices relationship question / troubles

                 Yeah I figured it out. I had the fields defined as fields from the other table rather than fields of their own. Thanks!