Need help to add a simple scripted validator for a multiselect checkbox

Question

I'm using SSIS to query JIRA.

One of the fields being returned is part of a multi-select. The fields/checkboxes designates one part of a whole and the other designates the other part of the whole.

When I query for these, these fields show up as NULL.

How can you query a multiselect (checkbox) fields?

I would assume as long as I have the field name and the right datatype set (I've tried boolean and string). Yet this returns null regardless.

Is there a trick to querying these types fields?

Justin Webber · Answer

I think you're misunderstanding.

This query is done in SSIS through a third party tool that utilizes the schema, Essentially I can alter the schema via the 3rd party tool to account for 'references' to the customfield table.

However for certain customfield 'types' the returned value is either not what i expected or not stored the same.

If I run the query I described above for another customfield that is just a text field. Everything works just fine. I understand not querying from SQL due to the associations or complexitiy of the schema, but that is solved by the plugin (Or so it is supposed to be). Just wanting to understand the types of fields and how that translates into SQL.

I'll contact the plugin creator and see what's up.

Just curious if there is something unique about multi-selects or dropdowns and how they are stored in JIRA? I'm not the admin so I can't gain access to the JIRA DB directly.

Nic Brough -Adaptavist- · Answer

No, your query is utterly wrong.  This is *exactly* one of the reasons you should not be using SQL to read it - you don't know how to read the database at all.  I'd say whatever your plugin is has the same problem.

Custom field data is held in customfieldvalue.

Justin Webber · Answer

Hi Nic,

Thank you for the response. Here are some more details regarding the issue.

This is a customfield, and we have many more which are working. What is unique with this field is that it's a multiselect.

Now I'm querying the JIRA DB from SQL Server using SSIS, there is a 3rd party plugin that can pull the JIRA schema and allow you to add any custom fields as you see fit.

Here is that bit of code to define the custom fields:

Now my actual query where I know the multi-select has been set is pretty simple:

select * from Issues where [ProjectId] = 10060
 AND key = 'TEMPLATES-22653'

I assume the issue is with my type "string". I've tried integer, boolean. No luck.

Is the logic different for these type of fields?