Lookup validation rule – values from the records of another entity¶
A lookup validation rule allows only values that exist in the records of another entity, offers these records for selection and can copy details of the selected record into the current one. This page describes its settings and behaviour. It is intended for administrators who configure business rules.
1. At a glance¶
| Purpose | Accepts only values that match a record of a lookup entity; offers the matching records for selection; optionally fills further fields from the selected record |
| Typical use | Postcode → city, cost centre → manager, product code → price, customer → contact person |
| Related | Population rules, Sorting rules, List validation rule, Queries |
Terms used on this page:
- Validated field – the field of the current record that the rule restricts.
- Lookup entity – the entity whose records provide the allowed values.
- Lookup field – the field of the lookup entity that the value is matched against and that is shown as the text of an option.
2. Settings¶
The editor has four tabs.
Tab "General"¶
| Setting | Meaning | Default / required |
|---|---|---|
| "Disabled" | The rule does not apply | off |
| "Field" | The validated field | required |
| "Entity" | The lookup entity. Without a query, every record of it is a valid option. | required |
| "Lookup Field" | A field of the lookup entity. The value is matched against it, and it is the text shown for each option. Also needed with "Use RecId", where it only provides the text. | required |
| "AI Prediction Description Field" | Optional field of the lookup entity that describes each option in natural language. It helps the AI to suggest a value for the field. | empty |
| "Use RecId" | The validated field stores the record id of the selected lookup record instead of the value of the lookup field. The field must be able to hold a record id. Forms still show the text of the lookup field. | off |
| "Enforce Validation" | On: a value without a matching record is rejected. Off: every value is accepted; the rule only offers suggestions and fills the populated fields when a value matches. | on |
| "Limit Behavior" | What happens to the validated field when a field of the current record changes that the query on the tab "Query" compares with: KeepValue, ClearValue, ClearIfNoLongerValid (see section 3) |
KeepValue |
| "Condition" | Optional condition. "The rule only applies while this expression evaluates to true. Without an expression it always applies." | empty – the rule always applies |
Plus "Display Name", "Description" and "Tags" in the header (see Business rules).
Tab "Query"¶
A query on the lookup entity that restricts which of its records are valid options – for example only active products, or only the cities of the country selected in the current record. The fields on the left side of a condition are fields of the lookup entity. The values on the right side are expressions evaluated against the current record; this is how the options depend on other fields of the record. Without conditions, every record of the lookup entity is valid. See Queries.
Tab "Sorting"¶
The order in which the options are offered. See Sorting rules.
Tab "Populate Values"¶
"Clear Populated Values" and the list of "Population Rules" – fields of the current record that are filled from the selected lookup record. See Population rules.
3. Behaviour¶
Checking a value¶
When the validated field is set, nexxcite searches the lookup entity for a record that
- meets the conditions of the tab "Query", and
- has the value of the validated field in the "Lookup Field" – or, with "Use RecId", has this record id.
| Result | "Enforce Validation" on | "Enforce Validation" off |
|---|---|---|
| A record is found | Value accepted, population rules run | Value accepted, population rules run |
| No record is found | Value rejected – unless another validation rule of the field accepts it | Value accepted, nothing is populated |
A rejected value is not kept: the field gets its previous value back, without a message. Empty values are always accepted. Whether the comparison distinguishes upper and lower case depends on the database and its collation.
Offering values¶
Input fields that show a selection offer the records that meet the query, sorted by the sorting rules – in forms, at most 500 options per rule. The sorting happens in the database before the number is limited, so the first options really are the first ones in the chosen order. If several list or lookup rules apply to the field, their options are listed rule after rule.
Limit Behavior¶
When a field of the current record changes that the query compares with – for example the country when the cities depend on it – the validated field reacts:
| "Limit Behavior" | Effect |
|---|---|
KeepValue |
The value stays, even if it is no longer among the options. |
ClearValue |
The value is emptied. |
ClearIfNoLongerValid |
Meant to empty the value only if it is no longer valid. In the current version, the value is always emptied, as with ClearValue. |
The condition¶
While the "Condition" is true, the rule checks and offers values. While it is false, the rule is ignored completely. A condition that does not return true or false, or whose expression no longer exists, is treated as true.
Errors¶
If the configuration is incomplete – no lookup entity, a lookup field that no longer exists, a query that cannot be found – no record is ever found. With "Enforce Validation" on, the rule then accepts nothing, and if it is the only validation rule of the field, every non-empty value is rejected. The reason is written to the application log.
4. Example¶
Postcode with city. Entity Address with the fields Country, Postcode and City. Entity Postcode with the fields Country, Code and City name.
| Setting | Value |
|---|---|
| "Field" | Postcode |
| "Entity" | Postcode |
| "Lookup Field" | Code |
| "Limit Behavior" | ClearValue |
| Tab "Query" | Country (of the postcode) equals Country (of the address) |
| Tab "Sorting" | Code, Ascending |
| Tab "Populate Values" | "Clear Populated Values" on; City ← City name |
A user selects the country "DE" and the postcode 10115: the rule finds the record and sets City
to "Berlin". The user then changes the country to "AT": the postcode is emptied, and because
"Clear Populated Values" is on, City is emptied as well.
5. Limitations¶
ClearIfNoLongerValidbehaves likeClearValue(see section 3).- Existing values are not checked again when the lookup records change – for example when a product is deactivated. They stay until the field is set again.
- Forms offer at most 500 options per rule. With a large lookup entity, narrow the options with the query and order them with sorting rules so that the relevant ones come first.
- Rejections are silent.