Skip to Content
author's profile photo Former Member
Former Member

Calculation Query

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?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jan 10, 2008 at 07:47 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 10, 2008 at 07:14 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.