Yellow arrow - SQL Query in SAP business One

[former_member588108]

Hello,

Is it possible to get yellow arrow in the query manager in SAP Business One?

I have query report and I need to get yellow arrow for Business Partner, Account Code and DocNum. On this moment I've got only in BP Code.

I tried to make connections using different JOIN but without result.

My query is:

SELECT T0.[CardCode], T0.[CardName], T1.[AcctCode], T0.[DocDate], T0.[TaxDate],
T0.[DocDueDate], T0.[VatDate], 'FZ' [Typ dokumentu], T0.[DocNum], T0.[NumAtCard],
T1.[ItemCode], T1.[Dscription], T1.[U_Opis], T1.[Quantity], T1.[Price],
T1.[Currency], T0.[DocTotal]
FROM OPCH T0
INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry]
UNION ALL
SELECT T0.[CardCode], T0.[CardName], T1.[AcctCode], T0.[DocDate], T0.[TaxDate],
T0.[DocDueDate], T0.[VatDate], 'FKZ' [Typ dokumentu], T0.[DocNum], T0.[NumAtCard],
T1.[ItemCode], T1.[Dscription], T1.[U_Opis], T1.[Quantity], T1.[Price],
T1.[Currency], T0.[DocTotal]
FROM OCPI T0
INNER JOIN CPI1 T1 ON T0.[DocEntry] = T1.[DocEntry]
UNION ALL
SELECT T0.[CardCode], T0.[CardName], T1.[AcctCode], T0.[DocDate], T0.[TaxDate],
T0.[DocDueDate], T0.[VatDate], 'AFZ' [Typ dokumentu], T0.[DocNum], T0.[NumAtCard],
T1.[ItemCode], T1.[Dscription], T1.[U_Opis], T1.[Quantity], T1.[Price],
T1.[Currency], T0.[DocTotal]
FROM ORPC T0
INNER JOIN RPC1 T1 ON T0.[DocEntry] = T1.[DocEntry]
UNION ALL
SELECT T0.[CardCode], T0.[CardName], T1.[AcctCode], T0.[DocDate], T0.[TaxDate],
T0.[DocDueDate], T0.[VatDate], 'SFKZ' [Typ dokumentu], T0.[DocNum], T0.[NumAtCard],
T1.[ItemCode], T1.[Dscription], T1.[U_Opis], T1.[Quantity], T1.[Price],
T1.[Currency], T0.[DocTotal]
FROM OCPV T0
INNER JOIN CPV1 T1 ON T0.[DocEntry] = T1.[DocEntry]
UNION ALL
SELECT T0.[CardCode], T0.[CardName], T1.[AcctCode], T0.[DocDate], T0.[TaxDate],
T0.[DocDueDate], T0.[VatDate], 'FS' [Typ dokumentu], T0.[DocNum], T0.[NumAtCard],
T1.[ItemCode], T1.[Dscription], T1.[U_Opis], T1.[Quantity], T1.[Price],
T1.[Currency], T0.[DocTotal]
FROM OINV T0
INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]
UNION ALL
SELECT T0.[CardCode], T0.[CardName], T1.[AcctCode], T0.[DocDate], T0.[TaxDate],
T0.[DocDueDate], T0.[VatDate], 'FKS' [Typ dokumentu], T0.[DocNum], T0.[NumAtCard],
T1.[ItemCode], T1.[Dscription], T1.[U_Opis], T1.[Quantity], T1.[Price],
T1.[Currency], T0.[DocTotal]
FROM OCSI T0
INNER JOIN CSI1 T1 ON T0.[DocEntry] = T1.[DocEntry]
UNION ALL
SELECT T0.[CardCode], T0.[CardName], T1.[AcctCode], T0.[DocDate], T0.[TaxDate],
T0.[DocDueDate], T0.[VatDate], 'AFS' [Typ dokumentu], T0.[DocNum], T0.[NumAtCard],
T1.[ItemCode], T1.[Dscription], T1.[U_Opis], T1.[Quantity], T1.[Price],
T1.[Currency], T0.[DocTotal]
FROM ORIN T0
INNER JOIN RIN1 T1 ON T0.[DocEntry] = T1.[DocEntry]
UNION ALL
SELECT T0.[CardCode], T0.[CardName], T1.[AcctCode], T0.[DocDate], T0.[TaxDate],
T0.[DocDueDate], T0.[VatDate], 'SFKS' [Typ dokumentu], T0.[DocNum], T0.[NumAtCard],
T1.[ItemCode], T1.[Dscription], T1.[U_Opis], T1.[Quantity], T1.[Price],
T1.[Currency], T0.[DocTotal]
FROM OCSV T0
INNER JOIN CSV1 T1 ON T0.[DocEntry] = T1.[DocEntry]

I need yellow arrow here:

Best Regards

Kamila

[azizelmir · Accepted solution]

Hi Kamila,

The yellow arrow is only available for fields that represent keys of a table and not shown in queries that contain groupings, Archive tables,contain UNION and temporary tables.

Try to create a Views on SQL server and select it from SAP Query manager.

Thank you,

Aziz El Mir

[former_member588108]

Yes in Select 🙂 thank you Aziz 🙂

[former_member588108]

Hello Aziz,

Thank you!

Great, everything works fine if I have a views in SQL and in the SAP query manager select from this view 🙂

But I can not add selection criteria, e.g. date from to.. 😞

Best Regards

Kamila

[azizelmir]

You can add selection criteria to your views.

Thank you,

Aziz

[former_member588108]

Hi,

Thanks Augustin,

I use DocEntry to get yellow arrow to drill down original document but for AcctCode yellow arrow still not working, even if I use OACT table. It's work only for one part of this query (it does not matter if I use INNER or LEFT JOIN, the result is the same).

If I run query only for one type of doc.(eg. for AP Invoice) I have yellow arrow for AcctCode, but if I run with UNION ALL to another doc.(AP Invoice + AP Credit Note) yellow arrow disappears.

I am not SQL expert, so I do not know.. Is there another method/possibility to connect many tables (not UNION ALL).?

Regards

Kamila

[agustin_marcoscividanes]

Hi

in the docnum field you cannot obtain the yellow arrow because this field may be not unique in the database.

The account code, I think it should appear. Try to obtain from OACT table.

Kind regards

Agustín