Creating 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.

This chapter discusses how to:

• View record definitions.

• Save record definitions.

• Name record definitions.

• Create new records.

• Open existing records.

• Manipulate fields in record definitions.

• Set record properties.

• Set record field properties.

Viewing 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 lists common elements and discusses how to:

• View basic field definitions.

• View key-related characteristics and default values.

• View editing options.

• View PeopleCode program types.

• Reorder fields.

• Size and sort columns.

Common Elements Used in This Section

Viewing 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.

Viewing 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.

Viewing 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.

Viewing 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.

Reordering 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.

Sizing 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.

Saving 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.

Naming Record Definitions

Use these guidelines for record definition names:

• The name length can be up to 15 characters, with the exception of the Temporary Table type, which has a maximum length of 13 characters.

• The name must begin with a letter and can contain underscores to make it more readable.

• Avoid special characters, such as # or $, which can cause problems in some database environments.

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:

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

Creating New Records

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.

   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.

See Also

Manipulating Fields in Record Definitions

Opening Existing Records

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.

Manipulating Fields in Record Definitions

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

• Insert fields into records.

• Insert subrecords into records.

Understanding 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.

Inserting Fields Into Records

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

• Using the project workspace tree.

  To insert a field into a record definition directly from the project workspace by using the drag-and-drop method:

  1. Locate a field using the PeopleSoft Application Designer project workspace tree.

  2. Drag the field from the project workspace tree to the object workspace.

     The field and its attributes are automatically added to the list of fields in the record definition.

• Using the Insert menu (Insert, Field).

  Use the Insert, Field menu selection to search for fields with selection criteria, and then add them to the record using the Insert button. You can also double-click a selected field definition and select Insert the Field Definition. The system inserts the fields below the selected field in the record definition.

• Dragging fields from existing records.

  To drag a field from one record definition to another:

  1. Open a record that contains the fields that you need.

  2. Select the field.

  3. Drag the field to a new record.

Inserting 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.

Setting 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:

• Set general properties.

• Set use properties.

• Create user-defined audit record definitions.

Setting General Properties

Access the Record Properties, General tab.

Setting 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

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.

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.

Record Information

Record Information contains options used during PeopleSoft 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.

Creating 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:

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.

Setting Record Field Properties

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

• Set record field use properties.

• Configure Search Results grid columns in persistent searches.

• Add from and through logic to a search page.

• Set record field edit properties.

• Move fields in the same record.

• Move fields to another record definition.

• Delete fields from a record definition.

• Rename record definitions.

• Delete record definitions.

• Print record definitions.

• Create SQL view and dynamic view Select statements.

Understanding 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.

Understanding 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:

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:

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.

Setting 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

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:

• In the PSAUDIT table, the field length of the OLDVALUE and NEWVALUE fields is 65 characters. Field values that are longer than 65 characters are truncated.

• In the PSAUDITEXT table, the field length of the PTOLDVALUE and PTNEWVALUE fields is 254 characters.

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.

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:

• Label1 (Long Name1, Short Name1), marked as default.

• Label2 (Long Name2, Short Name2).

• Label3 (Long Name3, Short Name3).

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.

Autocomplete Configuration

Persist in Menu Configuration

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.

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.

Configuring 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.

Adding 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:

Setting 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

Table Edit

Moving 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.

Moving 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.

Deleting 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.

Renaming 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.

Deleting 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.

Printing 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:

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

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.

Creating 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.

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