Guide Categories

Advanced Queries

Use powerful filtering, sorting, and grouping capabilities to analyze your business data effectively.

Overview

Advanced Queries allow you to filter, sort, and group your data to create custom views and extract specific information from your business records. This powerful feature helps you analyze trends, identify patterns, and generate targeted reports.

Accessing Advanced Queries

To use Advanced Queries:

  1. Navigate to any tab with tabular data (Customers, Sales Invoices, etc.)
  2. Click the Advanced Query button or menu option
  3. Build your query using the available options
  4. Click Apply to run the query

Query Components

SELECT (Choose Columns)

Select which columns to display in your results:

  • Choose from all available fields
  • Include calculated fields where applicable
  • Customize column order

WHERE (Filter Conditions)

Set conditions to filter which records are included:

  • Date ranges (e.g., "Date is between Jan 1 and Mar 31")
  • Amount conditions (e.g., "Amount is greater than 1000")
  • Text matching (e.g., "Customer name contains 'Smith'")
  • Status filters (e.g., "Status is 'Unpaid'")

GROUP BY (Group Results)

Group records by common values:

  • Group by customer, supplier, or product
  • Group by time periods (month, quarter, year)
  • Group by categories or custom fields

ORDER BY (Sort Results)

Sort your results in ascending or descending order:

  • Sort by date, amount, or name
  • Multiple sort criteria supported
  • Ascending or descending options

Common Query Examples

Overdue Invoices

Find all unpaid invoices past their due date:

WHERE Status = 'Unpaid' AND Due Date < Today

Top Customers by Revenue

Show customers grouped by total sales amount:

SELECT Customer, SUM(Amount) GROUP BY Customer ORDER BY Amount DESC

Monthly Sales Summary

Group sales by month for trend analysis:

SELECT MONTH(Date), SUM(Amount) GROUP BY MONTH(Date) ORDER BY Date

Tips for Effective Queries

  • Start with simple queries and gradually add complexity
  • Use date ranges to improve query performance
  • Save commonly used queries to avoid rebuilding them
  • Combine with column customization for optimal views
  • Export results for deeper analysis in spreadsheet applications
  • Use grouping to identify trends and patterns

Pro Tip

Advanced Queries work seamlessly with the Copy to Clipboard feature, allowing you to export filtered and organized data directly to external applications.
Batch OperationsCopy to Clipboard