Yes, but when you change a price, do you want ALL of your job costing records to update or just those in current use?
Normally, this kind of system uses a looked up value because you don't want all of your records to update automatically when there is a price change.
The problem is I try and update the job costs fairly quickly using a lookup of last known price.
Each job may have 5 products.
Three weeks later the invoice for the products arrives with a different price for perhaps only one of the products.
Rather than update each job what method would you recommend re costing.
I don't understand what you are trying to do. Can you describe your work flow in more detail?
Your basic options are to use a looked up value as you are now or to replace the looked up value with a direct reference to the field in the product table--in which case all your records will update when a cost value is changed in the products table.
But I suspect that you need to only update the data in a sub set of the total records in your tables. If so, you need to stick with option one but set up a script that enables you to find and update those records that need to be updated.
Thanks for your views
I think the best option will be find all the records containing product "x" used after date "y"
and then manually re enter the new price.
I enclose a screen shot of the raw data if you could suggest a script.
Scripts can perform finds.
Replace Field Contents can update a field for all records in a Found set.
So you can manually find records and then use Replace Field Contents to do a "batch update" of the records found or you can script the process.
Here's a thread of scripted find examples: Scripted Find Examples
Thank you for this I am working through it .
Trying to get to grips with "Global Storage" ...