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

Andrew Pane August 10, 2016

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]

1 answer

1 accepted

1 vote
Answer accepted
Alex Medved _ConfiForms_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 15, 2016

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 August 30, 2016

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 September 2, 2016

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 September 6, 2016

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 September 12, 2016

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!

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events