Solved

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

• 3 replies
• 22 views

Userlevel 5
• Varsity I
• 98 replies

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

+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

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
+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.