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

sql query to view data

Sasmitha Chalamala March 19, 2022

Hi, which database table has the required ( 1 or  0 ) column for custom fields ? I checked the below tables, but they do not have this column that shows if a particular customfield is required or optional.

 

select * from customfield
select * from customfieldoption
select * from customfieldvalue

 

 

 

2 answers

1 vote
Nic Brough -Adaptavist-
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 20, 2022

I strongly recommend that you stop reading the database.  It's not designed for reporting or asking questions of, and it's a total mess when you're trying to find config data.

For field mandatory flags for example, you're going to have to join four tables together to get one way of doing it, and read xml and parse it from another set of three joined tables.

Sasmitha Chalamala March 20, 2022

Thank you, can you suggest how to change a custom field from required to optional in all the forms at once in bulk (there are a lot of forms and cannot do manually for each form).

Nic Brough -Adaptavist-
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 20, 2022

You absolutely can not do this with the database - you will break it if you write to the database while Jira is running.

Also, you don't make fields mandatory or optional in the screens, so I think you might be looking at the wrong thing anyway.

If you don't want to do this in the UI, you'll need to get one of the scripting apps that would enable you to write something to bulk-change the field configuration schemes.

Sasmitha Chalamala March 20, 2022

Hi, below highlighted shows where we make a field required or not. Is this not the right place? If so, can you please share the right place.

 

sd4.png

Nic Brough -Adaptavist-
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 20, 2022

Ah, ok, those are not issue fields in screens, they are request fields in the service desk portals.  They're done in yet another way.

You'll still want to get a scripting app if you want to bulk-change these.  

Like Mauricio GALVEZ likes this
0 votes
Vishwas
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 19, 2022

Hey @Sasmitha Chalamala 

My guess we have to check field config scheme table or field layout table to get the custom field required/option thing.

Check this kb which as an sql query there you can see the tables list in join query https://confluence.atlassian.com/jirakb/how-to-list-projects-and-field-configurations-by-issue-types-in-jira-1005782012.html

Sasmitha Chalamala March 20, 2022

Thank you, but this sql did not show me the column I asked about. I even used the below where clause with all left joins, and did a select * to see all columns . Ran the sql during off-hours to avoid any issues.  

where fcs.fieldid in ('customfield_10302')

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events