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

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

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

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.

 

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

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

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
Community showcase
Published Nov 29, 2018 in Marketplace Apps

How to set up an incident workflow from the VP of Engineering at Sentry

Hey Atlassian community, I help lead engineering at Sentry, an open-source error-tracking and monitoring tool that integrates with Jira. We started using Jira Software Cloud internally last year, a...

1,128 views 0 8
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you