Hello, this question may have been asked already, but here goes…
I have a large database of parts from multiple vendors. This parts list (parent) has a child database with multiple date-based entries for each part (part# is the unique ID). Entries are added when the vendor updates us with a new price for the same part.
I calculate the price of my products, by accumulating multiple parts (total of parts cost + overheads + margin etc.)
However, I also maintain a history of prices, for e.g. price calculated in April 2024 might be more than Jan 2024, especially if the cost of one more parts has gone up.
I am able to pull the latest price for a part from the child database (date-based entries - MAX of date). However, when I edit a previously created price record, for e.g. to update a non part field (e.g. discount), I do not want it to refresh already looked up prices.
You may want to share a bit more about your data structure , relevant table columns and some pseudo data examples to describe the issue with more clarity. This will help the community to suggest a solution, if any.
You are welcome. Okay sure no problem. I think in general the approach based on the understanding so far can be to capture date also with price and allow the part price to be updated only if the record in the other table has a record created date later than the latest price.
There could be a kind of an indexing between part price revision dates and the record creation dates in the other table where these prices are used.
But whether it is feasible or not will depend on how the data is structured and where ( which table) the issue is.
