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!!!!