Design and Run a Query

On This Page

• Design and Run a New Query
• Work with the Results
• Download the Results
• Other Options
• Variants
• Example: Memberships That Ended in the Last 30 Days
• If Something Goes Wrong
• Related

Use the query designer to build a list from your records. You choose the record type, the records to include, the columns to show, and the sort order. Then you run the query to see every matching record, and you can download the results if you need them outside Rhythm.

Use a query when you need a list, such as renewals due, members who lapsed last month, event registrants, contacts whose email matches a list, or record IDs for an import or mass update. If you only need one record, use search instead. Queries list records; they do not count or total them (see Query Limits and Execution Rules).

Prerequisites

• You can open the Query Center for the application whose records you need by choosing Queries in that application's left navigation.
• Your role allows you to run the query type you want. Standard types you cannot run are not shown.
• To download results, your role must also allow you to download results for that type.
• A query works within one application. Rhythm cannot pull or join data from another application into the same query, and a bulk update or mass change started from query results can only reach records available in that application's context. If you need something that spans applications, submit it as product feedback instead of trying to configure it here.

Design and Run a New Query

1. In the application, go to Queries. The Query Center opens to the view you used last.
2. Choose New Query to open the Select Query Type dialog.
3. Choose a category on the left, then choose a query type in the middle. Read About this Query Type on the right to confirm it lists the records you want; see Query Types and Field Selection.
4. If a record picker appears under the type, such as Event, search for and choose the record the query is about. If the picker is labeled (optional), you can leave it empty.
5. Choose Continue. The query designer opens with the type's default output fields and sort order, no criteria, and a preview of up to ten rows.
6. Set the criteria. Choose Edit Criteria... to open Edit Query Criteria:
   • At the top, choose All when every condition must be true, or choose Any when one matching condition is enough.
   • Choose Add Condition, then pick a field, an operator such as equals or is in list, and a value. To leave records out, add a condition with an excluding operator, such as is not in list on an email field to keep known bad addresses out of a contact query for a chapter or distribution list.
   • Choose Add Group to add a nested group with its own All or Any. To match either of two situations at once, set the top-level operator to Any and add two groups. For example, use one group for membership status Suspended with a specific membership type, and a second group for blank membership status, so the query returns organizations with either the suspended membership or no membership status.
   • For membership-specific lists, add conditions such as Membership Type is not in list to exclude certain types, Membership Start Date compared to a date to filter by when membership began, or Membership Owner Type set to Contact to avoid duplicate rows when a contact has more than one membership relationship.
   • Choose is greater than or equal to instead of is greater than when a date condition should include the boundary date. For example, is greater than on a start or renewal date excludes a record whose date exactly matches your boundary, which can leave a same-day renewal out of a weekly membership query.
   • When criteria filter by a related record's status, such as an active membership, add a separate condition for the contact's own active status. A membership-based list can still include contacts whose own contact record is inactive if you do not filter for that separately.
   • For a date that should move each time the query runs, select the alarm-clock icon (Toggle relative date) and set a relative date, such as 30 days before the query execution date. To match "today or later," use the same control next to an end-date condition and choose on the... query execution date. For a condition that should return records expiring in exactly 90 days, set an Expiration Date condition to equals and give it a relative date 90 days after the query execution date.
   • To remove a condition, select the red trash icon. When you finish, choose Save Changes.
7. Choose the output fields. Pick a field in Add a field... to add it to Selected Fields, or open Field Browser at the top, select one or more fields in its list, and choose Copy Selected Fields to Query.
8. Arrange the columns. Drag a field by its grip to reorder it. Select its pencil to rename the column heading, type the new heading, and choose Save Changes. Select × to remove it.
9. Set the order. Choose a field in Sort by: and tick Descending order? to reverse it. Choose Add Another Sort Field for a tie-breaker, up to five sort fields.
10. Check the preview on the right after each change. It shows at most ten rows, with the note Previewing a limited number of records. When you're ready, execute the query to see all of the rows.
11. Choose Run.

Result

Query Results replaces the designer. It first shows Running query... this will take a few seconds..., then the header shows <query name> - <n> records returned above a table of every matching record, with one column for each output field.

The condition editor keeps Add Condition and Add Group unavailable while any row in the group is incomplete. To replace a condition, add the new one first and then delete the old one, because Rhythm needs at least one active condition to remain in place. The order you add conditions does not limit which fields or operators are available in another row, so you can add another date condition later and keep the query reusable instead of tied to one specific year.

If a field or value you expect does not appear in a dropdown, confirm you are using a supported browser, such as Chrome, and try clearing your browser's cache. A stale cache has caused this even when the same query works elsewhere. After you add many conditions or fields, the search bar or the Add Group control can appear in an unexpected part of the screen, rather than actually being unavailable. If a later criteria row, or its dropdown, has no visible space to open while you are editing an existing query, try zooming your browser out to around 80%–90% before rebuilding the query from scratch. For every operator and how it handles blanks, dates, and lists, see Query Criteria and Filter Operators.

Tip

