Skip to Content
0

Golden Arrow not showing on Query

Dec 16, 2016 at 03:53 PM

104

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
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
10 |10000 characters needed characters left characters exceeded

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

1
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
10 |10000 characters needed characters left characters exceeded

Hi Roy,

Happy new year. :)

Kind Regards,

Diego Lother

0