Applies to: Version 5.0
For Version 4.0, please click here to download PDF instructions.
PastPerfect’s Query Builder provides a way to create multiple-line filters statements that can broaden or fine tune search results. Each filter statement line will connect to the adjoining statement line with either an “AND” or “OR” connector. The “AND” connector narrows your results, while the “OR” connector will broaden the results that appear.
When more than two lines are needed for your filter statement, there is a chance where the AND/OR connectors are used in the same filter statement. When this happens, you may find that certain lines should be grouped together to generate the correct search results. To group multiple lines together, you often need to manually modify the Boolean language to add parentheses around the selected filter statement lines.
Parentheses should always be added in a set consisting of one open parentheses “(“ and one closed parentheses “)”. The parentheses should go around the statements that you want to group together, essentially creating one large statement to compare to the remaining statements that appear outside of the parentheses.
Here are some examples to help demonstrate how to use parentheses to create a compound filter statement with both “AND” as well as “OR” connectors.
Example 1
Goal: Run a query for all records cataloged after January 1, 2003 and cataloged by either Mary Parr or Pamela Meyers.
Begin with the filter statement “Catalog date | is greater than or equal to | 01/01/2003.” This filter statement will give you all records cataloged on or after January 1, 2003.
Next, we need to add the statements to narrow down to only records cataloged by Pam Meyers and Mary Parr. Create the next filter statement of “Cataloged by | Begins with | Meyers, Pamela”, select the “AND” connector, and then click the “Add to Statement” button. Then create the third statement line of “Cataloged by | Begins with | Parr, Mary L.”. Select the “OR” connector, then click the “Add to Statement” button again to add the last line to the filter statement box.
As this filter statement currently stands it will not give us the results we want. Without the parentheses the statement will return any records that were cataloged after January 1, 2003 by Pam Meyers as well as records that were cataloged by Mary Parr regardless of date.
To narrow our results to ONLY include records cataloged by Mary Parr or Pam Meyers since January 1, 2003, click on the “View or Modify Filter Statement Syntax” button. This will bring up a new window with the Boolean-style version of the filter statement.
To manually add the parentheses, click the “Edit” button at the bottom right of the Filter Statement screen.
For this example, we will put the open parentheses at the start of the second line and the closed parentheses at the end of the third line. There may be times when you put parentheses next to existing parentheses. This is OK.
Once you have added the parentheses, click “Save” and “Exit” to return to the Query window. Your filter statement will look different than before; it will now show the Boolean language and indicate that the statement was modified manually.
Now when you run the query with the parentheses, it will return records that were cataloged after January 1, 2003 by either Mary Parr or Pamela Meyers.
Example 2
Goal: Create a query for all contacts who live in either Perfectown or Pastville, Pennsylvania.
Begin your filter statement with “City | begins with | Perfectown” as our first statement line. Next, add the second statement line of “City | begins with | Pastville”, using the OR connector. This filter statement should give you all Perfectown and Pastville residents, regardless of state.
In our search results, notice we have a contact that resides in Perfectown, Hawaii.
Since we want only residents who live in Pennsylvania, add a new filter statement line to include “State | begins with | PA” connected with the “AND” connector.
To make sure that our Pastville and Perfectown residents are only from Pennyslvania, we need to add parentheses around the “City” filter statement lines. To add the parentheses, click the View or Modify Filter Statement Syntax button. Next, click the “Edit” at the bottom of the Filter Statement window, and add parentheses to surround the first two filter statement lines.
Once the parentheses are added, click the “Save” button, then click “Exit” and run the query again.
When you run the query with the added parentheses, you will no longer see the contact residing in Hawaii: