cancel
Showing results for 
Search instead for 
Did you mean: 

Query manager yellow arrow for docnum from QIPF

Risky_Biscuits
Explorer
0 Kudos
234

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.                  
Screenshot 2024-09-19 155714.jpg

 

 

 





Any clues or advice?

SAP Business One 

SonTran
Active Contributor
0 Kudos
Share your query, please

Accepted Solutions (1)

Accepted Solutions (1)

SonTran
Active Contributor

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
Explorer
0 Kudos

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.

Answers (1)

Answers (1)

Risky_Biscuits
Explorer
0 Kudos

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