In my humble opinion, one of the coolest tools in the Raiser’s Edge is the merge queries tool. Using that tool, you can take 2 existing queries and merge them, using 1 of 4 operators, to create a 3rd query. One of the coolest operators to use with this cool tool is XOR.
Fear not, my dears, for you know I shall put it into words that make sense to you. 🙂
When you merge queries, think of it as taking 2 parent queries and creating a child! The query created as a result of the merge is the child of the 2 parents!
Why would I ever do that, you ask?
- Well, sometimes you have 2 existing queries that you can merge to learn some new piece of information (who have we mailed to only once this year, using the queries from your 2 previous mailings as parents.).
- Sometimes, your boss gives you so many conditions that should (or should not) be met that you’ll never wrap your head around it in one query! It would just easier to break it down into 2 smaller groups (parents), and then merge them to create your final group (child).
So, what will the cool child created look like using this cool XOR operator with my 2 existing cool parents ? In the previous example about not mailing to anyone we’ve already mailed to twice this year, the child (merged query) created with the XOR operator (using our previous mailing queries as parents) would include anyone we mailed to in either of the previous mailings, but no one we’ve mailed to twice. The child includes:
Any records in either parent (Blue or Yellow sections)
No records in both parents (Green section)
Graphically, it looks like this:
It’s the opposite of AND, which would include only the records in BOTH parents, or the people we’ve mailed to twice (Green section).
You should know some important rules about merging queries, regardless of the operator you use:
- Parent queries must be of the same type (Constituent to Constituent, Individual to Individual, Gift to Gift, etc.)
- Parents may be of different formats (static to dynamic)
- The child (merged) query is ALWAYS static
- A static parent that needs to be refreshed must be refreshed before the child (a dynamic parent will refresh automatically when the child is refreshed)
I’m often asked if you can merge a merged query. Well, of course, you can! After all, having once been a child doesn’t preclude you from being a parent, does it? 😉
The other merge operators are equally as powerful, but those are a topic for another post. I chose XOR not only for its cool factor, but also because it is the least understood by most people.
I would LOVE to hear examples of how you do or could use XOR in your Raiser’s Edge database 🙂
Keeping you on the Leading Edge,