I'm in the process of creating a bidding solution for a construction company (a rather ambitious project for my limited skills!) Anyway, I have created a Products file that allows for a product ID, name, unit cost, etc. that seems to be working OK.

What I would like to do is have the ability to modify the unit cost as price changes occur. However, I need to be able to keep a running track of a product's previous price(s). The problem I'm running into is that I need to keep the integrity of all proposals created using previous prices.

In other words, if the product SuperWiget was bid at $250, and unit cost increased to $300, I still need to maintain the $250 price for proposals created prior to the price increase.

Ideally, I would like to create a magic button that would allow me to (1) modify the price; (2) capture the modification date; (3) create a revision ID; (4) maintain the original product ID; (5) show the modified price as the current price.

I imagine this would entail somehow creating/duplicating the existing product record. I'm just not sure where to begin...

Any suggestions would be greatly appreciated.

fm_products_test.zip

I haven't looked at your file, but...

If you create a Bid table which has the Project ID, Bid Date and Price, then each time you change a bid, you'd create a new record. The Project's active bid would be a calculated value taking the last bid entry. You would have a complete history of the bods then.

I'm not quite sure I follow what you're suggesting. Wouldn't previous bids dynamically change as product price increases take place?

Please bear in mind, I truly am sleep deprived (newborn!), and sometimes logic just defies me.

You want to use Lookups.... lookup "lookup" in the help file for more details. Brief example attached, main file is Proposal.

I have entered 2 Proposals, now change one of the Item prices and create a new Proposal. Notice on the new Proposal the new pricing is used but the old ones retain the original pricing. This is because the PriceUnit is "Looked Up" at the time the Item is entered, check the ProposalItems file for further details.

Proposal.zip

Sweet! This is perfect! Thanks a million!!!