Indexes are defined by creating an index ID and then adding data items to the index ID. More than one data item can be associated with an Index ID.
Index ID
There are several ways to characterize an index:
- Unique index—These do not allow fields with duplicate values in records. Configure fields as unique creates a primary key for your table's records.
For example, the application Chart of Accounts allows you to view, edit and create general ledger accounts. The application's table has a unique index which contains the field Account Number. If a user tries to create a record for an account number which already exists, the unique index prevents a duplicate record from being created by generating an error when you try to save.
Subtables of type Main can use unique indexes, but type Sub cannot. In order to create unique indexes which are unique to the parent record, but not globally unique, specify the nwHeaderId.
Primary index —A primary sort needs to be added in the applications built over the table, or in the logic blocks associated with them. Learn more in the Sort configuration topic.
Index Fields and Sub Fields
Identify one or more fields from the Fields page to associate to an index. The Sort Order determines whether the field will sort ascending or descending. By default, the index sorts in ascending order. You can also specify sub fields for fields that are Component data items. This means the index is associated with a specific component of the field, such as the currency code from a currency field, rather than the entire object.
In order for a user to use wildcard searching in a field, an index must be created for that field in which the Text Pattern checkbox is selected. If an index is not created, or the Text Pattern checkbox is cleared, then any filter searches in that field must be an exact match to an existing value.
Types of indexes
| Indexed Fields | Notes | |
|---|---|---|
| Default Indexes | nwId, nwHeaderId, and nwExternalId | The system automatically creates these when a table is defined. nwHeaderId and nwExternalId include nwNateId and nwTenantStripe automatically. |
| Auto-Generated Indexes | Fields of type Table Lookup, List Lookup, Auto Number, and Translatable | Auto-generated indexes include nwNateId and nwTenantStripe automatically unless the index is a base index. They are visible beneath the Indexes section on the Indexes tab in the Table Definition. |
| User-Defined Indexes | Any fields dictated by a developer. | nwNateId and nwTenantStripe are automatically added unless the index is abase index. |
| Workflow Indexes | WorkflowCurrentState and WorkflowCurrentStateType | When workflow is enabled for a table, the system automatically creates these indexes. |
| Subtable Mains Indexes | nwHeaderId | The system automatically adds InternalSeq as a field to the default index containing nwHeaderId when a table of type Subtable Main is defined. |
| Join Indexes | nwId stored as text | The nwId is stored as text, so it works better in certain situations than others. For example, it helps connect the join tables, but is not great at filtering. You may still need to create more specific indexes with the nwID first and then any fields you want to filter on. This allows the system to use the same index for the join table and the filter operations. |
Index query restriction
Index query restrictions allow application developers to limit which fields a user can filter and sort in the list form of an application. Putting these restrictions on fields removes them from fetch filtered data queries. This improves performance, especially for applications that access large numbers of records.
Control index query restrictions through the Index Query Restriction checkbox on the Indexes page in the Table Definitions application.
When Index Query Restriction is selected, the following data items types are no longer filterable or sortable in the list form:
- Address
- Currency
- Date
- DateTime
- Name
- Number
- Phone
- True False
- Text
- Text Area
All other data item types are still filterable and sortable in the list form, including any Text data items that have list lookups associated with them. These list lookup data items are automatically indexed upon their creation, which allows them to remain filterable and sortable in the list form even when index query restrictions are applied.
Tables that are meant to save records in the thousands, such as tables that hold transaction or master data, should have Index Query Restriction selected. Tables that store large amounts of data and don't have any index query restrictions could face significant performance issues and failures when they perform queries to fetch filtered data from the database. Tables that are meant to be smaller, such as setup tables, do not require index query restrictions because the amount of stored data should not significantly impact performance levels.
Index query restrictions in Relationship applications
If you want to enable index query restrictions on a relationship application, you must select the Index Query Restriction check box on the table that the relationship application is built on and the table that the associated detail application is built on.
For example, the Suppliers and Contacts application is a relationship application that is built over the ContactRelationships table. The associated relationship detail application, Suppliers, is built over the Directory table. To enable index query restrictions for Supplier and Contacts, you must select the Index Query Restriction check box on both the ContactRelationships and Directory tables.
Sort and filter on restricted fields
If index query restrictions is enabled on a table and it contains a list form field that you want a user to be able to filter and sort on, you can create index IDs for that field to enable filtering and sorting. If there are multiple fields associated with the same index ID, only the first data item associated with an index ID is recognized by the index query restrictions. If there is a data item that must be filterable and sortable but is not the first data item listed for an index ID, you must create another index ID in which it is either the only data item or it is listed first in the sequence.
The steps required to enable filtering and sorting on a restricted field vary by data item type. Some data item types only require one index to enable both filtering and sorting. Other data items types require two separate indexes, in which the first enables filtering, and and the second enables sorting.
These data item types require one index to enable both filtering and sorting:
- Currency, if Text Pattern is not selected for the index field
- Date
- DateTime
- Number
- True False
These data items must have two index IDs created for them if they must be filterable and sortable. The first index you create makes the field filterable, and the second index you create makes the field sortable. These fields cannot be sortable without being filterable.
- Currency, if Text Pattern is selected for the index field
- Address
- Name
- Phone
- Text
- Text Area
The table below demonstrates how you should structure indexes in a table with Index Query Restriction selected. The table has a Name field, Date field, and a Number field that all need to be both filterable and sortable. The table also has a Text field that must be filterable, but not sortable.
| Index ID | Field | Result |
|---|---|---|
| A | FirstName | Filterable |
| B | FirstName | Sortable |
| C | TodaysDate | Filterable and sortable |
| D | NumberOfItems | Filterable and sortable |
| F | Description | Filterable |
Wildcard search through Text Pattern
If a Text, Email, Address, Name, or Phone type data item must be filterable, sortable, and searchable using wildcards, you must select the Text Pattern check box on one of the indexes you create for that data item. Text Pattern does not need to be selected for both indexes.
See Indexes for more information wildcard enabling through indexes, and Wildcard characters for more information on the wildcard characters used by Nextworld.
Example: index query restrictions
The WOTransactionHeaders table is a helpful example of index query restrictions because it has the restrictions enabled and also requires additional indexes since it has Date fields that need to be sortable, filterable, and searchable with wildcards.
Object overview
The WOTransactionHeaders table is used as the header table for the WOMaterialTransaction header detail application. The WorkOrderMaterialTransaction application allows the user to issue parts, from a specified issue-location, from a work order.
WOTransactionHeaders has the Index Query Restriction check box selected. The table contains a field in the list form that must have additional indexes created for it: Transaction Date is a field that must be filterable and sortable.
Configuration elements
The Index Fields subtable is configured as such:
| Index ID | Field | Sort Order | Text Pattern |
|---|---|---|---|
| A | WOTransactionNumber | DESC | False |
| A | WOTransactionDate | DESC | False |
| B | WOTransactionDate | DESC | False |
Index A—Created for logic block purposes. WOTransactionNumber and WOTransactionDate are listed in the order that they are requested in a logic block query action. WOTransactionNumber is listed first, so it is recognized by the restrictions and becomes filterable in the generated application's list form. WOTransactionDate is not recognized by the restrictions because it is not the first data item listed in the index.
Index B—Created to make WOTransactionDate filterable and sortable. WOTransactionDate only needs to be in one new index because it is a DateTime data item.
Index sequencing
The information here describes the configuration options and requirements that you should be aware of when sequencing fields in indexes and logic blocks.
Indexes are ordered by the fields in the index from left to right. If you want to use an index your filter needs to have, at minimum, the first field defined in the index.
For example, you could have an index with the fields of Last Name, First Name, and Org Unit. You would need to include Last Name in your filter, then could optionally include First Name. If you filter for a last name, the entries would then be sorted by first name. If you included Last Name and First Name, you could then also include Org Unit. The order of fields is not important, so filtering for Org Unit, First Name, and Last Name is treated the same as filtering for Last Name, First Name, and Org Unit.
The order of data items in an index does not need to match the order of data items in a logic block request if all of the data items are contained in an index. For example, the following table demonstrates a logic block fetch request with indexed fields:
| Index ID | Index A fields | Fetch order | Successful index | Successful index |
|---|---|---|---|---|
| Index A | Field 1 | 1st | Field 1 | Field 2 |
| Field 2 | 2nd | Field 2 | Field 3 | |
| Field 3 | 3rd | Field 3 | Field 1 |
You can perform a partial logic block request of an index. For example, the following table demonstrates a partial logic block request:
| Index ID | Index B fields | Fetch order | Successful index | Successful index | Failed index (does not contain first index field) |
|---|---|---|---|---|---|
| Index B | Field 1 | 1st | Field 1 | Field 1 | Field 2 |
| Field 2 | 2nd | Field 2 | Field 3 | Field 3 | |
| Field 3 | 3rd | ||||
Fields can also be associated with more than one Index ID. For example, the following table demonstrates multiple indexes with the same field in each index.
| Index ID | Indexed fields |
|---|---|
| Index A | Index A Field 1 |
| Index A Field 2 | |
| Index A Field 3 | |
| Index B | Index B Field 1 |
| Index B Field 4 | |
Structure
When indexes are created, you should consider how you expect queries to be structured. If you have two fields, Field 1 and Field 2, the indexes you create would differ depending on the expected queries. If the queries are over:
- Field 1, Field 1 and Field 2—Create an index for [Field 1, Field 2]. This allows you to query Field 1, Field 1 and 2, but not Field 2 alone.
- Field 1, Field 2, Field 1 and Field 2—Create an index for [Field 1] and an index for [Field 2]. This allows you to query each index individually, as well as together. If there are many queries for Field 1 and Field 2 together, you should also create a third index for [Field 1, Field 2].
You can always combine separate indexes, but you will have increased performance if you create additional indexes with the necessary fields included. This is especially true if there is large amounts of data being queried.
Fetch actions and dynamic expressions
You can define multiple indexes for dynamic expressions used in the fetch actions of your logic block, though it may be more performant to have a single index which contains all of the fields.
For example, you could have the following configurations:
- Index A: [Field 1, Field 2]
- Index B: [Field 3, Field 4, Field 5]
- Dynamic Expression action contents: [Field 1, Field 2]
- Fetch action content: [Field 3, Field 4, Field 5], [dynamic expression] OR [dynamic expression], [Field 3, Field 4, Field 5]
You could have Index A and B defined, but if those 5 fields are always going to be queried together, it is more performant if there is a single index with all 5 fields.
Index configuration
Configure an index using the Table Definitions application to have a field, sub field, or a list of fields designed to improve the performance of queries, whether it be through filtering or logic block fetches, by making it possible to locate a specific row without reading the entire table
You must define your Indexes using the Indexes page in the Table Definitions application. Use the two subtables on the page to define your indexes.
Indexes
On the Indexes subtable, you must:
- Enter a unique identifier for your index fields in the Index ID field. These values can be anything, such as A, B, or C.
- Indicate whether the Index is unique or not with the Unique checkbox.
- Primary indexes must be configured at the application level, or with logic blocks. See the Sort configuration topic for more information.
Index Fields and Sub Fields
On the Index Fields subtable, you must enter the Index ID you created and all the fields that you want associated with each ID. The sequence in which the fields are entered heavily impacts how you configure logic blocks that use indexed field.
You can also configure sub field indexes if the Field is a Component data item. For example, if you wanted to index a workflow state type from a workflow, or the currency code from a currency field. To configure this, you would select the workflow as the field, then the workflow state type as the subfield. Or, you would select the currency field as the field and the currency code as the sub field.
If you don't add the subfield, the index is on the entire object.
Index best practices
These best practices ensure that all indexes are configured consistently and correctly throughout Nextworld.
Indexes and index fields are created and configured in the Table Definitions application. Indexes that are part of fetch actions should be designed to perform efficiently throughout Nextworld.
Common best practices
Name index identifiers in alphabetic sequence. For example, the first index ID should be named A, the following should be named B, and the next C.
Unique index
Optionally, the unique index can be named UK1, UK2 as a reference to the unique key on the data model.
Every table automatically has a unique key created for the nwId. Do not create a unique index over the nwId.
There should be at least one unique index created for the table which is human readable that can be used to uniquely identify the record using one or more data items that are in the table. For example, Item is the unique key for the Items table. Item and OrganizationalUnit combined are used to uniquely identify a record in the ItemOrgUnits table. There can be exceptions to this rule since on occasion there are tables that do not have a unique key.
Avoid creating unique indexes on the NwHeaderID field in Detail tables. A unique index is only appropriate on Header tables or in rare 1:1 header-detail relationships. The standard header-detail pattern assumes a 1-to-many relationship so applying uniqueness at the Detail level contradicts this design. If a 1:1 mapping is required, a different construct should be explored.
Position unique index fields at the top of the list on the Fields page. Additionally, list these fields in the same order on the Fields page as they appear on the Indexes page.
Once the table is in production and contains data, the these additional considerations apply:
- A field cannot be removed from the unique index that will cause existing data to be non-unique.
- A unique index cannot be modified or added that will cause a unique key violation on existing data.
Learn more about what an nwId is in the nwId topic.
Performance considerations
NwId is a unique-friendly field and ideal for identifying a single record. It is not included in user-defined indexes by default, but including it manually in indexes can boost performance
Ensure that you don't create too many indexes, which may create performance issues. Keep in mind the auto generated indexes count towards the number of indexes against a table. A list of auto generated indexes display for any list lookup or table lookup field. The list of auto generated indexes can be found on the table definition by using the Refresh Auto Generated Indexes button. Disable the auto generation of these indexes if you do not need them. Each insert, update, and delete to the table requires all indexes to be maintained and updated accordingly.
Do not create index that has the same fields as another index in the same order. For example, if index A has Field A, B, C and index B has Field A, B, C, and D, only index B is needed as partial indexes will be used.
Do not create additional indexes that include the unique key plus additional fields, since the unique key is already unique, you cannot make it more unique.
All fetches in logic blocks should be backed by an appropriate index. Indexes can be used for fetches with partial filtering fields. Indexes with the correct filter but different sort may identify the result set efficiently but force the database to do a costly extra sort step. See Index sequencing for more information on index and fetch sequences.
Index selection is determined by:
- Index definitions
- Fetch filtering criteria, which also includes sorting
- Data distribution, or the nature and characteristics of the data in the table
Missing table indexes can result in a full table scan, which is a sequential read-through of all records to determine which records should be in the result set.
Index query restrictions
Index Query Restriction should be turned on for transaction tables and tables that contain a large amount of data.
When Index Query Restriction is enabled on the table, additional indexes will need to be created to allow filtering using the table filter. Be cautious on the number of indexes created which will defeat the purpose of turning on index query restrictions to limit the filtering.
Limit the enablement the Text Pattern on the index which will allow wildcard and case insensitive searches on the fields that are listed in the index.