Add fields, field groups, indexes, and relations
When you create a table and fields, you add those fields to the table, and then create field groups to pool associated fields. Then, you drag fields, such as EDTs and enums, from the AOT or from your project in Solution Explorer. If you do not define any field groups, by default you only see data from two fields (the primary key and description fields) in the primary table because these are indexed automatically.
Field groups improves consistency in form design and reduces occurrences of users having to individually choose fields for form patterns. When a modification is made to a field group, such as a changed property or a new added field, any forms that contain that field group automatically updates to show the new changes. Field groups are added through the table designer by right-clicking the Field groups node to select a new group. Then, you can drag fields from your table into the various field groups that are listed under the Field groups node.
The following animation shows a field being selected and dragged from the Application Explorer window to the Fields node of a new table in the Table designer window.
Indices are created on a table to improve efficiency with data retrieval. They are added by using the table designer. An index is defined by using one or more fields as the lookup criteria. When you are determining which fields to use for the index, consider fields that are often searched by a range or query, fields that are frequently used in joins, or fields that are often used to order or group results.
The order of the columns in an index is important. For efficiency, an index should be organized from the most granular column (highest number of unique values) to the least granular column.
The three types of indices that are used in finance and operations apps are:
- Primary index - Provides a unique key to each record in a table. The property for Allow Duplicates must be set to No because multiple values with the same result cannot exist. An example of a primary index is a phone number or account number.
- Clustered - This type of index organizes the data for a table according to the order of the index. For example, the data in a phone book is first sorted by last name and then by first name. For each last and first name, there is a corresponding phone number. The clustered index in this case would be the last name.
- Non-clustered - This type of index provides a way to quickly reference data that is found in the clustered index by using a specified set of columns. An example of this is the index at the back of a textbook. You can look up a specific topic, and the index provides a list of page numbers that have information on that topic.
Multiple tables are often used to collect data about a similar subject. You can create a table relation on the primary table to define how it is related to a secondary table. A relation is added on the Relations node under the table heading in the Table designer window. You can use table relations for several reasons, including associating rows in one table to another, creating auto joins in forms to join one table to another, looking up values in other tables, and validating data by providing constraints.
When you are creating forms, it is common to have related tables as underlying data sources so that lookups can be performed. You can establish this relationship so you can access the information in both tables by using a common field, such as an account number. Relations can also be used to restrict the rows that are returned in a table.
The following table lists the different properties of relation notes.
| Property | Options | Description |
|---|---|---|
| Cardinality | NotSpecified, ZeroOne, ExactlyOne, ZeroMore, OneMore | Specify the cardinality between the current table and a related table. The relation between a header table and lines could be ZeroMore, meaning one header exists and has zero-to-many lines. |
| EDTRelation | No, Yes | Specify whether the relation migrated from an EDT, meaning the relations were in previous versions defined on the EDT? |
| Index | index | Specify the index used to find the related item. |
| Name | Relation name | Specify the Relation name, which is typically the name of the related table. |
| On Delete | None, Cascade, Restricted, CascadeRestricted | Specify how the Delete action is used with the relation. It indicates how deletions in the main table are handled in related tables. |
| Related Table | table | Specify the reference table. |
| Related Table Cardinality | NotSpecified, ZeroOne, ExactlyOne | Specify the cardinality between a related table and the current table. The relation between lines and a header is ExactlyOne, and a header always exists. |
| Relationship Type | NotSpecified, Association, Composition, Link, Specialization, Aggregation | Specify the type of relationship: NotSpecified -The system infers the value from the following sequence: Specialization, Link, Composition, Aggregation, Association. Specialization - Applies only to table inheritance. Link - Used in migration tools to support earlier versions. You should not use Link as a relationship type. Composition - Use if the current relation only exists once. It could be a specific pallet in a warehouse. Aggregation - Use if the parent table has a delete action defined to be used in the current relation. Association - Standard foreign key relation. |
The Field, Related field, and Value properties are used to add details about the relation and dictate the fields that are related between two tables.
Relation types are defined as follows:
- Normal relation - Specifies relation fields without conditions.
- Field fixed relation - Specifies relation fields to restrict the records in the primary table.
- Related field fixed relation - Specifies relation fields that restrict the records in the related table.
- Foreign key relation - Specifies a correspondence between a foreign key field in the present table and the primary key field in another parent table.
Relations have one or more relation field types defined.
Delete actions use relations for validation when deleting:
- Restricted - Delete is restricted when data exists in a related table.
- Cascade - Delete occurs for a table and related table.
- CascadeRestricted - Depending on the parent table, select Restricted or Cascade.