Technique to pass in updated values of custom field to another dependent custom field

[Jira DummyUser1]

Currently I am working on a project where I migrate an interface from legacy platform onto JIRA. The target design involves creating form fields who's contents are dependent on the selection of the other parent fields.

An initial list of single select options are retrieved using an SQL query in DBPicker. There is also secondary custom field who's single select options are retrieved using a parameterized SQL query in DBPicker. My question is, how can I pass the live value of the initial custom field into the DBPicker script environment so that it may access the latest selected value of that field? I would like for the SQL statement to be dynamically updated each time a different selection in the first custom field is made.

[Ram Kumar Aravindakshan _Adaptavist_]

Hi @Jira DummyUser1

Could you please share screenshots of your DB Pickers configurations? You don't have to put the actual data, some dummy example data would be enough.

I am requesting this to better understand your question and see if I can provide a solution.

From what I understand you want to pass the value selected from the first DB  picker to the second DB picker is that correct?

Thank you and Kind regards,

Ram

[Jira DummyUser1]

Hi @Ram Kumar Aravindakshan _Adaptavist_ ,

Let me send you a simplified example scenario of what we are attempting to do:

Lets say we have two example tables in the DB; Table 1 and Table 2:

We would like to have a two custom field single select style drop down lists with search ability. They will be of type Single Database Values Picker. The Custom fields will be named Field_A and Field_B for this example. 

Field_A is for selecting the initial Type selection from Table 1 and will be independent.

For Field_A we use a simple SQL query to retrieve the values that can be selected:

SELECT Type FROM Table1

When an option in Field_A is selected or changed by the user during creation of the Issue, the options available for selection in Field_B should be dynamically updated to display only the SubType(s) in Table 2 whose ParentTypeDBID matches the DBID of selection made in Field_A.

For Field_B, we are currently stuck in points:

1. We are not sure how to retrieve the String value of selected option in Field_A and form a parameterized SQL statement.

2. We are not sure how to set the configuration Field_B to re-execute the newly updated SQL to fetch new list of values when Field_A is changed

This is currently the search and validation SQL configuration we are using for Field_B which does not work as it will return all the entries in Table2 as options.

We would greatly appreciate any example solution if possible for this scenario

Thank you

[Ram Kumar Aravindakshan _Adaptavist_]

Hi @Jira DummyUser1

From the screenshot that you have shared, it appears the approach is not correct.

You have set the same SQL for both the Retrieval/validation SQL and the Search SQL. This will only return the dbid value in the list.

If you intend for the DB Picker to display the SUBTYPE value, you need to modify the Search SQL to:

select dbid, SUBTYPE from Table2 
  where SUBTYPE = ? || '%'

as shown in the screenshot below:-

I hope the helps to solve your question. :-)

Thank you and Kind regards,

Ram

[Jira DummyUser1]

Hi @Ram Kumar Aravindakshan _Adaptavist_ ,

Thanks for the reply I will implemented this and send an update soon.

[Jira DummyUser1]

Hi @Ram Kumar Aravindakshan _Adaptavist_

Thank you for the reply. I attempted the implementation suggested but this solution seems to displays all values present in Table 2 without filtering.

We are looking to live filter the selectable options such that it only displays the results where the associated ParentTypeDBID is the same as the DBID selected in a previous custom field

Is there a way I can use the script to retrieve the value selected in another custom field and reference it the SQL?