Exact Row Number of Document Detail

[Former Member]

Hi all,

I want to find out the exact row number of document detail. At first i thought is take linenum in child document table, for example RDR1. But I just reliaze that actually the linenum is not correct if there are text type in it.

For example my line 1 is item, line 2 is text type, line 3 is item again. but i saw in rdr1 the linenum of those item is 0 and 1. what I except is 0 and 2, Anyone have suggestion on it?

PS: I got think about using rdr10.aflinenum to do it. but I still thinking of how to write the query. Since if i rename the aflinenum to linenum , union rdr1 and rdr10 but i cannot control the sorting.

Regards

Wong

[Former Member · Accepted solution]

Hi Wong Jing Dian,

Try to use this select writing <YourDocNum>:

SELECT O.DocNum, R1.LineNum, R1.ItemCode, R1.Dscription, '' As LineText
FROM ORDR As O
LEFT OUTER JOIN RDR1 AS R1 ON O.DocEntry = R1.DocEntry
WHERE O.DocNum = <YourDocNum>
UNION ALL
SELECT O.DocNum, 0 As LineNum, '' As ItemCode, '' As Dscription, CAST(R10.LineText AS VARCHAR(1000)) As LineText
FROM ORDR As O
LEFT OUTER JOIN RDR10 AS R10 ON O.DocEntry = R10.DocEntry
WHERE O.DocNum = <YourDocNum>

BR

Sierdna S.

[Former Member · Accepted solution]

Hi BR,

like I mention before, I want to know the exact line number of the row detail. your sql just can show all the data but not correct row number.

Regards

Wong

[former_member201110 · Accepted solution]

Hi,

If you want to show the row number as it would appear in the first column of the matrix in the screen, you can use the following query:

select 
	1 + VisOrder + isnull((select count(LineNum) from RDR10 T1 where T1.AftLineNum < T0.LineNum and T1.DocEntry = T0.DocEntry), 0) 
from 
	RDR1 T0
	inner join ORDR T2 on T0.DocEntry = T2.DocEntry
where 
	T2.DocNum = 1111 
group by 
	T0.LineNum, T0.VisOrder, T0.DocEntry

Note: VisOrder is the correct column to use in this case because LineNum will show gaps if a line from the document has been deleted.

Kind Regards,

Owen

[Former Member · Accepted solution]

Thank you!!!!!!!

Regard

Wong