1 Reply Latest reply on Oct 3, 2013 12:03 PM by philmodjunk

    How do I select prices from one of several price lists??



      How do I select prices from one of several price lists??


           We have an inventory with prices for each item.  Those prices change on a monthly basis and we need to keep a history of the monthly price changes so that we can can calculate on the prices of any selected month.

           The INVENTORY TABLE has Item, JanPrice, FebPrice, MarPrice, etc.,

           The PRICE SELECTION TABLE has Item, PricePeriod, Price

           "Item" is the only related field between the inventory and the price selection table.

           "PricePeriod" is simply a value list of JanPrice, FebPrice, etc.

           "Price" is a calculation using Case, i.e (PricePeriod=JanPrice; InventoryTable::JanPrice) etc.

           This works but seems a somewhat laborious process to place the Case calculation for every single priced item in the price selection table.  Any suggestions to make things more streamlined and flexible would be greatly appreciated!!!!

        • 1. Re: How do I select prices from one of several price lists??

               Are these the only tables used here. Seems like there should be more.

               a typical invoicing system, for example, uses a minimum of 3 tables--often more:

               Invoices---<LineItems>------Products (inventory)

               Prices are then stored in a field in Products and are copied via an auto-enter field option into a field in LineItems. That way, future price changes do not affect past invoices.

               And if you need a pricing table where each price for each product has an effective date, an additional pricing table can be added:


               The relationship between lineItems and pricing would then be set up like this:

               LineItems::Item = Pricing::Item AND
               LineITems::Date > Pricing::EffectiveDate

               And you then define a sort order on Pricing in this relationship that sorts them in descending order by EffectiveDate. This makes the most recently dated pricing record that has an effective date on or before the LineItems::Date the first related record and thus the price that is auto-entered into a price field in LineItems.