Use criteria for practical tasks such as monitoring event add-on uptake with a query filtered on Add On Product Name; add that field as an output column too, so the results are easier to review. You can approximate which orders may be Pay Later by combining criteria such as Amount Paid greater than zero, Balance Due equal to zero, and status, because there is no single field that marks an order as Pay Later. You can also build a Contacts query that filters Email with is in list and includes Contact ID as an output field, which turns a list of email addresses into the record IDs you need for an import. Portal login activity on a contact appears through the Portal - Last Login Date criterion, with Last Login Date and # of Logins as output fields. For payment line items tied to an event, use the Payment Line Items with Products query type and select Event or Event ID from the Order section; the standard payment line items query does not expose event fields. Sys Last Modified can help you find orders that changed recently, including orders with newly added line items, but it also returns orders modified for any other reason, so it is not a precise change-only filter. Because membership records do not carry payment status, a query that depends on payment needs to check related order or billing-run records too, and it helps to exclude canceled orders and dropped memberships from the results. A contact-count figure shown elsewhere in Rhythm, such as a dashboard widget, can include inactive contacts; when you need an active-only count, build and save a query that adds an active-contact condition.

Warning

A record's screen can show more than a query can return. Email activity shown on an individual CRM record's timeline is not available as a query field, and engagement data such as email opens is not available even when related record fields, such as a committee position, are. Rhythm also has no clean, query-only way to confirm that a credit card processing fee should have applied to a payment but did not; that comparison needs the related Stripe data. There is currently no query that combines a membership's expiration date with its last dues payment date in one result, and an event registration query cannot be combined with the sessions or functions a registrant selected. You need separate queries for those. You also cannot combine the output fields of two different queries into one report; choose the single query type whose available outputs best match what you need.

Work with the Results

On Query Results you can:

• Filter the rows on screen. Type in Filter results. A row stays on screen when any of its values contains the text. The header adds , <m> matching filter.
• Change the page size. Choose 10, 20, 50, or 100 rows per page. Rhythm remembers your choice in this browser.
• Sort on screen. Select a column heading.
• Open a record. When the query type supports it, select the icon at the start of a row to open that record in a new browser tab. Opening many result records this way in separate tabs is not a supported high-volume workflow; after the first several tabs, later ones can show a sign-in screen instead of the record.
• Act on the records in bulk. When the query type supports it, a bulk operations menu appears in the header; see Update Records in Bulk.
• Change the question. Choose Designer to return to the designer with the same question, or Start Over to go back to the Query Center's lists.

Filtering and sorting here change only what you see on screen. They do not change the query or the downloaded file.

Download the Results

1. On Query Results, choose Options.
2. Choose Excel (Formatted), Excel (Unformatted), CSV, or JSON.

Result

The button reads Downloading... while Rhythm runs the question again in that format. Then your browser opens the file. Each download runs the query again, so the file reflects the data at that moment.

Each format uses a different layout. Formatted Excel includes a title block and column headings, while unformatted Excel and CSV put field names in the first row. See Query Results: Fields, Sort, Paging, and File Formats.

For an integration that only needs new or changed records, build a saved query with a condition on the record's modified-date field, such as Sys Last Modified, set to a relative date. Then retrieve its results through the query API instead of downloading it by hand each time. The API follows the same start-and-check pattern as a long-running query in the Console: you start the question, or an existing saved query's definition, and receive an identifier, then poll for status until it finishes and download the result from the link it returns.

A complete export of your whole database is not a query, and it is not self-service. Contact Support, and Support submits it to engineering for fulfillment, typically with about a one-week turnaround. Support can also prepare a follow-up export limited to activity between a prior export and a later date; open a new request once that later date arrives.

Other Options

The Options menu also offers:

Option	What it does
Show Execution SQL	Opens View Query SQL, which shows the database statement Rhythm ran for these results and includes Copy to Clipboard. Use this technical view to diagnose results that look wrong
Refresh Query	Runs the question again and redraws the table. Text left in Filter results stays in the box, but it no longer narrows the rows. After you edit a record, rerunning or refreshing should bring the results in line with the change, and new records ordinarily appear right away too. If results still look out of date after a refresh, they can lag behind very recent changes while a background sync catches up. KPI-backed figures can take up to about an hour, and new meeting registrations can take up to about 15 minutes. Redesigning the query will not fix that, because the underlying data snapshot, not the query, is behind
Save Query	Opens Save Query so you can save the question as a new saved query. The name starts with Copy of ; change it before saving. You can also use an existing saved query as a starting template. For example, clone one built for a prior month, rename the copy for the month you need, and save it to My Organization's Queries
Schedule Delivery	Sets up a delivery schedule. This option appears only for a saved query; see Query Delivery Schedules. An automated report for "last month" works this way only when its date criteria are relative to the run date; you must update a fixed date range by hand each time
Integrate	Links the query to a mailing list. This option appears only for a saved query. There is currently no way to send an ad hoc email straight from a query's results outside of a schedule or an integration
Start Over	Returns to the Query Center's lists

