PeopleTools 8.52: PeopleSoft Search Technology

Defining Search Definition Queries

This section contains an overview and describes:

Defining search definition queries with PeopleSoft Query.
Defining search definition queries with Connected Query.
Defining a deletion query.
Working with attachments.

Understanding Search Definition Queries

To define the data that end users can run their searches against, you use these standard PeopleSoft query design tools:

PeopleSoft Query
Connected Query

Using these tools provides a familiar interface for PeopleSoft implementation teams and a standard means of defining searchable data amongst all PeopleSoft applications. You will also need to make sure that the data you want to expose to Query is authorized by way of the Query Security Manager.

You do not need to create new queries solely for creating search definitions. You can re-purpose existing queries in PeopleSoft Query or connect multiple existing queries using Connected Query. Except for a handful of requirements for the Search Framework, queries used for creating search definitions are created exactly as you would any other query.

Queries need the following items defined for use with the Search Framework:

List of fields to index from authorized records.
Last Updated” record field
Drilling URL
Prompt against the “Last Updated” field.
Criteria for the “Last Updated” field.

This section describes the requirements and guidelines to follow when defining queries for use with the Search Framework. The PeopleSoft Query and Connected Query documentation is not duplicated within this guide. It is assumed that you have a working knowledge of those products.

Defining Search Definition Queries with PeopleSoft Query

This section discusses:

Specifying a list of fields to index.
Inserting a “Last Updated” Field.
Creating a Drilling URL.
Creating a Prompt for the “Last Updated” Field.
Defining Prompt Criteria.
Testing the query.

Specifying a List of Fields to Index

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.

Note. All search keys and alternate search keys must be indexed. Select additional fields to be indexed as required by your business processes. Also keep in mind that some fields are codes and might need to be translated to their description for searching.

Important! When saving the query, make sure it is of type Public.

Inserting a “Last Updated” Field

The SQL table that the application data resides in must have a column that tracks the update history of a row of data. Likewise, the query must also contain the corresponding query field in the fields list. The “last updated” field is instrumental for enabling incremental index updates. An example of this field is the LASTUPDTTM field.

Important! The Last Updated field must be of the type datatime.

Once the system creates the full index, only those rows that have been updated or added need to be included in the next incremental index update. Using incremental index updates improves performance and decreases system overhead.

Note. For upgrade purposes, all PeopleSoft application tables come with a LASTUPDTTM field. For any custom tables, you need to ensure the field exists in the record.

Creating a Drilling URL

A drilling URL is required to create the URL in the search result document. The drilling URL enables the end user to click the URL in the search result document to display the appropriate PeopleSoft application page, with the appropriate data populated in the page.

To create a drilling URL for Search Framework:

In PeopleSoft Query Manager, select the Expressions tab.
Click Add Expression.
On the Edit Expressions Properties dialog box, select Drilling URL from the Expression Type drop-down list.
Click the Component URL link.
On the Select a Component dialog box, provide the navigation to the appropriate page.
Click OK.
Click the Use as Field button to associate the Drilling URL as a field in the Query.

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

Creating a Prompt for the Last Modified Field

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 passes the appropriate prompt data to the query. Use the prompt tab in Query Manager to create your prompt. On the Edit Prompt Properties dialog box, select the field you are using to detect record modifications from the Field Name drop-down list.

Defining Prompt Criteria

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:

Item	Value Selected
Expression 1	Field. Your “last updated” field.
Condition Type	not less than(recommended) greater than
Expression 2	Prompt Your prompt

For example:

Defining Search Definition Queries with Connected Query

You can also use Connected Query to develop your queries for Search Framework. Connected Query enables you to connect multiple queries together in a parent-child relationship, where the child queries filter results for the parent queries. Using Connected Query lets you connect numerous simpler queries, rather than writing one more complicated query. The smaller more modular queries can be reused in different queries.

When using Connected Query, only the top-level (or root) parent query needs to have the requirements described in this chapter. The child queries do not require these elements. For example, only the parent query requires a Drilling URL defined.

Defining a Deletion Query

In many cases, you may want to consider also writing a deletion, or pruning, query to keep the index and the transactional tables in sync with regard to rows that have been deleted from the transactional tables.

For example, assume a row exists in the transactional table for Big Company, and that row has been included in the search query criteria and indexed by the Search Framework crawler. Now, suppose that row gets deleted from the transactional table because Big Company went out of business and no longer exists. Because the row no longer exists in the transactional table, you do not want the previously indexed data to appear in a user’s search results. In a search definition, you associate a deletion query for an index on the Advanced tab in the Define Query to Delete SBO section. There you specify the query name and the Drilling URL field for the deletion query.

The Delete query generates a feed containing only the records that need to be deleted from the index or those documents/records that are marked for deletion from the index. One method of capturing the deleted rows is to create an audit record on the transactional table in which a record of all deleted rows gets inserted into the audit table. The delete query would then capture the rows in the audit table and remove from the index the document entries matching those rows. The delete query also needs a datetime prompt which returns rows deleted after that datetime value.

Working with Attachments

An attachment is a special type of Drilling URL, which is also specified in the query as an expression. You associate the Attachment URL with a URL Identifier which will tell SES where to look for the data. You must have previously set up a URL pointing to the location of the data, which could be a record with the database or a secure file location such as an FTP site.

To create an attachment URL for Search Framework:

In PeopleSoft Query Manager, select the Expressions tab.
Click Add Expression.
On the Edit Expressions Properties dialog box, select Drilling URL from the Expression Type drop-down list.
Click the Attachment URL link.
On the Enter an Attachment URL dialog box, provide the previously created PeopleTools URL and identify a field within the Query which designate the correct attachment file.
Click OK.
Click the Use as Field button to associate the Attachment URL as a field in the Query.

Testing Your Search Definition Query

After you have defined your query in Query Manager with all the correct fields included along with the Search Framework requirements, you can test the query using the Run tab. This runs the query outside of the Search Framework. That is, you do not need to deploy the search definitions to perform this initial test.

This enables you to determine if:

The correct rows are being returned by your query.
The Drilling URL displays the appropriate page when clicked.
You can open an attachment (if you can’t open it from the test results, then neither can SES).

To test your query with the Run tab:

In Query Manager, with your query open, click the Run tab.
At the prompt, enter a valid value to run against the Last Modified field.

For example:

01/01/1900 0:0
View the results and click the Drilling URL and/or attachment links.
Confirm that you arrive at the desired page with the appropriate data loaded as expected.