Design Recommendation Needed

Question

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 calculated based on the user's input. For example, a vendor license cost is calculated by # of Users times the Unit Cost of a license. The # of Users is user entered, and the Unit Cost is hardcoded into the formula. Example, [# of Users] * 1.50 where # of Users is entered in the form and 1.50 is the current unit cost of the license.

This works as long as the unit costs don't change. Once the unit cost changes, it changes the value of the field for all historical Records in the table which is inaccurate.

The two routes to preserve the integrity of the historical records while allowing new records to be created with new unit costs are to utilize snapshot fields which will freeze a field value, or look up the unit cost from another table every time the record is opened based on a date in the record.

Here is where I am running into trouble. For the snapshot method, I don't know how to make the field default to a specific record in the Unit Cost Table. For example, I have 200 unit costs in a table. I need to default each of those unit costs into their own fields in the form. It is not up to the end user to choose which Units they want.

For the look up the unit costs from another table every time, I don't know how to make the formula look to the right version of the unit cost in the reference table based on the date in the Estimate. For example, the Unit Cost Table has 3 records for Vendor License Cost. One Record is the unit cost for 01/01/2022-01/01/2023, the second record is the unit cost for 01/01/2023-01/01/2024 and the final record is Current Cost. Depending on the date the Estimate was completed it should pick the appropriate unit cost. So if the Calculation was done in June of 2023 it should go to the table and get the correct unit cost.

I am happy to share any examples or any new detail. I am totally redesigning this tool so I can start from scratch.

markshnier__you · Answer

Interesting Challenge.You say that your unit cost table has 200 records.&nbsp; But any records have duplicates for the same item, just differentiated by the effective date. So are you saying for argument sake that you have about say 100 items and each item has an average of 2 effective dates.If that is the case are you looking to freeze in the values of all unique item costs into the Estimate record, by only bringing in the most current record for each unique item?That is the case then a suggestion would be to use a Formula Query to have the item master cost table auto flag the record which is the most current for each item.When you create a new estimate a pipeline or a formula URL button would import say the 100 unique items into the estimate as a child record, just bringing in the most current cost for each item.&nbsp;&nbsp;&nbsp;Then while it's a little bit of a pain but something you can do while watching TV, you would create 100 summary fields to roll up the cost up to the estimate record for each different item.&nbsp;Does that approach sounds like it would work

nickbellows · Answer

Let me answer in parts.&nbsp; 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.&nbsp; 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).&nbsp; Each unit cost can change at any time.&nbsp; For example a vendor changes their price on License Fee A.&nbsp; 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.&nbsp;&nbsp;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 · Answer

OK 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.

Forum Discussion

Design Recommendation Needed