Skip to main content

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?

 

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 = ‘  ritem B to exclude]’ function to work. It spits out “Conversion failed when converting the varchar value 'SOLID SURFACE' to data type int.”

 

Workaround?

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


Terms & ConditionsCookie settings