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.

Dynamic Views!

16 replies [Last post]
Guest's picture
Guest

Hi Compshack,

Can you give me some information on Dynamic Views?

I am not able to get a clear idea on this particular topic through Peoplebooks. After surfing over the net, I feel that there seems to be a lot on this than mentioned on the books.

I would really appreciate if you can provide some inputs over this especially regarding the usage of the record class property SQLtext

Thanks in advance,
Danny

CompShack's picture
User offline. Last seen 3 years 46 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
Re: Dynamic Views!

Danny,

A Dynamic View doesn’t correspond to any database object. A regular database view is a query executed by the database, however, a dynamic view is a query executed by PeopleTools to populate a page, dropdown list, or bring back data behind a prompt field.

I have used dynamic views to change values in a prompt table depending on the user logged in. To accomplish this, you need to use %oprclause in your where clause that will resolve to OPRID='current operator' at run time. See my post for an example.

usage of the record class property SQLtext

Just a small correction, SQLtext is a field class property and not record class. I would like to think of this property as "edit on the fly". I’m gonna assume that you know what %EDITTABLE is and how to use it. If so, then a simple example regarding SQLtext will clear it up.

Lets say we have field1 on a page that has a dynamic view as its prompt record. You can make the SQL text on the fly and replace what is already behind the dynamic view.

LOCAL string &SQLSTRING;

&SQLSTRING = "SELECT JOURNAL_ID FROM PS_JRNL_HEADER";
IF ALL(JOURNAL_ID) then
        &SQLSTRING = &SQLSTRING | " Where JOURNAL_ID ='" | JOURNAL_ID | "'";
End-IF;

Table1.field1.SqlText = &SQLSTRING;

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

HH's picture
HH (not verified)
Re: Dynamic Views!

Table1.field1.SqlText = &SQLSTRING;

Is the field a field on the table with the prompt record ? or is it the prompt record itself ?

Guest's picture
Guest (not verified)
Re: Dynamic Views!

Hey Compshack,

Thanks a million on sharing the information!

and yeah.....I have done dynamic prompting using %EDITTABLE....just wasn't clear about the usage of dynamic views.....but now I know! :)

Thanks again pal,
Danny

CompShack's picture
User offline. Last seen 3 years 46 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
Re: Dynamic Views!

field1 is on table1 and field1 has a dynamic %edittable set as its prompt table.

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

jag's picture
jag (not verified)
Re: Dynamic Views!

Hi,
Dynamic view is not a DatabaseObject.We Can create one record and insert some fields set the record as a dynamic view.And create one more record and insert some fields,In the record field properties ,Edit tab we can give the prompt table as a dynamic view name(1st record name).What i mean to say is using this we can change the sqltext dynamically and we can change the prompt table values dynamically..
This is the main advantage of this.

Thanks
Jag

Larry's picture
User offline. Last seen 43 weeks 1 day ago. Offline
Joined: 03/18/2008
Posts: 19
Re: Dynamic Views!

I'm trying to use a Dynamic view as prompting to replace XLAT table prompts on several records (long story...don't ask why).

I've defined a new Dynamic View PND_XLAT_DVW7 w/ the following structure:
FIELDNAME
FIELDVALUE
EFFDT
EFF_STATUS
XLATLONGNAME
XLATSHORTNAME

Then I reference that new Dynamic View as the Prompt Table for SYSTEM_SOURCE field on Record PND_ACTIVITY_7. In RowInit PC on PND_ACTIVITY_7.SYSTEM_SOURCE, I define the SQLText property as
Select FIELDNAME, FIELDVALUE, EFFDT, EFF_STATUS, XLATLONGNAME, XLATSHORTNAME From PSXLATITEM Where Fieldname = 'SYSTEM_SOURCE'

Yet I keep getting an "Invalid View text for record PND_XLAT_DVW7..."

My actual view text on PND_XLAT_DVW7 is blank/empty. Didn't think I needed any text there, since it's Dynamic and I've set the SQLText property. In fact, I also tried putting some more general "Select FIELDNAME, FIELDVALUE, EFFDT, EFF_STATUS, XLATLONGNAME, XLATSHORTNAME From PSXLATITEM" in the SQL view text definition, and then I got a hard-SQL error.

Not sure what's going on there. Any ideas?

CompShack's picture
User offline. Last seen 3 years 46 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
Re: Dynamic Views!

hmm,
I don't see anything wrong with what you have done so far, BUT I just have to ask this:

Then I reference that new Dynamic View as the Prompt Table for SYSTEM_SOURCE field on Record PND_ACTIVITY_7.

You have done that by placing %EDITTABLE as the Promt Table behind the field, correct?

Larry, please share your findings with us when you pinpoint the cause of the problem.

Thanks and good luck!

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Larry's picture
User offline. Last seen 43 weeks 1 day ago. Offline
Joined: 03/18/2008
Posts: 19
Re: Dynamic Views!

No, I have defined PND_XLAT_DVW7 as the Prompt View behind the field.

Are you saying I need to define %EDITTABLE as the prompt view on the Record definition, and then use PC to set the Edittable value = 'PND_XLAT_DVW7'???

CompShack's picture
User offline. Last seen 3 years 46 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
Re: Dynamic Views!

Larry, never mind, I was thinking about something else. No you don't need to define %EDITABLE as the prompt view.

Does your prompt work fine just calling the dynamic view from the field prompt without the SqlText code?

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

chandarcst's picture
User offline. Last seen 10 years 10 weeks ago. Offline
Joined: 06/10/2008
Posts: 1
Re: Dynamic Views!

hi,
i am new to peoplesoft.How to working in Dynamic view .Example
regards
chandar

nitin.kumarmv's picture
User offline. Last seen 9 years 47 weeks ago. Offline
Joined: 09/11/2008
Posts: 3
Re: Dynamic Views!

Hi,

I am trying out dynamic prompts....
have 2 fields Semester and Month....
My requirement-
if 1 is entered in the semester,then month to prompt to mon1_tbl,
if 2 is entered in the semester,then month to prompt to mon2_tbl,

have used %edittable in the record field properties for month field in the main table....
have created 2 views for the two prompts and used them in peoplecode, which has the if-else logic for table selection....

the error im getting....

---------------------------
GetField: Field 'DERIVED.EDITTABLE' not found in scroll level 0. (2,155) NIT_MON_COMP.GBL.NIT_MON_TBL.NIT_SEM.FieldChange PCPC:151 Statement:3

The specified field was not found in the indicated scroll level. This may occur with various PeopleCode built-in functions that specify a "scroll context" via a series of records and a field name.

Review the PeopleCode that is generating the message and the page definition and correct as appropriate.
---------------------------
OK
---------------------------

please help me in resolving this...
also came to kno that dynamic prompting can b done using SQLtext...
also tel me if u kno dis....

thanks in advance..
nitin

Lepa's picture
User offline. Last seen 16 hours 21 min ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Dynamic Views!

Duplicate post - here is the response to your original post http://www.compshack.com/forum/peoplesoft/peoplesoft-technical/dynamic-p...

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Vishal's picture
Vishal (not verified)
Re: Dynamic Views!

I am getting a similar error " Invalid view text for record " when I open the page online.
Did you resolve your issue ? If yes please let me know what was it ?

Compshack , can you shed some views on this please.

Thanks

Dev's picture
Dev (not verified)
Re: Dynamic Views!

Larry I am facing similar issue. It would be great if you share your findings. Please let me know is there any other way to solve this issue?

Hari_aindian's picture
User offline. Last seen 4 years 25 weeks ago. Offline
Joined: 08/02/2010
Posts: 84
Re: Dynamic Views!

If you have selected the record type as SQL VIEW then you ve to ve some dummy sql in the view definition. Later on you can override this dummy view defn through code.

Guest's picture
Guest (not verified)
Dynamic Views throwing error!

Hi there,

I am using a page in two diffrent components...1) C1:One with all access 2) C2: for limited access
and used a dynamic view as a search record.
Now when from page1 through a TransferExact comand trying to navigate to page(in component C2).
then ther is an error occured SQL Error .
If i am tryng direct navigation its working as expected.

any one please help me....

Thanks in advance
Ashish

Post new comment

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