GI (Generic Inquiry) output with line/row number

​@ManishPatel132008 Hey there - depending on the GI, the “Line Nbr.” exists (as an autonumbered field).  When you say Row Number what are you referring to?  Are you trying to format based on, say, an odd or even row but the Line Nbr. doesn’t necessarily reflect the display order?

In order to reference the SO Line number, you need to reference the table SOLine and look for LineNbr

 

If you’re just trying to get a view of the raw ROW_NUMBER() of a result set, you might be able to make use of the ROW_NUMBER() SQL command, though I’m guessing that could get kind of temperamental.
Also be aware that resorting columns wouldn’t recalculate a ROW_NUMBER() function call.

Hi ​@ManishPatel132008 ,

The SOLine table also has a SortOrder field which I think is less likely to have gaps than LineNbr if the user has deleted or reordered the rows on the sales order.

Hope this helps!

Laura

Thanks to all of you for the responses.

What I however wanted was to add a sequential line number, in any or all GIs. So this line number may not necessarily be a value in any of the tables. If the GI output has 10 rows, the line number should start from 1 to 10 and be displayed as a row value. 

Hope I have explained this a little better, 

Thanks anyways.

Hi ​@ManishPatel132008 were you able to find a solution? Thank you!

This can be accomplished with help of nesting Generic inquiry into Generic inquiry.

1. Create Generic inquiry SalesOrdersBase:

FROM SOOrder
ORDER BY SOOrder.OrderNbr
SELECT
  SOOrder.OrderNbr,
  SOOrder.OrderDate,
  SOOrder.CustomerID

2. Then Generic inquiry SalesOrdersWithRowNum

FROM [GI.SalesOrdersBase] AS B
LEFT JOIN [GI.SalesOrdersBase] AS A
  ON 'B.orderNbr' <= 'A.orderNbr'
GROUP BY
  'A.SOOrder_orderNbr',
  'A.SOOrder_orderDate',
  'A.SOOrder_customerID'
ORDER BY
  'A.SOOrder_orderNbr'
SELECT
  COUNT(B.OrderNbr),
  'A.orderNbr',
  'A.orderDate',
  'A.customerID'

execute SalesOrdersWithRowNum and you will see row numbers. The only big caveat would be performance issue, so I’d rather go with SQL View if performance will become important.