I have two tables. “Margins” and “Orders”. I need a script that would be triggered by an “New Record in the Orders table” automation that would use the value in the “Product” field to lookup a “Margin” value in the “Margins” table, then record this against the new order record, assuming a match is found.
In doing this, I hope to preserve the current margin against a new order, something that would not happen with a normal lookup filed as it would simply update all the linked order records when a margin is altered.
How do I select the “Product” value from the latest record(s) in the Orders table and only update those records that have not been already updated previously be the script?
Are there examples of similar scripts that anybody is aware of that I could work from?
Many thanks in advance!