5 Replies Latest reply on Dec 6, 2011 2:29 PM by philmodjunk

    Update lookup, but keep old rates for previous transactions

    MikeF

      Title

      Update lookup, but keep old rates for previous transactions

      Post

       

      A quandary that has almost certainly been asked previously …

      Each state has a sales tax rate.

      So you have a lookup to that rate, and the tax properly calculates.

      But when that tax rate changes, and the lookup is updated to the new rate, how do you ensure each *previous* transaction keeps calculating on the old rate?

      A similar analogy would be a contact in a company.  When there’s a new person in that position, and you update the lookup, how do you prevent all your previous transactions from updating to the new contact?

      Many thanks in advance for any input on resolving this issue.

        Mike

        • 1. Re: Update lookup, but keep old rates for previous transactions
          philmodjunk

          Check out the Looked up value auto-enter setting and also examine the line items table of the Invoices starter solution that uses this method to look up pricing info from the Products table.

          A field with a looked up value setting, copies the value from the related table into the field. Since this data is copied, changes to the original value in the related table do not change the value, but new records will copy in the new value...

          (and there are ways to use effectivity dates to control when a change goes into effect.)

          • 2. Re: Update lookup, but keep old rates for previous transactions
            MikeF

             That works great for the tax rates. Having the looked-up field *copy* its value is fantastic.

            Wondering how it would work for the company/contact example, as that wouldn't be a looked-up field.

            And ... do you have an example of using dates?

            Thanks,

              Mike

            • 3. Re: Update lookup, but keep old rates for previous transactions
              philmodjunk

              Here's a thread that discusses the two basic ways to make info appear automatically when you select a value that links it to a related record. Both rely on a relationship, but one is the looked up value method, the other is a dynamic link that will automatically display updates made to the lookup table. Which is best depends on the needs and prefrences of the user.

              Company/Contacts is a classic case where different needs will take you to one method or the other. Some businesses need to know "what contact info was current when we created this invoice?" which tells you that copying the data into the invoices table is needed. Other businesses need to know "What's the right now current contact info for this company?" which tells you that you want a more dynamic link.

              Auto Fill

              For Date controlled lookup settings, let's start with the Invoices Starter solution. You have a products table where a unit price for each product may be stored. You can only store one price for each product so you cannot make price changes before they go into effect and you cannot keep a record of previous pricing.

              Add a Pricing Table with these fields:

              ProductID
              Price
              EffectiveDate

              To LineItems, add an unstored Calculation field, cToday, define as: Get ( CurrentDate ) and select "unstored" in storage options.

              define this relationship:

              LineItems::ProductID = PricingTable::ProductID AND
              LineItems::cToday > PricingTable::EffectiveDate

              In this relationship specify that the PricingTable Records be sorted by EffectiveDate in Descending order.

              Now set up your looked up value field option to look up Price from PricingTable instead of Products.

              Looked up value settings will copy the "first" related record when there is a match to more than one record. By specifying the sort order on EffectiveDate, we make sure that the matching PricingTable entry with the most recent effectiveDate that is the same as today or earlier is the record from which the price will be copied.

              • 4. Re: Update lookup, but keep old rates for previous transactions
                MikeF

                 "Company/Contacts is a classic case where different needs will take you to one method or the other. Some businesses need to know "what contact info was current when we created this invoice?" which tells you that copying the data into the invoices table is needed. Other businesses need to know "What's the right now current contact info for this company?" which tells you that you want a more dynamic link."

                ... That's it, except I need to know both ..!

                So in just reading thru the effective date solution, it looks like exactly what is required.

                Thanx again Phil ..!!!!!

                   Mike

                • 5. Re: Update lookup, but keep old rates for previous transactions
                  philmodjunk

                  The two methods are not mutually exclusive. You can use both. You can also selectively update looked up value fields by editing the key field used to the define the look up relationship or in a batch operation on the entire found set via the Relookup Field Contents option found in the records menu.