4

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.

1
  • Disclaimer: Very draft extension and probably horrible code: 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.
    – bgm
    Nov 16, 2020 at 18:56

1 Answer 1

4

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".

1
  • 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.
    – Aidan
    Nov 17, 2020 at 20:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.