Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root


1 badge earned


Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!


Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.


Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!


Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
Community Members
Community Events
Community Groups

Adaptavist Scriptrunner Database Picker Searching on Multiple Attributes


I have been trying to use the Database Picker from Adaptavist to search on multiple attributes of a simple database.

The aim is to have a Database Picker field where users can start to enter part numbers or part descriptions and have the results filter to a single selection.

I have tested this searching on one attribute, say part number and works ok. It is connecting to a Microsoft SQL Server.

If I follow the examples to search on two attributes I get an error message on validation saying "The SQL statement should have exactly one parameter marker". But with this approach there will be more than one ? marker.

Search SQL is:

import com.onresolve.scriptrunner.canned.jira.fields.editable.database.SqlWithParameters

getSearchSql = { inputValue ->
new SqlWithParameters("select PartNumber, PartDescription from PartDetails where PartNumber like concat(lower(?), '%') or PartDescription like concat(lower(?), '%')", [inputValue, inputValue])

 Could anyone offer any support on what could be wrong with this and why I get the error mentioned above?

I would also like to be able to do a true wildcard search allowing text from mid-description to filter the results. The approach at the moment requires the search to start with the entered characters. I.e. typing in "wire" would return "equipment wire black" in the results, not just results starting with "wire".

Thanks in advance!

1 answer

1 accepted

Ok I sussed out how to do the partial search easy enough. I.e. like below

select PartNumber, PartDescription from PartDetails
where lower(PartNumber) like concat('%', lower(?), '%')

But this is just for a single attribute. I would still like it to search on PartNumber and PartDescription but if I have a statement with two ? then I get the error about only permitting one parameter marker?

Ok, I have worked out how to do this ignoring the examples in the Adaptavist documentation for Database Picker. I have read over the examples several times but really it was more of a SQL creation issue. Their examples do show two ? in a single statement which seems to be rejected when I try.

However it would appear the following does what I need:

select PartNumber, PartDescription from PartDetails
where concat(lower(PartNumber), lower(PartDescription)) like concat('%', lower(?), '%')
Like # people like this

Thanks @Mark Williams ! Just helped me solve a problem I'd been struggling with for an hour and trying so many combinations. I'd done a CONCAT(field1,field2) on the left of the LIKE, but the concat on the right side of the LIKE was what I was missing



Suggest an answer

Log in or Sign up to answer