Query

Note: Query is a work in progress as we move features from database view to web view. To track details and features, review Query in Web View.

Use Query to search for records that meet certain criteria. For example, you can use a query to see total budgets of all expense accounts.

While most of your queries are visible in web view, you may not be able to view, edit, or run a query if you don’t have permission to view them or support for the query is coming in a future release.

Note: Some queries don't support View, Edit, or Run and are only visible in the list of queries. Others, such as query lists, will not be available in web view.

To access Query, select it from the following:

  • General ledger

  • Payables

  • Treasury

  • Fixed assets

  • Receivables

Note: The availability of some options, such as type and category, depend on which area you access Query. For example, if you access Query from Payables, the Payment and Deposit types appear for you to select.

To quickly locate the query you want by name, use search. You can also select to filter queries by category, type, or format or view only merged queries or queries you created .

Note: Don't see this yet? No need to worry. The ability to add queries will be made available to you in waves.

  1. From the list of queries, select New query.

  2. Under Query type, select the types of fields and records to include in your query. For more information, see Type.

  3. Under Query format, select to create a static or dynamic query to determine which records are included when you run your query. For more information, see Format.

  4. Select OK.

  5. In the query editor, select criteria fields, operators, and the sort order of your query’s output fields to customize your query. For more information, see Query Editor.

  6. Select Save or Save and close.

  7. Enter a query name and description, make changes to the query format or category, and select to allow others to run or modify the query.

  8. Select Save.

To add or remove records from one query to another, you can merge queries into a third static query.

  1. From the list of queries, select New merge query.

  2. Under Query 1 and Query 2, search for and select a primary and secondary query to merge.

    Tip: To search for a query, select the search icon. From Query search, quickly find a query by name. To filter search results, select to filter by query format, only query favorites, queries you created, or queries created by a specific user.

  3. Select merge operators to determine how to combine them. For more information, see Merge Operators.

  4. Select OK.

Note: Don't see this yet? No need to worry. The ability to edit queries will be made available to you in waves.

If you have rights to edit a query, Edit is available in the query menu, and you can select its name from the list of queries to open the query editor.

For more information, see Query Editor.

If Run is available from the query menu, you can select it to open the Results tab of the query editor. This allows you to run the query, but you won't be able to view other details.

Note: For queries that support Run, you can also select to export query results to a CSV file.

Note: When you export queries from the query list, exports run asynchronously in the background.

To export query results to a CSV file, select Export from the query menu.

To delete a query, select Delete from the query menu.

Query categories help organize queries you create. For example, queries can be grouped by user or by how you intend to use the query.

To assign a category to a query, from the query’s menu, select Edit category.

To manage query categories, select Edit categories from the list of queries. Select New category to create a new category or select the arrow next to the query and drag and drop categories to reorganize how they appear in the Category drop down list.

Tip: You can select a category from the Category drop down list to filter the list of queries to only queries in that category.

To rename or delete a query category, select the category’s menu.

Each query only includes one query type, and the fields and records included in the query are only those related to that record type.

Query format determines the records included when you run the query:

  • Static queries – A “snapshot” of your records when the query is run. When you save a static query, it searches and returns records that match the specified criteria. Any records added or updated after you created the static query are not included.

  • Dynamic queries – The query automatically refreshes each time you run it. This is helpful if you routinely run queries to view the latest results.

To filter the list of queries to your favorites, select Favorites only.

Tip: To manage your favorite queries, select Add to Favorites or Remove from Favorites from the query’s menu.

To filter the list of queries to the most recently accessed queries, select Recent queries only.

Note: When you filter to only recent queries, only the 20 most recently accessed queries appear in the list. All other query list filters are disabled

Select the gear icon to choose whether to display code table entries by their short descriptions or long descriptions. By default, code table entries display by their long descriptions.