Brain frozen Newbie

[Garth]

Hi.

I'm having trouble with a price tracking solution. I'm writing an Inventory DB that track price changes of a product. I have two tables one is the Tbl_Product table that has the particulars(including PrdRecID_pk is the serial# auto created) and a Tbl_Product_Price table that contains: EffectiveDate(of the price change);PrdRecID_fk;UnitCost;PriceRecID(serial)

I have a One-to-many realtionship set between PrdRecID_pk --> PrdRecID_fk

I've been trying to set a calculated field to (Tbl_Product::CurrentCOG) to contain the most current price based on the EffectDate. Here is the calculation:

If ( LookupNext (Tbl_Product_Prices::EffectiveDate ;Higher) ; Tbl_Product_Prices::UnitCost)

I'm missing something or barking up the wrong tree. It's probably very simple.

Thanks for any help or ideas.

Garth

[cobra]

Hi Garth,

Temporal databases...what fun! :-)

Here's a quick and dirty solution:

create a calculation field as follows:

MaxPriceDate =

Max(Tbl_Product_Prices::EffectiveDate)

create a clone of Tbl_Product_Prices (call it Tbl_Current_Product_Price) and relate it to your Tbl_Product table using MaxPriceDate=EffectiveDate and PrdRecID_pk=PrdRecID_fk. Edit: To clarify, the clone is create in the relationship tab (click the plus sign near the bottom-left of the window and select Tbl_Product_Price in the dialog), I'm not suggesting that you create a whole new table.

Create a new calculation on Tbl_Product as follows:

CurrentPrice =

Tbl_Current_Product_Price::UnitCost

Note that this solution will not work reliably if one of your Tbl_Product_Price records has the same PrdRecID_fk and EffectiveDate as another. To make it work reliably, you need to change EffectiveDate to a TimeStamp field.

Hope this helps!

Cobra

[Ender]

Here's another method. Use one table occurance to enter the prices and effective dates, and a second table occurance to get the price after the last effective date. The second table occurance is related by:

Product::ProductID = Price::ProductID

AND Product::CurrentDate >= Price::EffectiveDate

(price sorted by Effective Date)

Then use the Last() function to pull out the last price from this relationship.

Enclosed is a sample.

prices.fp7.zip

[Garth]

Thanks for the info/ideas, Corba and Ender, I'll give it a try and let you know it worked out. I really appreciate it.

Thanks again.

Garth

[Garth]

Ender,

That worked like a champ.

Thanks again.

Garth