Answers.Body: <p>Yes, <a href="https://github.com/MegaphoneJon/fieldlookup" rel="nofollow noreferrer">I have such an extension</a> - and <a href="https://chat.civicrm.org/civicrm/pl/cay1rwboo7fqbnjehw7gqopkqo" rel="nofollow noreferrer">so do many others</a>!  I think mine is the most functionally advanced, but lacks a UI, or, uh, documentation (oops), and so is unsuitable in most use cases.</p>
  <p>If you're interested, I can ask one of my staff members to write some documentation on this today, but broadly:</p>
  <ul>
  <li><p>This creates two new entities (with full APIs): <code>FieldLookupGroup</code> and <code>FieldLookup</code>.</p>
  </li>
  <li><p>There is one <code>FieldLookupGroup</code> for every &quot;lookup&quot; set.  <code>FieldLookup</code> contains the actual corresponding values.</p>
  </li>
  <li><p>Here are some SQL tables of <code>FieldLookupGroup</code> from two different clients:</p>
  </li>
  </ul>
  <pre><code>+----+----------------+--------------------+----------------+--------------+-------------+-----------------+
  | 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     | =               |
  +----+----------------+--------------------+----------------+--------------+-------------+-----------------+
  </code></pre>
  <pre><code>+----+----------------+--------------+----------------+--------------+--------------+-----------------+
  | 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 | =               |
  +----+----------------+--------------+----------------+--------------+--------------+-----------------+
  </code></pre>
  <p>The first set handles a &quot;reverse&quot; hierarchical lookup - e.g. &quot;given a county, simply fill in the state and country on save&quot;.  The second set is a &quot;chain-select&quot; and more closely matches what you're looking for.</p>
  <p>Here's is a small sample of values from <code>FieldLookup</code> for the second client:</p>
  <pre><code>+----+-----------------------+---------------+-----------------+---------------+
  | 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            |
  +----+-----------------------+---------------+-----------------+---------------+
  </code></pre>
  <p>Taken together with the table above, this reads: &quot;If a user selects a value of <code>4</code> in <code>custom_50</code>, then <code>custom_52</code> should be limited to the options with values <code>3</code>, <code>19</code>, or <code>20</code>&quot;.</p>
  <p><code>field_1_value_2</code> is if you use the <code>BETWEEN</code> operator - e.g. &quot;if the <code>start_date</code> is between <code>2020-01-01</code> and 2020-12-31`, limit the value of the second field to the following options&quot;.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-11-17T20:18:53.463Z
Answers.Text: Thanks Jon - that sounds good and seems to be something that is a recurring need. If yours is the most functionally advanced then adding a bit of documentation and getting it linked on c.o/extensions or lab.c.o/extensions might help consolidate effort into adding a UI.
Answers.UserDisplayName: Aidan
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-11-17T17:07:45.797Z
Answers.LastActivityDate: 2020-11-17T17:07:45.797Z
Answers.UserDisplayName: Jon G - Megaphone Tech
Body: <p>Does anyone have a generic extension for hierarchical custom fields?</p>
  <p>For example, we have 'Country', 'State' and 'County' - the value of Country determines what States are valid, and the value of State determines the valid Counties.  But that is done with specific coding for those fields.  I'm interested in a generic extension that enables reproducing the Country/State/County behaviour without further coding.</p>
  <p>The tags feature allows custom hierarchies and has a convenient drag-n-drop interface for creating it ... but the end result is a hierarchy in tags rather than fields.</p>
  <p>The focus here is creating hierarchies of data attributes, not an organisational hierarchy of contacts.</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-11-16T18:56:10.183Z
Comments.Text: Disclaimer: Very draft extension and probably horrible code: https://lab.civicrm.org/extensions/fieldconditions/-/tree/master - it was coded for a client-specific project, for conditions between 3 custom fields, but I never got around to finishing a more public version.
Comments.UserDisplayName: bgm
Tags: <custom-fields><tags>
Title: Extension for hierarchical custom fields?
ThreadText: # Extension for hierarchical custom fields?
  
  (Asked by: username_0 on 2020-11-16)
  
  Does anyone have a generic extension for hierarchical custom fields?
  
  For example, we have 'Country', 'State' and 'County' - the value of Country determines what States are valid, and the value of State determines the valid Counties. But that is done with specific coding for those fields. I'm interested in a generic extension that enables reproducing the Country/State/County behaviour without further coding.
  
  The tags feature allows custom hierarchies and has a convenient drag-n-drop interface for creating it ... but the end result is a hierarchy in tags rather than fields.
  
  The focus here is creating hierarchies of data attributes, not an organisational hierarchy of contacts.
  
  **Tags:** custom-fields, tags
  
  > **Comments:**
  > **username_1**: Disclaimer: Very draft extension and probably horrible code: https://lab.civicrm.org/extensions/fieldconditions/-/tree/master - it was coded for a client-specific project, for conditions between 3 custom fields, but I never got around to finishing a more public version.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2020-11-17. Score: 4
  
  Yes, [I have such an extension](https://github.com/MegaphoneJon/fieldlookup) - and [so do many others](https://chat.civicrm.org/civicrm/pl/cay1rwboo7fqbnjehw7gqopkqo)! 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".
  
  > **Comments:**
  > **username_0**: Thanks Jon - that sounds good and seems to be something that is a recurring need. If yours is the most functionally advanced then adding a bit of documentation and getting it linked on c.o/extensions or lab.c.o/extensions might help consolidate effort into adding a UI.  
  
  ---
  
  