Query manager yellow arrow for docnum from QIPF

[Risky_Biscuits]

Hi,

I created a query on Query Manager, to pull DocNum from QIPF (Landed Costs).
Somehow the Yellow Arrow, when clicked always pointed to the document in the year 2017. 

So far I haven't encountered this issue for DocNum on Sales Orders or other documents.                  

Any clues or advice?

SAP Business One 

[SonTran]

Share your query, please

[SonTran · Accepted solution]

Hi,

The query is ok but you need to check document numbering for Landed Cost. You may defined same document number for multi period (restart numbering).

For example:

2017: 1 >>> 9999

2024: 1 >>> 9999

In this case, It is better to use Internal Number instead of Document Number for Drill Down.

Hope this helps,

SonTran

[Risky_Biscuits · Accepted solution]

Hi,

Now it makes sense why my other documents have no issue with it.
Yes, we do have the numbering reset for this document.

Guess nothing can be done via the query. It's just how the yellow arrow works.

[Risky_Biscuits]

This is the query:

SELECT T0.[CardCode], T0.[SuppName], T2.[WhsCode],
FORMAT(sum(T2.[Quantity]), '#,##0') as Qty,
FORMAT(SUM(T2.[TtlCostLC]), '#,##0.00') as 'Landed Cost', 
T0.[DocDate], T0.[DocNum], T0.[DocEntry]
FROM OIPF T0 
INNER JOIN IPF1 T2 on T0.DocEntry = T2.DocEntry
INNER JOIN OITM T3 ON T2.ItemCode = T3.ItemCode
INNER JOIN OITB T4 On T3.ItmsGrpCod = T4.ItmsGrpCod
WHERE (T0.[DocDate] >= [%1] and T0.[DocDate] <= [%2])
and (T0.[CardCode] >= [%3] and T0.[CardCode] <= [%4])
and T4.[ItmsGrpNam]  <> ''                                  
GROUP BY T0.[CardCode], T0.[SuppName], T2.[WhsCode], T0.[DocDate], T0.[DocNum], T0.[DocEntry]

When I click the yellow arrow on the Internal Number (T0.[DocEntry]), I will be linked to the correct document