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

[Justin Webber]

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?

[Nic Brough -Adaptavist-]

Interesting use of a Space Station Information System, but hey, NASA got robots to Mars using the Atlassian stack ;-)

I'm assuming you are using some form of database query.  To keep this simple, this is generally a very Bad Idea (tm).  A JIRA database is absolutely not designed for any form of direct database reporting and you'll find your queries are complex and ugly.  If, for example, you want a query for "everything on an issue", you've got at least 18 tables to read, often with multiple joins and plenty of potential for nulls and esoteric data. 

Anyway.  A multiselect custom field (I'm assuming it's a custom field) will have a record in customfield, tied to an issue.  But only if there is data.  If the field has no checks at all, there will not be any rows in the database with that issue id.  If it has one check, you'll get rows with data.

I guess we'd need to see your exact query to help you if you do think it has data in it!

[Justin Webber]

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:

<attr name="Requested_Images" xs:type="string" other:outputname="fields_customfield_14352" other:supportedoperators="LIKE,NOT LIKE,IS,IS NOT" other:urlname="customfield_14352" desc="Requested_Images." />

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?

[Nic Brough -Adaptavist-]

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]

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.