Query Editor

Writing queries is as easy as it gets in Brobench. Features like Content Assist of Objects/Fields/Picklist Values and Query Templates can significantly reduce the amount of time it takes for you to author a correct query without errors.

To author, goto Query editor and start typing the query. The first thing you notice is auto-complete showing Query Templates like below. Query Templates are re-usable pieces of Query that act like a template of actual Query that would want to write. Select a snippet or just continue typing what you intend to type, and the app will continue to prompt auto-complete suggestions based on context.

Once you got the query, you intended next step is to execute. See Executing Query for various ways to execute the Query.

Query Comments

Brobench supports both single line comments //... and multi-line comments /*...*/. You can select the query text including comments and query executor will ignore the commented piece of the query and execute the rest.

You can use shortcut Cmd+/ or Ctrl+/ to toggle the single line comment of current line

Query Editor Minimap

Editor Minimap is a strip on the right side of the editor that shows an overview of editor contents. You can also use that as scroller to navigate to part of the editor.

Query Editor Regions

Brobench uses a single Query editor with auto-save feature so you don't have to switch between multiple editors. However, this can lead to a lot of queries in single view and can be overwhelming to navigate. To facilitate identifying area of editor with friendly name, Query Editor supports Editor Regions.

An editor region is block of code separated between // #region <optional name> and // #endregion. As you can see, it is just a convention on top of single line comments feature.

Regions add the following capabilities

• You can fold a region to collapse its contents so reduce the visible contents
• Regions show up in the editor minimap on the right side with increased font-size
• They also show up in Query Outline view for listing all regions and search for one

Here is an example of how regions can be defined and how they show up in Minimap and Query Outline.

Content Assist

Brobench supports content assist of

• Objects
• Fields
• Parent Fields
• Child Fields
• Picklist Values

Those resources are shown in the following parts of the query

• Select
• Child Query
• From
• Where
• Having
• Order By
• Sub Query

Since From <object> is essential to know the Object which defines the context, it is advised to enter select from <object> first and then compose rest of the query. This way, the app can fetch and show the appropriate list of values.

App should pop up the suggestions as you compose the query. If for whatever reason suggestions are not shown, you can request the suggestions by entering Ctrl+Space shortcut.

Here are some examples of various ways of Content Assist.

Objects

Fields

Parent Object Fields

Child Relationships in Child Queries

Child Object Fields

Picklist Values

Query Outline

Query Outline feature shows you a tree with summary of various statements in the editor. You can search or navigate for Query of your interest and then go to that Query starting position by selecting that node in the Outline Tree. If you have used any of the modern code editors, you would feel right at home using this feature.

Note that Outline also treats the comment just above the query as description for that query and shows that description in the Outline view. For ex.,

When you are in the editor, user shortcut Cmd+O (Mac) or Ctrl+O (Windows) (that is letter O, not zero) open the Outline. It shows a dialog as shown below. You can search for some text of your choice and select the item to go to start of that selected item.

Note that at this time we show only selected fields and object name details in the Outline item. More details may be added depending on the user feedback.

Query SOQL Enhancements

Brobench provides the following enhancements on top of Salesforce SOQL query language.

• Wild Card fields
• All Rows clause
• Query Variables
• bb_fields Function
• Ignore Dangling Comma
• Formula Columns

Wild Card Fields

While Salesforce provides fields(all)/fields(standard)/fields(custom) constructs to fetch all fields, it comes with many limitations with critical one being you cannot fetch more than first batch of records with it and others.

To overcome these issues, Brobench wild card selection of fields is a more versatile and capable enhancement as outlined below

• Use * by itself to select all fields. For ex., select * from account. Note that if there are a lot of fields in that object, that construct will only include the first 600 fields.
• Use * anywhere in a field to match by prefix or suffix or contains.
  • select *street from account will select both BillingStreet and ShippingStreet
  • select billing* from account will select all fields starting with Billing
  • select *name* from contact will select all fields containing Name like FirstName, LastName, Name etc.,
• Use wild card multiple times
  • select billing*, shipping*, *street* from account will select all fields starting with either billing or shipping. If a field is matched by one or more matchers, then that field is included only once.
• Use * with specific parent prefix to select matching fields in that parent object
  • select Id, *name*, Account.billing* from contact will select contact id, contact fields containing name and its account fields starting with billing
• Use * in child queries
  • select Id, name, (select * from contacts) from account limit 100 will select id, name for Account and all fields of Contact.

All Rows Clause

Salesforce allows you to include All Rows clause when you run the query in Apex language context. This clause shows all records, including deleted/archived records. This clause doesn't work outside of Apex Language context.

Brobench provides language enhancement to parse all rows clause and if specified, query SOQL with the Include Deleted option.

Note that you can also query to show deleted records by executing query using Execute as Include Deleted option. See Execute Other for more information.

bb_fields Function

bb_fields is a special soql function, designed after soql standard function fields but with enhanced capabilities.

Function syntax is bb_fields(arg1, arg2, ... argN) where each arg indicates a set of fields to be matched.

• all: This is similar to the standard function fields(all), which includes all fields. Note that this is same as select * from.
• standard: This is similar to the standard function fields(standard), which includes all standard fields
• custom: This is similar to the standard function fields(custom), which includes all custom fields
• type_<Field Type> where Field Type is field type. All possible values are shown below.

Argument List	Argument List	Argument List	Argument List
type_address	type_multipicklist	type_datetime	type_rollup
type_autonumber	type_number	type_email	type_text
type_boolean	type_percent	type_formula	type_textarea
type_currency	type_phone	type_location	type_textarealong
type_date	type_picklist	type_lookup	type_textarearich

For ex.,

