Skip to Content

AR Invoice Delivery Table Links

Hello

I'm trying to create a query that will show me sales invoice details, delivery details so I can indicate if its delivered or not and paid details so I mark as paid or not.

The query codes I have are as follows but I can't get links correct to give me the right report:-

SELECT T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T1.[Dscription], T1.[Quantity], T1.[ShipDate], T1.[LineTotal], T2.[DocNum], T3.[Dscription], T3.[Quantity], T3.[ShipDate], T1.[OpenSum], T0.[PaidToDate], T4.[SlpName] FROM OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry], ODLN T2 INNER JOIN DLN1 T3 ON T2.[DocEntry] = T3.[DocEntry] INNER JOIN OSLP T4 ON T0.[SlpCode] = T4.[SlpCode]

Any help with this would be really apprecaited.

Many thanks

James

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Sep 25, 2017 at 12:00 PM

    Hi,

    Try this query and add if any additional field required,

    SELECT T0.Docentry, T0.[DocNum], T0.[DocDate], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[ShipDate], T3.[DocNum], T3.[DocDate] FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.[DocEntry] = T1.[DocEntry] LEFT JOIN INV1 T2 on T2.[BaseEntry] = T1.[DocEntry] and T2.[BaseLine] = T1.[LineNum] INNER JOIN OINV T3 ON T2.[DocEntry] = T3.[DocEntry] WHERE T0.[DocDate] between [%0] and [%1]

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 25, 2017 at 12:16 PM

    I am not receiving such error message. Please copy and paste in query generator and then execute.

    Add comment
    10|10000 characters needed characters exceeded

    • Thanks, I was running it through an ODBC connection but it works from Query generator - think it was because it needed posting date parameters putting in.

      I will have a play and see how i get on now.

      Many thanks for your help.

      James