Skip to Content

Total quantity for an order

I am trying to create a FMS to put in the Remarks box so the user can click to initiate a sum of the quantity of all the lines on the order, before Adding the order. I successfully created the query if the order has been added and the user brings it back up and runs the FMS to get the total of the quantity for all the lines (which looks something like this below). But I need to get the total qty as a checking method during order entry to match to the total qty the customer sends on the PO.

any suggestions appreciated.

thanks,

Laura

works after order is added:

declare @tq as float

SEt @tq= (select(sum (t1.quantity)) FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry =
T1.DocEntry and (t0.docnum = $[$8.0.0]))

select @tq

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

6 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Sep 05, 2013 at 03:28 PM

    Hi,

    This is not possible by FMS. You may only get the line total after the document is.added. Try SDK if you need such result.

    Thanks,

    Gordon

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 06, 2013 at 12:10 PM

    Hi Laura,

    Before add not possible to fetch the sum of quantity in FMS, but you try below solution,

    1. Create one PLD name it Qty
    2. In the "Qty" PLD hide all the fields except Repetitive Area Footer0
    3. create one formula field and past the formula ColSum("F_Qty")
    4. Before add, ask the user to press Alt + F+ E
    5. And double click the Qty pld
    6. now user can see the sum of quantity

    I hope this helpful....

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 06, 2013 at 03:20 AM

    Hi,

    Tested with above query and transferred to total quantity to PO, not getting correct result. You may simplify the above query like below

    SELECT sum(T1.[Quantity]) FROM ORDR T0
    INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry WHERE t0.docentry =
    $[ORDR.docentry]

    So, I would recommend to use system calculation for required quantity at the time of raising PO, if available quantity falls into negative.

    Further system also includes committed quantity from sales and production order as well.

    Thanks & Regards,

    Nagarajan

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 06, 2013 at 05:51 AM

    hi Laura Nesteriak

    when you enter line total we can capture sum of quantity of all items before adding the document.

    Add this simple query to UDF in the form of FMS so you can get sum of quantity for particular Document

    SELECT sum(T1.[Quantity]) FROM ORDR A
    INNER JOIN RDR1 B ON A.DocEntry = B.DocEntry WHERE A.docentry = $[ORDR.docentry]

    Regards

    Jenny

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 06, 2013 at 06:29 AM

    Hi Laura,

    I agree 100% with Former Member when he says that "this is not possible by FMS".

    before saving the document and when you use a FMS in the document field or in a UDF you can't get values from the document lines.

    If you use a FMS in the document lines you can only get information from the line that the FMS is running and not from all lines.

    After saving the document your FMS works because the query is getting data from the table.

    Hope it helps

    Augusto

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 06, 2013 at 06:59 AM

    Hi,

    "I am trying to create a FMS to put in the Remarks box so the user can click to initiate a sum of the quantity of all the lines on the order, before Adding the order."

    ----Not possible to get total quantity before adding the document.

    I successfully created the query if the order has been added and the user brings it back up and runs the FMS to get the total of the quantity for all the lines (which looks something like this below).

    ------This is true, after adding, the quantity will be updated in remarks field.

    But I need to get the total qty as a checking method during order entry to match to the total qty the customer sends on the PO.

    ------Recommendation:

    1. To get total quantity in PO, the user you need to update sales order number in PO ( Create new UDF and add your sales document number)

    2. Assign below query in PO remarks field to get total quantity of SO.

    SELECT T0.[U_CustWO] FROM ORDR T0 WHERE T0.[DocNum] = $[OPOR.U_rgDNno]

    Add your UDF fields in above query.

    3. The total quantity of the SO will be updated in remarks field of PO.

    Thanks & Regards,

    Nagarajan

    Add a comment
    10|10000 characters needed characters exceeded

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.