• Dynamic View In Peoplesoft

    Posted on May 30, 2012 by in Other Programming, People Soft

    Views are a useful feature of SQL databases, letting us create virtual tables based on SQL select statements.

    PeopleSoft 8 provides the functionality to create dynamic views. These are essentially SQL statements executed on the fly by the PeopleSoft component processor. We can use dynamic views in Peoplesoft pages only because they are Peopletools objects, not SQL Objects.

    Using Dynamic view as Prompt Table

    In this article, we will see the use of Dynamic view as prompt table. Please note that dynamic view can be used for other purposes such as search records which will not discussed in the article.

    One major question that pops up to many of us the first time we use dynamic view, why not use a normal view instead of dynamic view. A dynamic view’s select statement may include Peoplesoft’s meta-SQL, and it may be replaced by a different SQL statement while the user is using the page. And also there might be situations where which you do not save the view in the database.

    Using Edit Table for prompt tables

    Assume that you have page in which user selects a country and depending on the selection you want a different prompt table for the states. For example if user selects US show states for US and user selects Canada show states of canada.

    This can achieved by following the steps below

    Step 1 : Create two views/dynamic views for show the states of US and canada.
    Step 2 : Go to record field ,in our case it is state field (Right click and select View Definition on the state field on the page).
    Step 3 : Right Click and select Record Field Properties
    Step 4 : Select Edits tab and then select Table Edit. Select Prompt table with edit( or No Edit) as per your requirement. Write %EDITTABLE aganist Prompt table.*EDITTABLE is a field in table named DERIVED, provided by peoplesoft.

    Step 5 : Place the EDITTABLE field from DERIVED Table on the page.
    Step 6 : On field change of Country Field, write the below code

    if YOURRECORDNAME.COUNTRY = "US";
      DERIVED.EDITTABLE = "STATE_US_DVW"; // dynamic view of list of states of US
    Else
      DERIVED.EDITTABLE = "STATE_CAN_DVW"; // dynamic view of list of states of CAN
    End-if;
    

    Note : Using views or dynamic views in this situation doesnot change anything. It is up to the user to decide whether he wants two sql objects to be created or not.

    Using SQL Text

    Instead of creating two views/dynamic views, you can also create one dynamic view and pass the SQL dynamically to

    change the prompt table.

    Step 1 : Create and save A Dynamic view
    Step 2 : Give the dynamic view as a prompt table to the state field.
    Step 3 : On field change of the your country field you can write

    if YOURRECORDNAME.COUNTRY = "US";
    	YOURRECORDNAME.FIELD.SqlText = "Select State from MY_STATE_TBL where country = 'US'";
    ELSE
    	YOURRECORDNAME.FIELD.SqlText = "Select State from MY_STATE_TBL where country = 'CAN'";
    END-IF;
    
    Be Sociable, Share!
      Post Tagged with , ,

    Written by

    Vanamali Juvvadi is a Web enthusiast and loves all things design and technology. Founded qnownow with a group of friends to share anything/everything they know/find on the internet.

    View all articles by

    Email : [email protected]

    8 Responsesso far.

    1. prakash says:

      good exclent very helpful

    2. bryanelkins says:

      When I use the SqlText method, PeopleSoft automatically appends: AND EMPLID=:1 to the end of my Sql statement. I want my SQL to not only not limit it to that EMPLID, but I even want to exclude the EMPLID. The lookup works as desired, showing the appropriate values, but when the lookup list closes, the field is highlighted red, indicating that the value is unacceptable and I also get a Sql Error. Any idea how to avoid PeopleSoft appending the EMPLID?

    3. krishna says:

      how can we do the same in search page?

    4. Al Mejia says:

      I keep getting runtime error “There are no prompt values currently available for this field. (4,4)
      The prompt table for this field is currently not specified. Use any value you wish.”
      But it works fine after cliking OK.

    5. Vinod says:

      Hi..

      The dynamic view concept is explained very well here. But the only thing made me to think is the example given.

      So my questions are…

      1) The example has 2 dynamic views created. one for US and one for CAN. when we are using SQLText why we need to create 2 dynamic views.

      2) We have more than 100 countries in this world. If an organization runs in more regions, Do we need to create view for each and every region. In this case, we need to create more than 100 views?

      Please someone respond..

      Thanks in Advance….

    6. shalini says:

      Hi
      I have created a dynamic view and using sqltext but im getting an error message incorect select item number return 8003.Please respond. Thanks in advance

    Leave a Reply