Creating Record Definitions

This chapter presents an overview of record definitions and discusses how to:

Click to jump to parent topicUnderstanding Record Definitions

Fields that are grouped together as a unit are record definitions. A record definition represents what the underlying SQL database tables look like and how they process data. Two tabs exist in record definitions.

Click to jump to top of pageClick to jump to parent topicUnderstanding Record Types

Select the Record Type tab to view the record definition types. Select from these types:

SQL Table

Select to define a record definition that has a corresponding physical SQL table in the database. Create this table when you run the Build Operation from the Build menu. This value is the default setting.

SQL View

Select to define a record definition that corresponds to a SQL view, which is not a physical SQL table in the database but fields from one or more SQL tables that are reorganized into a different sequence. SQL view provides an alternate view of information that is stored in tables.

To create the SQL view, click the Click to open SQL Editor button, enter a SQL Select statement, and then run the Build process.

See Creating SQL View and Dynamic View Select Statements.

Dynamic View

Select to define a record definition that can be used like a view in pages and PeopleCode, but is not actually stored as a SQL view in the database. Instead, the system uses the view text as a base for the SQL Select that is performed at runtime. Dynamic views can provide superior performance in some situations, such as search records and in PeopleCode Selects, because they are optimized more efficiently than normal SQL views.

Note. Keys for dynamic views should not be effective date fields.

The dynamic view should contain only fields whose names exactly match the fields comprising the SQL query.

Derived/Work

Select to define the record definition as a temporary workspace to use during online page processing. A derived or work record is not stored in the database, so you do not build it.

SubRecord

Select to define the record definition as a subrecord: a group of fields that is commonly used in multiple record definitions and that you can add to other record definitions. This way, you can change a group of fields in one place, as opposed to changing each record definition in which the group of fields is used.

Query View

Select to define the record definition as a view that is constructed using the PeopleSoft Query tool. Before you can create the view, PeopleSoft Application Designer prompts you to save the definition.

Temporary Table

Select to define the record definition as a temporary table. You can specify temporary images of the table on the PeopleTools Options page. Use temporary tables for running PeopleSoft Application Engine batch processes. Temporary tables can store specific data to update without risking the main application table.

Non Standard SQL Table Name

Specify the SQL table name that you are defining to override the standard convention of prefixing PS_ to the record name.

Build Sequence No.

This field is available when the record type is a SQL or Query view. Set the order in which the view is to be created. The default value is 1 when the record or view is initially created. Views that must be created first can be set to 0, while views that you want created last can be set to any number from 1 to 99. The build sequence number is stored with the other details of the record or view in the database.

Click to jump to parent topicViewing Record Definitions

You can see four views of a record by selecting View from the main toolbar: Field Display, Use Display, Edits Display, and PeopleCode Display.

This section discusses how to:

Click to jump to top of pageClick to jump to parent topicCommon Elements Used in This Section

Num (number)

The number of the field in the order in which it is defined in the record.

Field Name

The name of the field in the SQL database.

Type

The data type of the field, such as character, number, or date.

Click to jump to top of pageClick to jump to parent topicViewing Basic Field Definitions

Field Display mode shows the basic field definition characteristics for fields in the record definition. Field definition characteristics are global: they affect all record definitions in which the field is used. To open the associated field definition, right-click it while in Field View and select View Definition.

Len (length)

Indicates the maximum length of the field, including decimal places.

Format

Notes special formatting for the field, such as mixed case, date, international phone number, or RawBinary.

Short Name and Long Name

Displays the short and long names of the field as users see them on pages.

Click to jump to top of pageClick to jump to parent topicViewing Key-Related Characteristics and Default Values

Use Display mode shows key-related characteristics and default values for fields that determine how fields are used in a record. The use characteristics might differ for fields that are used in more than one record definition. Double-click the field to access the Record Field Properties dialog box in which you define these parameters.

Key

Indicates whether the field is a key to the record definition. Key fields are included in an index that is automatically created during the table build process.

The word Not in this column indicates that this field cannot be used as a chart field. You can set this parameter only through PeopleCode using the SetDBFieldNotUsed function.

See SetDBFieldNotUsed.

Ordr (order)

Indicates the order of key fields in the index. You can configure this order from the Index dialog box. It is reflected in this column.

Dir (direction)

Indicates the order in which the key field indexes are created in the database: ascending or descending.

CurC (currency control)

The currency format for this field is controlled by the currency code in another field.

Srch (search)

Indicates a search key, that is, a field for which one is prompted from a search record dialog box.

List

Indicates a list box item, that is, whether the values for the field appear in the search record list box.

Sys (system)

Indicates that the system generates and maintains the field.

Audt (audit)

Specifies whether additions, changes, or deletions to data in this field are written to the standard PeopleTools Audit Tables (PSAUDIT and PSAUDITEXT).

InAutoComp

Indicates that the field is used as an autocomplete field and appears in the autocomplete window.

EnAutoComp for Srch Record

Specifies whether autocomplete is enabled in a search record.

Default

Represents the default value that is used to initialize the field.

Click to jump to top of pageClick to jump to parent topicViewing Editing Options

Edits Display mode shows all editing options (edit as a validation rule) that are available for fields in a record. Edits on a field vary from one record definition to another. To define parameters, access the Record Field Properties dialog box by double-clicking the field.

Req (required)

Indicates whether the field is required. A user must enter a value before the record can be written to the database.

Edit

Specifies that the values for this field are validated against a table.

The translate table stores the codes and translate values:

Y/N (yes or no): Only two values, Y or N, are valid.

Prompt: Values reside in a designated prompt table.

Prompt Table

Indicates the prompt table for a field. When a user clicks the prompt button or presses the ALT+5 key combination on a page, the system retrieves the values that are stored in this table.

Set Control Field

If you plan to use the Table Sharing feature to add an additional high-level key to identify common sets of values and handle exception values, add the name of the key here. The set control field determines which set of values appears, based on how you define table sharing.

Rs Dt (reasonable date)

Specifies whether a reasonable date test is performed on a date field. All date fields are automatically edited to ensure that you can enter only valid values. The reasonable date test warns if the date is outside a 30-day range before and after the current date. You can use this, among other tests, to guard against entering the wrong year in a date.

