Skip to main content
Version: 1.23.3

Reference Fields

Use the reference column type to connect different records with each other. These fields retrieve values from the referenced table, for example, the Timezone field references the Timezone (sys_timezone) table.

In SimpleOne, the field is accompanied by a magnifier icon that opens a record picker window, and with the plus icon that opens a window for creating a new record in the referenced table.

You can create records via the reference filed on the forms.

To create a missing record in the referenced table, perform the following steps:

  1. Click the plus icon .
  2. In the modal window, fill in the fields.
  3. Click Save.
caution

The new record will be created in the table which the field references. That is, if you created a custom field called Incident which references the Task table, the record will be created in the Task table, not Incident.

In lists, use inline editing to edit values.

Use the following column types to create a reference:

  • Reference
  • List
caution

Keep in mind the dot-walking limitations for fields of the List type:

  • Do not use dot-walking with a chain in more than one field to build conditions in the condition builder.
  • Do not use it for list and form views configuration.
ReferenceList
DescriptionReferences a record in the specified table. It stores the record ID. References several records in the specified table. It stores the record IDs.
Scalar typeint8text
Validation criteriaThe values specified should not exceed this range: [-9223372036854775808 ... 9223372036854775807].All elements must be separated by a comma without spaces.
note

Sometimes, reference columns display record IDs instead of record values. Values cannot be shown because of the loops.

For example, a reference column refers to a table with a Display by ref column. This column can also be a referenced one. At some point, the next column may refer to the table that has already been viewed – it means a loop is formed, and it is impossible to identify the displayed value. The displayed ID is the way to navigate to the necessary record.

Create a reference column​

When creating a Reference column, in addition to the mandatory fields, you need to fill in the following fields on the form:

FieldMandatoryDescription
ReferenceYSpecify a table with the values you need.
On deleteNDefine actions to be taken in case the referenced record is deleted. Available options:
  • No action – no actions will be taken.
  • Set NULL – the reference to this record will be deleted from the field (the value will be equal to null).
  • Cascade – the record containing reference to it will be deleted as well.
  • Restrict – deleting the referenced record is forbidden.
  • Set default value– the reference to it will be changed to the default value defined on the Default Value tab.
The field appears when the Reference option is selected in the Attribute type field.
Ensure that you set the default value on the Default Value tab if the Set default value option is selected. In case the default value is not set, and the referenced record is deleted, the reference to this record will be deleted from the field (the value will be equal to null).
If the Set NULL option is selected

When deleting a record A, an update error of a record B can occur if the record has a column that references the record A and the Action on delete for this column is Set NULL.

Unable to update the {link} record because it references to the deleted one

Reference qualifier​

With the reference qualifier you to create filters to select the data on certain conditions and return it to a reference field.

Reference qualifiers can consist of:

  • a reference to the table.
  • a reference qualifier type.
  • a reference condition created through the condition builder.
  • dynamic filter options.

You can create the reference qualifier for the table and its child tables by defining a reference qualifier when creating a column.

You can also modify the reference qualifier on the extended tables through the Column Override. Only one reference qualifier per field, form, or table can be defined.

There are two methods of using a reference qualifier:

  • Simple
  • Dynamic

Simple reference qualifier​

In the Simple Reference Qualifier, AND/OR statements are used to configure filters. Use them for simple filter conditions. For example, to filter only active users in the Users table, AND/OR only companies from the Pacific timezone. See the Condition Builder article to learn more about the filters.

To create a simple reference qualifier, complete the steps below:

  1. Navigate to System Settings → All Columns.
  2. Find the column you need to create a reference qualifier for, using a search box at the top of the list.
  3. Select the Reference Qualifier tab.
  4. Select the Simple option from the choice list.
  5. Specify a condition for this reference qualifier using the condition builder. You can create complex AND and OR filters, containing multiple ANDs and ORs in one condition.
  6. (optional) Select the Reference qualifier fixed checkbox to fix the filters in the breadcrumbs when using this field. This functionality will block using the condition builder functionality.

Dynamic reference qualifier​

To create a dynamic reference qualifier, complete the steps below:

  1. Navigate to System Settings → All Columns.
  2. Find the column you need to create a reference qualifier for, using a search box at the top of the list.
  3. Select the Reference Qualifier tab.
  4. Select the Dynamic option from the choice list.
  5. Specify a dynamic filter to use in this reference qualifier in the Dynamic reference qualifier field.
  6. (optional) Select the Reference qualifier fixed checkbox to fix filters in the breadcrumbs when using this field. This functionality will block using the condition builder functionality.

Use case​

A table contains the Assigned to field that references the User (user) table. By default, when you start typing something in this field, all values of the table appear in the autosuggest and any of them can be selected. It happens because the reference qualifier is not set, and it does not limit the reference lookup. You can select any user, from any department, in any state, even an inactive user (for example, the retired user). To prevent such issues, use the reference qualifier as described below or in the way your task requires it.

You need to limit the list of users who can be assigned to the tasks (by specifying their name in the Assigned to field). For example, only a Customer Support member can be assigned to the tasks.

To configure a reference qualifier, complete the steps below:

  1. Open the form that contains the field you need to configure.
  2. Right-click the Assigned to field title and select the Configure field item from the context menu.
  3. In the Column configuration form that appears, select the Reference Qualifier tab.
  4. Start configuring your qualifier:
    • Reference qualifier type - Simple
    • Reference qualifier condition - [Department] [is] [Customer Support]
  5. Click Save or Save and exit to apply the changes.

To verify the settings work correctly, enter the username for a user who is not a Customer Support staff member. The autosuggest should not provide any options.

Good to know​

  1. Ensure that all users have access to the field on which you are adding a qualifier. Otherwise, the qualifier will hide records for users who do not have access to the specified field due to ACL Rules.
  2. The Reference Qualifier tab on the column creation form is available only for the fields of the Reference or List types. For the fields of other types, use the condition builder options.
  3. The condition length for these fields is limited, so it is more useful to write the condition strings in the following format: field_name=value instead of searching the record sys_id within the ID's array.