6 Replies Latest reply on Dec 20, 2010 11:13 AM by philmodjunk

    How to retain correct customer address on an older invoice when that address may change later?

    DanF

      Title

      How to retain correct customer address on an older invoice when that address may change later?

      Post

      Please forgive the likely elementary question, but let's say I'm keeping track of customers, orders and invoices. How do I preserve data in a related record when the original data will eventually change. Ex. I will of course have ongoing records of invoices, but what happens when my customer's address changes? The invoice, by default, is a many-to-one with the customer table, but how is the customer's relevant address preserved on an older invoice if their address changes and is thus updated in the customer table?

      Thanks!

        • 1. Re: How to retain correct customer address on an older invoice when that address may change later?
          bumper

          Duplicate all the fields that you want to retain the data in the invoice table and set them to do a lookup (Option > Auto-Enter) on record creation. Note: this data can be overwritten in the future if someone were to do a re-lookup while the record is part of the found set, so the only fool proof way is to script it and then set the fields to not enterable in browse mode.

          • 2. Re: How to retain correct customer address on an older invoice when that address may change later?
            sunmoonstar.13

            Another option is simply to create a whole new customer record if the customer's address has changed. The old invoice thus preserves the old address data and the new invoice has a new customer record attached to it with new address data. This technique effectively allows you to maintain a "history" of the customer. With this method, it would also be useful to have a field in which you set the status of the customer record to "Active" or "Retired". If a customer's address changes, the old record would be set to "Retired" and would, of course, not be used when new invoices are created. The status field can be set to auto-enter as "Active" when a new record is created. Having auto-enter Creation and Modification dates would also be useful for maintaining the history of the customer records.

             

            Nick

            • 3. Re: How to retain correct customer address on an older invoice when that address may change later?
              alc

              Hopefully more experienced developers will pipe-in.

              But in the end, when it comes to invoices, you ideally have a copy of exactly what was sent that NEVER changes. (remember it WAS sent to the old address - you want evidence of this)

              Here's my preferred way:

              1. when printing or emailing the invoice, include a save as PDF script step.

              2. Link to that PDF and display in a container field on a layout (thru a script if possible, I'm still using Applescripts for this type activity)

              3. To view up to date info. Simply have whatever field contains the CURRENT client info at the top of your layout.

              HTH, xandra

              • 4. Re: How to retain correct customer address on an older invoice when that address may change later?
                bumper

                Dan F:

                You can also set up your db to have a separate table for addresses allowing you to have multiple addresses for one contact, home, office, warehouse, etc. Then you can expand on Nick suggestion and have an active/inactive field if the address changes. You would not want to just create a new contact record every time they changed any information because it mean a duplication of data which is what relational databases are all about. The suggestion about the pdf is valid, back in the 90s before we had pdfs in FileMaker, you would make a screen shot of the invoice/letter and save it in a container field. The difference is that the graphic is instantly viewable as opposed to a pdf that has to be viewed in an external program. Both suffer from the same fact of bloating your db, and while the cost of a large hard drive in very low today, you will eventually get a performance hit. The real question is why and if you really need to save the address with the invoice data. You may already be making paper copies of the invoice or packing slip and if you keep all addresses in one table forever you could just add one field for the address ID in the invoice and have the best of both worlds. 

                • 5. Re: How to retain correct customer address on an older invoice when that address may change later?
                  RickWhitelaw

                  The answer is simple. My DB issues cheques. I like to have an address on the cheque (not uncommon). In the table that handles the cheques is a lookup field that looks up the address from the "Contacts" table each time a cheque i(a record in the "Cheques" table)  is created. That way the address (per cheque) is stored and you can see where the cheque was sent at any given time. This is analogous to what you're saying.

                  • 6. Re: How to retain correct customer address on an older invoice when that address may change later?
                    philmodjunk

                    In our invoicing system, we use the looked up values option so that updates to a customer's address in the customer table do not change the address for past invoices.

                    Since no field in a completed invoice should be changeable, we use record level access control to keep all invoices ( and related line items ) that have a status of "Printed" from being editable unless the database has been opened with the master password. This keeps a Relookup from possibly changing past values.

                    To learn more about Record Level Access, see: "Editing record access privileges" in FileMaker help and pay close attention to the section titled: "Entering a formula for limiting access on a record-by-record basis".