Event

Indicates whether any type of PeopleCode was added for this field. In addition, if PeopleCode was added, the field name will be boldface.

Click to jump to top of pageClick to jump to parent topicViewing PeopleCode Program Types

PeopleCode Display mode has a column for each PeopleCode program type. If a field contains a PeopleCode event, a check mark appears in the event type column. Double-click in a cell to launch the PeopleCode Editor. Note that fields containing PeopleCode are in bold in all display modes.

Click to jump to top of pageClick to jump to parent topicReordering Fields

You can reorder the appearance of fields in a record definition by double-clicking the attribute name. For example, if you double-click Field Name, the fields appear in alphabetical order; double-clicking Num returns the fields to their numeric order. This action does not change the order of the fields in the actual record. The numbers that the fields are originally assigned remain the same. This fact is important when it comes to key fields.

To actually reorder the fields in the records, you must cut and paste or select a field and move it.

Click to jump to top of pageClick to jump to parent topicSizing and Sorting Columns

You can change column lengths in any of the displays by dragging them to the appropriate size with your cursor. For example, you might make the short name column smaller so that the entire long name for each field appears. The default sizing of all columns returns after you close the record and reopen it.

You can also sort the rows in columns by double-clicking the column heading. For example, double-clicking the Num column heading returns the list of record fields to the default sort order.

Click to jump to parent topicSaving Record Definitions

You should save your work every time you define a new record definition. As soon as you add or change one element in the new definition, save your work and name the record. You cannot save a record definition until you make at least one change to the record definition by changing record properties, adding or deleting at least one field, or changing the record field properties.

You cannot add PeopleCode to a field until you save the record definition.

To save a new record definition, select File, Save or File, Save As. If you have not named the definition, the system prompts you to enter a record name. The system also prompts you to set the tablespace using the Change Space dialog box.

See Setting the Tablespace.

Click to jump to parent topicNaming Record Definitions

Use these guidelines for record definition names:

See Saving Definitions.

Record Naming Conventions

To help identify the purpose of different types of record definitions, you should adopt these naming conventions for record definition names and use these suffixes:

_TBL

Identifies edit or prompt tables that contain data that is used for validation, as opposed to data that is maintained by the application. Prompt tables store commonly used values. They include, but are not limited to, control tables, which store company-wide values. For example, the location table (LOCATION_TBL) stores values for all operating locations in which your company does business; the country table (COUNTRY_TBL) stores values for all valid countries.

_VW

Identifies record definitions that are physically implemented by defining a SQL view.

_DVW

Identifies dynamic views.

_WRK

Identifies derived work records.

_SBR

Identifies subrecords.

_QVW

Identifies query views.

_WL

Identifies records as worklist record definitions.

In some cases, PeopleSoft also uses these prefixes to identify special types of record definitions:

R_

Identifies work record definitions for Structured Query Report reports. The remainder of the record name consists of the program or report ID.

AUDIT_

Identifies record definitions that store audit information for other record definitions in the database.

WEBLIB_

Identifies record definitions that store internet scripts. Internet scripts are generally located in FieldFormula PeopleCode events. You must grant access in the Security component for a WEBLIB record with an internet script before it can be run in a PeopleCode program.

FUNCLIB_

Identifies record definitions that contain written PeopleCode functions, as opposed to built-in functions. You can include these records in the component and call them as functions. These self-developed functions are generally located in FieldFormula events, and the records are usually derived.

DERIVED_

Identifies shared record definitions (across an application module or group) that have fields for PeopleCode events.

Click to jump to parent topicCreating New Record Definitions

This section discusses how to create new record definitions.

To create a new record definition:

  1. Select File, New.

  2. Select Record.

  3. Click OK.

    The object workspace appears so that you can build a list of fields in a record definition.

  4. Select the Record Type tab to define the type of record definition.

See Also

Manipulating Fields in Record Definitions

Click to jump to parent topicOpening Existing Record Definitions

Open a record definition in the same way that you open other definitions in PeopleSoft Application Designer. In the open dialog box, you can narrow the search by selecting a record type or project. You can also open an existing record from the project tree view by double-clicking a record name.

Click to jump to parent topicManipulating Fields in Record Definitions

This section provides an overview of fields in record definitions and discusses how to:

Click to jump to top of pageClick to jump to parent topicUnderstanding Fields in Record Definitions

You create record definitions by adding field definitions to a new record definition or by cloning and modifying an existing record definition. You can add fields in any order and reorder them at any time. Locate keys at the top of the record definition, in order of importance.

Each field has basic attributes that are shared across all records that contain the field. These field properties include data type, field name, long name, short name, field length (or integer and decimal positions), formatting, help context number, and translate values. If you change any of these attributes for a field, the change affects every occurrence of the field in every record definition. If the change is not appropriate for every occurrence of this field, consider defining a new field instead.

Note. Changing a field name or length requires modification of the underlying SQL table, either by running the SQL Build or SQL Alter menu items or by a system administrator action. For example, if you change a field length for one record definition and 30 other record definitions contain the same field name, then you must build or alter 31 records. If the records are all within one project, you can run the build process on the project instead.

Click to jump to top of pageClick to jump to parent topicInserting Fields Into Records

You can insert a field into a record in one of several ways:

Click to jump to top of pageClick to jump to parent topicInserting Subrecords Into Records

Subrecords enable you to aggregate and configure a group of fields as a set, which you can then insert in multiple record definitions. All subrecord fields and configured properties propagate to the subsuming record definition.

You must define a subrecord before you can insert it into a record definition. You create and configure a subrecord as you do any record definition; however, you do not build subrecords because they are PeopleSoft structures, not standalone database structures. Select Insert, SubRecord to search, select, and insert subrecords into a record.

Viewing a Subrecord

Any open record in which a subrecord is inserted can have the subrecord expanded into the same record definition window by selecting View, Expand All Subrecords. After selecting this option, the parent record definition expands to show the fields in the subrecord. Subrecord fields are shaded.

When the subrecord is expanded, you cannot insert, cut, delete, paste, reorder, or sort fields. If you reordered the display of the fields, you cannot expand the subrecords.

