Can custom field options be updated from SQL?

[Montana Fox]

I have a custom field (checkboxes) which has several options. Let's say they are department names:

• Facilities
• Operations
• Human Resources
• Transportation

I also have a table in my SQL that has these same values in one column, plus other values in other columns. Then, I use a post-function that retrieves other information from that table based on the options selected, and sets other field values accordingly.

• Facilities / Assignee: John F / Manager: Linda R
• Operations / Assignee: Pamela M / Manager: Craig D
• Human Resources / Assignee: Roger C / Manager: Cindy H
• Transportation / Assignee: Howard W / Manager: Paul J

I have been able to make this work, but as you are probably aware, organizations change and departments get renamed, split up, merged, etc. When this happens, I have to manually update the department names in both the custom field configuration AND the SQL look-up table so that they still have matching values.

What I would like to do is make it use a SQL [UPDATE] script to change the department names in both the lookup table AND the custom field configuration at the same time. This seems like something I should be able to do without a whole lot of trouble, because they're already existing in both places. But...

What if a new department - IT Services - needs to be included? I can definitely use a SQL [INSERT] script to add the new row to the lookup table, but can I use an [INSERT] to add that department name as a new option in the field configuration?

[Nic Brough -Adaptavist- · Accepted solution]

Argh, no.

To do this, you will need to get a better understanding of the database (your insert statement will corrupt Jira unless it's done alongside some other stuff).  Once you know that well enough to construct non-breaking SQL, you willl need to shut down Jira, take a full backup, restore it to a test system to make sure it is a good backup, run the SQL, restart Jira, re-index it in full (not background), then test it in full to make sure you have not broken it, and then let the users back in.

I can't recommend you do that, it's too fragile, slow and risky.

I'd strongly recommend that you either get in one of the configuration manager apps and go via that, or have a look at one of the database fields provided by apps such as Elements-connect and Scriptrunner

[Montana Fox]

That is exactly the concern I had. Thanks for verifying.

[Nic Brough -Adaptavist-]

I forgot, there is another option, in that if you get one of the scripting apps (or do a bit of work coding an app), you can write something that can update, add or remove options from a field after reading changes from almost anywhwere.