The Query Editor

With the Query Editor, you can create complex, reusable data queries across one or more tables. This allows you to link, calculate, and analyze data exactly according to your needs.

Step 1: General Information

• Query Name: A descriptive name under which the query will appear in the menus (e.g., "Sales per Customer").
• Main Table: The central table from which your query originates. In most cases, this is the table containing the primary records (e.g., "Invoices").
• Description: A brief explanation of what this query does.

Tip: Select the main table first. Only then will the other steps of the editor appear.

Step 2: Linking Tables (JOINs)

A JOIN is like a bridge between two tables. You need a join if you want to display data from another table that is related to your main table.

• Example: You have an "Invoices" table and a "Customers" table. To display the customer's name in your invoice analysis, you need to link the two tables.

• JOIN Table: Select the table you want to connect (e.g., "Customers").

• ON (Condition): Define how the tables are related. The system automatically suggests connections based on "Lookup" fields. A typical condition is Invoices.customer_id = Customers.id.

• Type:

  • LEFT JOIN (Default): Displays all records from the left table (main table), even if there is no match in the right table.
  • INNER JOIN: Displays only records for which there is a match in both tables.

Step 3: Defining Result Columns

Here, you specify what information should appear in your analysis. Each row corresponds to a column in the final result.

• Source: Select the table from which the field originates (either your main table or one of the joined tables).
• Field: Select the specific field (e.g., "Invoice Number", "Amount", "Customer Name").
• Alias (Display Name): An alias is a custom name for a column. If you leave this blank, the normal field name will be used. An alias is particularly useful when using formulas (e.g., alias "Gross Price" for the formula {net} * 1.19).
• Formula: Here, you can perform calculations. Use curly braces {} for field names from the same record. Example: {price} * {quantity}.

Step 4: Filters (WHERE Conditions)

Filters allow you to restrict the result set. Only records that match all filter conditions will be displayed.

• Example: You want to display only invoices from the current year.
  • Field: Invoices.invoice_date
  • Operator: >= (greater than or equal to)
  • Value: 2024-01-01

Step 5: Sorting (ORDER BY)

Define the order in which the results should be displayed. You can add multiple sorting criteria.

• Example: Sort first by invoice date (descending) and then by invoice number (ascending).

Step 6: Grouping (GROUP BY)

Grouping combines multiple rows into a single row. This is the basis for aggregations like calculating sums or averages.

• Example: You want to determine the sum of all invoice amounts per customer.
  1. Grouping: Select the Customers.customer_name field as the grouping field.
  2. Result Columns:
     • Define a column for the customer name (Customers.customer_name).
     • Define a second column with the formula SUM({invoice_amount}) and give it the alias "Total Sales".

The result would be a list of each customer and their total sales.

Step 7: SQL Preview

Click Generate Preview to see the SQL statement that is created from your definitions. This is a useful tool for debugging and understanding the query logic.