Defining Search Definition Queries with PeopleSoft Query

The application data to be indexed must exist in a record containing a column that tracks the update history of the data. This column is referred to as the "Last Updated" field. The Last Updated field is a datetime field that captures when the set of data to be indexed has been changed. Keeping track of the data updates is critical for enabling incremental indexing. Rather than recreating the entire index each time the index needs to be updated, incremental indexing enables the system to gather only the information that has changed since the last time the index generation process has run.

The system keeps track of when the index generation process last ran, and compares that time to the Last Updated field value in the underlying record structure. Based on the comparison between those time values, the system can isolate only the data that has undergone a change since the last index generation process run. Once the system creates the initial full index, only those rows that have been updated or added since the last index process run will be collected and added to the existing index. Using incremental index updates improves performance and decreases system overhead.

If the query being used for the search definition only runs against a single record, then that record must contain a datetime field to capture data update date and time values. If the data exists within a hierarchy of tables (grand parent, parent, and child, for example), only one of the records within the hierarchy requires the existence of the datetime field.

The query defines which fields will comprise the index for a search definition. When working in the query, use the Fields tab to determine if your list is complete. Keep in mind that fields in the index can be used for different purposes. While some fields are the ideal field against which end users would intuitively search (Customer Number, Order Date, and so on), others can be included for different reasons.

For example, some fields are used as metadata to help describe the data contained in the row (resume, invoice, sales order, and so on). Other fields are useful for security in restricting the viewing of the data only to users that have access to a certain type or level of data.

Because the underlying records require the existence of a datetime field to track the "last updated“ value, the query must also contain the corresponding query field in the fields list.

The drilling URL defines the URL, the target, for the search result. The drilling URL enables the end user to click the link in the search result to display the appropriate PeopleSoft application page, with the appropriate data populated in the page.

To create a drilling URL for Search Framework:

1. In PeopleSoft Query Manager, select the Expressions tab.

2. Click Add Expression.

3. On the Edit Expressions Properties dialog box, select Drilling URL from the Expression Type drop-down list.

4. Select the appropriate option for the type of drilling URL(s) you are creating.

   • Component URL: For creating the URL to components.

   • Attachment URL: For creating the URL to attachments.

   • Image URL: For creating the URL to images.

   Note: For image URLs, also select the Query tab, select the Properties link, and select Image Hyperlink.

5. On the Select a Component page, provide the navigation to the appropriate page.

6. Click Search Keys to define which key(s) needs to be passed to launch the page to display the unique information automatically for a particular search result.

   For example, Field Name = CUSTOMER ID and Key Value = A.CUSTOMER_ID.

   Note: If you do not specify the correct search keys, the system will launch the page you have specified, but the user would still need to add the keys manually to view the information associated with a search result, which is not the desired behavior.

7. Click OK.

8. Click the Use as Field button to associate the Drilling URL as a field in the Query.

   Note: Once you have clicked the Use as Field button, you must edit the 'created' field in the Fields table and give it an existing field name in your PeopleSoft database. Query will assign a temporary field name, but this should be changed for easier reference.

Image: Edit Expression Properties dialog box

This example illustrates the fields and controls on the Edit Expression Properties dialog box. You can find definitions for the fields and controls later on this page.

When your expression is complete, click the Use as Field link in the Drilling URL grid on the Expressions tab. You should name the field with a valid field name, such as DRILL_URL. The field name should be valid in the PeopleSoft database.

See Working with Attachment Properties and Working with Images.

You need to create a prompt for your query so that the system can use the last update date and time for enabling incremental index updates. This is required by the Search Framework even if you do not intend to implement incremental index updates. The Application Engine program performing incremental index updates requires the valid prompt data. Use the prompt tab in Query Manager to create your prompt. On the Edit Prompt Properties dialog box, select the "last updated“ from the Field Name drop-down list.

Image: Edit Prompt Properties dialog box

This example illustrates the fields and controls on the Edit Prompt Properties dialog box. You can find definitions for the fields and controls later on this page.

So that the prompt identifies the correct rows for incremental updates, you must also add query criteria based on the “last updated” field. Use the Criteria tab in Query Manager, and click Add Criteria. Set the criteria as described in the following table.

For example:

Image: Edit Criteria Properties dialog box

This example illustrates the fields and controls on the Edit Criteria Properties dialog box. You can find definitions for the fields and controls later on this page.