Most of the times we would have faced a requirement of having Dynamic prompt table for Record Fields. Also PeopleSoft itself very much utilizes Dynamic Prompt table. How can we achieve the same in our project????

Here we go

This is accomplished by 2 ways

  1. Using EDITTABLE fields for Prompt Table
  2. Using Dynamic Views

1. Usage of EDITTABLE Fields

Ppl1

In this method, Prompt table property of Record Fields should be assigned with %EDITTABLE value. Actually what does it mean????

Prompt Table value for the Record Field is populated from the Record Field DERIVED.EDITTABLE value. The Record Field DERIVED.EDITTABLE should be assigned with value either in one of the Peoplecode events (Either in FieldChange or FieldEdit or RowInit events). This is simply done by

DERIVED.EDITTABLE = “PERSON_NAME”;

Note: EDITTABLE Field should be present in the SAME Page, where Record Field (In this case, The Field is nothing but EMPLID) is also referred. If the Record Field is not coming under Component Search Record Field, there is no need for placing the EDITTABLE field in the Page.

2. Usage of Dynamic Views

As we know, while creating Dynamic view, there is no need to specify the SQL. This SQL should be generated dynamically and the same should be assigned to the Record Field.

Ppl2

Say for example, if we see the above Record Field TASK_PROFILE_ID, it is assigned with dynamic prompt table view TL_TSKGRP_DVW and this view TL_TSKGRP_DVW is dynamically initialized by the following Peoplecode, which can be assigned both in FieldChange and RowInit events of the Record Field TASK_PROFILE_ID.

RECORDNAME.TASK_PROFILE_ID.SqlText = “SELECT T.TASKGROUP, T.TASK_PROFILE_ID, T.DESCR FROM PS_TL_TSKGRP_PRF_W T WHERE T.TASKGROUP = ‘” | &TSKGRP | “‘ AND T.EFFDT =(SELECT MAX(T1.EFFDT) FROM PS_TL_TSKGRP_PRF_W T1 WHERE T1.TASKGROUP = T.TASKGROUP AND T1.TASK_PROFILE_ID=T.TASK_PROFILE_ID AND T1.EFFDT<= %datein( ‘” | &maxdate | “‘) )”;

Both &TSKGRP, &maxdate are dynamic bind parameters.

Posted by Kannappan Krishnan
Comments (12)
December 21st, 2007

Comments (12)

Syed Usman Ahmed - January 25th, 2011

I would like to appreciate who initiate it , its really working :)

navata - June 23rd, 2010

WHERE EMPLID=:1

Suresh - September 7th, 2009

Hi, I am running an application engine in restratable mode. When it terminates abruptly im restarting it from process request page. instead of starting from previous commit point it is starting from the begining, can any one help me?

kasi - September 1st, 2009

Hi Gurus Can any one tell me how to pass a parameter from a Rowinit peoplecode to a dynamic view... Suppose if i have dynamic view with the following SQL: SELECT * FROM XYZ WHERE A = &val -- How to pass the &val through the rowinit peoplecode. Please guide me...

Manish Jain - August 17th, 2009

Thanks buddy :) It helped me.

suresh ravilla - March 24th, 2009

could you pls let me know how to bind values in a dynamic view from a page. Eg: i have a dynamic view like this "select location from ps_job where emplid=:1" now i need to bind emplid with the value from the peoplesoft page. 1)is the view correct? 2)if so how to do this?

kasiram - March 17th, 2009

Hi kannappan, I tried same way, AD is not allowing me to save the code it self, it is saying that SQLText is not the property. Waiting for your idea, Thanks in advance

Priya - October 7th, 2008

Hi Kannapan, The dynamic view worked perfectly fine. First I was trying to create the View and Dynamic View with the same number of fields which did not work out. Later I referred to the Rec.Field - GPUS_RPT_CMP_RC.COMPANY and found that I was wrong creating the Dynamic view. It worked perfectly fine. Thanks. Narasimha - Your SqlText does not pass any values from the page(I mean building the values dynamically), in this case the values can be directly fetched from the view that can be assigned as a prompt table. Not to point out - trying to explain and if it can help in anyway.

Narasimha - July 7th, 2008

hi krishnan It's beautiful explanation. I tried same way,however there is no error thrown and not displaying any values.can u pls let me know if i need any other set up. POSN_APPLIEDFOR.STATUS_REASON.SqlText = "SELECT A.STATUS_AREA,A.STATUS_CODE, A.STATUS_REASON, A.DESCR, A.DESCRSHORT FROM PS_ER_STS_RSN_TBL A WHERE A.STATUS_AREA = '3' AND A.EFFDT=(SELECT MAX(B.EFFDT) FROM PS_ER_STS_RSN_TBL B WHERE B.EFF_STATUS = 'A' AND B.STATUS_AREA = A.STATUS_AREA AND B.STATUS_CODE = A.STATUS_CODE AND B.STATUS_REASON = A.STATUS_REASON AND B.EFFDT <= GETDATE() )"; The above peoplecode syntax i have used to populate values

bhadra - March 27th, 2008

Thanks ! it has been explained very well.

S - February 7th, 2008

Hi, Could not achieve this .I have tried same way using sqltext ,there is no error thrown and not displaying any values.can u pls let me know if i need any other set up. Thx S

Aravind - January 21st, 2008

krishnan thanks a lot for such a beautiful explnanation :)

Comments are closed.