Please note that this feature is only supported for Jira Server and Jira Data Center.
In Jira Cloud, to search for issues that have at least one related Case we can use the following JQL query.
Jira Server and Jira Data Center
This function is used to filter JIRA Issues (with related Cases) based on Case object properties and/or Case-related objects using SOQL (Salesforce Object Query Language) condition expression inside JQL (JIRA Query Language).
In order to use sfquery(), it is necessary to use the API Name of the Salesforce fields.
Usage (in JIRA JQL)
Issue IN sfquery(<SOQL QUERY CONDITION>)
The query will search for all the JIRA Issues with related Salesforce Cases that satisfy the condition in <SOQL QUERY CONDITION>.
Example (via Advanced JIRA Search):
Issue in sfquery("case.Origin = 'Web'")
Where “Origin” is the API Name of the Case origin standard field.
Note that the content inside the double quotes indicates the SOQL criteria, This query will return JIRA Issues linked to Case(s) that have Origin = “Web” .
SFQUERY() Also allows you set the pagination results with :
Issue IN sfquery(<SOQL QUERY CONDITION>,<STARTING PAGE>,<NUM PAGES>)
sfquery() allows pagination of 2000 issues per page:
Issue in sfquery("case.Origin = 'Web'",0,5)
This will get a max of 10000 JIRA Issues related to Case(s) in advanced search in JIRA.
You can also use In order to get the next 10000 issues:
Issue in sfquery("case.Origin = 'Web'",5,5)
Getting the API name of Salesforce fields
You can look for the API name of the standard fields under Setup → Build → Customize → Case (or any Object)-> Fields.
For Standard Fields look for the column Field Name denotes the API name of the standard fields:
For Custom Fields look for the column API Name
You can also download the PDF for all standard field names from:
Common SOQL operators
SFQUERY() accepts SOQL syntax.
Using Logical Operators:
Issue IN sfquery("case.Issue_Type__c='Improvement' AND case.Origin='Email'")
Issue IN sfquery("case.Priority='High' OR case.Origin='Web'")
Using Comparison Operators
Issue IN sfquery("case.Issue_Type__c Like '%Improvement%' AND case.Origin='Email'")
Issue IN sfquery("case.CaseNumber >= '00041045'")
You can also check the SOQL syntax and conditions here:
Filtering by Case related objects
SFQUERY() can filter by fields from Case related objects like Account, Contacts, etc
Issue IN sfquery("case.<Related-Object>.<Related-Object.Field">)
Issue IN sfquery("case.Account.name = 'Aurora Feint'")
The example above returns all the Issues with a case related that have the Account “Aurora Feint”
Filtering by Issue created from Salesforce
JIRA Issues created from Salesforce can be returned with SFQUERY() .
Issue IN sfquery("zissue_sf.zsfjira__IssueCreated__c = true")
the query above is using the alias zissue_sf in order to replace the table api name zsfjira__ZIssue_SF__c
This function returns every Issue that have at least one Case related and it can be used with a case number as a parameter to make more specific query.
Issue IN SalesforceIssues()
This query will return all the Issues with at least one cases related.
Using with parameter CaseNumber:
issue in SalesforceIssues("00047017")
This query will return the Issues that are related to Case No. “00047017”.