In the designer, the angle-brackets icon beside Edit Criteria... opens View Query Source, a read-only JSON view of the whole question, not the database statement. Use Copy to Clipboard there to reuse or reconstruct the query's criteria. A saved query also keeps its own audit history, opened from the hashtag icon on the query. Turning on the raw view there can sometimes reveal a prior condition's value or operator after criteria change, though the trail does not always capture every changed condition.

Variants

• Open a saved query. In the Query Center, select the query's name or Run in its row menu to run it, or select Design to open it in the designer. From there, choose Edit Criteria... to change its conditions yourself later, such as excluding a specific business unit from the results. See Save a Query and Organize Folders.
• A question from another page. Some pages, such as a chapter's members panel, a KPI on an overview page, or a figure on a dashboard, send a ready-made question to the Query Center. The Query Center runs it right away and shows Query Results under that question's name. The question is not saved until you save it. When the query type supports bulk changes, the bulk operations menu is available here too, whether the list came from a fresh question, a saved query, or one of these ready-made questions.
• Results shown inside another page. Some pages show a query's answer in place, with Filter results and paging but no way to change, save, or download the question. This is different from adding a query to a page on the portal, which Rhythm does not support; query results also do not support JavaScript customization.

Example: Memberships That Ended in the Last 30 Days

Field names vary by query type, so use the ones your type offers.

1. In Membership, go to Queries and choose New Query.
2. Choose a membership query type, such as Memberships - All, and choose Continue.
3. In Edit Query Criteria, keep All and add two conditions on the membership's end date. Set the first to is greater than or equal to a relative date 30 days before the query execution date, and set the second to is less than a relative date on the query execution date. Add any other condition you need, such as the membership's status.
4. Add the contact's name and email address as output fields, then choose Run.

Using is greater than or equal to for the start of the range, instead of is greater than, makes sure a membership that ended exactly on the boundary date is not excluded. Because both dates are relative, the query covers the 30 days before each run. That makes it a good candidate to save and schedule.

If Something Goes Wrong

What you see	What it means and what to do
Access Denied: You do not have the required permissions to run query <query type> . Please check with your system administrator.	Your role cannot run this query type, so the question stays in the designer. Ask your administrator
Access Denied when downloading	Your role cannot download results for this type. Ask your administrator
No accessible queries	Your role cannot run any of this application's standard query types
Unable to describe query, with the designer stuck on Loading query definition...	Rhythm could not load the query type. Reload the page. For a saved query, its type may no longer exist
Unable to preview query, sometimes shown as [object Object]	The preview failed. When the message reads [object Object], it is only a generic wrapper around an underlying server or database error, not a description of the cause. Either way, the preview stops updating until you reopen the designer. If you want to keep the question, save it first, then choose Start Over and open it again
Unable to execute query, with Running query... still showing	The run failed, and the message explains why. Reload the page and correct the criteria
Running query... this will take a few seconds... never finishes	Some problems stop a run without an error, such as a range comparison like is greater than on a text field. Reload the page and check the criteria
No rows were returned from the preview.	Nothing matched. Check the All or Any choice and each condition
Unable to Execute Query on a saved query that used to run, with some fields showing blank in the designer	A field or value the query relies on may no longer resolve, often because it was renamed or removed. Open the query in the designer and reselect the affected fields
An event invoice you expect from an invoice query filtered by Event ID is missing	Filtering an invoice query by Event ID has, in some cases, left out a newly processed event invoice even though the related registration appears elsewhere. Try the query again after a short wait, or check for the invoice another way, and contact Support if it persists
A record you expect to see is missing, even though it looks correct on its own record screen	Query results can lag behind very recent changes while a background refresh completes, sometimes affecting orders, memberships, or section and affiliate records. Rebuilding the query with the same criteria does not fix this, because the underlying data, not the query design, is out of sync. Making and saving a small, harmless edit to the record, or to its related invoice for a missing payment amount, can prompt Rhythm to reprocess it sooner. If a backend change ever appears to have altered how an existing query's criteria are read, reopen Edit Criteria... to check the criteria as currently saved before assuming the query itself is broken. If the record still does not appear after a reasonable wait, contact Support with the application, or Query Center, you are using and the exact name of the query
The same saved query fails for you but works for a colleague, or works when you sign in from another machine	This points to your browser or local machine rather than a permissions problem. Clear your browser's cache, or try another browser, before escalating further
The designer misbehaves intermittently and you cannot reproduce it reliably	Capture your browser's Inspector Console, not just Elements, and, if you can, a short screen recording with the console open. Also try clearing your browser's cache, another browser, and a different browser zoom level. This evidence helps Support diagnose issues that do not reproduce consistently

If you contact Support because a specific record does not appear in a query, tell them which application, or Query Center, you are using and the exact name of the query. Support needs both to investigate.

Rhythm does not prompt you before you leave the designer or the results. Start Over, the left navigation, the browser's back button, and reloading all discard a question you have not saved.

Related

• Query Criteria and Filter Operators
• Query Types and Field Selection
• Save a Query and Organize Folders
• Query Results: Fields, Sort, Paging, and File Formats
• Update Records in Bulk