Generate Dynamic Drop-down List with SQL Script in Peoplesoft Pages

Hello,

I would like to share my knowledge about how can you create dynamic drop-down list and use in PS pages with some tricks.

Firstly, you need to create new field which must be translate field.

Field length must be four for that field cause we want to use this field with translate values.

Insert this field in a derived record and save it(you don't need to build derived record after saving).

View field properties for new translate field and go to edits tab. Change edit type as table edit and choose translate table edit as bellow;

drag and drop new translate field to a new page and you can see that view of field automatically change like it's a drop-down list.

Save it.

I created a new table to show values;

drop table asdasd;
create table asdasd (seqno number,product varchar2(100 char));
insert into asdasd values(1,'comp');
insert into asdasd values(1,'comp2');
insert into asdasd values(1,'comp3');
insert into asdasd values(1,'comp4');
commit;

and I'm going to use that script;
select seqno,product
from asdasd;

In page activate, type code as bellow;

Local SQL &SQL;
Local Rowset &rset0;
Local string &type, &descr;

/*dönem dropdown list*/
&FLD = GetRecord(Record.IMPRT_GRADE_WRK).GetField(Field.SSR_TERM);
&FLD.ClearDropDownList();
&SQL = CreateSQL("select seqno,product from asdasd;");
While &SQL.Fetch(&descr, &type)
   &FLD.AddDropDownItem(&descr, &type);
End-While;

Finally, you will be able to see values which is result of your select statement in drop-down list in the page.

If you view your field variable restricted, after save your page in App Designer than change field length more than four. So, life will become easier :)

Loves,