Skip to main content

Query Overview

Query module of Brobench provides highly sought-after capabilities to help with writing queries, view results, create favourite queries and recalling query history for easy re-execution.

Overview​


There are a lot of things in the Query module, and the below screenshot highlights many important aspects. Each of these features is explored further in the below sections.

Basic Usage​

The most basic usage is to author query and execute it. To do that goto Query Editor, write or paste the query and click on Execute button. Once executed, results will be shown in the below section. At this time, Query Results don't allow you to edit the records but that capability will be added in future versions.

Once results are shown, you can explore the results. Search for particular record by entering search term in Search in Grid input box. If there are a lot of fields, you can filter fields by entering search term in Filter Fields input box. You can resize the columns by dragging the end of fields and dragging to make it wider or narrower. You can re-arrange fields if you want to see specific fields together.

If your query matched more records than shown in the Grid, Fetch Next Batch button would be enabled. Clicking on that button, the app will fetch the next batch of records.

If you want to hide some fields based on some criteria, click on the Filter fields by Criteria button where you can choose the type of fields to hide.

Execute Query​

There are three ways to execute a query that you have authored.

  • Click on the Execute button in Query Editor toolbar
  • Right-click on the query text and select Execute menu item
  • Press keyboard shortcut Cmd+Enter/Ctrl+Enter

The query to be executed is picked in this order

  • If you have selected any part of the query, only that selected text will be executed.
  • If you haven't selected any text, then the paragraph around the current line will be used. The paragraph is identified by blank line on above and/or below current line. If the current line is blank line, then the paragraph below current line is executed. If the current line is blank and there is nothing else after that line, then nothing will be auto selected.

When you execute a query, result tab will be opened nad results or error will be shown. See Query Results for more information on how to navigate around the results

Query Types​

Query Editor supports executing a different type of queries, the primary one being SOQL query. It also supports SOSL Queries. More will be added in the future.

SOSL Queries​

Query editor supports authoring SOSL Queries and executing them as you would a SOQL query. Type up the SOSL Query and execute it.

caution

Keep these things in mind when you are executing SOSL.

  • While Query editor supports all aspects of SOSL query authorizing including With Snippets and With Highlights, the results shown are not highlighted in the grid. This is due to how the data grid rendering works and data as returned from Salesforce.

Here is an example of how to execute SOSL query, view results and modify them.

SOSL Query Auto-Complete​

SOSL Query is used to search data in Salesforce using various conditions.

Query Editor supports SOSL Queries with auto-completion for Objects and Fields, and all clauses of SOSL Queries. If auto-complete is not working in particular SOSL clause, please create a bug and will address it.

SOSL Query Pre-processing​

If you include an object in the query but do not specify specific fields, then app automatically includes Record Preview Fields for that object.

For ex., if you execute query

find {test} returning Account,

it actually runs this query behind the scene (list of fields may vary based on Record Preview Fields configuration)

find {test} returning Account (Id, Name, CreatedById, CreatedBy.Name, CreatedDate, LastModifiedById, LastModifiedBy.Name, LastModifiedDate).

This enables you to see more context around matched records than just the Id that Salesforce returns by default.

If you do include any field in that object, then Preview Fields are not added. For ex., if you run find {test} returning Account (Id), then that query is executed as is.

Viewing SOSL Result​

When you execute a SOSL Query, if there are multiple objects returned from Salesforce, then a sub-tab is shown for each of the results. Each tab shows matched records for that single Object as shown in the Tab title.

Each tab is a fully functional Data grid that you can search in grid, filter columns, edit/save, and export as usual.

Query History​

Every time a query is executed, it is added to Query History MRU (most recently used) lis along with execution time and execution count. You can view this history by clicking on the 'Click' icon in the Query Editor toolbar.

Execute Other​

Execute As options provide different ways to query the data you're looking for. These options can be accessed by clicking on the dropdown next to Execute button as shown below

Execute with Include Deleted​

