Skip to main content

Building Queries

Learn how to create powerful queries using filters, aggregations, and field selections.

Selecting Data Fields​

Understanding Field Types​

When you select a workflow, all available form fields are displayed with their data types:

  • Text Fields: String values (names, descriptions, etc.)
  • Number Fields: Numeric values (amounts, quantities, etc.)
  • Date Fields: Date and time values
  • Select Fields: Dropdown or choice values
  • File Fields: File upload references
  • Checkbox Fields: Boolean values

Field Selection Best Practices​

  1. Select Only Required Fields: Choose only the fields you need to minimize data transfer
  2. Consider Field Dependencies: Some fields may be related; include all relevant fields
  3. Check Field Availability: Ensure fields exist in all workflow versions

Working with Filters​

Filters allow you to narrow down which submissions are included in your query results.

Filter Operations​

Text Operations​

  • Equals: Exact match (case-insensitive)
  • Contains: Partial text match
  • Starts With: Text begins with specified value
  • Ends With: Text ends with specified value
  • Is Null: Field has no value
  • Is Not Null: Field has a value

Numeric Operations​

  • Equals: Exact numeric match
  • Greater Than: Values above threshold
  • Less Than: Values below threshold
  • Greater Than or Equal: Values at or above threshold
  • Less Than or Equal: Values at or below threshold
  • Between: Values within a range

Date Operations​

  • Date Range (From/To): Filter by date range
  • Before: Dates before specified date
  • After: Dates after specified date
  • Today: Current date only
  • This Week/Month/Year: Current period

Combining Multiple Filters​

Multiple filters work with AND logic by default:

Filter 1: status equals "Approved"
AND
Filter 2: amount greater than 1000
Result: Only approved submissions with amount > 1000

Filter Examples​

Example 1: Recent High-Value Submissions​

- created_date: From "2024-01-01" To "2024-12-31"
- amount: Greater Than 5000
- status: Equals "Completed"

Example 2: Pending Reviews by Department​

- department: Contains "Finance"
- status: Equals "Pending Review"
- assigned_to: Is Not Null

Example 3: Overdue Tasks​

- due_date: Less Than [Today]
- status: Not Equals "Completed"
- priority: Equals "High"

Using Aggregations​

Aggregations perform calculations on your filtered data.

Available Aggregation Functions​

Sum​

Adds up all numeric values in a field

  • Use for: Total amounts, quantities, hours

Average​

Calculates the mean of numeric values

  • Use for: Average ratings, scores, times

Count​

Counts the number of records

  • Use for: Total submissions, unique items

Count Distinct​

Counts unique values only

  • Use for: Unique customers, categories

Min​

Finds the minimum value

  • Use for: Earliest date, lowest price

Max​

Finds the maximum value

  • Use for: Latest date, highest score

Aggregation Examples​

Example 1: Sales Metrics​

Aggregations:
- Sum of order_total → Total Revenue
- Count of order_id → Number of Orders
- Average of order_total → Average Order Value

Example 2: Performance Metrics​

Aggregations:
- Average of rating → Overall Rating
- Count of feedback_id → Total Feedback
- Max of response_time → Longest Response

Query Preview​

Using the Preview Feature​

  1. After configuring fields, filters, and aggregations, click Preview Results
  2. The preview shows:
    • First 10 matching records
    • Applied filters summary
    • Aggregation results
    • Total record count

Interpreting Preview Results​

{
  "data": [
    {
      "submission_id": "REQ-2024-001",
      "requester_name": {
        "value": "John Doe",
        "type": "textfield"
      },
      "amount": {
        "value": 5000,
        "type": "number"
      }
    }
  ],
  "total_matches": 150,
  "aggregate": {
    "total_amount": 750000,
    "avg_amount": 5000
  }
}

Preview Limitations​

  • Shows maximum of 10 records
  • May not reflect real-time data changes
  • Aggregations are calculated on full dataset

Saving Your Query​

Naming Conventions​

Use descriptive, consistent names:

  • ✅ monthly-sales-report-2024
  • ✅ pending-approvals-finance
  • ❌ query1
  • ❌ test

Query Description​

Include:

  • Purpose of the query
  • Key filters applied
  • Expected update frequency
  • Target audience

Example Description​

Generates monthly sales report for Q1 2024.
Filters: Status = Completed, Date Range = Jan-Mar 2024
Updates: Monthly
Used by: Sales Team Dashboard

Query Validation​

Before saving, the system validates:

  1. Field Existence: All selected fields must exist
  2. Filter Logic: Filter values must match field types
  3. Aggregation Compatibility: Aggregations must use numeric fields
  4. Query Name: Must be unique and follow naming rules
  5. Permissions: User must have access to the workflow

Next Steps​