Skip to main content
Solved

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


Forum|alt.badge.img

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?

 

Best answer by miguel80

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.

View original
Did this topic help you find an answer to your question?

3 replies

Laura02
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3135 replies
  • November 22, 2023

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


Forum|alt.badge.img
  • Author
  • Varsity II
  • 109 replies
  • November 22, 2023
Laura02 wrote:

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?


miguel80
Semi-Pro I
Forum|alt.badge.img+1
  • Semi-Pro I
  • 68 replies
  • Answer
  • November 23, 2023

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


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings