Skip to main content

Advanced Features

Master complex query techniques and advanced Query Builder capabilities.

Complex Filter Combinations​

Nested Filter Logic​

While the Query Builder uses AND logic by default, you can create complex filter combinations:

Date Range with Status Filters​

Filters:
- created_date: From "2024-01-01" To "2024-12-31"
- status: Equals "Approved"
- department: Contains "Finance"
Result: Finance department approvals in 2024

Multiple Value Comparisons​

Filters:
- amount: Greater Than 5000
- amount: Less Than 50000
- priority: Equals "High"
Result: High priority items between 5K and 50K

Advanced Aggregations​

Combining Multiple Aggregations​

You can apply multiple aggregations to different fields simultaneously:

Query: Sales Performance Analysis
Aggregations:
- Sum of order_total → Total Revenue
- Count of order_id → Number of Orders
- Average of order_total → Average Order Value
- Count Distinct of customer_id → Unique Customers
- Max of order_date → Last Order Date

Conditional Aggregations​

Use filters to create conditional aggregations:

  1. Apply base filters to segment data
  2. Add aggregations on the filtered subset
  3. Results show calculations only for matching records

Example: Quarterly Performance​

Filter: created_date From "2024-01-01" To "2024-03-31"
Aggregations:
- Sum of revenue → Q1 Total
- Average of satisfaction_score → Q1 Satisfaction

Dynamic Query Parameters​

Runtime Parameter Substitution​

Override saved query configurations via API:

Filter Override​

GET /api/query/base-report?
  filters[status][operation]=equals&
  filters[status][value]={dynamic_status}&
  filters[date][operation]=date_range&
  filters[date][from]={start_date}&
  filters[date][to]={end_date}

Aggregation Override​

GET /api/query/base-report?
  aggregates[amount]=sum&
  aggregates[quantity]=avg&
  aggregates[rating]=max

Parameter Templates​

Create reusable query templates with variable substitution:

  1. Save base query with default values
  2. Override parameters at runtime
  3. Single query serves multiple use cases

Working with Complex Data Types​

Array and List Fields​

For fields containing multiple values:

Count Operations​

  • Count: Number of items in the array
  • Count Distinct: Number of unique items

Contains Operations​

  • Check if array contains specific value
  • Filter records with specific array elements

Nested Object Fields​

For form fields with nested structures:

  1. Access nested properties using dot notation
  2. Apply filters on nested values
  3. Aggregate nested numeric fields

Example: Address Field​

Field: address (object)
Properties:
- address.street
- address.city
- address.postal_code

Filter: address.city Equals "New York"

Query Optimization​

Performance Best Practices​

1. Index Utilization​

  • Filter on indexed fields first
  • Use exact matches when possible
  • Avoid complex string operations

2. Data Volume Management​

  • Apply restrictive filters early
  • Limit field selection
  • Use pagination for large results

3. Aggregation Efficiency​

  • Aggregate after filtering
  • Avoid unnecessary calculations
  • Cache aggregation results

Query Performance Monitoring​

Monitor query performance metrics:

{
  "query_metrics": {
    "execution_time": 145,
    "records_scanned": 10000,
    "records_matched": 150,
    "cache_hit": false
  }
}

Export and Integration​

Export Formats​

Export query results in multiple formats:

CSV Export​

GET /api/query/{query-name}/export?format=csv

Excel Export​

GET /api/query/{query-name}/export?format=xlsx

JSON Export​

GET /api/query/{query-name}/export?format=json

Webhook Integration​

Configure webhooks for query result changes:

  1. Set up webhook endpoint
  2. Configure trigger conditions
  3. Receive notifications on data changes

Webhook Configuration​

{
  "webhook_url": "https://your-system.com/webhook",
  "trigger": "data_change",
  "query": "critical-alerts",
  "frequency": "real-time"
}

Scheduled Queries​

Setting Up Scheduled Execution​

Configure queries to run automatically:

  1. Navigate to query settings
  2. Enable "Scheduled Execution"
  3. Set schedule parameters:
    • Frequency (daily, weekly, monthly)
    • Time of execution
    • Time zone
    • Output destination

Schedule Examples​

Daily Report​

Schedule: Daily at 9:00 AM EST
Output: Email to team@company.com
Format: Excel attachment

Weekly Summary​

Schedule: Every Monday at 8:00 AM
Output: Save to shared folder
Format: CSV file

Query Versioning​

Version Management​

Track changes to queries over time:

  1. Each save creates a new version
  2. View version history
  3. Compare versions
  4. Rollback to previous versions

Version Control Features​

  • Change Tracking: See what changed between versions
  • Audit Trail: Track who made changes and when
  • Rollback: Restore previous query configurations
  • Branch Management: Create variations of queries

Cross-Workflow Queries​

Joining Data from Multiple Workflows​

Advanced feature for combining data across workflows:

  1. Select primary workflow
  2. Add related workflows
  3. Define join conditions
  4. Select fields from all workflows

Example: Order and Customer Data​

Primary: Order Workflow
Related: Customer Workflow
Join: order.customer_id = customer.id
Fields: order.*, customer.name, customer.email

Query Templates​

Creating Reusable Templates​

Build template queries for common use cases:

  1. Create base query with placeholders
  2. Save as template
  3. Generate instances with different parameters
  4. Share templates across teams

Template Examples​

Monthly Report Template​

Name: monthly-report-template
Placeholders:
- {month}: Target month
- {department}: Department filter
- {status}: Status filter
Usage: Generate department-specific monthly reports

Advanced Security​

Row-Level Security​

Configure data access at the row level:

  1. Define security rules
  2. Apply user-based filters automatically
  3. Ensure data isolation

Security Rule Example​

{
  "rule": "user_department",
  "filter": {
    "field": "department",
    "operation": "equals",
    "value": "{current_user.department}"
  }
}

Field-Level Security​

Control access to specific fields:

  • Mask sensitive data
  • Exclude fields based on user role
  • Apply data transformation rules

Query Analytics​

Usage Metrics​

Track how queries are being used:

  • Execution frequency
  • Average response time
  • Most accessed queries
  • Error rates

Performance Dashboard​

Monitor query performance:

Query: monthly-sales-report
Executions (30d): 1,245
Avg Response Time: 234ms
Success Rate: 99.8%
Last Error: None

Troubleshooting Advanced Issues​

Complex Filter Debugging​

When filters don't return expected results:

  1. Test each filter individually
  2. Check for data type mismatches
  3. Verify date format consistency
  4. Review null value handling

Aggregation Accuracy​

Ensure aggregation accuracy:

  1. Verify numeric field types
  2. Check for null values in calculations
  3. Confirm aggregation scope
  4. Test with known data sets

Next Steps​