Apricot Results | Pre- and Re-queries

How to filter a data set with another query

Updated over a week ago

It may be necessary to limit the data being pulled into your report using another form not available in the universe used in the current query. In this case, we can set up a Pre- and Re-query using multiple queries.

In this example, we have a set of participants from a Tier 2 form (left table) that are linked to a Tier 1 form (right table) registering them with an Adventure Company. Our goal is to filter the reports to only include participants for one Adventure Company, but this Tier 1 is not a parent form of the Tier 2, and the participants will not be available in the same universe.

If we want to focus on the Turtles All The Way Down Diving company, we can see that there are 5 members registered, but our first table is showing all participants.

Step 1: Set up the Pre-Query

First set up a query that will prompt for the company name. In the Query Filter section we will create the parameters that the report will be filtered on. In our example, we want to filter by the Company Name, and it's a prompt in case we would like to look at other companies later in the report.

Make sure to include the Record ID of the Tier 1 (Adventure Company Registration), and the field that links to the members in the Tier 2 (Company Members).

Now click Run and we'll select Turtles All The Way Down Diving for our example:

Our report now includes the Adventure Company Name, Record ID, as well as the Record ID for all linked individuals in the Company Members_2076 field.

Step 2: Results from another query

In the query we are going to use for our main data set, we now need to reference our Pre-query. We need to limit the participants to only those that are linked to our company. The [Company Members_2076] field shows all of these values. Because these participant records are associated with a different Tier 1 form, they are not available in the same universe, so we need to go back into the Data Query Panel and add another query.

In our new query we need to filter to the participant record IDs that we retrieved in the pre-query. From the new query, bring [Record ID] from the Tier 2 participant table into the query filter. Press the properties menu and choose "result from another query." This will do exactly what it says and will filter this data based on values that are in a different query. In this case, we're filtering the participants from the Tier 2 table to only the values that were already retrieved in our Adventure company pre-query.

This will show you a list of all objects that are in the pre-query. We need to filter down the Participant [Record ID] values to only participants who are linked. We already have these in the [Company Members_2076] link field, so setting the filter up like this will filter to only linked participants.

Step 3: Run the report

Now when the report is run, we can compare the Record ID in the first table to the Company Member ID in the second table and they are the same. Our main data set is now being filtered by the company name chosen for the report.

Note: As an extra step for our report, we created a merge and detail to bring in the Company Name to the table from our main data set query to confirm what group they are registered to.

Learn more about merges here:

Did this answer your question?