Hi,

I am generating a custom inquiry session for inventory transaction history table (tdinv700) based on the key 2. (i.e. order type, order number). (Form Type 3)

Key Sequence is Order type, Order #,Pos#, Trans Dt, Trans Time,
serl#, Item, container, Warehouse

I want the records to show in the descending order of Transaction Date and Time on the Multi Occurance for every Order type and Order #.

I have more than 1mil rows in the table.

Could you please share your wisdom in solving this problem.

Thanks for your help in advance.

I think descending order is going to be the big obstacle for a display seesion (at least for Baan IV).

Here's one possible poor approach:
Create a new table that contains the primary key fields of tdinv700, plus at least one more additional field that will be used to store the sort sequence per order type / order #. Create a combined field in the new table related to the primary key fields of tdinv700. The index of the new table would be order type, order #, sort sequence

Then whenever this session runs or when doing a display refresh, you will do something along the lines:
clear new table


select tdinv700.*
from tdinv700
order by order type, order date, trans date desc, trans. time desc
selectdo
|if it's a new order type or order #, set sort sequence field to 1
|otherwise increment sort sequence field
|create and insert a record in the new table containing the key values from tdinv700 and the sort sequence
endselect


Once the new table is loaded properly, the display session should display the transactions in the order you want.

Due to the large # of records in your tdinv700, it's hard for me to imagine this solution being preferable to a custom print session.

Another alternative:
add the sort sequence field to the tdinv700 table
create a new index of order type, order #, sort sequence (don't make it a unique key)
create a display session based on this new index

Create a new session that calculates/updates the sort sequence field for all tdinv700.
Run the update sort sequence session as a job.

When new inventory transactions are added to the history, the sort sequence will be 0 so they will appear first, although not in desired order until the job runs. So you would need to run the job often enough to minimize this inaccuracy.