I am able to retrieve tickets from JIRA-tables, but I seem unable to find values for the custom fields defined on https://acme.atlassian.net/secure/admin/ViewCustomFields.jspa.
For instance, we have a field “Billable” as shown below as custom field.
How do I get to see it’s value?
Thanks for your question. We are studying what the best way is to include custom fields more abundantly in the predefined tables.
Could you execute the following query in the UniversalSQL editor on your JIRA-database, and then indicate whether the suggested column names in the third column (suggestedFieldName) would match your needs?
Please export the results to Excel in the UniversalSQL-editor, remove all rows matching your needs and copy&paste the remainder from Excel to your reply.
select fld.id
, fld.schema_type
, 'c_'
|| replace
( case
when fld.key like 'customfield_%'
or fld.key like '%-custom-field'
then lower(coalesce(names.clauseNamesList, fld.key))
when fld.key like 'com.atlassian.atlas.jira__%'
then replace(fld.key, 'com.atlassian.atlas.jira__', 'jira_')
when fld.key like 'io.tempo.jira__%'
then replace(fld.key, 'io.tempo.jira__', 'tempo_')
else fld.key
end
, ' ', '_'
, '-', '_'
)
suggestedFieldName
, fld.name
, names.clauseNamesList
--, fld.schema_items
--, fld.schema_system
from fields fld
left
outer
join ( select field_id
, listagg(clauseName) clauseNamesList
, count(*) clauseNamesCount
from fieldclauses
where clauseName not like '%[%'
group
by field_id
) names
on names.field_id = fld.id
where 1=1
and fld.schema_type not in ('array', 'sd-approvals')
and fld.custom
order
by id
The output should resemble the following (output from a test database):
| id | schema_type | suggestedFieldName | name | clauseNamesList |
|---|---|---|---|---|
| aggregateprogress | progress | c_aggregateprogress | Σ Voortgang | |
| aggregatetimeestimate | number | c_aggregatetimeestimate | Σ Resterende schatting | |
| aggregatetimeoriginalestimate | number | c_aggregatetimeoriginalestimate | Σ Oorspronkelijke schatting | |
| aggregatetimespent | number | c_aggregatetimespent | Σ Bestede tijd | |
| assignee | user | c_assignee | Uitvoerder | assignee |
| comment | comments-page | c_comment | Opmerking | comment |
| created | datetime | c_created | Aangemaakt | created, createdDate |
| creator | user | c_creator | Ontwikkelaar | creator |
| customfield_10000 | datetime | c_ Needs work | [CHART] Date of First Response | |
| customfield_10001 | any | c_ Needs work | [CHART] Time in Status | |
| customfield_10016 | any | c_epic_link | Epic Link | Epic Link |
| customfield_10017 | option | c_epic_status | Epic Status | Epic Status |
| customfield_10018 | string | c_epic_name | Epic Name | Epic Name |
| customfield_10019 | string | c_epic_color | Epic Color | Epic Color |
| customfield_10020 | any | c_rank | Rank | Rank |
| customfield_10022 | number | c_story_points | Story Points | Story Points |
| customfield_10100 | any | c_parent_link | Parent Link | Parent Link |
| customfield_10200 | any | c_development | development | development |
| customfield_10400 | any | c_team | Team | Team |
| customfield_10600 | number | c_value_initial | Value Initial | Value Initial |
| customfield_10601 | number | c_value_recurring | Value Recurring | Value Recurring |
| customfield_10700 | date | c_start_date | Start date | Start date |
| customfield_10701 | sd-customerrequesttype | c_request_type | Request Type | Request Type |
| customfield_10703 | number | c_story_point_estimate | Story point estimate | Story point estimate |
| customfield_10704 | string | c_issue_color | Issue color | Issue color |
| customfield_10705 | sd-feedback | c_satisfaction | Satisfaction | Satisfaction |
| customfield_10706 | datetime | c_satisfaction_date | Satisfaction date | Satisfaction date |
| customfield_10708 | option | c_impact | Impact | Impact |
| customfield_10709 | option | c_change_type | Change type | Change type |
| customfield_10710 | option | c_change_risk | Change risk | Change risk |
| customfield_10711 | option | c_change_reason | Change reason | Change reason |
| customfield_10712 | datetime | c_change_start_date | Change start date | Change start date |
| customfield_10713 | datetime | c_change_completion_date | Change completion date | Change completion date |
| customfield_10714 | option | c_urgency | Urgency | Urgency |
| customfield_10717 | option | c_pending_reason | Pending reason | Pending reason |
| customfield_10718 | option-with-child | c_product_categorization | Product categorization | Product categorization |
| customfield_10719 | option-with-child | c_operational_categorization | Operational categorization | Operational categorization |
| customfield_10720 | option | c_source | Source | Source |
| customfield_10721 | option | c_investigation_reason | Investigation reason | Investigation reason |
| customfield_10722 | string | c_root_cause | Root cause | Root cause |
| customfield_10723 | string | c_workaround | Workaround | Workaround |
| customfield_10724 | sd-servicelevelagreement | c_time_to_resolution | Time to resolution | Time to resolution |
| customfield_10725 | sd-servicelevelagreement | c_time_to_first_response | Time to first response | Time to first response |
| customfield_10726 | sd-servicelevelagreement | c_time_to_close_after_resolution | Time to close after resolution | Time to close after resolution |
| customfield_10727 | sd-servicelevelagreement | c_time_to_approve_normal_change | Time to approve normal change | Time to approve normal change |
| customfield_10728 | sd-request-lang | c_request_language | Request language | Request language |
| customfield_10729 | date | c_next_review_date | Next Review Date | Next Review Date |
| customfield_10730 | string | c_agreement_code | Agreement Code | Agreement Code |
| customfield_10731 | option2 | c_tempo_team | Tempo Team | Tempo Team |
| customfield_10732 | option2 | c_tempo_account | Account | Account |
| customfield_10735 | string | c_waarde_verkoopkans | Waarde verkoopkans | Waarde verkoopkans |
| customfield_10736 | date | c_target_start | Target start | Target start |
| customfield_10737 | date | c_target_end | Target end | Target end |
| customfield_10738 | sd-servicelevelagreement | c_tijd_tot_oplossing | Tijd tot oplossing | Tijd tot oplossing |
| customfield_10739 | sd-servicelevelagreement | c_tijd_tot_eerste_reactie | Tijd tot eerste reactie | Tijd tot eerste reactie |
| customfield_10740 | string | c_contenttype | Contenttype | Contenttype |
| customfield_10741 | date | c_publicatiedatum | Publicatiedatum | Publicatiedatum |
| customfield_10745 | string | c_work_category | Work category | Work category |
| customfield_10746 | number | c_open_forms | Open forms | Open forms |
| customfield_10747 | number | c_submitted_forms | Submitted forms | Submitted forms |
| customfield_10748 | number | c_locked_forms | Locked forms | Locked forms |
| customfield_10749 | number | c_total_forms | Total forms | Total forms |
| customfield_10750 | string | c_compass | Compass | Compass |
| customfield_10751 | option | c_categorie | Categorie | Categorie |
| customfield_10752 | string | c_belangrijk_incident | Belangrijk incident | Belangrijk incident |
| customfield_10753 | option | c_ernst | Ernst | Ernst |
| customfield_10754 | string | c_getroffen_hardware | Getroffen hardware | Getroffen hardware |
| customfield_10755 | datetime | c_geplande_start | Geplande start | Geplande start |
| customfield_10756 | datetime | c_gepland_einde | Gepland einde | Gepland einde |
| customfield_10757 | string | c_implementatieplan | Implementatieplan | Implementatieplan |
| customfield_10758 | string | c_backout-plan | Backout-plan | Backout-plan |
| customfield_10759 | string | c_testplan | Testplan | Testplan |
| customfield_10760 | string | c_jira_project_key | Atlas project key | Atlas project key |
| customfield_10761 | string | c_jira_project_status | Atlas project status | Atlas project status |
| customfield_10762 | string | c_department | Department | Department |
| description | string | c_description | Beschrijving | description |
| duedate | date | c_duedate | Vervaldatum | due, duedate |
| environment | string | c_environment | Omgeving | environment |
| issuerestriction | issuerestriction | c_issuerestriction | Voorbehouden voor | |
| issuetype | issuetype | c_issuetype | Issuetype | issuetype, type |
| lastViewed | datetime | c_lastViewed | Laatst Bekeken | lastViewed |
| priority | priority | c_priority | Prioriteit | priority |
| progress | progress | c_progress | Vooruitgang | progress |
| project | project | c_project | Project | project |
| reporter | user | c_reporter | Melder | reporter |
| resolution | resolution | c_resolution | Resolutie | resolution |
| resolutiondate | datetime | c_resolutiondate | Opgelost | resolutiondate, resolved |
| security | securitylevel | c_security | Beveiligingsniveau | level |
| status | status | c_status | Status | status |
| statuscategorychangedate | datetime | c_statuscategorychangedate | Statuscategorie gewijzigd | statusCategoryChangedDate |
| summary | string | c_summary | Samenvatting | summary |
| timeestimate | number | c_timeestimate | Resterende Schatting | remainingEstimate, timeestimate |
| timeoriginalestimate | number | c_timeoriginalestimate | Oorspronkelijke schatting | originalEstimate, timeoriginalestimate |
| timespent | number | c_timespent | Tijd gespendeerd | timespent |
| timetracking | timetracking | c_timetracking | Tijdregistratie | |
| updated | datetime | c_updated | Bijgewerkt | updated, updatedDate |
| votes | votes | c_votes | Stemmen | votes |
| watches | watches | c_watches | Kijkers | watchers |
| workratio | number | c_workratio | Werkratio | workratio |
Invantive’s SQL JIRA-drivers in release 24.0 have been extended by December 2023 to include all custom field values as columns.
Please use a recent 24.0 release such as on Invantive Cloud.