You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Peoplesoft Dynamic prompt table

3 replies [Last post]
Guest's picture
Guest

Hi All,

I have a page having fields namely Institution,Academic Year,Stream ,Term begin dt,Term End Dt fields respectively.
Requirement is that based upon the values of Institution,Academic Year,Stream field values the fields Term begin dt,Term End Dt should be dynamically prompted.

I created a dynamic view which selects the Institution,Academic Year,Stream ,Term begin dt,Term End Dt
fields from PS_TERM_TBL.

I wrote the peoplecode in the field change of
Stream field Field Change. But im not able to get the dynamic data from the prompt table. My Peoplecode goes like this

Local Rowset &rws0, &rs2;
Local string &ins, &acd, &strm, &tmdt, &tmedt;
Local Field &dt;
Local string &SQLSTRING;
Local Record &Rec;
Local SQL &sqltext;

&rws0 = GetLevel0();

&ins = PPPT_SH_DUE.INSTITUTION.Value;
&acd = PPPT_SH_DUE.ACAD_CAREER.Value;
&strm = PPPT_SH_DUE.STRM.Value;

&SQLSTRING = "SELECT b.institution ,b.acad_career ,b.strm ,b.term_begin_dt,b.term_end_dt FROM PS_TERM_TBL b WHERE b.INSTITUTION='" | &ins | "' AND b.ACAD_CAREER='" | &acd | "' AND b.STRM='" | &strm | "'";

PPPT_SH_DUE.TERM_BEGIN_DT.SqlText = &SQLSTRING;

And my sql in the dynamic view goes like this

SELECT b.institution
,b.acad_career
,b.strm
,b.term_begin_dt
,b.term_end_dt
FROM PS_TERM_TBL b

I kept the prompt table as the dynamic view name for the fields Term begin dt,Term End Dt

In both the view and the table Institution,Academic Year,Stream are the keys.Can anyone tell me where its going wrong??

Thanks

apoliset's picture
User offline. Last seen 7 years 10 weeks ago. Offline
Joined: 04/25/2011
Posts: 3
Re: Peoplesoft Dynamic prompt table

try this :

1> open ur dynamic view and keep the fields term_begin_dt and make the field key in the record field properties. Open the SQL editor and remove the SQL.

2.>In field change :

Local Rowset &rws0, &rs2;
Local string &ins, &acd, &strm, &tmdt, &tmedt;
Local Field &dt;
Local string &SQLSTRING;
Local Record &Rec;
Local SQL &sqltext;

&rws0 = GetLevel0();

&ins = PPPT_SH_DUE.INSTITUTION.Value;
&acd = PPPT_SH_DUE.ACAD_CAREER.Value;
&strm = PPPT_SH_DUE.STRM.Value;

&SQLSTRING = "SELECT b.term_begin_dt FROM PS_TERM_TBL b WHERE b.INSTITUTION='" | &ins | "' AND b.ACAD_CAREER='" | &acd | "' AND b.STRM='" | &strm | "'";

PPPT_SH_DUE.TERM_BEGIN_DT.SqlText = &SQLSTRING;

It should work with this.........

But ur requirment can be easily done with peoplecode rather than going for dynamic view .
Dynamic views are mostly used for dynamic prompts , but in this case as ur saying that Inst and other fields are Keys , u will retrieve a single row and so the code which u written can be used for directly assigning a value to the field like this

Local Rowset &rws0, &rs2;
Local string &ins, &acd, &strm, &tmdt, &tmedt;

&rws0 = GetLevel0();

&ins = PPPT_SH_DUE.INSTITUTION.Value;
&acd = PPPT_SH_DUE.ACAD_CAREER.Value;
&strm = PPPT_SH_DUE.STRM.Value;

&SQL = sqlexec("select %dateout(b.TERM_BEGIN_DT), %dateout(b.term_end_dt) FROM PS_TERM_TBL b WHERE b.INSTITUTION= :1 AND b.ACAD_CAREER= :2 AND b.STRM= :3",&ins
,&acd,&strm,&tmdt,&tmedt);
PPPT_SH_DUE.TERM_BEGIN_DT.SqlText = &tmdt;
PPPT_SH_DUE.TERM_END_DT.SqlText = &tmedt;

u may get errors related to date formats , I hope u can correct those .

Reply back ......

Regards
Aditya

apoliset's picture
User offline. Last seen 7 years 10 weeks ago. Offline
Joined: 04/25/2011
Posts: 3
Re: Peoplesoft Dynamic prompt table

Replace the code
PPPT_SH_DUE.TERM_BEGIN_DT.SqlText = &tmdt;
PPPT_SH_DUE.TERM_END_DT.SqlText = &tmedt;

with

PPPT_SH_DUE.TERM_BEGIN_DT.value = &tmdt;
PPPT_SH_DUE.TERM_END_DT.value = &tmedt;

and make this change i forgot in the previous post

Regards
Aditya

sapthagiri's picture
sapthagiri (not verified)
Re: Peoplesoft Dynamic prompt table

I need to send the input values to a promt table dynamically based on that input values the prompt table should display when user press on loop-up button kindly help me to solve my issue incase please cal me on 09768-350-360

Post new comment

CAPTCHA
The question below is to prevent automated spam submissions.
5 + 15 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.