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:

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

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()

Example: Find all companies from a certain country:

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

contactsWhereField()

Example: Find all contacts with a certain name:

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

salesWhereField()

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

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", "f.demir@email.random")
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.

Last updated