CiviCRM Community Forums (archive)

no support for hierarchical custom fields as yet. u'll have to do this via hooks

there has been some demand for this, so coming up with a generic custom field(s) that does this would be awesome

lobo

You might be able to put together an interim solution strictly with a customized template. I'm thinking a combination of jQuery magic to dynamically build the select options for custom field 2 based on custom field 1 selection, combined with using crmAPI in the template to set default filtered set of options when editing existing record.

http://civicrm.org/node/599

Maybe using the grouping column in civicrm_option_value table for to link the child (custom 2) rows to the parent row...

CAVEAT: More of a brainstorm - might not be do-able once u try and put the pieces together. 

You know, the more I look into this, the more I'm realizing single-column Custom Fields, even ones that work with hierarchies, might not be cutting it for us.  Our need for custom fields is really building a table; an entity that has a key plus other descriptive fields for that key, just as any normal transactional db table would have.

It seems like what we really need are Custom Schemas and Custom Tables.  I'm sure the concept makes sense ... here are some ideas (sorry if they are all over the place, just thinking out loud)

A Custom Schema would have one or more Custom Tables, with some characteristics:

• Every table has an id field - auto increment, pk
• Table names have rules, but in general, are user driven
• Each Table has one or more Custom Fields similar to the current Custom Fields (same rules, etc)
• Each Table has a Unique Key and users pick the Fields belonging to that Key

A Custom Schema would also have Relationships between Custom Tables.  This sets up our joins and FKs.

Users would then be able to include Custom Fields in profiles by picking the Custom Table first, then the Field from that table.  Maybe the form logic would create a "View" of all the tables, pre-joined so the user picks fields from that view.

Custom Searches and Reports would be able to pull in these Schemas and allows report builders to choose Fields from different Tables (and either manually or automatically include Relationships as JOINs in the query).

Of course, we'd need an Admin interface to manage the data in the Tables once the Schema is built - to start, maybe something simple like a hook into phpMyAdmin that just provides the tables and allows users to edit the data from there?

It seems to me like a bunch of the logic needed for this might already be in place as schema creation exists today with custom fields (MySQL tables are created based on those groups and fields).  I wonder how much incremental work it would be to expand on that and make Custom Tables instead.

Is this something that might interest others or is it something specific to our team?  How have others handled this type of thing so far?

For now, I'm actually thinking of providing my Admin users (or maybe a new set of "super admins") a phpMyAdmin login that has permissions to a new database which has our custom tables and then link/mimic those tables to current Custom Fields in some way (have the Custom Field Value be the id of the table so they join) - I'd have to then build DB triggers to populate/modify those linked Custom Fields with whatever has changed on the back-end, but I can't think of a better work-around for what we need.  I'd love to get some opinions on this.

Thanks!