Calculation Query

[Former Member]

Some of our customers order single items and some order in cases. We have the master item list set up as 1 for the Items per sales unit and some other number for the Quantity for Packaging uom. This latter # can vary for different items.

I defined a field called U_Case_order in the RDR1 table that will allow the order taker to enter a case quantity. I then set up a Query as follows:

SELECT T0.SalPackUn * T1.U_Case_order AS 'Quantity' FROM dbo.OITM T0 INNER JOIN dbo.RDR1 T1 ON T1.ItemCode = T0.ItemCode AND T1.ItemCode = T0.ItemCode.

Finally, I defined a formatted search for the Quantity field using this query to refresh when I leave the U_Cae_order column. The result is blank with no error message.

Can anyone help?

[former_member583013 · Accepted solution]

Hello Art,

I would ask you to do a simple test. Remove the U_Case_Order and simply mutiple T0.SalPackUn * 1 and check if the query works.

Also, the query you have does not seem to reference the Form variable as Dan has also pointed. Therefore try this

SELECT (T0.SalPackUn * $[/RDR1.U_Case_order]) FROM [[dbo]].[[OITM]] T0

WHERE T0.ItemCode = $[/$38.1.0]

Please remove the / which has to add to avoid the http://

Let me know

Suda

[Former Member · Accepted solution]

Thanks. This query works!

[former_member346201]

You said that you use this query in a FMS on the Order document rows right? But in the query I don't see the use of B1 screen variables? The query is syntactically correct but since you don't use variables you are not pulling the data off the current document when the FMS runs. The query needs to be re-written with variables like this and placed on the QTY column.

SELECT SalPackUn * $[RDR1.U_Case_order.Numeric] FROM OITM WHERE ItemCode = $[$38.1.0]

the $[RDR1.U_Case_order bring in the value that the user enters into case column and the $[$38.1.0] gets replaced with the item code of the current line.

Dan

Edited by: Dan Love on Jan 10, 2008 11:14 AM

Edited by: Dan Love on Jan 10, 2008 11:16 AM

[former_member346201]

I don't know why the "[" is getting replaced by the http:// in my message above but the variable needs to have a dollar sign then the "[" then the varaible name or number and the a closing "]"

Dan

Edited by: Dan Love on Jan 10, 2008 11:20 AM