Yes, I have such an extension - and so do many others! I think mine is the most functionally advanced, but lacks a UI, or, uh, documentation (oops), and so is unsuitable in most use cases.
If you're interested, I can ask one of my staff members to write some documentation on this today, but broadly:
This creates two new entities (with full APIs): FieldLookupGroup
and FieldLookup
.
There is one FieldLookupGroup
for every "lookup" set. FieldLookup
contains the actual corresponding values.
Here are some SQL tables of FieldLookupGroup
from two different clients:
+----+----------------+--------------------+----------------+--------------+-------------+-----------------+
| id | field_1_entity | field_1_name | field_2_entity | field_2_name | lookup_type | lookup_operator |
+----+----------------+--------------------+----------------+--------------+-------------+-----------------+
| 1 | Event | start_date | Event | custom_302 | reverse | BETWEEN |
| 2 | Event | start_date | Event | custom_303 | reverse | BETWEEN |
| 3 | Activity | activity_date_time | Activity | custom_305 | reverse | BETWEEN |
| 4 | Activity | activity_date_time | Activity | custom_306 | reverse | BETWEEN |
| 5 | Participant | custom_265 | Participant | custom_307 | reverse | = |
| 6 | Participant | custom_265 | Participant | custom_308 | reverse | = |
+----+----------------+--------------------+----------------+--------------+-------------+-----------------+
+----+----------------+--------------+----------------+--------------+--------------+-----------------+
| id | field_1_entity | field_1_name | field_2_entity | field_2_name | lookup_type | lookup_operator |
+----+----------------+--------------+----------------+--------------+--------------+-----------------+
| 1 | Contact | custom_50 | Contact | custom_52 | chain-select | = |
| 2 | Contact | custom_54 | Contact | custom_56 | chain-select | = |
| 3 | Contact | custom_58 | Contact | custom_60 | chain-select | = |
| 4 | Contact | custom_62 | Contact | custom_64 | chain-select | = |
| 5 | Contact | custom_63 | Contact | custom_62 | chain-select | = |
+----+----------------+--------------+----------------+--------------+--------------+-----------------+
The first set handles a "reverse" hierarchical lookup - e.g. "given a county, simply fill in the state and country on save". The second set is a "chain-select" and more closely matches what you're looking for.
Here's is a small sample of values from FieldLookup
for the second client:
+----+-----------------------+---------------+-----------------+---------------+
| id | field_lookup_group_id | field_1_value | field_1_value_2 | field_2_value |
+----+-----------------------+---------------+-----------------+---------------+
| 1 | 1 | 4 | NULL | 3 |
| 2 | 1 | 4 | NULL | 19 |
| 3 | 1 | 4 | NULL | 20 |
+----+-----------------------+---------------+-----------------+---------------+
Taken together with the table above, this reads: "If a user selects a value of 4
in custom_50
, then custom_52
should be limited to the options with values 3
, 19
, or 20
".
field_1_value_2
is if you use the BETWEEN
operator - e.g. "if the start_date
is between 2020-01-01
and 2020-12-31`, limit the value of the second field to the following options".