You can query on trip expenses to review travel costs after a trip, compare expenses by trip component, or calculate cost per attendee. Expenses may be associated with trips, stops, forms, events, interviews, and Scheduler slots, so the query structure depends on the level of detail you need.
Use the Expense base when you need one row per expense. Use the Trip base when you need one row per trip with aggregate totals.
⭐ Get Inspired
This article was adapted from a post by Technolutions staff in the Slate Community Forums' Get Inspired space. Have a great idea for a Get Inspired post? Let us know!
Review expense data on the trip
The trip's Expenses tab shows expenses associated with the trip and related records. The Record column indicates whether an expense is associated with the trip, a stop, a Scheduler appointment, or an event.
💡 Tip
If you only need a trip expense report for review or reimbursement, use Print Expenses from the trip. Build a query when you need filtering, reporting, totals across trips, or ROI-style calculations.
Build a trip expense summary query
Go to Queries / Reports.
Create a Configurable Joins query on the Trip base.
Add filters to return the relevant trip, trip folder, date range, or user.
Add exports such as trip name, start date, stop date, assigned user, and folder.
Export trip activity details
Add subquery exports for the trip components you want to review alongside expenses.
Flights and stops
Add a subquery export.
Join to Trip Stops.
Filter by stop type, such as Flight, when you need one type of stop.
Add exports for the stop details you need.
Events and Scheduler appointments
Add a subquery export.
Join to Forms.
Use the form or event type when you need to separate events from Scheduler appointments.
Add exports such as title, type, start date, location, registrant count, or assigned user.
Calculate trip expense totals
Expenses associated directly with the trip
Add a subquery export.
Join to Expenses.
Set Output to Aggregate.
Set Aggregate to Sum.
Export the expense amount.
Expenses associated with trip events or Scheduler appointments
Add a subquery export.
Join to Forms.
From Forms, join to Expenses.
Set the output to an aggregate sum of the expense amount.
Expenses associated with trip stops
If your database does not expose a direct join from Trip Stops to Expenses in the needed context, use an independent Expense subquery and a nested filter that compares the expense record reference to the trip stop GUID.
Add a subquery export.
Set Type to Independent Subquery.
Set Category to Related and Base to Expense.
Set Output to Aggregate and Aggregate to Sum.
Export the expense amount.
Select Display SQL and identify the expense table alias, such as
exp_....Reopen the subquery export and add a nested subquery filter joined to Trip Stops.
Export Trip Stops GUID, set the aggregate to Formula, and compare the trip stop GUID to the expense record reference.
@Trip-Stops-GUID = expensealias.[record]
📝 Note
Table aliases from Display SQL are generated by the query. If you rebuild the subquery, confirm the alias before relying on the formula.
Combine totals and calculate cost per attendee
To report total trip cost, create a formula subquery export that adds the relevant nested expense totals together.
To calculate cost per attendee, divide the total trip expense amount by a count of attendees for events and Scheduler slots associated with the trip.
Review the query output
Run the query and confirm that the result returns the expected trip details, expense totals, and cost calculations. Compare the query totals against the trip's Expenses tab or printed expense report before using the results for analysis.