The expanded subrecord fields are read-only, which means that you cannot change the properties of these fields. To access the properties, first open the subrecord. The shortcut to opening a subrecord is to click the expanded subrecord and select View Definition. This action opens a subrecord definition from which you can view the properties of the fields.

Collapse the subrecord by selecting View, Collapse All Subrecords.

Nesting Subrecords

Nested subrecords are fully supported to any level. Expanding a record toggles the record field list to show all of the fields from all levels of nesting. Changes to subrecords are immediately reflected in expanded records.

Editing PeopleCode in Subrecords

You can edit PeopleCode that is attached to a subrecord field by double-clicking the appropriate field as it appears in the expanded record. Any PeopleCode changes that you make apply to all records that contain that subrecord.

Configuring Persist in Menu Properties

All Persist in Menu properties that you explicitly define on record fields in subrecords propagate to all subsuming record definitions. When you configure custom persistent search fields, the Persist in Menu properties in both the subrecord and its subsuming records are counted toward the maximum number of persisted fields. Selecting the Persist in Menu property for record fields in a subrecord that is already being used by other record definitions displaces any lower order fields that, as a result of the subrecord reconfiguration, are beyond the five-field maximum.

See Configuring Search Results Grid Columns in Persistent Searches.

Click to jump to parent topicSetting Record Properties

After you create a record definition, you will want to apply attributes so that the record functions. Access the dialog box by selecting Record Properties, and then select either the General tab or the Use tab. This section discusses how to:

Click to jump to top of pageClick to jump to parent topicSetting General Properties

Access the Record Properties, General tab.

Description

Enter a descriptive name for the record.

Record Definition

Enter appropriate comments in this area, including details about the record type, use, parent and child relationships, or other information that is important to other application designers.

Owner ID

Displays a list of applications with which this record is used. This list is helpful to identify the applications with which the record is associated during application development.

Last Updated

Displays the date and time of the last modification that was made to the record and the name of the user who made the modification.

Click to jump to top of pageClick to jump to parent topicSetting Use Properties

Access the Record Properties, Use tab.

These properties control the way the record definition is used in the system.

Specify the field with which the system maps between the original key and the tablesets in the Set Control Field drop-down list box.

See Defining Set Control Fields.

Record Relationship Settings

Parent Record

If this is a subordinate or child record, specify the parent record.

Related Language Record

Specify a related language record:

  • The master record definition, which contains the appropriate key and nonkey field definitions.

  • A clone of the master record definition, to which you add an additional key for language code.

    This is the related language record definition. This definition should contain only those nonkey fields for which contents vary by language. Link the two record definitions by specifying the name of the related language record definition in this field on the master record definition.

    At runtime, the system checks the user’s language preference and retrieves the data value from the appropriate related language record definition.

Query Security Record

Specify a query security record if you want secure access to a record using a security view.

Analytic Delete Record

Specify a delete record to use for optimization.

Record Name

Specify the user-defined audit record.

Audit Options

While you can audit individual fields at the field level, you might find it more efficient to have the system audit the entire row whenever a user adds, changes, or deletes information. For record-level audits, the system focuses on rows of data instead of specific fields. Consequently, a record-level audit writes a single row of audit data rather than writing multiple rows for each insert, change, or delete transaction at the field level.

Add

Inserts an audit table row whenever a new row is added to the table underlying the record definition.

Change

Inserts one or two audit table rows whenever a row is changed in the table underlying this record definition.

Selective

Inserts one or two audit table rows whenever a field that is also included in the record definition for the audit table is changed.

Delete

Inserts an audit table row whenever a row is deleted from the table underlying the record definition.

Record-Level Auto-Update Options

These settings are used exclusively for mobile applications, enabling developers to display derived values on a mobile page. These settings are not available for subrecords.

Important! PeopleSoft Mobile Agent is a deprecated product. These features exist for backward compatibility only.

System ID Field

Set this field to assign a unique way to identify the record for mobile synchronization purposes. You must create a user-defined index for this field.

Timestamp Field

Set this field to automatically update the field with the date and time when there is a change to the record for mobile synchronization purposes.

Record Information

Record Information contains options used during PeopleSoft upgrades.

Tools Table

Identifies SQL tables owned by PeopleSoft PeopleTools development, which is typically the same ownership as indicated by the PPLTOOLS project.

Managed

Identifies SQL tables owned by PeopleSoft PeopleTools development that contain information that is used by managed (cached) objects.

Upgrade Job Not Needed

Identifies SQL tables that do not require an upgrade job to be defined. This option applies only to “A to B” upgrades.

Note. The Append ALL (Dynamic Views) option applies to dynamic views. If it is set, then at runtime the system appends the WHERE clause generated at runtime to all the SELECT statements in a dynamic view with a UNION.

Also, if it is set, at runtime the system generates FILL as the table alias in the WHERE clause condition. For example, FILL.<record field name> LIKE '%O%'. You need to specify which table corresponds to the FILL in the dynamic view definition. For example, FROM table A FILL. You must specify the corresponding table because the same record field name may exist in two different records.

See Also

PeopleSoft upgrade documentation for your installation.

Click to jump to top of pageClick to jump to parent topicCreating User-Defined Audit Record Definitions

To audit at the record level, you must create a record definition and SQL table in which you store audit information. When you create a new audit record definition, name it with an AUDIT_ prefix. Some processes, such as the Employee ID Change and Employee ID Delete processes in the PeopleSoft Human Resources Management product line, change fields, such as EMPLID (employee identification). These processes do not affect record definitions that begin with the AUDIT_ prefix, leaving your audit data secure.

The easiest way to create an audit table is to open the record definition that you want to audit and save it as a new record definition, prefaced with AUDIT_. Audit record definitions cannot contain key fields. Therefore, if you clone a record definition to create an audit record definition, you must remove all key attributes.

You should also use the audit-specific fields that are already defined for the PeopleTools audit tables (PSAUDIT and PSAUDITEXT), which the PeopleSoft application uses to track field-level audits. Place these audit fields at the top of the audit record definition, as you would keys. If you are creating several user-defined audit record definitions, you might consider creating and using a subrecord definition with these audit fields instead of adding them individually to each audit record definition.

