Skip to main content

Best Practices

Use these recommendations to create Query Builder queries that are easier to maintain, safer to share, and more reliable for reports and integrations.

Use Stable Query Names​

The query name becomes part of the API URL and cannot be changed after saving.

Good names describe the purpose of the query:

approved-purchase-requests-q1
leave-report-by-department
monthly-budget-summary

Avoid temporary names such as test, query1, or new-report for production integrations.

Add a Useful Description​

Use the description field to explain:

  • What the query is used for
  • Which workflow it reads from
  • Important filters or aggregations
  • Which dashboard, report, or integration consumes the API endpoint

This helps other users understand whether the query is still needed before editing or deleting it.

Select Only Needed Fields​

Every selected field appears in the API response. Select only the fields required by the report, dashboard, or integration.

This keeps the response easier to inspect and reduces unnecessary processing for API consumers.

Use Date Ranges for Large Workflows​

Query Builder loads matching workflow submissions into memory and filters them in PHP. Large unfiltered queries can be slow.

For high-volume workflows, include a date range filter whenever possible:

submittedOn from_to 2025-01-01 through 2025-03-31

If an external system needs historical data, call the API in smaller date-based batches instead of requesting all records at once.

Keep Filters and Aggregates Separate​

Do not configure a filter and an aggregation on the same field. If both are configured, the aggregation takes priority and the filter is discarded when saved.

Use one field to filter and another field to aggregate when possible:

status equals Approved
amount sum

Verify Totals with the Full API​

Query preview is useful for checking response shape, but it limits results to 2 rows before calculating totals and aggregates.

Use the full API endpoint to verify final total_matches and aggregate values.

Protect API Tokens​

Query endpoints require Bearer token authentication. Any valid API token holder can call the endpoint regardless of workflow permissions.

Recommended token handling:

  • Store tokens in server-side environment variables or a secrets manager.
  • Do not place tokens in client-side JavaScript.
  • Rotate tokens when staff, vendor, or integration access changes.
  • Share tokens only with trusted systems.

Use URL Overrides Carefully​

URL filters replace all saved filters for that request. They do not merge with saved filters.

URL aggregates replace all saved aggregates for that request. They do not merge with saved aggregates.

If an integration sends URL parameters, make sure it sends the full intended filter or aggregate set.

Account for Current Field Limits​

Use API URL parameters instead of saved UI filters for:

  • is_null
  • is_not_null

Avoid relying on saved checkbox filters because Is True and Is False are not reliably saved in the current version.

For time fields, test with known submissions before using the query in production because filtering uses date-level boundaries.

Plan Around Unsupported Features​

Query Builder does not currently provide file export, pagination, group-by reporting, cross-workflow joins, scheduling, or webhooks.

Use external tools for those needs:

NeedRecommended Approach
ExportConsume the JSON API response and transform it outside OSPROV
BatchingUse date range filters and call the API in smaller periods
SchedulingSchedule API calls in the consuming system
Cross-Workflow ReportingQuery each workflow separately and combine results outside OSPROV