Can I make ConfiForms database dropdown values dependent the value in another database dropdown?

[Andrew Pane]

Is there a way to supply the value selected in one database dropdown field to a second database dropdown field, so that we can update the value of the latter based on what is selected in the former?

For example, I have a category table and a subcategory table, linked via many-to-one foreign key relationship. The category table has a dozen entries, each with about 20 corresponding entries in the subcategory table. Rather than have the user just be exposed to all 240 subcategories in the Subcategory dropdown, I would like them to only see the 20 subcategories relevant to the currently selected option in the Category dropdown.

I tried populating the "SQL Query" field as below to reference another fields id, but it doesn't appear like this record gets resolved at runtime:

SELECT
	id, name
FROM
	subcategories
WHERE
	subcategories.category_id = [entry.CategoryDropdown.id]

[Alex Medved _ConfiForms_ · Accepted solution]

Hi @Andrew Pane

You will need to have your query to select all the options in ConfiForms Field Definition macro, and then have a ConfiForms Filed Definition Rule macro with action set to "Apply Filter on field" where you can specify a filter to reduce the options list

This rule should be set to fire on changes happening with your "category" field... 

So it will work like this:

when "category" changes a "subcategory" will get a filter applied based on the value selected in the "category" field

[Andrew Pane]

Thanks @Sash [ConfiForms]. This answer makes sense but I can't figure out how to get it working in practice:

1. It requires that there be some part of the category ID or LABEL be embedded in the subcategory ID or LABEL in order to do the filtering. So the only way I can figure out to do this is to embed the category label in the sub-category label: "Category 1: Subcategory A", "Category 1: Subcategory B", etc. Is there a more nuanced way to do this that I'm not thinking of?
   
   
2. Even given the above, I can't figure out the syntax in the filter to get the category label. If I hardcode the filter to be "Subcategory.label:Category 1*" then it indeed filters the subcategory list to all the subcategories that start with "Category 1". However, I can't figure out how to dynamically determine the category label, and none of these attempts have worked: "Subcategory.label:Category.label*" , "Subcategory.label:[Category.label]*" , etc.

[Andrew Pane]

I managed to figure out #2 with the value "{{[entry.Category.label]*}}".

Still, this is a clunky, ugly solution, and unless I can figure out a better way to address #1 it appears that ConfiForms is ill-suited to address this situation...

[Andrew Pane]

This page claims to solve #1...

https://wiki.vertuna.com/display/CONFIFORMS/Using+ConfiForms+Field+Definition+Rules+macro+to+reduce+number+of+choices+in+a+dropdown+field+based+on+the+value+selected+in+another+field

...but the example given does not work for me: clicking the "only active" checkbox results in ALL the options being filtered away.

I'm guessing this is something to do with the fact that I'm using MySQL which uses TINYINT for Boolean values, but I can't find a modification to the filter that works.

I'm not aware of any logging done by ConfiForms, so in the face of a "fail silent" plugin I don't know how to figure out what is wrong...

[Andrew Pane]

Alex at Vertuna has helped me out and kindly added a wiki page to their docs that showed me how to get this working:

https://wiki.vertuna.com/display/CONFIFORMS/Working+with+Dependent+DB+dropdowns

Thanks Alex!