This table lists audit field names and their purpose:

Audit Field Name

Purpose

AUDIT_OPRID

Identifies the user who caused the system to trigger the audits, either by performing an add, change, or delete to an audited field.

AUDIT_STAMP

Identifies the date and time the audit was triggered.

AUDIT_ACTN

Indicates the type of action that the system audited. Possible actions include:

  • A: Row inserted.

  • D: Row deleted.

  • C: Row changed (updated), but no key fields changed.

    The system writes existing values to the audit table.

  • K: Row changed (updated), and at least one key field changed.

    The system writes existing values to the audit table.

  • N: Row changed (updated), and at least one key field changed.

    The system writes new values to the audit table.

AUDIT_RECNAME

Identifies the name of the record definition that was audited.

In most cases, you should include AUDIT_OPRID, AUDIT_STAMP, and AUDIT_ACTN. The AUDIT_STAMP must be given the attribute AUTOUPDATE. You might also add AUDIT_RECNAME if you are creating an audit table to audit more than one record definition.

Note. Select the Auto-Update check box in the Record Field Properties dialog box; otherwise, the audit record does not receive a date and time stamp.

Click to jump to parent topicSetting Record Field Properties

This section provides overviews of record field properties and record key considerations, and discusses how to:

Click to jump to top of pageClick to jump to parent topicUnderstanding Record Field Properties

When you add a field to a record definition, you must define attributes for how the field is used in that record. These attributes include key settings, default values, table edits, and PeopleCode programs.

The properties of record fields are not shared among records; they are specific to a single record definition and are stored with the record. Therefore, even though you might add the same field to multiple records, each record stores a unique set of record field properties, while the primary field definition remains the same.

When you access field properties from within a record definition, you set the record field properties, not field definition properties or record definition properties.

To edit record field properties from a record definition, select the record field, then select Edit, Record Field Properties from the PeopleSoft Application Designer menu. You can also double-click a record field to set the properties.

Click to jump to top of pageClick to jump to parent topicUnderstanding Record Key Considerations

When creating key fields in your record definition, keep in mind several guidelines that are discussed in the following topics.

Records With Multiple Keys

You can specify more than one field as a key to a record definition; that is, a record definition can have a compound key. This example shows a compound key structure:

The keys to the PO_LINE record definition are BUSINESS_UNIT, PO_ID, and LINE_NBR. Therefore, each purchase order must be unique to a business unit, and each line item in a purchase order must have a unique number.

Keys in Parent and Child Tables

In some cases, you have a field in a table for which you want to allow multiple occurrences, in which case you create a subordinate or child table. For example, for employee reviews, an employee can be reviewed for performance in multiple categories—organization skills, interpersonal skills, and so on. These categories and ratings are stored in a separate child table, EE_REVIEW_RT, which is directly related to REVIEW_DT, the parent table that stores information about employee reviews.

The keys that you establish in a parent record definition determine which keys are required in child record definitions. The child must have the same keys as the parent, plus one or more keys that uniquely identify each row:

 Item

Parent Table

Child Table

Record Definition

REVIEW_DT

EE_REVIEW_RT

Key Fields

EMPLID

REVIEW_DT

EMPLID

REVIEW_DT

CATEGORY

Most record definitions have either one primary key, or multiple keys that comprise independent, or parent and child key combinations. Exceptions exist, however, such as record definitions without keys and duplicate order keys.

Records Without Keys

Some record definitions, such as INSTALLATION, do not require keys because only one row of data exists in the table. Whereas a table normally has keys to help distinguish between multiple occurrences of data, when a table has only one row of data you do not need to distinguish one row from another. Another primary use for keys is to build database indexes to rows; because each table has only one row, the record does not need indexes. This example shows a record definition with no defined key structure:

Records With Duplicate Order Keys

Occasionally, you might encounter situations in which a unique identifier for each row does not exist. Duplicate order keys are a way of ordering data in the table when duplicate values are allowed.

For example, in the EDUCATN table, PeopleSoft developers anticipated that an employee can receive two degrees of the same type on the same date. For example, though rare, Simon Schumaker could receive two honorary degrees in computer science on the same day. Because no unique identifier exists—the employee ID, date earned, and degree are all the same—the user must maintain the data differently. The key, instead of defining a unique row, defines a group of rows. In that group, you must determine the order in which you want to display the information.

In the EDUCATN record definition, three keys together determine how information is stored and retrieved. EMPLID identifies the group of rows; and the placement of the duplicate order keys, DT_EARNED and DEGREE, instructs the system to order rows in the group first by date and then by degree:

EMPLID

DT_EARNED

DEGREE

8001

June 1, 1992

June 1, 1992

HON

HON

The system concatenates or joins keys when it checks for uniqueness. In this case, the system accepts duplicate entries in the DEGREE and DT_EARNED fields because they are part of a set that is identified by EMPLID.

Note. Records with duplicate order keys should not have related language tables associated to them. If the record requires a related language record, you must remove the order keys. Do not create a related language record with a duplicate order key.

Ordering Keys

The position of keys relative to one another is critical in a record definition; always list them in order of importance. The primary key must be the first field in the record, followed by the next most important key, and so on.

Key fields are followed by duplicate order keys, in order of importance, and then by fields that are not keys. You can scatter alternate key fields anywhere among regular fields, in any order. They do not need to be grouped.

Key, Duplicate Order Key, and Alternate Search Key are mutually exclusive.

Note. The display of key fields in record definition views does not always match the order in the record. Always check the Num column to see the actual order.

Each alternate search key that you configure creates a database index when the SQL creates the table. While database indexes are important, they consume disk space and, when the system must support the alternate key, processing time. Therefore, do not add alternate search keys unless you really need them.

For both records and subrecords, you can only add 10 alternate search keys.

Click to jump to top of pageClick to jump to parent topicSetting Record Field Use Properties

As you add fields to a record definition, you must decide which fields uniquely identify each row; these become the record keys. The nature of the data that you are storing should naturally determine the keys for the information in the database.

The record field properties options that are available for field types might vary slightly; for example, some of the properties that you assign might be relevant only to a character field in a record and not to a date or time field. However, all of the options are described in this section. This example shows the available record field properties options:

Keys

Key

Select to identify the field as the search criteria that uniquely identifies each row. You cannot have duplicate values for primary keys. For example, EMPLID is the only key to the PERSONAL_DATA record definition. Therefore, EMPLID must be a unique value for each employee, and only one PERSONAL_DATA row can exist per employee.

Duplicate Order Key

Select to indicate that duplicate values can occur. The order in which you place duplicate order keys in a field list determines the order in which the system retrieves duplicate keys.

Alternate Search Key

Select to make this field part of the search structure when accessing your application data. Duplicate values are allowed in an alternate search key field.

If you define a field as an alternate search key in a search record or prompt record, the system includes the field on the dynamically generated search page or prompt lookup dialog box that you use to enter search criteria.

Descending Key

Note. This option no longer produces an index with descending order.

Select to identify the field as descending if you want rows of data to be retrieved in reverse alphanumeric order (for example, 3, 2, 1). If you deselect this check box, the key is ascending, meaning that rows are stored and retrieved in alphanumeric order (for example, 1, 2, 3). This option applies only to a field that is specified as a key, duplicate order key, or alternate search key. PeopleSoft applications use descending order primarily for effective date fields; most character keys are ascending.

Search Key

Select to make the field available for entering search criteria on the basic and advanced search pages, the lookup prompt pages, and as a column that is stored in the menu structure for persistent searches. A search key is valid only for keys and should be used only in search and prompt records. If you select this check box, the system automatically selects the List Box Item check box.

Search Edit

Enabled only if Search Key is selected. Select to enforce the required property and table edits on the search page. It also enforces these edits under circumstances in which the search page would be bypassed. When this option is enabled, the user no longer has the ability to perform partial searches on this field.

List Box Item

Select to include the field as a column in the search results grid on:

  • Standard component search pages. The system includes all fields designated as list box items in the search results list.

  • Prompt lookup pages. The system includes all fields designated as list box items in the search results list.

  • Recent (persistent) search results pages. The system includes no more than five list box items in the search results list.

    Note. This behavior is the default. To explicitly define which fields appear in the recent (persistent) search results, you must set other configuration options.

See Configuring Caching.

See Configuring Search Results Grid Columns in Persistent Searches.

If the field has values in the translate table and you designate it as a list box item, the list box automatically shows the translated value instead of the code.

If you select this check box, the system enables but does not select the Persist in Menu check box.

From Search Field and Through Search Field

Select for fields that are used as search records. If you select From Search Field, the displayed list contains rows in which the field value is greater than or equal to a value that the user enters. If you select Through Search Field, the displayed list contains rows in which the field value is less than or equal to a value that the user enters.

If you do not want these fields to be in a search list box, clear the List Box Item check box, even if the field is an alternate search key.

Default Search Field

Select to control which field name appears in the Search By drop-down list box on the basic search page.

Disable Advanced Search Options

Select to prevent runtime search pages from displaying advanced search features for this field.

Allow Search Events for Prompt Dialogs

Select to enable the SearchSave and SearchInit PeopleCode events on a prompt dialog for this field. The default value is Cleared.

Note. Limit your use of these events in this context. The amount and complexity of the PeopleCode affects the speed of data retrieval.

Audit

Audit options apply only to data that is manipulated on a normal PeopleSoft application page through component processing. These options do not apply to data that is added using SQLExec in PeopleCode or by some other means.

PSAUDIT and PSAUDITEXT are the standard PeopleTools audit tables for field-level audits. Both audit tables log identical information such as the user ID, time of change, and so on as well as the values of audited fields both before (OLD) and after (NEW) any changes.

Note these differences in the field properties of the two audit tables:

Note. If you are auditing a field with a field length greater than 65 characters, you should query PSAUDITEXT to ensure that you retrieve the entire field value and not a truncated subset.

To audit certain fields, regardless of the record definitions in which they are included, you want to control when the fields are audited. To audit several fields in a record definition, consider specifying audits at the record definition level.

Field Add

Audits this field whenever a new row of data is added.

Field Change

Audits this field whenever the content changes.

Field Delete

Audits this field whenever a row of data is deleted.

System Maintained

Select to indicate that the field value is system-generated. This option is for documentation purposes only and does not affect processing.

Auto-Update

Select to have the field updated with the server’s current date and time whenever a user creates or updates a row. Any user entries—even if permitted on a page—are overwritten by the server time.

Currency Control Field

Enables the user to specify where to find the currency code that is used to display the currency symbol, decimal digits, and scale of a number field. To use this option, the multicurrency option must be set. You might also want to enable the current display when you define the field on the page.

Record Field Label ID

Specify which label, long name or short name, to use for the record. The default value is ***Use Default Label***. This setting enables you to change labels of record fields dynamically anytime that the default label on the field definition is changed.

For example, a field definition has three labels:

When the label ID in a record field is set to Use Default Label, initially the long name and short name are Long Name1 and Short Name1. If the default label is changed to Label3 in the field definition, then the long name and short name automatically become Long Name3 and Short Name3.

Default Value

Select the most commonly used value as the default. You can always enter a different value if the default is inappropriate. The more defaults that you provide, the more data entry time you will save your users.

For a field, you can enter a default value as a system variable or as a combination of record and field names.

Constant

Specify a default value.

This value is case-sensitive. If the record has translate values for this field, the values appear in a drop-down list.

You can specify a system variable only when you want to display the value of the current date or time. The accepted system variables to be used as constants are:

  • %DATE for DATE, TIME, or DATETIME fields.

  • %TIME for TIME fields.

  • %DATETIME for DATETIME fields.

  • %CLIENTDATE for DATE, TIME, or DATETIME fields. Note that in this case %CLIENTDATE is only relevant for a DATE field. Otherwise it behaves the same as %DATE.

Record Name and Field Name

Enter the record and field names of the default value.

 

Default Page Control

Specify the default appearance of a field as it appears on the page that corresponds with the record field that you are creating.

Autocomplete Configuration

Enable Autocomplete when used in Search Record

Select to make this field appear as a column in the autocomplete list when the containing record is used as a search record.

This field is enabled for character, number, and signed number fields if the following conditions are met:

  • The Default Page Control field value is Edit Box or System Default.

  • The field is a search key or an alternate search key.

Persist in Menu Configuration

Persist in Menu

Select to explicitly include the field as a column in the search results grid of a persistent search; select only if the field does not appear in the search results grid by default.

The Search Key and List Box Item properties determine which columns appear in the search results grid by default.

You must enable recent search results caching at the system level in the web profile for this property to take effect.

See Keys.

See Configuring Caching.

Important! If you select this check box for any record field in a search record, then the search results grid uses this property—not the list box item property—to determine all fields that appear in the grid. Consequently, you must select the Persist in Menu check box for each record field that you want to include in the search results grid.

See Configuring Search Results Grid Columns in Persistent Searches.

Time Zone and Related Date Fields

These fields are enabled only if the current field is a time or date and time field. They determine whether the field is displayed or entered in a specified time zone.

Specified Time Zone

Times are always stored in a database base time zone, but when you place a time field on a page, you can display the time in the base time zone or another time zone.

If you deselect this check box, the time appears in the database base time zone. If you have users in multiple time zones, you can reduce confusion by showing the time zone along with the time.

If you select this check box, the system converts the time according to the time zone specified in Time Zone Control field. This control field must be a field in the current record. Set an appropriate default value for the time zone control field.

Date Control Field

Select which related date field in the current record stores the calendar date to which this field should be adjusted.

Typically, you want to make a date field a descending key so that the row with the latest and most current time appears first. If you want the default value to be the current system date, enter the value %date (or %time) as the constant in the Default Values group box.

In general, each Time record field that is related to the Time Zone field should also be related to a Date field in that same record. Otherwise, the time data that appears or is saved may not properly reflect the appropriate state of daylight savings in that time zone. Furthermore, each Time record field should be associated with a different Date record field.

For applications where two Time record fields need to share a common Date record field (as in, records with a start time and an end time always occurring on the same date), you can relate each Time record field to the same Date record field. However, keep in mind that the Date record field will only consider itself associated with one Time record field—the related Time record field that has the earliest position in the record field ordering for that record.

Click to jump to top of pageClick to jump to parent topicConfiguring Search Results Grid Columns in Persistent Searches

The Persist in Menu property enables you to override the default fields that appear on the persistent search page and explicitly determine which fields the system includes in the search results grid. If you select the Persist in Menu property for any record field in the search record, then you must select the same property for each field, including search key fields, that you want to appear as columns in the search results grid.

Note. This property, when selected, controls the appearance of the persistent search results grid. Search key fields remain in the menu structure but are hidden from view.

To explicitly define the fields that appear in the results grid for a recent (persistent) search:

  1. Open the search record.

  2. Double-click the field you want to include in the results grid and select the List Box Item check box to enable the Persist in Menu check box.

  3. Select the Persist in Menu check box for the field.

  4. Click the OK button.

  5. Double-click each additional field that you want to include and repeat Steps 2 and 3.

  6. Save the record.

Note. When you save the record definition, PeopleSoft Application Designer alerts you if your custom configuration exceeds the maximum number of fields that can appear in the search results grid of a persistent search page. This alert is informational.

Click to jump to top of pageClick to jump to parent topicAdding From and Through Logic to a Search Page

Refer to the following example when applying from and through logic to a search page:

To apply the from and through logic to a search page:

  1. Determine which component you want to change.

    You must modify the search record for a particular page, so you must first identify the appropriate component.

  2. Determine on which field to use the from and through search logic.

    Open the component in PeopleSoft Application Designer and examine the search record that is associated with the component. In the CNT_CLAUSE_TBL record, the search record is CNT_CLAUSE_TBL, and the field to which you want to apply the from and through search logic is CONTRACT_CLAUSE.

  3. Create a new view that contains the same fields as the original search record.

    To use the from and through search logic, the search record must be a view. If the search record is already based on a view, you can modify the existing view instead of creating a new view.

  4. Select the From Search Field property for the field.

    In the new view, select the from search field and apply the following record field properties: Key, Search Key, and From Search Field (List Box Item is optional).

  5. Create a new field with exactly the same attributes as the from field.

    For example, if CONTRACT_CLAUSE represents the field from which you want to search, then create a field called CONTRACT_CLAUSE_TO representing the field through which you want to search.

  6. Insert the new field directly below the original field.

  7. Select the Through Search Field property for the field.

    Select the new through search field and apply the following record field properties: Key, Search Key, and Through Search Field only.

  8. Update the SQL view text.

    Update the SQL view Select statement in the new view to reflect the new column. That is, the view text should select the same field twice in a row (as shown in the example that follows this procedure) because the same field is used for the from logic and the through logic.

  9. Save and build the new view.

  10. Update the component properties.

    You must update the search record so that the component uses the new view.

  11. Test the new search page.

    The search page should now include the new through field so that the user can specify a range of values for the field. Because you want to search on both fields, you must use an advanced search.

    The results in the list box should reflect a range of values between the from and through fields that are specified. The results are inclusive: they include the minimum and maximum values that the user specified.

Example of Updating the SQL View Select Statement

After you define a search field as a Through Search Field, you need to update the SQL view text, as shown in this example:

Click to jump to top of pageClick to jump to parent topicSetting Record Field Edit Properties

The Edits tab enables you to specify whether the system is to perform special edits or validations on a record field. You also name the record definition for the prompt table that stores the valid values, as shown in this example:

Required

Select if you do not want users to skip a field or leave it blank. Users are unable to save their work until they complete all of the required fields on a page.

Note. The system only checks required fields when the page field is changed or the record buffer is changed.

Edit Type

No Edit

Select if you do not want to edit the contents of this field against a table. This value is the default; it makes the options in the Table Edit group box unavailable.

Table Edit

Select to edit the contents of the field against the values that are maintained in the specified table. When you select Table Edit, the Type field becomes available in the Table Edit group box.

Display in Autocomplete Window

Select to make this field appear in the autocomplete list.

This property is enabled for date, datetime, time, character, number, and signed number fields if the following conditions are met:

  • The field is a list box item.

  • The field is not a search item.

