Happy Monday everyone, I hope you all had a nice weekend.
Bit of a complicated one this time, I'm not sure if there is an answer.
We have a vendor we buy goods from, we often ship these goods as assembly builds. We receive rebates for various items from the builds.
We have an overall rebate price which is denoted by the field : {custcol4}
What I am attempting to do is write a formula which says when an build contains certain items, the cost of those items is deducted from this overall amount. I have written the following:
CASE WHEN {item.memberitem} IN ('XXXXXXX','XXXXXXX','XXXXXXX','XXXXXXX') THEN {custcol4}-({item.memberquantity}*{item.cost})
The problem I have of course is the item.cost field. I need it to look at the Netsuite purchasing cost for this item but it does not seem to be able to see this field or indeed of the other fields I have tried - {rate} etc.
As a workaround I came up with the much longer formula below whereby I can specify the cost based on the item:
CASE WHEN {item.memberitem} = 'XXXXXXX' THEN {custcol4}-({item.memberquantity}*'49.09')
WHEN {item.memberitem} = 'XXXXXXX' THEN {custcol4}-({item.memberquantity}*'59.09')
WHEN {item.memberitem} = 'XXXXXXX' THEN {custcol4}-({item.memberquantity}*'69.09')
WHEN {item.memberitem} = 'XXXXXXX' THEN {custcol4}-({item.memberquantity}*'79.09')
ELSE {custcol4} END
However the problem with the above is the it lists the results on individual lines, but what I need to do is add the 4 statements and deduct a total from the {custcold4} amount.
Is what I am trying to do possible?
Thank you for hints and suggestions in advance.
Kind regards
Oliver