Developer

Complex Table Indexes

A complex table index definition orders the records of that table by the field for which the index is created. A field must be indexed to allow for the table to be searched on that field. A complex table can have multiple indexes. Indexes can be defined to have a parent-child relationship to give structure to the table’s records.

The Index definition is the most important of the Complex Table. When an index is defined, you specify the field to be indexed. When the Complex Table is downloaded to the Agentry Client, its records are sorted by the fields you have indexed. Only those fields that have been indexed can be searched by the user on the Agentry Client.

Additionally, all Complex Tables must contain at least one index. This is the primary index of the table. The field for this index must contain the unique value for each record in the table. Whenever you define the indexes for a Complex Table, the first index defined is the one treated as the primary index. This cannot be changed once set, so be sure to determine which field should contain the Primary index beforehand. Also, any complex table definition that does not contain a primary index cannot be selected for use by any other definition in the application.

Though only those fields which contain an index can be searched on the Agentry Client, do not simply define one index for each field in the table. There is a certain amount of overhead that goes into each index definition. Also, whenever the records in the complex table are changed, each index must be resorted for each new, updated, or deleted record in the table. This also takes a certain amount of time and resources during a transmit. In a table with a large number of records, superfluous indexes can result in an unnecessary delay for users during transmit.

Complex Table Index Attributes

  • Name: The internal name of the index. This value must be unique among all index definitions within the same parent complex table.
  • Display Name: The value displayed for the index definition on the Agentry Client’s user interface. In most contexts the index is, to the user, the same as the field and it is a common practice to set the display name of the index to match the display name of the field for which it is defined.
  • Field: The field for which the index is being created and by which the complex table records will be sorted.
  • Parent Index: The parent index, set to create parent-child indexes within the complex table.
  • Order: This attribute specifies the order in which records should be sorted; either ascending (default) or descending.

Parent-Child Indexes

In addition to index a field within the complex table, indexes can also be defined to have parent indexes. This can allow you to create a parent-child relationship among the records of a complex table. The Primary index cannot be defined to have a parent index.

This structure can be very useful when the records of the table support this kind of relationship. One example of such data would a complex table containing locations, with each record representing one location within an industrial park. These locations can be structure to have parent-child relationships and the indexes for the complex table can be created to support this. In this case, a parent location could be a building. Within this building there may be five child locations, one for each floor. Within the first floor of the building, there may be 20 child locations, one for each office suite. Within the first suite, there may be 15 child locations as well, one for each room within the suite. Within the Complex Table, each record would contain, among the other fields, one for the location’s ID and one for its parent location, named LocationID and ParentID, respectively.

When defining the indexes for the complex table, an index could be defined on the ParentID field, named ParentIDIdx. Then, a second index definition can be defined for the LocationID field, and this index would have a parent index of the ParentIDIdx index. Within the user interface definitions in Agentry, there are the field types used to create a Cascade. If a cascade were defined for the Locations complex table, the user would first be required to select the Parent ID. Then, they would be presented with a list of just those records in the table with a parent ID equal to the one selected. In the Agentry Editor, these controls are defined to use these parent and child indexes.