use non-stock item price as multiplier in Price list GI

  • 5 December 2021
  • 7 replies

Userlevel 4

I'm not sure how to do this but I want to use a non stock item as the "master" pricing record for an item class in a GI so that for all items of the same class I can create a price list that is multiplied by a common GP percentage. 




Best answer by DanielStarke 6 December 2021, 06:08

View original

7 replies

Userlevel 7
Badge +17

Hi @DanielStarke Can you please clarify below doubts?

Blow are GI fields

  • Stock Item Inventory ID
  • Item Class
  • Last Cost
  • RRP → Is this custom field in Stock Items screen?

Second sheet is showing all the Non-Stock items ($ field = Las Cost * RRP) ?

Userlevel 4

I was hoping to be able to build a pricelist GI where it will look at the last cost and then multiply by a number to mark the price up. If this number was a constant for all items, that would be easy, but I want to be able to have different markups for different item classes. I also want colleagues to be able to easily alter the markups without editing the gi. 

I had hoped to create a 'dummy' non stock item for each item class and the pull the 'last cost' for the dummy item and use this as the markup multiplier for the pricelist GI. I plan on hard coding the relevant ones in. If I could do it in SQL it would be a select statement within the select statement to find me the hard coded margin numbers. (Below demo sql is not using the right tables and assumes two classes, but hopefully explains what I'm trying to get)


For the multiplier: 

If  [ClassID] = A, Select [last cost] from [inventory items] where [inventory ID] = [12345], Select [last cost] from [inventory items] where [inventory ID] = [54321]

This will mean that if our sales manager wants to alter the margin on a particular class of product, all he has to do is go to the dummy stock item and alter the last cost (the multiplier) and all items in the pricelist GI will update to the new figures with the new mark-up.

Userlevel 7
Badge +17

Hi @DanielStarke  Can you please try below option. 

  • Create a one custom field at Item Class level → Markup field
  • Do a import scenario to fill the values OR update the values
  • Add a join to the Item Class table and Non Stock Items table and then do calculation (Last Cost * Markup) and show it on the GI for each NonStock item.

Hope this helps!

Userlevel 7
Badge +11

Hi @DanielStarke, @Naveen B 

Instead of using a custom field, may be use can use an attribute and set the markup as default value ( no need to make the attribute mandatory) on Item class profile?



Userlevel 4

I really want to do this the way i have outlined as i have a few different sheets that can use this function, such as where i have several attributes (item surface area, item weight) that i want to be able to multiply from a non-stock item - I’ll leave the question here, but will contact our support partner too.

Userlevel 7
Badge +17

@vkumar  Yes, OOB we can use UDF fields in Item Class screen. :)

@DanielStarke  Do you see any challenges/impacts with this approach?


Userlevel 4

Solved it. I added the inventory table twice (second time with different alias) to my GI and in the relations I added the second appearance with full join on a formula of inventorycd on the parent table equals ="specificitemcd" which allowed me to pull the "baseprice" value for the specific item for use in calculations 


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved