Database Picker Field - Scriptrunner

[Jeyanth Kumar]

Hello,

I am trying to create a DB picker field which has multiple values. Currently I need these values to restrict based on projects. Is there any way to do this ?

[Ram Kumar Aravindakshan _Adaptavist_ · Accepted solution]

Hi @Jeyanth Kumar

Welcome to the community!

In your description, you mentioned:-

I am trying to create a DB picker field which has multiple values. Currently I need these values to restrict based on projects. Is there any way to do this ?

I am going to assume that you want the DB Picker options to be filtered according to the Project it is applied in. Please clarify if this is what you are trying to achieve.

If yes, this is doable; the Best approach would be to use ScriptRunner's Behaviour.

Let me know so I can provide an example.

Thank you and Kind regards,
Ram

[Jeyanth Kumar]

Hi @Ram Kumar Aravindakshan _Adaptavist_ ,

Thanks for replying. I am aware of using scriptrunner behaviour but I need to use Database picker field and I have nearly 500 projects in Jira. So the purpose is basically, I need 500 values from DB picker field and this is again filtered in each project. So basically the DB picker should show either 1 or 2 values for each project. 

Hope my requirement is clear for you. Thanks in advance

Best regards

Jeyanth

[Jeyanth Kumar]

I have to use an SQL like this. 

select a.col5

from a.tab1, b.tab2

where a.col3=b.col5 and a.col2 = 12406 and b.col3 = {project}

order by a.col5 

Here in this SQL col3 from table b is projectkey which is based dynamically.

Hope this helps.

[Ram Kumar Aravindakshan _Adaptavist_]

Hi @Jeyanth Kumar

Please share a screenshot of your DB picker configuration.

From the query structure you have shared this will not work on the Retrival and Search SQL field.

It's more for the display value.

Thank you and Kind regards,

Ram

[Jeyanth Kumar]

Hi @Ram Kumar Aravindakshan _Adaptavist_ ,

Please find the screenshot.

This is my current configuration in DB picker field. But I am able to see all the values but when I choose the value. It says missing ID in preview. 

Best regards,

Jeyanth

[Ram Kumar Aravindakshan _Adaptavist_]

Hi @Jeyanth Kumar

Thank you for getting back and sharing the screenshots.

I can confirm the approach you are taking will not work.

Firstly, the structure of both the SQL Queries in the Retrieval / Validation and Search SQL Fields must be the same, i.e. if the Retrieval / Validation fields query starts with:-

SELECT customValue, ID from CUSTOMFIELDOPTION

the Search SQL field must also start with the same structure. Else it will not work.

If you intend to modify the displayed output, then you will need to use the Configuration Script field, i.e. Customizing the Search SQL  to manage the display of the output.

If you need more info, let me know, and I'll prepare an example.

Thank you and Kind regards,
Ram

[Jeyanth Kumar]

Hi @Ram Kumar Aravindakshan _Adaptavist_ ,

Thanks again for replying back. It would be great if you can help me writing the below query in both retrival/validation and search SQL. 

Basically, I have 2 tables and they have a similar column col3 and col5 and adding 2 conditions col2 = fixed value and col3 from table 2 = fixed value from the project

select a.col5

from a.tab1, b.tab2

where a.col3=b.col5 and a.col2 = 12406 and b.col3 = {project}

order by a.col5 

Best regards,

Jeyanth

[Jeyanth Kumar]

Hi @Ram Kumar Aravindakshan _Adaptavist_ 

Please find the exact query that needs to be used in both retrival/validation and search SQL below

select pt.value
from AO_00B950_AOPROJECT_VALUE pt, PROJECT pj
where FIELD_ID=5 and pj.ID=pt.PROJECT_ID and pj.pkey={project}
order by pt.value

Best regards

Jeyanth

[Ram Kumar Aravindakshan _Adaptavist_]

Hi @Jeyanth Kumar

In your last comment, you mentioned:-

Basically, I have 2 tables and they have a similar column col3 and col5 and adding 2 conditions col2 = fixed value and col3 from table 2 = fixed value from the project

What do you intend to display in the DB Picker?

The query you shared in your last comment will not work.

The Select statement must have 2 columns. Ideally, the first will be an ID column, and the Second will be the value you intend to display in the DB Picker field.

I am looking forward to your feedback.

Thank you and Kind regards,
Ram

[Jeyanth Kumar]

Hi @Ram Kumar Aravindakshan _Adaptavist_ ,

Let me give an example here. Lets say I have a DB picker field called "Countries". Where it as 10 countries as values. But I have 'n' of projects where I use this field. which should display specific value for specific project. For project A it should display UK, US and for project B it should display India, China. The same is applicable for every project in Jira.

So in my case I need DB picker to display restricted value based on projects.

Best regards,

Jeyanth

[Ram Kumar Aravindakshan _Adaptavist_]

Hi @Jeyanth Kumar

For your requirement, using the Configuration Script of the DB picker is the best approach.

Below sample working code taken from Adaptavist's code snippets.

import com.atlassian.jira.issue.Issue
import com.onresolve.scriptrunner.canned.jira.fields.editable.database.SqlWithParameters

def columnName = 'Country_Name'

getSearchSql = { String inputValue, Issue issue, originalValue ->

    if (issue.projectObject.name == 'Mock') {
        new SqlWithParameters("""
        select ID, COUNTRY_NAME from COUNTRIES
        where ${columnName} in (?,?,?)
        """, ['India', 'United States', 'United Kingdom'])

    } else if (issue.projectObject.name == 'Example') {
        new SqlWithParameters("""
        select ID, COUNTRY_NAME from COUNTRIES
        where ${columnName} in (?,?)
        """, ['Malaysia', 'Singapore'])
    } else if (issue.projectObject.name == 'Sample') {
        new SqlWithParameters("""
        select ID, COUNTRY_NAME from COUNTRIES
        where ${columnName} in (?,?)
        """, ['Japan', 'Australia'])
    }
}

renderViewHtml = { displayValue, row ->
    displayValue
}

Please note that the sample working code above is not 100% exact to your environment. Hence, you must make the required modifications.

Below is a screenshot of the DB Picker Scripted Field Configuration:-

Below is the sample database table that I am using for testing:-

id	country_name	capital_city
1	India	New Delhi
2	United States	Washington DC
3	United Kingdom	London
4	Malaysia	Kuala Lumpur
5	Singapore	Singapore
6	Japan	Tokyo
7	Australia	Canberra

Below are a couple of test screenshots for your reference:-

1. If the MOCK project is selected on the options India, United States and United Kingdom are available in the Countries field (DB Picker) as shown in the screenshot below:-

2. If the Example project is selected, only the options Malaysia and Singapore are available from the DB Picker as shown in the screenshot below:-

3. Finally, if the Sample project is selected on the options Japan and Australia are available in the DB Picker as shown in the screenshot below:-

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

Thank you and Kind regards,
Ram

Senior Support Engineer

[Jeyanth Kumar]

Hi @Ram Kumar Aravindakshan _Adaptavist_ ,

Thanks for sharing the script. You got my point correctly. Unfortunately I get an error with respect to permissions. Should enable anything in the shared path ?

The script could not be compiled:

org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:
\sewd-jira07\G\sharedhome\scriptrunner\classes\Script93.class (Access is denied)

\sewd-jira07\G\sharedhome\scriptrunner\classes\Script93$_run_closure1.class (Access is denied)

2 errors

Best regards

Jeyanth

[Ram Kumar Aravindakshan _Adaptavist_]

Hi @Jeyanth Kumar

Are you on a DC single instance or a clustered environment with Multiple nodes?

If it's the former, you don't need to use the shared path. Otherwise, you must ensure that those folders have full read-write permission. 

You can refer to this community post for more information.

Also, has the error occurred previously?

I am looking forward to your feedback and clarification.

Thank you and Kind regards,
Ram

[Jeyanth Kumar]

Hi @Ram Kumar Aravindakshan _Adaptavist_ ,

I am on DC with multiple nodes. The error didn't occur previously. This has happened after adding the configuration script.

Best regards

Jeyanth

[Ram Kumar Aravindakshan _Adaptavist_]

Hi @Jeyanth Kumar

Please share a screenshot of your configuration so I can review it and provide some feedback.

Thank you and Kind regards,
Ram

[Jeyanth Kumar]

Hi @Ram Kumar Aravindakshan _Adaptavist_ ,

Here you go. 

import com.atlassian.jira.issue.Issue
import com.onresolve.scriptrunner.canned.jira.fields.editable.database.SqlWithParameters

def columnName = 'VALUE'

getSearchSql = { String inputValue, Issue issue, originalValue ->

if (issue.projectObject.name == 'ServiceNow') {
new SqlWithParameters("""
select ID, VALUE from AO_00B950_AOPROJECT_VALUE
where ${columnName} in (?,?,?)
""", ['1000 Core Transport Network Sweden', '1015 Fixed Switch', '4459 Service Now'])

} else if (issue.projectObject.name == 'Confluence Administration') {
new SqlWithParameters("""
select ID, VALUE from AO_00B950_AOPROJECT_VALUE
where ${columnName} in (?,?)
""", ['4461 IT Dev Tools - Recharge'])
} else if (issue.projectObject.name == 'Jira Administration') {
new SqlWithParameters("""
select ID, VALUE from AO_00B950_AOPROJECT_VALUE
where ${columnName} in (?,?)
""", ['4461 IT Dev Tools - Recharge'])
}
}

renderViewHtml = { displayValue, row ->
displayValue
}

Also, I could see that we have 4 nodes (4 servers) where sharedfolder is available on only one node. As verified, I could see the permission is full for the same.

Best regards

Jeyanth

[Ram Kumar Aravindakshan _Adaptavist_]

Hi @Jeyanth Kumar

Thank you for getting back and providing your feedback.

If you read carefully through the code, you have 

In the first SQL Query you updated, i.e.:-

new SqlWithParameters("""
select ID, VALUE from AO_00B950_AOPROJECT_VALUE
where ${columnName} in (?,?,?)
""", ['1000 Core Transport Network Sweden', '1015 Fixed Switch', '4459 Service Now'])

You are putting 3 question marks and, accordingly, 3 field names. Hence this works as expected.

However, in your 2nd and 3rd SQL Queries, i.e.:-

new SqlWithParameters("""
select ID, VALUE from AO_00B950_AOPROJECT_VALUE
where ${columnName} in (?,?)""", ['4461 IT Dev Tools - Recharge'])

and

new SqlWithParameters("""
select ID, VALUE from AO_00B950_AOPROJECT_VALUE
where ${columnName} in (?,?)""", ['4461 IT Dev Tools - Recharge'])

You put 2 question marks but only 1 column name, i.e. 4461 IT Dev Tools - Recharge. Hence it fails.

If you are only putting 1 column name, you must only put 1 question mark for the query to work.

Please correct your code accordingly to:-

import com.atlassian.jira.issue.Issue
import com.onresolve.scriptrunner.canned.jira.fields.editable.database.SqlWithParameters

def columnName = 'VALUE'

getSearchSql = { String inputValue, Issue issue, originalValue ->

    if (issue.projectObject.name == 'ServiceNow') {
        new SqlWithParameters("""
        select ID, VALUE from AO_00B950_AOPROJECT_VALUE
        where ${columnName} in (?,?,?)
        """, ['1000 Core Transport Network Sweden', '1015 Fixed Switch', '4459 Service Now'])

    } else if (issue.projectObject.name == 'Confluence Administration') {
        new SqlWithParameters("""
        select ID, VALUE from AO_00B950_AOPROJECT_VALUE
        where ${columnName} in (?)""", ['4461 IT Dev Tools - Recharge'])
    } else if (issue.projectObject.name == 'Jira Administration') {
        new SqlWithParameters("""
        select ID, VALUE from AO_00B950_AOPROJECT_VALUE
        where ${columnName} in (?)""", ['4461 IT Dev Tools - Recharge'])
    }
}

renderViewHtml = { displayValue, row ->
    displayValue
}

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

I am looking forward to your feedback.

Thank you and Kind regards,
Ram

[Jeyanth Kumar]

Hi @Ram Kumar Aravindakshan _Adaptavist_ ,

That's a nice learning for me. I was not aware of that but unfortunately the same error persists with respect to shared folder. I am in different node but the sharedfolder with respect scriptrunner is available in another node. When verified the permissions. I could see the permission is enabled fully. Let me know if I am looking it wrong.

Best regards

Jeyanth

[Ram Kumar Aravindakshan _Adaptavist_]

Hi @Jeyanth Kumar

This seems to be something related to the permission on the NFS Server.

Try to temporarily grant all permission to Everyone and see if it makes a difference.

I suggest reading through this troubleshooting doc from Microsoft for more info.

Thank you and Kind regards,
Ram

[Jeyanth Kumar]

Hi @Ram Kumar Aravindakshan _Adaptavist_ ,

Good day. I was able to fix the shared file permissions. But sadly I get error this time, I am using 1 table right with 2 columns selected, instead of 2 tables. Please find my screenshot below. 

As you could see, 3 options are getting listed as per the specific project but the value is getting displayed multiple times. Also when I chose the value. I get error "Failed to find ID:163"

Best regards

Jeyanth

[Ram Kumar Aravindakshan _Adaptavist_]

Hi @Jeyanth Kumar

Please clarify: Are the values you are querying in the SQL Query unique, or do they appear in multiple rows in the table?

If it's the latter, then the code works just fine. However, you must add additional parameters to make the result displayed in the SQL Query unique.

The values in the example I have provided are unique, as shown in the Database Table. Hence, I am not facing any issue.

It would be helpful if you share a full screenshot of your configuration and all the updated codes you are currently using.

I am looking forward to your feedback and clarification.

Thank you and Kind regards,

Ram

[Jeyanth Kumar]

Hello @Ram Kumar Aravindakshan _Adaptavist_ ,

Good day. I see three entries for all the three projects For example: For CNA project we have only one value but I get the same option thrice and all three are selectable. From table perspective we don't have any duplicate entries.

Please see the screenshot for my entire configuration.

Best regards

Jeyanth

[Jeyanth Kumar]

Hi @Ram Kumar Aravindakshan _Adaptavist_ ,

Adding more screenshot for your understanding.

Best regards,

Jeyanth

[Ram Kumar Aravindakshan _Adaptavist_]

Hi @Jeyanth Kumar

This clearly indicates that the columns you are invoking are used in multiple rows, hence the duplicates.

Please modify your SQL Query and add the DISTINCT keyword, as shown below and see if the duplicate values are removed.

import com.atlassian.jira.issue.Issue
import com.onresolve.scriptrunner.canned.jira.fields.editable.database.SqlWithParameters

def columnName = 'VALUE'

getSearchSql = { String inputValue, Issue issue, originalValue ->

    if (issue.projectObject.name == 'ServiceNow') {
        new SqlWithParameters("""
        select DISTINCT ID, VALUE from AO_00B950_AOPROJECT_VALUE
        where ${columnName} in (?,?,?)
        """, ['1000 Core Transport Network Sweden', '1015 Fixed Switch', '4459 Service Now'])

    } else if (issue.projectObject.name == 'Confluence Administration') {
        new SqlWithParameters("""
        select DISTINCT ID, VALUE from AO_00B950_AOPROJECT_VALUE
        where ${columnName} in (?)""", ['4461 IT Dev Tools - Recharge'])
    } else if (issue.projectObject.name == 'Jira Administration') {
        new SqlWithParameters("""
        select DISTINCT ID, VALUE from AO_00B950_AOPROJECT_VALUE
        where ${columnName} in (?)""", ['4461 IT Dev Tools - Recharge'])
    }
}

renderViewHtml = { displayValue, row ->
    displayValue
}

I am looking forward to your feedback.

Thank you and Kind regards,

Ram

[Jeyanth Kumar]

Hi @Ram Kumar Aravindakshan _Adaptavist_ ,

Many thanks again for your reply. I was able to successfully fix the problem with our earlier script. The reason why there are 3 entries in the drop down value is due to 3 custom fields with same values in Jira. I have added additionally the customfield id. Please find it below

import com.atlassian.jira.issue.Issue
import com.onresolve.scriptrunner.canned.jira.fields.editable.database.SqlWithParameters

def columnName = 'CUSTOMVALUE'

getSearchSql = { String inputValue, Issue issue, originalValue ->

if (issue.projectObject.key == 'SNOW') {
new SqlWithParameters("""
select ID, CUSTOMVALUE from CUSTOMFIELDOPTION
where ${columnName} in (?,?,?) and CUSTOMFIELD = '12406'
""", ['1000 Core Transport Network Sweden', '1015 Fixed Switch', '4459 Service Now'])

} else if (issue.projectObject.key == 'CNA') {
new SqlWithParameters("""
select ID, CUSTOMVALUE from CUSTOMFIELDOPTION
where ${columnName} in (?) and CUSTOMFIELD = '12406'
""", ['4461 IT Dev Tools - Recharge'])

} else if (issue.projectObject.key == 'JIRA') {
new SqlWithParameters("""
select ID, CUSTOMVALUE from CUSTOMFIELDOPTION
where ${columnName} in (?) and CUSTOMFIELD = '12406'
""", ['4461 IT Dev Tools - Recharge'])
}
}

renderViewHtml = { displayValue, row ->
displayValue
}

Once again, I appreciate your sincere replies for getting this fixed. Cheers !

Best regards

Jeyanth

[Laurie Sciutti]

Hello @Jeyanth Kumar and welcome to the Community!  I have not used the Data Picker field but it looks like you can use a "Retrieval/validation SQL" script to achieve what you want.  There are some sample scripts (provided you are using a recent version of SR).

[Jeyanth Kumar]

Hi @Laurie Sciutti ,

Thanks for replying. I have used the examples but my case is. I have to use an SQL like this. 

select a.col5

from a.tab1, b.tab2

where a.col3=b.col5 and a.col2 = 12406 and b.col3 = {project}

order by a.col5 

Here in this SQL col3 from table b is projectkey which is based dynamically.

Hope this helps.

Best regards

Jeyanth