6 Replies Latest reply on May 8, 2015 1:22 PM by kasenofonte

    Keep old prices


      Good Morning


      I have a very simple invoice system, in one table I list all the products and their prices.


      At times the price will change, but I want to keep an history of the older prices, date when put in place and date of when it expired.  Then I need they invoice to pick up the newest price.  Is there a way to do this without fields for oldPrice1, oldPrice2, etc and newPrice?






        • 1. Re: Keep old prices

          Often I'll have a related table to Products that has an effective date of a price.  When the price goes up, I don't erase it, I create a new record with the new price and effective date.  When I need to know the current price of the date, I get the one with the most recent effective date.  I usually do this with a join that is sorted by the effective date descending.  Would that work? 

          • 2. Re: Keep old prices

            I agree with Taylor Sharpe. A separate Prices table is a better design. My only comment is that sorting by date would include the price records with the effective start dates in the feature. I would actually flag the dates with a "current" flag and use it as a predicate in the relationship. The other option is to relate to a price record by a date range, but this will be much slower.

            • 3. Re: Keep old prices

              For complex pricing, I usually keep a separate prices table, similar to how stock price tracking works.


              I keep the relationship between products and prices sorted so the newest price record is always first.


              Then a lookup to pull the current price to the invoice when it's generated.


              Invoices --<- InvoiceLineItems ->-- Product --<- Prices


              And I see Taylor beat me to it.

              • 4. Re: Keep old prices

                What Taylor said; and if you want your line items to pick up the current price, use a relationship between LineItems and the Price table by productID, also sorted descending by date, and use a lookup / auto-enter calc.

                kasenofonte wrote:

                fields for oldPrice1, oldPrice2, etc and newPrice?

                The need for field1, field2 … fieldn is almost always a sign that you should rather create a related table.

                • 5. Re: Keep old prices

                  Agree with the above posters.


                  Depending on the whole structure, a PriceChange database can be very useful. It only contains a ProductID, a timestamp and a CurrentPrice value. If you're really generous and forward thinking, you might add a "discontinued" flag which will propagate to the article, together with the moment when it occurred.


                  By searching into it, you can see the price variations of an item over time, or you can see which items changed their price in a given time frame.


                  In our special case, it's a must of form following function - clients download daily an XML with changes that were made to the compendium.ch database, which lists all the medicinals available in Switzerland and lots of related data, including their current price.

                  • 6. Re: Keep old prices

                    thanks everyone - got it to work.... made my Friday very productive and I learned something


                    Have a great weekend!