Design Recommendation Needed
I have a table used as a Cost Estimator. The user fills out values in a form, and the table outputs a series of costs based on formula fields. There are some 200 individual costs that are calculate...
Forum Discussion
Let me answer in parts. The Estimate is made up of about 200 different costs i.e. License Fee A, License Fee B, Labor Cost A, Labor Cost B, Training Cost A, Training Cost B. Each one of these 200 different costs is made up of some combination of user input item (# of Locations, # of Users etc) and unit cost of the specific item (License Fee A or B etc). Each unit cost can change at any time. For example a vendor changes their price on License Fee A. Without a way to freeze all the values in the completed estimates, updating the unit cost for License Fee A will change that cost on previously completed estimates.
The ideal situation would let me create a rule to freeze all values in a record once it is finalized and then I can update the existing fields as needed and the previous records would not update but I don't think that is possible.
The customer has a requirement that all previously completed Estimates remain in the same table.
I will have to do some research on Summary fields as that isn't something i have done before.
MarkShnier__You
Qrew Legend
Qrew LegendOK well if my approach makes sense to you the answers of my solution has two parts. One is to have the unit cost records auto identify the most current unit cost for that item and that involves a Formula Query and the syntax for that can be a little tricky. Then the other part of my solution was to copy those unit costs as a child records to the estimate so that they will be frozen in time. There would be a Join table called Frozen Estimate Unit Costs or something like that.