Table Edit

Type

Select from the drop-down list box:

Prompt Table with No Edit: Provides users with a list of suggested values but does not edit the contents of the field against the prompt table. Users can enter any value. Selecting this option makes the Prompt Table field available.

Prompt Table Edit: Edits the contents of the field against the values that are maintained in the specified prompt table. Selecting this option activates the Prompt Table field. When you enter a prompt table name and exit the field by pressing the tab key, the Set Control Field is activated.

Translate Table Edit: Edits the contents of the field against the translate table. The translate table stores values for fields that must be validated but do not need individual tables of their own. If you select this option, the Prompt Table and Set Control Field fields become unavailable.

Yes/No Table Edit: Makes the values for this field Y (yes) and N (no) only. If you select this option, Prompt Table and Set Control Field fields become unavailable.

For this 1/0 table edit, 1=True and 0=False.

Note. The list of available table edit types is dependent on the field type.

If you add a field (other than with the paste action) to a record and the field name is already defined in the database with previously defined translate values, then the system automatically selects Table Edit and selects Translate Table Edit as the table type. For both Translate Table Edit and Yes/No Table Edit, you should follow PeopleSoft design standards, select Required, and specify a default value for the field in the Record Field Properties dialog box.

Prompt Table

Enter the name of the record definition that you want to use as the prompt table for this field.

If you want the prompt table to vary depending on the context of the field, indicate a field in the derived or work record (DERIVED) that contains the name of the prompt table at runtime. Then, in this field enter %FieldName.

The % is required, and indicates that you are referencing a derived or work record definition named DERIVED. FieldName is the name of the field in the DERIVED record definition.

Set Control Field

Select a set control field that overrides the set control field of the record definition specified in the prompt table. If you do not specify a name in this field, the default is the set control field of the record definition specified in the prompt table.

Reasonable Date

Select if you want the system to test the field value to determine whether it is within 30 days of the current date. If the date is out of range, a warning message appears when the user exits the field.

Click to jump to top of pageClick to jump to parent topicMoving Fields in the Same Record

You can move fields in a record by dragging a field to another place in the open record definition. In addition, you can change the visible order of fields by clicking any of the grid headings. In this sample record definition, the DIM_DATA_SRC_TYPE field is being moved to the location that is indicated by the red line:

To move a field in the same record definition:

  1. Select the field that you want to move.

    Press the Ctrl and Shift keys to select multiple fields at once.

  2. Drag the selected field number to the new position in the record definition window.

    A red line between rows indicates the new placement options as you drag the field. The Num column automatically renumbers the fields.

    Note. The fields are reordered only in the visible display of the record definition, not in the actual table.

Click to jump to top of pageClick to jump to parent topicMoving Fields to Another Record Definition

To move fields from one record definition to another, you can cut and paste the fields. You can also drag a field from the project workspace into a record definition in the object workspace or between open record definitions.

To move a field from one record definition to another:

  1. Open the two records between which you want to move the field.

  2. Select the field that you want to move.

    Move multiple fields by using the Ctrl or Shift key.

  3. Select Edit, Cut.

    If this is the correct field to cut from the record, click Yes.

  4. Select the new position for the field in the destination record.

  5. Select Edit, Paste to paste the field into the new record definition.

Note. PeopleCode that is associated with fields is not carried over with cut and paste operations. The same is true for delete and undo with field deletions in records. RecordField attributes, such as key, search key, and so on, are retained.

Click to jump to top of pageClick to jump to parent topicDeleting Fields From a Record Definition

To delete a field from a record definition:

  1. Select the field that you want to remove and press the Delete key.

    This action deletes the field completely and does not copy it to the clipboard, unlike a cut operation, which does copy it to the clipboard.

  2. When the system prompts you to confirm the deletion, click Yes.

    If you already created the underlying table for the record definition from which you are deleting the field, recreate the table or use the SQL Alter function to alter the table.

    Important! If you delete a field from a record definition, you must also delete it from any pages on which it appears. When you delete a field, the system does not automatically delete references to the field in PeopleCode, so you must do this manually. To determine where the field is referenced in PeopleCode, use the Find Object References feature.

    You can also reference the following two reports: "Fields and Records" (XRFFLRC) shows which records contain the field; "Fields Referenced by PeopleCode Programs" (XRFFLPC) shows PeopleCode that refers to the field in the record.

Modify or remove PeopleCode when you find references to the deleted field.

Click to jump to top of pageClick to jump to parent topicRenaming Record Definitions

To rename a record definition:

  1. Select File, Rename.

    The Rename Definition dialog box appears.

  2. Select Record from Definition Type.

  3. Click Rename.

  4. Select the record and click Rename.

    A rectangular box appears around the name.

  5. Enter the new name and press the Enterkey.

Results of Renaming Record Definitions

When you rename a record definition, the system automatically renames all references to it, including data in tables with columns named RECNAME where the data matches the record being renamed.

The only references that are not renamed are the text portion of SQL functions, such as SQLExec and Scroll Select, and the record names in the view text. To find the text portion of SQL functions in PeopleCode or record names in view text, select Edit, Find In to search for matching text.

If you already created the underlying tables for the record definition that you renamed, recreate that table. To preserve data in the tables, use the SQL Alter function to rename the database tables.

Click to jump to top of pageClick to jump to parent topicDeleting Record Definitions

To delete a record definition:

  1. Select File, Delete.

  2. Select the record definition to delete.

  3. Click Delete.

    Warning! When you delete a record definition, the system automatically deletes any PeopleCode that is associated with the record.

  4. Click Yes if you really want to delete the record definition.

    Note. Notify your database administrator about which record definition you deleted so that the administrator can drop the underlying SQL table and its contents from the database.

Click to jump to top of pageClick to jump to parent topicPrinting Record Definitions

You can print your record definitions as references identifying all of the fields and their various attributes—any special use, edits, or PeopleCode that you applied.

To print a record definition, it must be open in the object workspace.

