3 Replies Latest reply on Dec 17, 2013 11:07 AM by philmodjunk

    How Can I Change A Field for only Future Records (not affecting current records)?

    enyko

      Title

      How Can I Change A Field for only Future Records (not affecting current records)?

      Post

           Sorry if this has been answered somewhere but I tried searching and couldn't find it.

           I have some price changes on our services and I'm wondering how to change the price on one product moving forward so it doesn't affect our current / previous clients. For example, our "Product A" has always been $500, we are now changing it to $650 moving forward for new clients. But when I change the amount to $650, it changes all our previous clients amount too which in turns shows that they have a remaining balance of $150 now. How do I just change it moving forward without affecting current & previous clients?

           We are using a calculation field that says; If ( Product A = "Yes" ; 500 ; 0 )

           Thank you!

        • 1. Re: How Can I Change A Field for only Future Records (not affecting current records)?
          philmodjunk

               You'll need to explain the design of your database, it sounds like you have a number of issues that are complicating the function of your database.

               What you describe sounds like an Invoice or Purchase Order system. Most such are modeled after these tables and relationships:

               Orders----<Line Items>-----Products/Services    (---< stands for "one to many")

               Then one record in Orders is a single invoice or purchase order. The individual line items are entered in a portal to the line items table. A "price" field in the Line Items table looks up (copies) the current price as set in the Products table. Since this data is copied over, a future change to the price field in the Products/Services table does not affect past Line Items records and thus the invoice totals computed in Orders will not change for past invoices.

          • 2. Re: How Can I Change A Field for only Future Records (not affecting current records)?
            enyko

                 Admittedly, our database is not that complex or advanced. In fact, it's "thrown together" and don't have any relationships at all. We just have tons of fields. We add a field when we need to capture data for our clients. One of those fields is for us to know if they are adding "Product A". It's as simple as; YES = $500, NO = $0. That total is then used in another calculation field that adds all their services together and gives us a total cost. There are no relationships or anything. We use these fields to generate an invoice. 

            • 3. Re: How Can I Change A Field for only Future Records (not affecting current records)?
              philmodjunk

                   Then you indeed have a problem brought about by the complicated way you chose to design your database. (The three related tables is actually a simpler way to set this up.)

                   Only thing that I can think of is to change your calculation field into a number field with an auto-entered calculation. Changing the calculation in an auto-entered calculation will not update existing records with new values. (but editing a field referenced by this calculation will update it, so be careful.)