Column limits settings

In the "Limits" option in the Column settings, the template administrator can set their own limits on the values entered into the column. For example, if you need only numbers greater than or equal to 0 to be entered in the "Age" column with the "Integer" data type, then you should create a limit in this column: "MORE OR EQUAL" 0.

Data types and available limits

The limit options available depend on the data type of the column.

For the "Text" data type, the available limit options are: "ONE OF LIST", "NONE OF LIST", "EQUAL", "EMPTY VALUE", "REGULAR EXPRESSION".

For the "Time" data type, the available limit options are: "FROM–TO", "ONE OF LIST", "NONE OF LIST".

For the "Number", "Date", "Date and time" data types, the available limit options are: "MORE", "MORE OR EQUAL", "LESS", "LESS OR EQUAL", "FROM–TO", "ONE OF LIST", "NONE OF LIST".

For the "Integer" data type, the available limit options are: "MORE", "MORE OR EQUAL", "LESS", "LESS OR EQUAL", "EQUAL", "FROM–TO", "ONE OF LIST", "NONE OF LIST".

Limits

After the "MORE", "MORE OR EQUAL", "LESS", "LESS OR EQUAL", "EQUAL" limits, you need to enter a text, number, date or time (depending on the column's data type). The values entered in the column should be greater than/less than/equal to this number or date:

• "MORE" - column values must be greater than this value; if the value is less than or equal to this, an error will occur.
• "MORE OR EQUAL" - column values must be greater than or equal to this value; if the value is less than this, an error will occur.
• "LESS" - column values must be less than this value; if the value is greater than or equal to this, an error will occur.
• "LESS OR EQUAL" - column values must be less than or equal to this value; if the value is greater than this, an error will occur.
• "EQUAL" - column values must be equal to this value. This limit can also apply to text columns, in which case the value in the column must be exactly the same as the specified value.

With the "FROM-TO" limit, you need to enter two values that will define the range of acceptable values. The "TO" value can't be less than the "FROM" value. Both the start and end values of the interval will be available for input.

Here's an example of how the "FROM - TO" limit works:

You have a column with a Date data type, if you create:

• Variant 1:
  • "FROM – TO" - from 2020-01-01 to 2020-12-31

This means you can only enter dates from 2020 in this column, including the dates 2020-01-01 and 2020-12-31.

For the "ONE OF LIST", "NONE OF LIST" limits, you need to enter one or more values (a list of values). The input column values must exactly match (for "ONE OF LIST") or not match (for "NONE OF LIST") any of the values from the list.

Here's an example of how the "ONE OF LIST" limit works:

You have a column with a Text data type, if you create:

• Variant 1:
  • "ONE OF LIST": ["kilograms", "pieces", "liters"]

Then you can enter only one of these three values in this column.

Values for the "ONE OF LIST", "NONE OF LIST" can be added to the list individually or by separating them with a semicolon (";"). For example, if you enter "kilograms; pieces; liters" and then click add, three values will be added at once: "kilograms", "pieces", "liters". Also, any spaces at the beginning or end of a word/phrase will be automatically removed when they're added to the list of values.

If the limit is "EMPTY VALUE", no values can be entered into the cell. It is most often used when a column has several variants, in one of which the value must be left empty.

The "REGULAR EXPRESSION" limit allows you to enter values that match the specified regular expression.

For example, if you enter the regular expression ^[\w-\.]+@([\w-]+\.)+[\w-]{2,}$ only email can be entered in the column. You can find many variants of ready-made regular expressions and test them here.

Variant

Each limit must be inside its own Variant. That's why setting up limits begins with adding a new Variant. A Variant is a group of limits that must be met simultaneously for a column value. For example, if you've added two limits within the same Variant for the "Age" column:

• Variant 1:
  • "MORE OR EQUAL" - 0
  • "LESS OR EQUAL" - 18

Then the age that can be entered in this column must range from 0 to 18.

Different Variants operate with each other according to the "OR" principle (this does not apply to "Excluded variant", which is described below). This means that the input values must match at least one of the Variants. For example, if you have created two Variants for the same "Age" column:

• Variant 1:
  • "MORE OR EQUAL" - 0
  • "LESS OR EQUAL" - 18
• Variant 2:
  • "MORE" - 60

Then, the ages that can be entered in the "Age" column range from 0 to 18 OR are more than 60.

Be careful when creating limits to ensure that they don't conflict with each other. For example:

If all the limits from the Variants above were within only one Variant, like so:

• Variant 1:
  • "MORE OR EQUAL" - 0
  • "LESS OR EQUAL" - 18
  • "MORE" - 60

Then you would be unable to enter any value in this column, since the age must simultaneously be from 0 to 18 and more than 60, which is impossible.

Variants with "IF" limits

You can set limits for each Variant to dictate whether it will operate or not, depending on the value in other columns. To do this, you need at least two columns. Then, you can append these limits to "IF" at the bottom of the Variant (by pressing the "+" button near "IF").

A Variant with "IF" limits will only operate if other column values (added to "IF" limits) simultaneously match all the "IF" limits.

The limits after "IF" operate the same as in a Variant. When you choose a column, the "IF" limit will also depend on the data type of the chosen column.

If you have created one or more Variants with "IF" limits, and none of them match the input values, then none of the Variants will work. This means users can enter values in the column as if it had no limits. However, if at least one set of the "IF" limits within a Variant is valid, the column value must correspond to this Variant, or an error will occur.

Example:

Let's consider two columns: "Product Name" (Text data type) and "Units" (Text data type). We will set the limits for the "Units":

• Variant 1:
  • “ONE OF LIST”: [“liters”, “gallons”]
  • IF:
    • "Product Name":
      • “ONE OF LIST”: [“water”, “milk”]
• Variant 2:
  • “ONE OF LIST”: [“kilograms”, “pounds”]
  • IF:
    • "Product Name":
      • “ONE OF LIST”: [“sugar”, “rice”]

Now, if the "Product Name" column has values "water" or "milk", then Variant 1 will operate in the "Units" column and the user can only enter "liters" or "gallons" values.

However, if the user enters any value other than "water", "milk", "sugar", or "rice" in the "Product Name" column, then no "IF" limits on the "Units" column will be applied, and the Variants will not operate. As a result, the user can enter any value. If you want one of these Variants to always operate, you can resolve this by adding a Variant with a limit "ONE OF LIST": ["water", "milk", "sugar", "rice"] to the "Product Name" column and making it a required field. Then, in all cases, the user would need to enter values in the "Product Name" column for which one of the Variants in the "Units" column will operate.

You need to avoid contradictions in the "IF" limits just as you would when creating a Variant, as they can cause the "IF" limits to never be met, and as a result, the Variant with these "IF" limits will never operate.

For example, such a contradiction in the "IF" limits for the "Units" column:

• Variant 1:
  • “ONE OF LIST”: [“liters”, “gallons”]
  • IF:
    • "Product Name":
      • “ONE OF LIST”: [“water”, “milk”]
      • “ONE OF LIST": [“sugar”, “rice”]

These "IF" limits will never be met, and the Variant will never operate, since the value in the "Product Name" column must be both "water" or "milk" and "sugar" or "rice".

Excluded variant

At the top, before all the variants you can see Excluded variant. This variant is active if all of the following variants are not active at the same time. If no other variants were even set, it will always be active.

This variant is mostly used in combination with variants that have IF limits, as without them the other variant will always be active. Also, you cannot add IF limits to Excluded variant, because the conditions are already there if the other variants did not work.

Let's see how we can use Excluded variant to improve on the example we had earlier:

Two columns: "Product Name" (Text data type) and "Units" (Text data type). We will set the limits for the "Units":

• Excluded variant:
  • “ONE OF LIST”: [“liters”, “gallons”, “kilograms”, “pounds”]
• Variant 1:
  • “ONE OF LIST”: [“liters”, “gallons”]
  • IF:
    • "Product Name":
      • “ONE OF LIST”: [“water”, “milk”]
• Variant 2:
  • “ONE OF LIST”: [“kilograms”, “pounds”]
  • IF:
    • "Product Name":
      • “ONE OF LIST”: [“sugar”, “rice”]

If the "Product Name" column has a value other than [“water”, “milk”, “sugar”, “rice”], then Variant 1 and Variant 2 will be inactive, but Excluded variant will start working. As a result, only one of the list values can be entered in the Units column [“liters”, “gallons”, “kilograms”, “pounds”].

As a result, this variant helps us to handle values that we have not considered.

Deleting Columns with "IF" Limits

If you want to delete a column that another column's limits are dependent on, you must first remove that dependency. If you try to delete such a column, you will receive a notification, and the dependent column will be marked with the corresponding icon.

Apply and save changes

After editing column limits, you need to apply the changes by pressing the "Apply changes" button; otherwise, all new limits will be lost. Don't forget to save your changes on the Column settings page as well. If you don't, all changes will be lost.