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
View original
+18
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
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?
+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.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
