The following answers assume that you have these tables and relationships. You'll need to substitute your table and field names for mine:
Have both wholesale and retail unit price fields in the LineItems table. Give each "Looked up value" auto-enter options and set up your cost calculation field with an If function that computes with either unit price based on the value of the Customer::Category field:
If ( Customer::Category = "Wholesale" ; Qty * WholeSaleUP ; Qty * RetailUP )
Define your cost field as a number field with an auto-entered calculation:
If ( Customer::Category = "Wholesale" ; Qty * Products::WholeSaleUP ; Qty * ::Products::RetailUP )
Thanks! Option 1 worked a treat and allows me to keep the historical price data. So even if the prices change in a few weeks, prior orders will keep reflecting the old price (which option 2 couldn't do or I probably implemented it incorrectly).
Thanks a lot for your help, greatly appreciated.
Option 2 uses an auto-enter calculation. The computed value will not change for existing invoices when you change prices in the future.
Auto-enter calculations do not update when you change the value of a field from a related table. (But do make sure to clear the "do not replace existing value" option so that if you modify the Qty field, the cost will update.)