What I am trying to do is create a list of ids for a pick list (subset of all ids). User picks the user and that is used to retrieve data from a releated table. Then I want to display decodes of fields in original table of all ids.

What I tried to do was save the physical record number of table of ids which has the values to be decoded by doing a sql select from two ables, where table b is used to restrict the records goton from table a i.e.

select fields_in_a , recno() from a,b
where a.field = b.field and b.amt > 0
into dbf c

my problem is that recno() returns record number of record in table b not record nunber of table a, recno('a') returns same value for all records, last record number in b.

There must be a simple way to do this. Any help appreciated.