Saturday, May 06, 2006

Lookup Exclusion Table

One of the catchphrases that I have adopted (I think from Cary Milsap of Hotsos) is 'the fastest way to do something is not to do it at all'. Instead of making something take less time by tuning, it is much better make it take no time at all by not doing it in the first place!

This applies to PeopleSoft as much as anything else. In this posting I want to draw attention to the Lookup exclusion table. Lots of fields in PeopleSoft applications have lookups associated with them, indicated by the magnifying glass icon.

Click on image to enlarge
When you click on that you are taken to a lookup dialogue, but normally the search fires automatically. Sometimes the operator must wait a while for the SQL queries to return the data. If there is a lot of data only 300 rows are retrieved into the component buffer, and only the first 100 rows of that set are shown. For example, here is an example from Application Hire in HR illustrated with the demo database.

Click on image to enlarge
These results are usually useless to the operator who must enter criteria into the lookup search dialogue and search again. It would be better if the first automatic search could be suppressed. Fortunately, this can be done by putting the prompt table associated with the field on the lookup exclusion table.

Click on image to enlarge
Now the user goes straight into a waiting page and is prompted to enter search criteria to find the relavent data. Even better, the benefit to the user is immediate. You don't have to restart any servers for this configuration setting to be recognised, and for the behaviour of the lookup to change.

Click on image to enlarge
The lookup exclusion table is a very simple and quick way of improving performance by cutting out unnecessary work. It is also much easier to implement these changes because there are only configuration, rather than customisation.

4 comments :

Anonymous said...

Thanks a lot. I was looking for this desperately. Didnt know how to replicate the SUPERVISOR_ID type thing in another situation. Wouldnt it be nice if we could also force user to enter a value?

Anonymous said...

I have followed the same process.
I have specified view name in the look up exclusion table link.
But the problem is this functionality working some times it's not working some times.
How can i resolve this issue?
If can i grate this to other environment?
is it like inserting the values in to psrecxl table?

Anonymous said...

I have followed the same process.
I have specified view name in the look up exclusion table link.
But the problem is this functionality working some times it's not working some times.
How can i resolve this issue?
If can i grate this to other environment?
is it like inserting the values in to psrecxl table?

Anonymous said...

Thanks, works as expected!