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

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:

Image: Example of a compound key structure in the PO_LINE record definition

This example illustrates the fields and controls on the Example of a compound key structure in the PO_LINE record definition. You can find definitions for the fields and controls later on this page.

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.

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:

Image: INSTALLATION table: example of record definition with no defined key structure

This example illustrates the fields and controls on the INSTALLATION table: example of record definition with no defined key structure. You can find definitions for the fields and controls later on this page.

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:

Image: Record Field Properties dialog box: Use tab

This example illustrates the fields and controls on the Record Field Properties dialog box: Use tab. You can find definitions for the fields and controls later on this page.

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.

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.

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

Image: Example of a CNT_CLAUSE_TBL record

This example illustrates the fields and controls on the Example of a CNT_CLAUSE_TBL record . You can find definitions for the fields and controls later on this 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:

Image: Example of updating the SQL view Select statement

This example illustrates the fields and controls on the Example of updating the SQL view Select statement. You can find definitions for the fields and controls later on this page.

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:

Image: Example of the Record Field Properties dialog box: Edits tab

This example illustrates the fields and controls on the Example of the Record Field Properties dialog box: Edits tab. You can find definitions for the fields and controls later on this page.

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:

Image: Moving field to new position

This example illustrates the fields and controls on the Moving field to new position. You can find definitions for the fields and controls later on this page.

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.

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.

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.

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.

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.

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.

   Field or Control	Definition
   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.

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