September 19, 2007
"Derived records" are useful PeopleSoft objects, but sometimes there
is some confusion about them.
Recall that "record," as used by PeopleSoft, is a generic term that
covers actual database tables and views, plus subrecords and derived records.
You can't select from a subrecord or a derived record, since they
don't exist in the database. Derived records are record definitions
that are never created as actual tables. But there are other differences
as well, and these are not always obvious.
PeopleSoft makes normal data entry pages a snap to create. But for more
complex page designs, we must use elements that are not directly bound
to tables or views. This is where derived records are useful. Now, we
might think that a derived record field should act exactly the same as a "real"
table column on a page. Sometimes this is true, but not always.
If you try to define a scroll area or grid consisting only of derived
record fields, the page will not be valid. These objects must contain at
least one "real" database field. Sometimes, though, it is necessary to
create a scroll or grid that doesn't relate directly to a table or view.
One easy way around this problem is to create a dummy view containing
just one field that returns one blank space or other unused value.
Put that field into your scroll or grid, mark the scroll/grid as
"no auto select," and you can now fill and manipulate the scroll/grid
without any complaints from the system. (I have also used a dummy
view as a component's search record when I wanted to do all of my
data selection in code.)
Another major difference between derived and other records is in the way
the component processor handles keys to tables and views. When you have
several tables or views on a page, and a search record attached to the
component, you know that selecting one row on the search dialog causes
all of the related data to be loaded automatically. The various records
share their keys. The component processor looks at the definition of each
record, finds the keys, and checks to see whether values are available
for those keys (from other records on higher scroll levels). If so, those
key values are used in loading the data. But derived records do not
participate in this. You can set a derived field to a value before the
page loads (in PreBuild, for example), but that won't help. You need to
do actual SQL using SQLExec() or Select() or some other function/method.
I don't really know why these limitations exist. But they do, and understanding
that they are there will help to eliminate many bugs and uncertainties
about how our pages operate.
Until next time...