Creating Record Definitions

Fields that are grouped together as a unit are record definitions. A record definition represents what the underlying Structured Query Language (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 a new record.

• Open an existing record.

• Manipulate fields in record definitions.

• Set record properties.

• Set record field properties.

Viewing Record Definitions

You can see four views of the 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 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 on 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 contains a column for each PeopleCode program type. This display indicates which fields contain PeopleCode events with a check mark in the event type column. Double-click on 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 display of fields in the 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 doesn’t change the order of the fields in the actual record. The numbers that the fields are originally assigned remain the same. This is important when it comes to key fields.

To actually reorder the fields in the records, you must cut and paste or select the 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

PeopleSoft recommends that you save your work every time that 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 haven’t named the definition, the system prompts you to enter a record name. The system also prompts you to set the tablespace with 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.

• 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, PeopleSoft recommends that you 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 a New Record

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 (Structured Query Language 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 is the default setting.
   SQL View (Structured Query Language view)	Select to define a record definition that corresponds to a SQL view, which is not a physical SQL table in the database, but rather fields from one or more SQL tables that are reorganized into a different sequence. This 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.
   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 are commonly used in multiple record definitions—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. Temporary images of the table can be specified on the PeopleTools Options page. Temporary tables are used 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 (nonstandard Structured Query Language 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 an Existing Record

Open a record definition in the same manner 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. Keys should be located 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 isn’t 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 on one record definition and 30 other record definitions contain the same field name, you have 31 records that must be built or altered. 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 drag and drop:

  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. Fields are inserted 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

Select Insert, SubRecord to search, select, and insert subrecords into a record. A subrecord enables you to add a group of fields that are commonly used in multiple record definitions. A subrecord must be defined before it can be inserted into a record definition.

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, and 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 see 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 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.

Setting Record Properties

After you create a record definition, you'll want to apply attributes so the record functions. Access the dialog box by selecting Record Properties, then either the General or 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. With 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. They are not available for subrecords.

Sync type (MSF)

The Sync type settings are used only with the PeopleSoft Enterprise Mobile Synchronization Framework (MSF).

Note. PeopleSoft intends to support the Mobile Synchronization Framework in a future release.

Record Information

The Record Information contains options used during PeopleSoft upgrades.

Note. The Append ALL (Dynamic Views) option applies to dynamic views. If it is set, 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 tableA FILL. This is because the same record field name may exist in two different records.

See Also

Understanding the Synchronization ID and Datetime Stamp

PeopleSoft upgrade documentation

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 can’t contain key fields. Therefore, if you clone a record definition to create an audit record definition, you must remove all key attributes.

PeopleSoft recommends that you also use the audit-specific fields that are already defined for the PeopleTools audit table (PSAUDIT), which PeopleSoft 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, 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.

• Add from and through logic to the search page.

• Set record field edit properties.

• Move fields in the same record.

• Move fields to another record definition.

• Delete a field 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 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 record field properties from 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 Edit, Record Field Properties from the PeopleSoft Application Designer menu.

Understanding Record Key Considerations

When creating key fields in your record definition there are several guidelines to keep in mind.

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.

Defining compound key combinations

For example, the keys to the ABSENCE_HIST record definition are EMPLID, ABSENCE_TYPE, and BEGIN_DT. This means that an absence history can be created only once.

Keys on 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, multiple keys that comprise independent, or parent and child key combinations. There are, however, exceptions, such as record definitions without keys and duplicate order keys.

Records Without Keys

Some record definitions, such as INSTALLATION, don’t 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, in this case there’s only one row of data, so there’s no need to distinguish one row from another. Another primary use for keys is to build database indexes to rows—because there’s only one row for each table, the record doesn’t need indexes.

INSTALLATION Table

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 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 there is no unique identifier—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 information.

In the EDUCATN record definition, there are three keys that together determine how information is stored and retrieved. EMPLID identifies the group of rows; the placement of the duplicate order keys, DT_EARNED and DEGREE, instructs the system to order rows in the group first by date, 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 key(s). 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, 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 establish creates a database index when you SQL Create the table. While database indexes are important, they consume disk space and, when the system must support the alternate key, processing time. Therefore, don’t 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" to 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.

Keys

Audit

The 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 logs the user ID, the date and time the field was modified, and the old and new values.

To audit certain fields, regardless of the record definitions in which they’re included, you want to control when the fields are audited. To audit several fields in a record definition, you might want to 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 enables you to change labels of record fields dynamically anytime that the default label on the field definition is changed.

For example, if 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’ll 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.

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 on that same record. Otherwise, the time data that is displayed or 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.

Adding From and Through Logic to the 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, 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). This is 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

Once you define a search field as a Through Search Field, you’ll need to update the SQL view text.

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

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.

To move a field in the same record definition:

1. Select the field that you want to move.

   Use 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 in the new order.

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

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 a Field From a Record Definition

To delete a field from a record definition:

1. Select the field that you want to remove and press the del (delete) key.

   This deletes the field completely and doesn’t copy it to the clipboard, unlike a cut operation, which does copy to the clipboard.

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

   If you’ve already SQL Created the underlying table for the record definition from which you are deleting the field, re-create 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 enter.

Results of Renaming Record Definitions

When you rename a record definition, the system automatically renames all references to it, including data on 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 the matching text.

If you have already SQL Created the underlying tables for the record definition that you renamed, re-create 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’ve 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)	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. Cleared 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. Cleared 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 to avoid errors. For example, the following format is not recommended:

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

Rather than having a WHERE clause in the sub query, 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.

See Also

Using Cross-Platform Meta-SQL for Dynamic Views