• select bb_fields(type_lookup, type_date) from account; selects all Lookup and Date fields from Account
• select bb_fields(type_lookup, custom) from account selects all Lookup and Custom Fields from Account;

Ignore Dangling Comma

Brobench query parser ignores the dangling comma after fields in select clause. For ex., select id, name, from account is a valid query to run. With this enhancement, you have one less issue to worry about while executing Queries.

Formula Columns

Enterprise

Formula Columns are virtual and in-memory columns you can add to SOQL Query, which evaluates Brobench Formula and shows the result. This expression can make use of Brobench Formula Functions, and other Formula constructs to implement your business logic.

info

Keep these things in mind when you are using Formula Columns.

• You need to select all fields you are using inside the expression. If you don't select, engine will treat that value as null and evaluates, which would not be want you looking for.
• You can add any number of Formula Colums to a query
• You can only use Formula Columns in select clause so you cannot use that in where or having or other clauses
• You can use Formula Columns in the child query as well

Examples

SOQL	Description
select Id, CreatedDate, LastModifiedDate, {{diff_date(CreatedDate, LastModifiedDate, 'days')}}:number DateDiff from Account limit 100	This is calculating the date diff between 2 date columns using diff_date function

Other Editor Features

Brobench uses the widely used editor component from VSCode, hence following editor capabilities work as they do in VS Code. Note that other VS Code editing capabilities not listed below are not supported.

• Search and Replace
• Column Selection
• Multiple Selections

Query Editor Tabs

Query module supports creating multiple tabs of Query Editor. You can use this feature to organize your queries for various Orgs and or different clients.

Saved Queries

Saved Queries feature allows you to save queries for later use. You can saved queries in various folder to organize as you see fit and also can make use of Query Variables to full reuse of queries across different Orgs.

Query Templates

Query Templates feature allows you to configure custom reusable soql statements with dynamic placeholders. These snippets show up as Content Assist suggestions. When selected, the app inserts a specified template into the editor and allows you to change placeholder values. Brobench comes with many standard snippets, so you can get started immediately.

Template Usage

Dynamic placeholders are indicated by ${n} or ${n:Default Value} where n is an integer starting with 1 and Default Value is the default value for that placeholder. Value 0 indicates final cursor position after all dynamic placeholders are navigated.

When a snippet is inserted into the editor, it will navigate you from placeholder position 1 and onwards. To go to the next placeholder, click Tab. You can also escape (esc) to abandon dynamic placeholders.

For ex., if this is the snippet content select Id${2} from ${1} where ${0};, when inserted into editor it will look like below

• First, it will focus on the cursor after from keyword as that is placeholder ${1}. You can enter object name and click tab
• Next, it will focus cursor next to Id field. If you want to add more fields, type those fields. Otherwise, just press Tab
• Next (and finally), it will focus next to where keyword and concludes the dynamic placeholders as this is the final cursor position with value ${0}

List Query Templates

Click on the Query Templates icon (see Overview for location of the icon). When clicked, it shows the following dialog.

It shows both standard snippets and custom ones. Standard snippets cannot be edited or modified.

Add/Edit Query Template

Click on + icon in the toolbar to add new snippet or click on Pencil icon next to a custom snippet to edit it. It shows the dialog where you can enter details. The following table shows the fields shown in the dialog.

Field	Type	Description
Name	String (100)	Name of the snippet which shows up in the Content Assist list of suggestions. Keep it brief and unique.
Context	Picklist	The context where this snippet should be used. Depending on the context, suggestions are filtered.
Description	Text Area (1000)	Any description to help you understand the snippet
Active	Boolean	If Snippet is inactive, it will not be shown in the suggestions
Content	String (4000)	Content of the snippet. As outlined in Snippet Usage you can include dynamic placeholders with optional default value

Delete Query Template

You can delete the snippet by clicking on Trash icon next to custom snippet. App will delete the snippet after confirmation.

Query Variables

Query Variables enables reusing a same query with different values in Where clause. Query Variable is defined as :queryVarName, which is similar to how they are defined in SOQL queries Apex language.

caution

Note that Apex allows you to add space between : and variable name but Brobench parser engine doesn't support that. For ex., : accountId is valid in Apex SOQL but is not valid in Brobench

Each Query can contain more than one variable. For ex., select * from account where name like :name and accountnumber = :mainAccountNum

Each variable can be used multiple times ex., select * from account where name like :name or parent.name like :name

When you execute a query with variables, then a dialog will be shown to enter values. Enter the values and click Execute to run the query and show the results.

Query Vars in In clause

Query variables can be used in In clause. In such cases, the dialog will show input text area. Entered text will be split by new line, and each line is fed as single in clause value.

For example., for given query select id, name from account where accountnumber in (:accoutNums) and entered Query Var value of

123
456
789

then it will create the query select id, name from account where accountnumber in ('123', '456', '789')

Paginated Query Vars

Professional

If you are using a variable in in clause and that query values when added to a SOQL Query exceeds maximum length of soql, you can make use of Paginated Query Vars feature. This feature splits the values you enter into batch of 250 or so values, queries for records, and combines the records with id match.

To enable Paginated Query Var, turn on Paginate switch as shown below.

Query Vars History

Each query var, entered value will be persisted and automatically filled next time you open the dialog with in the same session. For non-in clause variables, it also keeps history across sessions which can be recalled by the dropdown control as shown below

Saving Editor Contents

Query editor auto saves the text you enter every once in a while as well as just before you execute any query. If you would like you can force save by clicking on the Save icon in the Query Editor

caution

If you have multiple tabs open and are editing the text in both places, it may not sync properly. If you do see the query you entered is not in the editor, the next time you open it, you can recall from the Query History