Great question. For adding to the discussion, a few more things.

If you want to store multiple rows in a work record, you would either have to

1. place the work record in level 1.

is that doable ? probably not . You need a real record on a level 1/2/3. You cannot create a level 1 work record based on a work record only.

One options could be to fool it into believing that there is a real record. ( creating a view for e.g. ) and that view and the work record can be on a
scroll level 1. I remember having read about it somewhere.

There might be other complications. Like making sure that at save time the rows are not saved. probably checking the no autoupdate flag will do the trick.

2. Create a standalone rowset based on work record.

Have not tried either methods.

Maybe someone can comment.

I am just wondering can work can hold more than one row during any processing?

I don't see why not. That's being said, you should be careful using derived/work record with processing that performs some kind of commits on data. In application engine for example, STAT records that are of type derived/work have their field values reinitialized after each commit. So, prior to the commit, your values are there, and then after the commit they are all gone!

Thanks Compshack!!! The state record will contain only one row during AE Processing. This record can be a work record or sql table. In PeopleCode Processing, can work record/ Derived record will hold more than one row?

Well, Let’s think about this for a moment. A derived/work record is not a database table, so you can't use SELECT, UPDATE, INSERT on it. And as HH said in comment #1 – a derived record can't be the only record on a grid in any level. But once it is on a grid with another main record, you can loop through the rowset and fetch values corresponding to different rows. The derived record acts like a place holder.

From your post above, it sounds like you are not using the record in an Application Engine program. Are you using it for online page processing? If you are, then there is no limitations that I'm aware off except for what have been mentioned already.

Compshack,

Have you tried creating a rowset ( standalone) based ona derived work record and the populated it wit multiple rows ?

Just did that and here is the code - It works. So, if you don't want to use temp tables, then use stand alone rowset along with a derived record.

After this, you can loop through the rowset and get the data that you need. The standalone rowset in this example holds over 2000 rows.

awesome !! thank you.