# 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

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

10|10000 characters needed characters exceeded

### Related questions

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

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

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

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

10|10000 characters needed characters exceeded
• I did try this but it shows 0.00 - if you run this on an order you have already added it works but I want to get the total before press the Add button. thanks anyway

• 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

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]

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

Thanks & Regards,

Nagarajan