Getting totals from different documents

[Former Member]

Hi all

I need help i need , I have a report for loading goods in despatch.

I created a query for the Loading List report and so far its showing me the info I need except one problem.

I need the report to show me the totals for the invoice from the lines , the customers pay a different price ( we using special prices) .

I need the report to show me totals for each product.

please check this query below

SELECT T1.ItemCode, T1.Dscription, sum ( T1.Quantity) as 'QTY', T1.U_RouteID_Row as 'Route ID',t0.u_rtname as 'Route Name', T4.ItmsGrpNam

FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode INNER JOIN OITB T4 ON T3.ItmsGrpCod = T4.ItmsGrpCod

WHERE T0.DocdueDate = '[%0]' and t0.u_rounds = '[%1]' and t0.docstatus = 'O' and t0.u_routemarktn = [%2]

GROUP BY T1.ItemCode, T1.Dscription, T1.U_RouteID_Row, T4.ItmsGrpNam,t0.u_rtname

ORDER BY T1.U_RouteID_Row

[Former Member · Accepted solution]

Hi

I tested your query and added the item total from invoice. Without your UDF, it is no problem at all.

Try this first and add your UDF one by one:

SELECT T1.ItemCode, T1.Dscription, sum(T1.Quantity) as 'QTY', sum(T1.Linetotal) as 'Total',T4.ItmsGrpNam
FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode
INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode
INNER JOIN OITB T4 ON T3.ItmsGrpCod = T4.ItmsGrpCod
WHERE T0.DocdueDate = '[%0]' and t0.docstatus = 'O'
GROUP BY T1.ItemCode, T1.Dscription,  T4.ItmsGrpNam

Thanks,

Gordon