This option executes the query to include the deleted records. Note that you can also query to include deleted records using All Rows clause. Deleted records would be included when this option is selected or if query has All Rows clause hence you need not do both.

Execute as Count Query​

Many times during the data analysis, you would want to see the count of records a query matches. You can do that in two ways 1) Run the query as is and see the total records matched 2) Change the select clause to count() and run the query

Both of them are not an efficient approach to get the records count. In the first case, it fetches the data unnecessarily and might also keep the pagination cursor open on the server side. In second case, you need to change the query to replace select fields with count() and revert back to include fields afterward.

Execute as Count Query option automates the second option. Upon execution, app replaces the select fields in memory with count() and shows the result.

Execute as Polymorphic Query​

Polymorphic Field is a field that can look up to more than one Object. For ex., Task.WhatId, Event.WhatId or Attachment.ParentId etc.,

If you want to fetch related parent record information, you cannot use typical SOQL relationship names as those names may not be valid in all Objects that Poly Field can point to. Instead, you must use Poly Field clause in SOQL.

For ex.,

SELECT
  TYPEOF What
    WHEN Account THEN Phone, NumberOfEmployees
    WHEN Opportunity THEN Amount, CloseDate
  END
FROM Event

Brobench supports executing those queries fine. However, it is painful to construct such queries. Execute as Polymorphic Query automates construction of Type Of clause behind the scene.

For ex., if you run select Id, What.Phone, What.NumberOfEmployess, What.Amount, What.CloseDate from Event, via the above option, app transform that query to above Type Of clause and executes it.

Execute as Multi Conn Query​

Professional

Execute as Multi Conn Query allows you to execute the query against multiple connections, combine the results and show all records in the grid with connection details. This is helpful if you want to view details of records which are matched by common criteria across multiple connections. For ex., a Custom Setting value for the same name or custom metadata value or User details for the same email etc.,

Things to keep in mind
  • The query you are executing must be valid for all connections otherwise you will get an error message
  • The grid adds two additional columns Conn Name and Conn User which shows the connection details
  • There is no Query More support for Multi Conn Query so it will fetch all records matched by the query
  • Record preview and view record links will work only for main connection records
  • It supports Child Queries and similar to Parent Query, it adds Conn Name and Conn User columns

Video Guide


Usage

  • In the query editor or query builder, author the query you want to run, highlight the query text and then click on Execute as Multi Conn Query

  • App shows the following dialog asking you to select one or more connections and click Execute. When you launch the below dialog for the first time in a session, it will select current connection by default. Afterward, it remembers the previously selected connections and selects them by default.

  • App shows the results in the grid with connection details

  • You can sort, filter and group the results as usual including exporting the data

Execute via Bulk API​

If you have a Query that matches a lot of records, it can take time to download all of those records via Batch API, which is the default API that Brobench uses. Execute via Bulk API, will change underlying api to Bulk API V2. App will do all the heavy lifting behind the scene and shows the first batch of results, up to 50k. You can continue to fetch the next batch of 50k records till results are exhausted.

Execute via Tooling Api​

App automatically uses Tooling API if you are querying a Sobject which is available only in Tooling API. However, there may be objects that are available in both Partner Api and Tooling API, and in such cases, the app will use Partner API by default. If you want to force the app to use Tooling Api, execute the query with Execute via Tooling Api

Execute Query in Clipboard​

If you have copied a query from somewhere you want to execute, you don't need to paste into an editor, select and execute it. Click on Execute Query in Clipboard, which will save you some clicks.

Query Records by Ids​

This option helps you view the records by set of ids or extracted ids from blob of the text. When you select this option, it shows the following dialog.

Enter ids or text containing ids and click View Records. The app will extract the ids and view those records.

Please keep these points in mind when you use this feature.

  • You can enter ids that belong to different objects. The app would group by the object and show multiple result tabs, one for each Object
  • You can enter blob of text and in such cases, app would try to extract the ids and show the result

Query Records by Ids in Clipboard​

This is the same as Query Records by Ids which automatically uses the text in clipboard