Whilst I am quite new to filemaker and have managed to find my way around and create a functional solution for my business.
The solution allows me to enter details for a customised order, calculate the costs for each component and then track and the project from production through to invoicing.
I am having the problem of the dynamic nature of the pricing affecting closed invoices.
I have made use of lookups to gather the pricing data at the time of the order, but the pricing also relies on a series of formulas which may change in the future. ( I cannot seem to calculate these from the related pricing table as they rely on fields within my order item table ) My idea is to create a secondary archive field for each of the pricing breakdowns and when an invoice status is set to "sent" or "locked" the calculated prices are copied over so they do not change.
Does anyone have another work around they could share?
I would need to get a script that would loop through all the [Order Items] related to [Customer Order] and copy the data from each price field to the secondary field, is this easily achievable?
Any help would be greatly appreciated.