When performing searches, you may want to return search results by a specific criterion. This criterion must be indexed before it can be searched. If your existing index does not contain the property you want to use for searching, you must add it. For a property to be added to the index, you must add it to the token table.
The /atg/commerce/textsearch/OrderIndexingSQL and the /atg/userprofiling/ configuration files define which properties are selected as tokens.
textsearch/ProfileIndexingSQL
For example, the following OrderIndexingSQL snippet shows, in the first column, how it returns the order_id properties for an Oracle database. The second column is produced by appending all of the values in the columns together with their prefix and separated by a space:
SELECT o.order_id \ , NVL2(o.order_id, ''orid'' || o.order_id || '' '', '''') \ || NVL2(o.state, ''orst'' || o.state || '' '', '''') \ || NVL2(u.id, ''pfid'' || u.id || '' '', '''') \ || NVL2(u.login, ''pflg'' || u.login || '' '', '''') \ || NVL2(u.first_name, ''pffn'' || u.first_name || '' '', '''') \ || NVL2(u.last_name , ''pfln'' || u.last_name || '' '', '''') \ || NVL2(u.email , ''pfem'' || u.email || '' '', '''') \ AS tokens \
Note that properties are identified with the SQL column name, and a prefix name. The prefix name is defined in either the OrderSearchTreeQueryFormHandler or the CustomerSearchTreeQueryFormHandler configuration file. These files provide a map that matches the SQL column name to the name of the repository item.
For example, the following OrderSearchTreeQueryFormHandler snippet associates repository items with SQL prefixes:
uiFieldNameToIndexPrefixMap=\ id=orid,\ state=orst,\ profile.profile_id=pfid,\ profile.login=pflg,\ profile.firstName=pffn,\ profile.lastName=pfln,\ profile.email=pfem,\
To add a property to the index:
Add the property, and its associated prefix, to the list of tokens via the SQL statement.
For example, to add
middle_nameas an indexed property, you would add it to theOrderIndexingSQLconfiguration file:SELECT o.order_id \
, NVL2(o.order_id, ''orid'' || o.order_id || '' '', '''') \
|| NVL2(o.state, ''orst'' || o.state || '' '', '''') \
|| NVL2(u.id, ''pfid'' || u.id || '' '', '''') \
|| NVL2(u.login, ''pflg'' || u.login || '' '', '''') \
|| NVL2(u.first_name, ''pffn'' || u.first_name || '' '', '''') \
|| NVL2(u.middle_name,.''pfmn'' || u.middle_name || '' '', '''') \
|| NVL2(u.last_name , ''pfln'' || u.last_name || '' '', '''') \
|| NVL2(u.email , ''pfem'' || u.email || '' '', '''') \
AS tokens \Note that the
u.middle_nameis associated with thepfmnprefix.In the
OrderSearchTreeQueryFormHandler, associate themiddleNameproperty of the profile repository item with SQL column name prefix that was added to theOrderIndexingSQLfile:uiFieldNameToIndexPrefixMap=\
id=orid,\
state=orst,\
profile.profile_id=pfid,\
profile.login=pflg,\
profile.firstName=pffn,\
profile.middleName=pfmn,\
profile.lastName=pfln,\
profile.email=pfem,\Note that when creating prefixes, it is best to keep their names to four characters or less.
Once you have added the new properties to the database SQL as tokens, perform a
bulkloadoperation as outlined in the Preloading the Index section. This will add the new property to the index.Create a UI component with the name
profile.middleName. Refer to the Service Center UI Programming Guide for details on creating and modifying UI components.
Once you have added a property to an index, it can be used to sort search results.
