[sney]

Hoping someone can help with a definitive answer here – have looked over many posts with suggestions but not found a suitable solution!

Basically we want a custom transaction column field that displays the base price of an item in the currency chosen on the transaction. We label this field 'RRP', as we sell to resellers who then on-sell to end users, and most of them want to see the RRP to gauge the discount they're getting as well as a recommendation of what they can sell it for.

So far we've been able to get it to populate with the base price in the company's base currency by setting up the field like this:

Type = Currency

Store Value = T

Source List = Item

Source From = Base Price

…however this will only return the item's base price in our base currency (AUD)…it ignores the currency chosen on the transaction (could be AUD, NZD, USD, etc).

Happy to use a formula in the field if required, as long as we can store the value. Also happy to use a small script to populate the field. Field ID is 'custcolrrp'.

Any help greatly appreciated!

Cheers

Sam
This is a cached copy. Click here to see the original post.

[sney]

Well I figured this out. Steps below in case anyone else is interested in this.

The solution ended up being a custom column field and some client scripting.

The key bits of our script are below, it is triggered on Post Sourcing on the transaction:

// Loads the item record into a variable based on the item on the current line

// Note that 'inventoryitem' won't work for Description Items, Non-Inventory Items, and so on

// A basic IF statement can be used to limit this to run only if currency is NZD and item type is 'Inventory'

var itemRecord = nlapiLoadRecord('inventoryitem', nlapiGetCurrentLineItemValue('item','item'));

// Looks up the Base Price in the loaded item record and stores it in a variable

// 'price8' refers to the pricing sublist (id 'price') + NZD currency (id '8' for us though will vary for you)

// 'price' is what you're getting (the stored price value)

// first '1' refers to the ID of the price level (Base Price will usually be 1)

// second '1' refers to the ID of the quantity level (if quantity-based pricing is turned off, you can only reference 1)

var NZDRRP = itemRecord.getLineItemMatrixValue('price8', 'price', 1, 1);

// Sets the RRP on the line into a custom field 'custcolrrp'

nlapiSetCurrentLineItemValue('item', 'custcolrrp', NZDRRP, false);

For me, understanding and getting getLineItemMatrixValue to work was the hard part, but I got there. Note that 'LineItem' in this function doesn't refer to the item sublist on the transaction, it's referring to the pricing sublist on the item's record. You'll obviously want to include a lot more code around this to perform checks before running, consider the item type to load, to handle errors, and so on – but these are the key bits.

I didn't like the idea of loading the item record just for this purpose (results in an ever so slightly noticeable pause), but it was an important thing for us to get running, so worth it. It seems to handle items with an empty base price field ok – it just returns and writes 'null' to the field (leaving it empty).

Hope this is useful to someone

---

erictgrubaugh replied on 02/13/2017, 12:02 AM: Well done.

[Author]

Posts