To print a record definition:

  1. Select File, Print Setup to change any of the print record defaults.

    The system retains your changes until you reset them again. The Print Setup dialog box appears.

  2. Select printing options.

    Definition

    Print a picture of what the definition looks like.

    PeopleCode

    Select if you want the report to include a listing of any PeopleCode programs that are attached to each field, identifying the program type and listing all of the PeopleCode statements. Selected by default.

    Graphics

    Not used for record definition printing.

    Margins(mm) [margins (millimeters)]

    Set the distance (in millimeters) from the edge of the page to the left, right, top, and bottom edges of the page image or report. The defaults are: 20 (top), 5 (bottom), 5 (left), and 0 (right).

    Header

    Print a header at the top of the report indicating the date and time at which you printed the report and the database name, record name, version number, and page number. Selected by default.

    Footer

    Print a footer at the bottom of the report indicating the date and time at which you printed the report and the database name, record name, version number, and page number. Deselected by default.

    Border

    Print a border or box around a record definition report. To print reports faster, clear this check box so that the printer can print the report in character mode, rather than in graphics mode. Deselected by default.

    Border Space(mm)

    Insert a set amount of space between a graphical boarder around the record definition report and the margins of the report. The default value is 3.

  3. Click OK when you are done, to close the Print Setup dialog box and save your settings.

  4. Select File, Print.

Record Definition Report

The following table lists the columns in the record definition report and their contents:

Column

Contents

Field Name

The name of the field.

Type

The field type.

Length

The length of the field (not specified for long character fields).

Format

The field format.

Long Name

The 30-character name of the field.

Short Name

The 15-character name of the field.

Key

Identifies key attributes (characters defined by position).

Req (required)

Yes indicates that the field is required.

TblEdt (table edit)

Prompt: Field values are edited against a specified prompt table.

Y/N: The field uses the Yes/No Table.

Xlat: The field has values in the translate table. Values (if printed) are listed below the field.

AU (auto-update)

Yes indicates that the auto-update option is enabled.

Dt (date)

Yes indicates that the reasonable date option is enabled.

PC (PeopleCode)

Yes indicates that the field contains PeopleCode. PeopleCode text (if printed) appears below the field.

Aud (audit)

Yes: The field audit flag is enabled.

A: Audit add.

C: Audit change.

D: Audit delete.

Prompt Table

The name of the prompt table.

Default Value

Any default value or constant.

You might also see these additional reference lines below each field or at the end of the report:

Reference

Description

SQL View

Shows the SQL view Select statement for view-type record definitions. This appears at the top of the report.

System Maintained

Indicates that the System Maintained check box is selected. Because this option is for documentation purposes only, it doesn’t warrant its own column heading.

DbField Help Context: nnn

Indicates that the field has been assigned a field help context number to link it to a help file that describes how the field is used wherever it appears in the database.

RecField Help Context: nnn

Indicates that the field has been assigned to a record field help context number to link it to a help file that describes how the field is used only as it appears in this record definition.

Audit Record

Identifies a user-defined audit record. Lists the audit record name and the type of audit.

Set Control Field

Identifies a set control field that is designated for the record definition.

Related Language Record

Identifies a related language record that is designated for the record definition.

Query Security Record

Identifies views that are used to restrict query access to data that is stored in the table.

Parent Record Name

Identifies the hierarchical relationship of the record for query reporting.

Printing records with subrecords takes into account whether the record view is expanded or collapsed. If the record view is expanded, the subrecord fields are indented to the appropriate level of nesting.

Click to jump to top of pageClick to jump to parent topicCreating SQL View and Dynamic View Select Statements

If you are creating a SQL view or dynamic view record definition, you must enter a SQL view Select statement to indicate which field values you want to join from which tables. The only difference between the standard view and dynamic view is that the dynamic view is not defined as a view to the database; it is stored on the client and run as a Select at runtime. Dynamic views avoid some constraints on views on some platforms.

Non-Standard SQL Table Name

Override the standard convention of prefixing each record name with PS_.

Build Sequence No (build sequence number)

Set the order in which the view is to be created. The default value is 1 when the record or view is initially created. Views that must be created first can be set to 0, while views that you want created last can be set to any number from 1 to 99. The build sequence number is stored with the other details of the record or view in the database.

Click to open SQL Editor

The view text is saved when the record is saved by selecting File, Save. The record must be saved first, before you open the SQL Editor.

As a general guideline, the SQL used for dynamic views should be as simple as possible because the runtime system appends WHERE clauses to the SQL definition to add key field lookup criteria and does not parse the dynamic view SQL definition to find correlation names. For example, we do not recommend the following format:

SELECT...FROM(SELECT * FROM...WHERE...)

Rather than having a WHERE clause in the subquery, one approach would be to create a static view that the dynamic view references. For example, that static view might be:

... ABC_PRD_INS_VW as SELECT B.SETID AS SETID , A.BO_ID_CUST AS BO_ID_CUST , A.INST_PROD_ID AS INST_PROD_ID , B.PRODUCT_ID AS PRODUCT_ID , B.DESCR AS DESCR , A.SERIAL_ID AS SERIAL_ID , A.INSTALLED_DATE AS INSTALLED_DATE FROM PS_RF_INST_PROD A , PS_PROD_ITEM B , PS_RF_INST_PROD_ST C , PS_PROD_PGRP_LNK D WHERE A.SETID = B.SETID AND A.PRODUCT_ID = B.PRODUCT_ID AND A.SETID = C.SETID AND A.INST_PROD_ID = C.INST_PROD_ID AND C.INST_PROD_STATUS <> 'UNI' AND A.SETID = D.SETID AND A.PRODUCT_ID = D.PRODUCT_ID AND D.PRODUCT_GROUP = '00'

Which is referenced by the dynamic view:

SELECT SETID ,BO_ID_CUST ,INST_PROD_ID ,PRODUCT_ID ,DESCR ,SERIAL_ID ,INSTALLED_DATE FROM PS_ABC_PRD_INS_VW WHERE SETID = :1 AND BO_ID_CUST = :2 AND INST_PROD_ID = :3

Note. The order of the columns in the Select statement must be identical to the field order in the corresponding record definition. Also, only certain types of meta-SQL statements can be used in view text.

Note. Related language records should not use dynamic views but should be limited to physical views and records.

See Also

Using Cross-Platform Meta-SQL for Dynamic Views