Home → 360 → Searching → Query Builder

8.3. Query Builder

If you want to build a search that is more complex than the other, more basic search options, you can build a query. The Query Builder allows for you to select a group of search clauses that will work together to target any record or transaction that matches with the criteria you specify.

You can find the Query Builder under the Search menu on both the old and new layouts. From there, follow these steps to build your search query.

Building a Search Query

1. Specify a main search record: For this step, you will need to click on the drop-down, and select which type of record you want your search results to include. There are many different options available, but the most commonly used are the various entity types, such as Individuals or Committees, and the two transaction types - receipts and disbursements.

Example: 2020 donors

• If you want a list of every individual that made a contribution to your committee in 2020, you'll need to select the Individual option. Even if a donor gave multiple times, their name would only appear in the list once, since the query is targeting individuals.
• If you want a list of every single contribution your committee received in 2020, you'll need to select the Receipt option. If you had a donor that gave twice, you would see them appear in the list twice, once for each contribution entry.

 

2. Begin adding search clauses: Once you select a main search record, you can begin specifying your search clauses. You'll see three options for each clause: a first field, an operator, and then a second field.

Example: Searching for a list of disbursements dated in 2020

• First Field: This option allows you to select certain data points, and is kind of like the broader category you want to base your search on. For this example, you'll want to select Disbursement Date.
• Operator: This option sort of functions like a connector between the two fields. For this example, we'll select On or After. This option will include the date that we select in the next step.
• Second Field: Here, you'll need to select/enter what you are basing your search off of. Since we want our search to include disbursements dated on 1/1/2020, you'll need to enter that date. If you had select the After option for the operator, then the search results would only include disbursements dated on 1/2/2020 and beyond.

If we left this query as is and ran the search, it would essentially target all disbursements dated on or after 1/1/2020.

 

3. Adding more search clauses: After setting up one search clause, you have the option of adding as many more as you would like. On the right side of the screen, you'll see the New Clause Type drop-down. Once you make a selection from that menu, you'll have the option of specifying another search clause.

Example: Searching for a list of disbursements dated in 2020, continuing the one above

• And: This option will allow you to link the new clause to the previous one, so that the query builder takes both into account when searching. This is the option you'll want to use for this search; you'll be able to create a second Disbursement Date clause to effectively create a date range:
  
  Using the and type here will search for any disbursements that are dated on or after 1/1/2020 and on or before 12/31/2020. This will result in a list of disbursements that only include those that are dated in 2020.
• Or: This option allows query builder to consider the previous clause or the new one, but not both. If we used the Or option for our example, the search would include all disbursements dated on or before 1/1/2020, OR on or before 12/31/2020, which is essentially looking for all disbursements in the system. This is why selecting the correct new clause type is essential.
• ANDREL/ORREL: Short for "And Related"/"Or Related", these options function just like the ones above, but allow you to add a search clause for a data point that does not exist for your select main record. For our example, since we are searching for disbursements, are previous search clauses are only data points that exist specifically for disbursements (i.e. disbursement date). If we wanted to add a search clause based on a different data point that does not exist for disbursements, such as something like Employer, then you'll need to use on of these options.
  
  For our example, let's say we want a list of 2020 disbursements made to individuals employed by a hypothetical "ABC Corporation". We'll need to set the disbursement date clauses as mentioned above, and then select ANDREL so we can specify the employer we want to target:
  
  The ANDREL option will allow us to specify the employer even though that field doesn't exist for our main search record of disbursements.

4. View Results: Clicking this button will run the search with all of the clauses that have been selected. From here, you'll land on the same results page as any other search, and you'll be able to add extra fields or export the results from the options on the right side of the page.

Again, you can add as many clauses as you want to make the search as specific as needed, but keep in mind that the more complex the search is, the more difficult it will be to build it. Also keep in mind that the complexity of queries can also affect how long the system takes to run the search; it can sometimes be more time efficient to build a simpler query that targets more data than you need, and then simply export the data and delete anything that isn't necessary.

 

Other Options on the Query Builder Page

Edit Result Columns: This option will allow you to select the data points that will be included once you run the query. This can also be done after running the search (from the Add/Edit Fields and Calculations option on the results page), but you can so from here before running the search.

Also, if you choose to save your query for future use, additional columns selected here will be saved as well. If you have a query that you intend to run on a regular basis, you can pre-save any extra columns you want, so they don't need to be added to your search results every time.

Save/Save As: This will allow you to save your query for future use.

Quick Selection Management: And previously saved query will appear here. You can load a saved query into the query builder by clicking the Load icon on the left side of the query's name.