Using Dynamic View

Dynamic View:
'View' is nothing but a static sql that pulls data from atleast one record.
'Dynamic View' enables to pass values dynamically ( at runtime ) to the where clause.
Example:
Lets think of a page which has country and state fields.
RECA.COUNTRY -->RECORD.FIELD
RECA.STATE -->RECORD.FIELD

The state field prompt should show the list of states for the country selected in the Country field prompt.
That means the value of the state field depends on the value of the country field.
In this case , we can use dynamic view for the state field ( STATE ).
PS_STATE_DVW -- This should contain STATE as a key field and listbox item and leave the SQL definition as blank and Save.

Setting Record field properties in App designer.

Now add this Dynamic view as prompt to the record field STATE .

Until now , we have only defined that the STATE record will use the PS_STATE_DVW as Dynamic view. But we have not injected the SQL to this dynamic view to pull the required data.

Below is the code, which builds the SQL and attaches to the SQL Editor of the Dynamic view.

Now that the code is ready , we need to know where exactly to place this code? Since the user selects the 'Country value' from the country field , in the COUNTRY field , field change event we can place the below code.

/** Peoplecode Begin **/
/** This is the SQL string which is used to pull the states for a given country **/
&str = "select STATE from PS_CUST_CNTRY_TBL where COUNTRY = (:1) ";
/** ExpandSQLBinds will expand the &str and inserts the values into bind param **/
/** &sql_text_defn = "select STATE from PS_CUST_CNTRY_TBL where COUNTRY = 'IND'" assuming 'IND' is selected on the page. **/
&sql_text_defn = ExpandSQLBinds(&str, RECA.COUNTRY.value);
At this stage the SQL string is ready with its dynamic value (country).
Now, we need to pass this SQL string has to be injected to the Dynamic view SQL Text definition.
/** Passing the SQL string to the Dynamic view **/
RECA.STATE.sqltext = &sql_text_defn;

/*** Peoplecode End ***/
In the PIA, now once we select 'IND' for country field and select on STATE prompt, all states for given country will be listed.
This completes the usage of Dynamic View.

Add Attachment

Uploading files through Add button at level0 and showing the uploaded files onto a grid.
Main records:
Transaction Record at Level 0 : PS_REC_TRANS.
Transaction Record at Level 1: PS_REC_UPLD
Derived Work Record: FILE_ATTACH_WRK or custom record which is clone of the FILE_ATTACH_WRK

Details:
-->Suppose that we have a transaction record PS_REC_TRANS and TRANS_KEY is a Key.
-->Since we are showing uploaded files in the grid, the level1 record (PS_REC_UPLD) should have 2 keys (TRANS_KEY and ATTACHSYSUSERFILE).
-->Now we need to have a derived work record that holds the buttons 'Add Attachment/Delete Attachment/View Attachment'.
There is a delivered record (FILE_ATTACH_WRK) but PeopleSoft recommends to use own custom record which is a copy of delivered record. This recommendation is made so that in future if any changes are done it should not impact the current functionality.

Designing the page:
Add PS_REC_TRANS at level0.
Drag and drop the AddAttachment button onto the page. (level 0).
Optional: Mostly we would like to see the uploaded file next to AddAttachmet button. To accomplish this create a derived work record that holds the ATTACHUSERFILE field.
Insert grid.
Drag and drop the record PS_REC_UPLD onto the grid.
Also include the buttons Delete Attachment and View Attachment onto the grid.
Save the Page.
We need to add peopleocode to the buttons to implement the attachment functionality.
Better if we use Component record field peoplecode, as it will not impact any other comoponent.
Now consider the 'Add Attachment ' Button. Once the user clicks on this button file should be uploaded to the server and then after successful upload filename should appear in the level1 grid.
To make this happen, Declare the function Add_attachment and then call the function. There will be a status field present in the function which will let us know the attachment if failure / success.
Call the level1 rowset and insert the file data. Make sure that DoSave() method to be called.
I will be providing the code in the next post.

Grids

Some pointers using grid.
-> Derived work record cannot be used as main record in the grid.
-> Usually we use views to populate data in the grid.
you can nest a grid in a scroll area, you cannot nest a scroll area in a grid or a
grid in another grid.