Exact Row Number of Document Detail
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.
Owen Slater replied
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.