Total quantity for an order

[Laura_Nest]

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

[Former Member · Accepted solution]

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

[former_member197621]

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....

[Laura_Nest]

Thank you. this was an acceptable alternative. And since they don't print sales orders or Acknowledgments, I just made it the default form and they just hit the print preview

[Former Member]

If your question has been answered, please close your thread.

[kothandaraman_nagarajan]

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

[former_member186712]

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

[former_member325312]

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

[Laura_Nest]

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

[kothandaraman_nagarajan]

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