Solved

GI to look at total SO pre-tax amount subtracted by specific line on the SO

  • 22 November 2023
  • 3 replies
  • 22 views

Userlevel 5
Badge

Trying to build a GI that looks at total sales price on SOs minus a specific inventory ID.

 

Ex. 

SO# Sales Price Adjusted Price
SO000012 $45,000 $33,000

 

In this scenario, Sales Price is made up of two items (item A priced $33,000 and item B priced $12,000).

 

I’d like to build a GI that creates the table above. Does anyone know what I can enter on the GI to perform this math?

 

icon

Best answer by miguel80 23 November 2023, 01:45

View original

3 replies

Badge +16

Hello,

I think the GI needs to include Sales Order Lines and calculate the two above numbers using the extended Price field.

Sales Price is the Field SoLine.CuryExtPrice.

Adjusted Price will be an If statement: If (SoLine.InventoryID = ‘  [item B to exclude]’,  0, CuryExtPrice). Enter If statement here:

Sort/Group by Order Number, I think, and sum Sales Price & Adjusted Price to calculate the needed totals.

 

Laura

Userlevel 5
Badge

Hello,

I think the GI needs to include Sales Order Lines and calculate the two above numbers using the extended Price field.

Sales Price is the Field SoLine.CuryExtPrice.

Adjusted Price will be an If statement: If (SoLine.InventoryID = ‘  [item B to exclude]’,  0, CuryExtPrice). Enter If statement here:

Sort/Group by Order Number, I think, and sum Sales Price & Adjusted Price to calculate the needed totals.

 

Laura

I’m not able to get the SoLine.InventoryID = ‘  [item B to exclude]’ function to work. It spits out “Conversion failed when converting the varchar value 'SOLID SURFACE' to data type int.”

 

Workaround?

Userlevel 4
Badge +1

If you need to compare ‘SOLID SURFACE’ to inventory you can’t do it with InventoryID (which is in fact a number) you have to link the InventoryItem table and then compare it to the InventoryCD.

Reply


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