Hierarchical Custom Fields - Input, Display and Search

Specifications TBD.

Check Implementation details BELOW

Check out these implementations for possible use:

• http://www.givainc.com/labs/mcdropdown_jquery_plugin.htm

• I like the user experience on this one - need to think about how options / hierarchy is defined by admin. Might want to consider a simple input mechanism like we've used in premiums (a text box w/ list structure, or even a wysiwyg w/ nested list building support)

http://wimleers.com/demo/hierarchical-select

==========================
Example use case:
custom field: what colour is your shirt?
blue
--dark blue
----navy
----prussian blue
----ultramarine
--light blue
----cyan
----sky blue
red
--dark red
----burgundy
etc.

We have
John: dark blue
Jane: navy
Jean: cyan
Joan: blue
(all the rest being some shade of red)

a search for blue returns the four contacts that are marked respectively 'blue', 'dark blue', 'navy' and 'cyan'
a search for dark blue returns the two contacts marked 'dark blue' and 'navy'
a search for cyan returns only Jean.

IMPLEMENTATION DETAILS:

• We will implement this as a set of custom fields (CF) of type select. The interface will just present this as one custom field for view/edit. Add a new html type "Hierarchical Select". Add a new column called "parent_id". Level 1 will have a null parent_id, all the others will have a parent_id of the custom field at the previous level.
• Each of the custom fields will be associated with an option group. You can either create or reuse a new option group. A new ajax interface will be implemented to "create new option group". This same interface will be used for the existing select / checkbox / radio custom fields.
• We will support an n level hierarchy
• Each level will have its own column in the custom value table (due to the step 1 plan)
• A new table will be created to store the mapping of:

Level 1 value -> set of values in Level 2
Level 1 value, Level 2 value -> set of values in Level 3
Level 1 value, Level 2 value ... Level N - 1 value -> set of values in Level N

This table will need at least the following columns

• CF id of the level 1 field
• CF id of the level 'X' field
• option value of the level X
• option value(s) of the next 'level' separated by CRM_Core_DAO::VALUE_SEPARATOR

• The configuration screen for this field will be "ajaxy". The admin will first specify the level and the option groups for each level (and create the option groups if needed)
• The next step will be mapping each level to the next level. We can potentially integrate this with the first step and play with it as we build the interface

Query:

To get the list of elements to display for level Y, we need the

• CF id of root element
• CF id of level X element (predecessor of Y)
• option value 'value' of X

with the above info we can query the above table to get the list of values to display and then get the labels from the
Example:

Assume a 3 level structure:

Level 1 is the numbers: 1, 2, 3, 4, 5 .. 10
Level 2 is the alphabets: a, b, c, d ... z
Level 3 is the greek letters: alpha, beta, gamma, delta ...zeta

1 -> a, b, c
2 -> c, d, e
3 -> a, d, e

a -> alpha, beta
b -> beta, gamma
c -> alpha, beta, zeta

Restrictions:

• This set of custom fields will not be exposed to a profile
• For search and export this set of custom fields will be exposed individually