Lookup/Prompt Exclusion Table

Auto search on the prompt/lookup table is handy, so users can see the immediate search results as soon as he/she click lookup icon. However, this can be extremely painfully slow if you have ten/hundred-thousandths rows in the table.

From PeopleTools 8.44, the lookup/prompt table initial auto search can be suppressed by adding the record definition name into "Lookup Exclusion Table".

Here is the path:
PeopleTools > Utilities > Administration > Lookup Exclusion Table

Have fun!

How to use SQLExec with a criteria using IN or NOT IN

SQLExec is one of the most powerful peoplecode command to execute your SQL statement. Of course, there are several other ways to accomplish the same thing, such as CreateSQL, GetSQL, etc...If you ask me what I prefer, it really comes down to whether I need to loop through multiple rows vs single row from table. If multiple rows, CreateSQL and GETSQL will do a fantastic job, otherwise SQLExec is very efficient in fetching single row data.

Now and then, you will use IN or NOT IN SQL criteria to fetch a row from table in peoplecode. This example will use SQLExec technique.

Let say you have a string of values like this: 'A','B','C'. You want to use insert this string as a parameter in the SQLExec.

Bad Example:

Local string &in_values = "'A','B','C'";
Local string &out_val;

SQLExec("Select min(field1) from ps_table_1 Where field1 NOT IN (:1)", &in_values, &out_val);

Result: Bad


Good Example:

Local string &in_values = "'A','B','C'";
Local string &sql_cmd;
Local string &out_val;
&sql_cmd = ("Select min(field1) from ps_table_1 Where field1 NOT IN (" &in_values ")";

SQLExec(&sql_cmd, &out_val);

Result: Good


Happy Coding... :)

Component Error "You Are Not Authorized..."

This morning I was helping my colleague troubleshooting this common peoplesoft component error "You are not authorized to access this component. (40,20)"

It's not fun, HUH!...

Have you checked?
1. Component in the menu
2. Access to component in the permission list
3. permission list in the role
4. the role assigned to the user profile
5. clear browser cache, close the browser, reopen browser
6. clear and reboot app server cache
Ya..Ya..all checked, but why it still errors out??? I know your frustration, and don't bang your head against the table yet.

This is what I found today that I want to add it into the list:

7. non-existing permission list assigned to the role.

What!! How could it be possible?

The only way that non-existing permission list in the role is by doing a project migration from database to database. You may think the permission list in the project, but apparently not.

If you were like me, I do not add roles, permission lists, menu in my project. Unless you are the sole developer for the system, I would encourage to stay away from it.

Good Luck!

Code Tip:
Here is a SQL to check if there is non-existed permission list which is still attached to role:

Select R.rolename
, RC.classidFrom psroleuser R
      , psroleclass RCWhere RC.Rolename = R.Rolename
    And not exists
          (Select 'X' from psclassdefn C
           Where C.classid = RC.classid)
    And R.roleuser = '[--OPRID--]'

Note: Replace [--OPRID--] to the actual user oprid.

Cool Trick! Dynamic Prompt Table Using PeopleCode

Prompt table is used frequently in peoplesoft pages to eliminate human input error to the database. It is simple and easy to use for end users. Most of the prompt tables are displaying same results to all users from the underline table or view. However, there is time when you need to display different result based on a page/field condition, a previous field selection, or perhaps a different group of users. Can we do this?

YES! Have you heard of using dynamic view and sqltext in peoplecode? If you have not, then this maybe useful for you.

The cool thing about using a sqltext in peoplecode is to override the sql object in your view. First, you need to create a dynamic view that you will use as a prompt table. You may want to write a generic SQL in the view with all the fields you need to display in the search result.

Once you finished with creating dynamic view, then you can assign it as a prompt table edit. When the user click on the prompt table lookup from the page, it will display results from your generic SQL.

Now, in your peoplecode you can modify the generic SQL in the dynamic view by writing your own SQL with additional criteria using sqltext.

/* Update SQLText of the dynamic view */
RECORD_NAME.RECORD_FIELD.SqlText = "SELECT VAL1, VAL2 FROM PS_TABLE Where field_criteria = '" | &criteria_variable | "'";

Note:
1. Replace RECORD_NAME, RECORD_FIELD, VAL1, VAL2, PS_TABLE, field_criteria with your own
2. Assign criteria value to &criteria_variable

That's it, easy, huh!

Aha! BI Publisher - using XML element value in text form field options

This is exactly what I need!!! I spent hours trying to figure out how I can change background table cell using XML data element dynamically instead of using static colors.

This code below is to use XML data element value in the text form editor:

<xsl:value-of select="{XML_ELEMENT}">

Note: replace {XML_ELEMENT} above with the actual element tag in your XML.

Ok, now I know that I can use XML value in my text editor, then I can continue with shading background color in my table cell. I have fld_Y_XML_COLOR_1 as my XML element and its value as actual color name like "lightgrey" or RGB code like "#CCCCCC" (It works both ways).

Note: I am adding if statement below to only shade with a background color when the xml value exists. If xml value has null value, you end up with blackout cells.

<?if:fld_Y_XML_COLOR_1 != ''?>
 <xsl:attribute xdofo:ctx="block" name="background-color">
   <xsl:value-of select="fld_Y_XML_COLOR_1"/>
 </xsl:attribute>
<?end if?>  

I have fun playing with it, so here is my end result: