Ask Question
Help Others
Home › Forums › PeopleSoft › PeopleSoft Technical

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

  • Post reply
3 replies [Last post]
Wed, 04/13/2011 - 5:25pm
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

Bookmark/Search this post with
  • Buzz Up!
  • del.icio.us
  • Digg
  • Facebook
  • Google Buzz
  • Reddit
  • StumbleUpon
  • Technorati
  • Twitter
  • Yahoo
Top
Mon, 04/25/2011 - 8:23am
#1
apoliset's picture
apoliset
User offline. Last seen 1 year 2 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

Top
  • reply
Mon, 04/25/2011 - 8:48am
#2
apoliset's picture
apoliset
User offline. Last seen 1 year 2 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

Top
  • reply
Tue, 02/28/2012 - 9:21am
#3
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

Top
  • reply

Post new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
The question below is to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.