0

# Golden Arrow not showing on Query

Dec 16, 2016 at 03:53 PM

144

Hey guys - is anyone able to state why the Golden Arrow wont appear on DOCUMENT NUMBER for my below query?

Not essential but would be nice to have - thanks

SELECT T0.DocNum AS 'Document Number'
, T1.CardType AS 'Supplier / Customer'
, SUM (T2.LineTotal) AS 'Total ex-VAT'
, SUM (T2.VatSum) AS 'VAT'
, SUM (T0.DocTotal) AS 'Document Total'
, T0.DocDate AS 'Posting Date'

FROM OPCH T0
INNER JOIN PCH1 T2 ON T0.DocEntry = T2.DocEntry
INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
WHERE T0.[DocDate] BETWEEN [%0] AND [%1] AND T0.[Canceled] = 'Y'

GROUP BY T1.CardType
, T0.DocNum
, T0.DocDate

UNION ALL

SELECT T0.DocNum AS 'Document Number'
, T1.CardType AS 'Supplier / Customer'
, SUM (T2.LineTotal) AS 'Total ex-VAT'
, SUM (T2.VatSum) AS 'VAT'
, SUM (T0.DocTotal) AS 'Document Total'
, T0.DocDate AS 'Posting Date'

FROM OINV T0
INNER JOIN INV1 T2 ON T0.DocEntry = T2.DocEntry
INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
WHERE T0.[DocDate] BETWEEN [%0] AND [%1] AND T0.[Canceled] = 'Y'

GROUP BY T1.CardType
, T0.DocNum
, T0.DocDate

ORDER BY T0.DocDate

Regards

Roy

DIEGO LOTHER Dec 16, 2016 at 04:11 PM
0

Hi Roy,

As I know the golden arrow is linked to the primary key of a table. In your case de primary key is DocEntry for the document table.

Hope it helps.

Kind Regards,

Diego Lother

Show 1 Share

Hi Roy,

I forgot to mention. As you use a union clause, the golden arrow will not work 100%, because the golden arrow does not know what document the result is linked, then all the golden arrow will be connected with the last document table that you used, in this case OPCH.

Kind Regards,

Diego Lother

Roy Bright Jan 03, 2017 at 10:01 AM
0

Thanks, Diego and happy new year.

It's not essential so thanks for explaining :)

Regards

Roy

Show 1 Share

Hi Roy,

Happy new year. :)

Kind Regards,

Diego Lother