Orders & Inventory includes ten ready-made query types in the Query Center. Each one starts from a different part of your orders data, including orders, purchases, coupon codes, cancellations, returns, shipments, entitlements, merchandise, and stock. Use this guide to choose the right starting point before you build a report.
Prerequisites
- You need access to Orders & Inventory queries in your Rhythm account to open the Query Center and run these query types.
The Ten Query Types
| Query type | One row is | What it brings in |
|---|---|---|
| Orders | One order | The billing and shipping contact or organization, the batch, and any installment schedule. If an order does not have one of these related records, such as a separate shipping contact, the order still appears, and those fields stay blank |
| Purchases (Order Line Items) | One line on one order | The product, its categories, and the order's buyer. If you run this query without choosing a specific merchandise item, it returns every purchase across every product. If you run it for one merchandise item, it also includes that item's variant option columns |
| Coupon Code Usages | One coupon code used on one order | The order's total. Each row includes the order's total, so summing that column over-counts any order that used more than one coupon code |
| Coupon Codes | One issued coupon code | The contact or organization who owns the code. A code with no owner still appears |
| Cancellations | One cancellation | The order it cancelled |
| Returns | One return | The order it returned |
| Shipments | One shipment | The order it shipped, plus the destination and tracking number |
| Entitlements | One entitlement | The contact or organization who holds it, and the order that granted it. Only the columns for the entitlement's actual owner type are filled in. A contact-owned entitlement leaves the organization columns blank, and an organization-owned entitlement leaves the contact columns blank |
| Merchandise | One merchandise item | Its category names, combined into one field. Merchandise has no related-record groups beyond its categories, so category filtering and reporting use a text match against those names |
| Product Inventories | One product's stock at one warehouse location | Although this query type is grouped under "Merchandise" in the Query Center, it includes any product type that tracks inventory, not only merchandise |
A few fields need context so you read them correctly. In the Orders query, Payment Method reflects the method on the order when it was created. If an order was set up to pay later and payment was collected afterward, this field still shows None even though a payment now exists. The Orders query also includes coupons used on an order: Order Coupon(s) and Order Coupon Code(s) return every coupon name or code used on that order as a comma-separated list, so you do not need a separate coupon query just to see what was applied to one order. If you need installment schedule details in the context of orders, start from the Orders query and add fields from the related Installment Schedule object, rather than starting from an installment-focused query and expecting order-scoped fields to populate there.
Orders & Inventory queries return only Orders & Inventory data. They cannot include information from other applications, such as membership records, in the same query. If you need both together, build a separate query and combine the results outside Rhythm. To report on paid memberships for a custom fiscal window instead of the membership's own dates, build the report from an Orders query. Filter the order date to your window, then use completion and balance fields, such as a completed status and a zero balance due, to identify the orders that were actually paid.
Order IDs also tell you something about a record's origin. A Rhythm-generated order ID looks like an ORD-prefixed value, while a long, mixed-character ID usually belongs to an imported record.
A changed order returns zero for some query functions, including tax amounts, even when the order itself is not empty. If a changed order's totals look wrong in a report, check the order directly rather than assuming the query is filtering incorrectly.
There is no query that reports a product's stock as of an arbitrary date in the past. If you need a historical snapshot, run or schedule the Product Inventories query on the day you need it and keep the results. Orders & Inventory does not reconstruct a past quantity for you afterward.
If you need a report on purchases created by a later change to an order, such as items added through an event registration modification, query them from Orders & Inventory. Do not rely on the original registration's own query, because the later change is recorded as an Orders & Inventory transaction.
Reporting on What Was Actually Bought
If you want to know which products were purchased, on which orders, and by whom, start with Purchases (Order Line Items) instead of Orders. The order-level query describes the order as a whole, including its buyer, batch, and shipping details. The purchases query gives you one row per product line, with the product's name and categories available by default. You can also add fields such as the order identifier and status. To report on categories of items sold, start from Purchases, because Orders does not include product or category information to join.
On this query type, Amount is the price for the individual line, while Order Amount is the total price of the whole order. Use Amount when you want each product's own contribution, not the order it came from.
To report on sales of one specific product, filter Product Name with an equals condition for that product rather than scanning every row by eye. You can also add fields such as Order ID (Purchase), a state field, and Taxes (Purchase) alongside the product name that is already there by default. Filtering Product ID equals a target product's ID works the same way when you already know the ID and want to count how often it has been bought.
Purchases (Order Line Items) does not expose every order-level field. Order Type, for example, is not available at the line level; use Product Type instead when you need a comparable field there.
Line-item reporting gives you the amount tied to one specific item rather than the order's total, which matters when an order includes more than one similar item. For an event registrant who purchased several sessions in one order, or a sponsor who selected several benefits, use Purchases (Order Line Items). Each session's or benefit's own amount and selection appears in its own row, instead of a session- or order-level report that shows only the full order amount. The same approach works for add-on selections attached to an event registration: build that report from Purchases (Order Line Items), not from a higher-level order or registration report.
To build a dated sales report, such as everything sold in a bookstore over a range with item name, price, purchaser name, purchase date, and whether the order completed, start from Purchases (Order Line Items). Filter the purchase date to your range, and add the purchaser, price, and order-status fields alongside the product name.
A field that is available in a Purchases (Order Line Items) query, or visible on an order line item's own record, is not automatically available as a merge field in an invoice or order-confirmation template. Query availability and document-template availability are separate, so check the template's own merge-field list before assuming a field will appear there too.
What a Payment Method Field Actually Reflects
The Payment Method (Order) field reflects the payment option the buyer chose at checkout, not any payment captured later. If a buyer chose a pay-later option, this field shows None on that order even if a credit-card payment is recorded against it afterward.
Coupon Reporting: Two Different Questions
Coupon Codes and Coupon Code Usages answer different questions, and choosing the wrong one is the most common coupon reporting mistake. Coupon Codes lists issued codes, with one row per code and its owner, whether or not the code has ever been used. Coupon Code Usages lists actual redemptions, with one row for each coupon applied to an order. Start there when you want to see which orders redeemed a code, or how much discount a code has produced in total. Just do not sum the order-total column across multiple coupons on the same order.
To show the discount amount and which product it applied to, add the Discount field for the amount deducted, and Order Line Item and Number fields to identify the discounted product and its order.
A coupon's usage count on its own record is higher than a payment-based report for the same coupon when one or more redemptions fully discounted an order to zero. Coupon Code Usages counts every redemption, but a payments query only includes orders that produced a payment record. An order a coupon fully discounts to zero never creates one, so it will not appear in payment-based totals even though the coupon was used.
To review which orders redeemed a specific code, filter Coupon Code Usages by that coupon or discount code and include the order and discount fields above. This gives you a working list of affected transactions without waiting on any other tool.
Tip
Two similarly named coupon-related fields can hold different values: one is an internal identifier, and another is the human-readable coupon code. If a field returns unexpected characters instead of the code you recognize, try the coupon code ID field instead of a generically named coupon field.
Going from a Merchandise Row to the Item Itself
Only the Merchandise query type lets you open a result row directly into that item's record page. Rows in the other Orders & Inventory query types do not open the related record this way.
Related
For general guidance on choosing a query type, filtering, and running a report, see Design and Run a Query and Query Types and Field Selection. These starting points also feed the dashboards described in The Orders & Inventory Reports Page.