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 a plus icon that opens a window for creating a new record in the referenced table.

You can create records via a reference filed on 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.

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

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
Description

References 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 criteria

The values specified should not exceed this range: [9223372036854775808 ... 9223372036854775807].

All elements must be separated by a comma without spaces.

Sometimes, reference columns display record IDs instead of record values. Values cannot be shown because of 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 a 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


Role required: admin.

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

Type Specification tab

FieldMandatoryDescription
ReferenceYDefine the table which the field will reference.
On deleteN

Select the action to take when 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).

    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.

    Error
    Unable to update the {link} record because it references to the deleted one
  • Cascade – the record containing reference to it will be deleted as well.
  • Restrict – deletion of 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.

Reference Qualifier tab

Reference qualifier typeN

Select the type of the reference qualifier. Available options:

  • Simple
  • Dynamic
Reference qualifier conditionN

Configure filters using the condition builder.

This field appears when the Reference qualifier type value is Simple.

Dynamic reference qualifierN

Select the dynamic reference qualifier from the list.

This field appears when the Reference qualifier type value is Dynamic.

Reference qualifier fixedN

Select the checkbox to fix filters in breadcrumbs. This functionality will block using the condition builder.

Default Value tab

Default value

N

Specify a default value that will be applied if the Set default value option is selected in the On delete field.

Reference qualifier

A Reference Qualifier allows you to create filters to select the data on certain conditions and return it to a reference field.

Reference Qualifiers can consist of:

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

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

You can also modify the reference qualifier on 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 filters.

Role required: admin.

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 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 want to create a reference qualifier for, using a search string on 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 in the table 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 chosen. 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 like described below or in the way your task requires.

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

To configure a reference qualifier in this way, 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:
    1. Reference qualifier type - Simple
    2. Reference qualifier condition - [Department]  [is]  [Customer Support]
  5. Click Save or Save and exit to apply the changes.

After that, type 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 fields of Reference or List types. For fields of other types, use the condition builder options.
  3. The condition length for these fields is limited, so it makes sense to use condition strings looking like (field_name=value) instead of searching the record sys_id within the ID's array.

  • No labels

© 2019-2024, SimpleOne

https://simpleone.ru