Advanced searching with JQL

Atlas CRM has support for the Jira Query Language built-in. You can use it to find issues related to CRM entities.

The basics

With the Atlas CRM support for JQL, you can search for issues with a reference to CRM entities. For example, search for all support issues raised by companies in a certain region, or issues that are linked to an important contact in your CRM.

JQL searches always result in a list of issues. If you want to search for companies, contacts or sales, use the Atlas CRM search function or overview filters instead.

Before you start with JQL support in Atlas CRM, make sure that you are familiar with the basics of JQL by reading the Jira documentation about JQL. The blog post Searching Jira like a boss with JQL is a good starting point.

Examples

An example is the best way to get a feeling for what you can with JQL in Atlas CRM:

Let's say that you want a list of all support issues that are created by a company. With Altas CRM JQL support you can type:

atlasReference ~ companiesWhereField("name", "matches", "Acme")

Let's break this query down:

atlasReference	The name of the field in the issue where links to CRM entities are stored.
~	The JQL operator
companiesWhereField	A JQL function provided by Atlas CRM. This function returns a string containing advanced search syntax. The arguments for this function are first the name of the field, then the operator on that field and last the value of the field you want to match.

This example shows a nice natural way of saying: give me all the issues that reference a company who's name matches with Acme.

Field names and ids

You can use both field names and field ids in JQL queries. Field names can be found when viewing an entity. Field ids are currently not easy to discover. This will be improved in an upcoming release.

There are a couple of things to consider when using field names:

• Field names can be duplicated in the template. For example, country may be used in a section named visiting address as well as a section named billing address. When Atlas CRM detects multiple fields with the same name, it will show an error message.

• Field names may be changed in the template. For example, state may be renamed to province. In this case the field id will not change, but the name does.

It is recommended that you use field names in one time queries. Field names are easy to remember or to guess. Just take into account that you may run into an incidental duplicate warning.

It is recommended that you prefer to use field ids instead of names when you save a query as a filter. The field ids are more stable than field name and cannot be duplicated when changes are made to the template.

Fields reference

atlasReference

syntax	atlasReference
supported operators	~, !~, is (only with EMPTY), is not (only with EMPTY)
supported values	Functions companiesWhereField(), contactsWhereField() and salesWhereField(). For even more flexibility this field also supports Lucene's text searching features.

Example: Find all issue with linked entities:

atlasReference is not EMPTY

Example with Lucene's text searching features: Find issues that are linked to either entity with id 1 or entity with id 2:

atlasReference ~ "1 || 2"

Which is equal to:

atlasReference ~ "1" OR atlasReference ~ "2" 

atlasReference may need a re-index in order to work correctly. More information.

Functions reference

companiesWhereField()

syntax	companiesWhereField(field, operator, value) field: the name or id of the field in the company template. operator: Supported operators by field type
supported fields	atlasReference
supported operators	~, !~

Example: Find all companies from a certain country:

atlasReference ~ companiesWhereField(country, "matches", "Spain")

contactsWhereField()

syntax	contactsWhereField(field, operator, value) field: the name or id of the field in the contact template operator: Supported operators by field type
supported fields	atlasReference
supported operators	~, !~

Example: Find all contacts with a certain name:

atlasReference ~ contactsWhereField(name, "matches", "Fatih")

salesWhereField()

syntax	salesWhereField(field, operator, value) field: the name or id of the field in the contact template operator: Supported operators by field type
supported fields	atlasReference
supported operators	~, !~

Example: Find all sales assigned to me:

atlasReference ~ salesWhereField(assignee, "matches", "myuserkey")

Operators

You can use different operators based on the type of field you are querying for. You can find and overview of which operators a specific field supports.

Supported operators by field type

Field type	Operators
Text (Single line)	matches
Text (Multi line)	matches
User	matches
Single select	matches, in
Decimal	matches, >=, >, <=, <
Date	matches, >=, >, <=, <

Examples

Text (Single line) & Text (Multi line) field

matches

atlasReference ~ contactsWhereField("name", "matches", "Fatih")

User field

Can be user key, username or email

matches

atlasReference ~ salesWhereField("assignee", "matches", "fatihuserkey")
atlasReference ~ salesWhereField("assignee", "matches", "[e-mail]")
atlasReference ~ salesWhereField("assignee", "matches", "fatihusername")

Single select

matches

atlasReference ~ contactsWhereField("salutation", "matches", "Mr")


in

atlasReference ~ contactsWhereField("salutation", "in", "Mr,Mrs")

Decimal field

matches

atlasReference ~ salesWhereField("Estimated Revenue", "matches", "500")


> >= < <=

atlasReference ~ salesWhereField("Estimated Revenue", ">", "500")
atlasReference ~ salesWhereField("Estimated Revenue", ">=", "500")
atlasReference ~ salesWhereField("Estimated Revenue", "<", "500")
atlasReference ~ salesWhereField("Estimated Revenue", "<=", "500")


Between two numbers:

atlasReference ~ salesWhereField("Estimated Revenue", ">", "500")
AND
atlasReference ~ salesWhereField("Estimated Revenue", "<", "1000")

Date field

Dates should be formatted as described in ISO 8601. June 8th 2020 = 2020-06-08

matches

atlasReference ~ salesWhereField("Opened at", "matches","2020-06-08")


> >= < <=

atlasReference ~ salesWhereField("Opened at", ">","2020-06-08")
atlasReference ~ salesWhereField("Opened at", ">=,"2020-06-08")
atlasReference ~ salesWhereField("Opened at", "<","2020-06-08")
atlasReference ~ salesWhereField("Opened at", "<=","2020-06-08")


Between two dates:

atlasReference ~ salesWhereField("Opened at", ">","2020-06-01")
AND
atlasReference ~ salesWhereField("Opened at", "<","2020-07-01")

Troubleshooting

After Atlas CRM is installed in Jira, you may not get all results for:

atlasReference is EMPTY

If this is the case, an admin needs to re-index Jira. For more information visit the Atlassian documentation on search indexing.