This documentation supports the 21.05 version of Action Request System.
To view an earlier version, select the version from the Product version menu.

Defining indexes

Indexing can greatly reduce database search time. Indexes can be defined for data fields on regular forms. You cannot create indexes for other form types because:

  • Join forms use the indexing defined for the forms from which they are constructed.
  • Display-only forms have no database table, so they need no indexing.
  • View and vendor forms are owned outside of AR System , so any indexing they support must be managed outside of AR System .

The Request ID field is already indexed, so you need not build a separate index for this field. Good candidates for indexing include fields that users search on frequently.

If you define an index for a character field, you might save search time by using a QBE Match setting of Leading or Equal, not by using a QBE Match setting of Anywhere. For more information, see the description of the "QBE Match" property under the Field Properties table. When you enable a unique index value for the field, make sure that you have atleast add one required field with an unique index enabled.

The following table describes multiple scenarios of defining unique indexes supported on Microsoft SQL Server, Oracle, PostgreSQL database:

ScenarioMicrosoft SQL ServerOraclePostgreSQL
Defined an unique index on a single field and you enter value for the field(error)(error)(error)
Defined an unique index on a single field and you keep the field blank(error)(tick)(tick)
Defined a composite index on multiple fields and you enter values for all the fields(error)(error)(error)
Defined a composite index on multiple fields and you enter values for only some of the fields(error)(error)(tick)
Defined a composite index on multiple fields and you keep all the fields blank(error)(tick)(tick)

((error)—Generates an unique index violation error; (tick)—Updates the database with the value) 

If you are creating or modifying indexes in a form for which a large amount of data exists, this process can take a significant amount of time and disk space because the index must be built or rebuilt. Therefore, avoid defining indexes during normal production hours.

More time is required to modify a form (for example, adding new fields) when indexes are defined for the form. The greater the number of indexes defined for the form, the more time and disk space is required. Submit and modify operations in a browser also take longer on forms with many indexes.

To define indexes for a form

  1. Open the form with which you want to work.
  2. Click the Definitions tab, and expand the Indexes panel.
  3. Add a new index to the list:
    1. Select Additive as the Overlay Type, then click New.
    2. Select the new index.
    3. To add fields to the index, click Add and complete the Field Selector dialog box for each field.
      You can combine multiple fields into a composite index. You can enter as many as 16 fields. Add the fields in the order you want them indexed, or use the Up and Down buttons to put them in the correct order. Each indexed field must be less than or equal to 255 characters, but the composite Index can have a total length greater than 255.
    4. Use the Remove and Remove All buttons to remove fields form the index.

      AR System does not verify if the size violates the databases rules, but a database error is returned if the size is too large.

  4. To remove an index from the database, select the index, and click Delete.
  5. Save the form.

Was this page helpful? Yes No Submitting... Thank you