Inventory descriptions

[DannyC]

Hi,
I want to retrieve from SQL the inventory descriptions listed in Inventory Maintenance, Other, Descriptions.
I need it in the same order they appear in the grid. In Jiwa it appears the sort order is via the key (k1, k2, k3 etc).

I thought if I just order IN_Description by LineNum it would be in the same order as the grid but that isn't the case. Is there some way I can extract the descriptions in SQL by using the key, or at least get it in the same order?
SELECT * FROM IN_Description WHERE InventoryID = 'ABCD1234' ORDER BY LineNum is not returning the same order as the grid.

Cheers

Danny

[Mike.Sheen]

Hi,
I want to retrieve from SQL the inventory descriptions listed in Inventory Maintenance, Other, Descriptions.
I need it in the same order they appear in the grid. In Jiwa it appears the sort order is via the key (k1, k2, k3 etc).

I thought if I just order IN_Description by LineNum it would be in the same order as the grid but that isn't the case. Is there some way I can extract the descriptions in SQL by using the key, or at least get it in the same order?
SELECT * FROM IN_Description WHERE InventoryID = 'ABCD1234' ORDER BY LineNum is not returning the same order as the grid.

Cheers

Danny

There is no LineNum column on the IN_Description table. If you want to get it in the same order as Jiwa gets it, don't ORDER BY at all.

Mike

[DannyC]

Mike,

For some reason my table in the database I am working with has a LineNum column.
If I don't use the ORDER BY and allow the db to sort how it wants, it does not display in the key order.

Never mind, I'll stumble onto something...maybe I can somehow populate that LineNum column with the key number (dropping the leading k) via a breakout?

[Mike.Sheen]

Mike,

For some reason my table in the database I am working with has a LineNum column.
If I don't use the ORDER BY and allow the db to sort how it wants, it does not display in the key order.

Never mind, I'll stumble onto something...maybe I can somehow populate that LineNum column with the key number (dropping the leading k) via a breakout?

If you do a SELECT DescriptionID, Description FROM IN_Description WHERE InventoryID = 'ABCD1234' in query analyser (substitute the 'ABCD1234' with an actual inventory id), does it come back in the same order as in Jiwa ? The answer should be yes. When no ORDER BY is provided, I believe the ordering is the order the rows were inserted originally - so you should get consistent results and it should match what you see in Jiwa (unless your grid in Jiwa is sorted - that's a client-side operation done after getting the results from SQL).

[Scott.Pearce]

My first concern would be why there is a LineNum column when there should not be.

[Mike.Sheen]

My first concern would be why there is a LineNum column when there should not be.

Agreed. This column must have been added by someone and it must have been NULLable - otherwise the Inventory Maintenance form would throw exceptions on edit or creation of the descriptions.

As Jiwa does not know of this column, it does not order by it, so populating it with "desired" values will have no effect.

I've added bug 11382 to add an ItemNo column to that table to allow items to be ordered (Version 7).