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 .

Tip: Under Show, select to show queries by their level of support. For more information, see Show.

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.

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.

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

To export query results to a CSV file, select Export 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.

From Show, select the queries you want to appear in the list. You can filter the list to the following:

  • Supports view – Queries you can view.

  • Supports edit – Queries you can edit.

  • Supports run – Queries you can run and export.

  • Fully supported – Queries you can view, edit, and run.

  • Currently unsupported – Queries that aren't currently supported.

    Note: Support for more queries is coming in future releases.

Tip: To show all queries, leave this field blank.