What's the difference between Retrieval/validation SQL and Search SQL

[Vincent Lee]

Helllo All,

I'm starting to learn ScripFields, and I create a Database picker.

I have already set up the Resurces Database and Connenction.

But I don't understand what't the difference between these two field, Retrieval/validation SQL and Search SQL.

Even I read the doucument here,

https://scriptrunner.adaptavist.com/5.6.8.1-jira8/jira/script-fields/database-picker.html

Could you give me more detail description or example?

Thanks in advance,

[PD Sheehan · Accepted solution]

I haven't played a lot with this, but I think it's like this:

The Search SQL controls the list of values that will be available in the drop-down.
It appears to expect 2 columns returned. The first one will be the value stored with the issue as the custom field value. The second is what you see in the drop-down.
You would normally want to filter that list based on the user's typeahead entry "where lower(pname) like concat(lower(?), '%')"

The Retrieval/validation SQL will extrapolate the value stored against the custom field with the details returned from the sql (second column). This is what you will see when viewing an issue in a view screen or in an issue navigator (column).

[Vincent Lee]

Thanks for your answers! 

I have try that, and the result is just like what you said.

I think your thinking is true.

Thank you