When writing a query, you do not have the capability to search for something that ‘is not there’, i.e. find a record that doesn’t have a particular type card. Since each constituent record contains cards representing information about that Constituent, be it gift, event, contact, proposal, etc. There is no ‘card’ that signifies an entry that isn’t in the record.
If a constituent did not give to Solicitation XXXX, the Constituent record does not have a gift card that signifies ‘no gifts with Solicitation XXXX exists’. Remember you are querying on cards, not the record as a whole. When querying on a specific card type, you are asking the system to look at each card within the Constituent record for particular criteria. So when you create a query that states, "Solicitation is not equal to XXXX", you are merely telling the system to find ANY gift card within the Constituent record that doesn’t have this particular Solicitation. Again the system is not viewing the record as a whole, it is looking at individual gift cards.
Many clients then make the logical ‘leap’ that the query is asking the system to find constituent records "without the particular solicitation" – this is not what your query is asking of the system. Because of the linear logic software uses, the above statement to find "Solicitation is not equal to XXXX", results in the system accessing a Constituent’s record, looking at each individual gift card and then find ANY gift card that does not have Solicitation XXXX, so irregardless if there is a gift card with Solicitation XXX, the constituent has met the criteria. The only time this particular query logic functions properly is for Constituent records that only contain a single gift card or do not contain any gift cards at all.
How your query should be structured to return the result you want:
In order to return the correct results you have to have the system ‘tag’ the Constituent records that you don’t want returned in your query.
One option is to create a Defined Field.
In this example, a client is attempting to solicit constituents who donated to the 05, 06, 07, or 08 Collection or have joined since 6/1/2009, but ARE NOT IN THE LIST OF 09 AF July Mailing.
To create a Defined Field first go to the Full Query Screen.
Choose the "Define" button and then choose "New".
Choose "Number of Contacts Where" and then hit "Next". You are then pointed to the contact card and in this example we choose Solicitation " 09 July Mailing".
We then select "Next" and name the defined field.
Next Select "Finish".
Now we want to use the Defined Field.
In this example, I will use a Custom Constituent Export Report.
We have the first part of our query parameters, - constituents who donated to the 05, 06, 07, or 08 Collection or have joined since 6/1/2009- but need to exclude the constituents who received the 09 AF July Mailing Solicitation.
With Group 1 Highlighted, browse to the Contact Card and choose the "Define" Button.
Select, "09 AF July". Select "Use" and choose the operator "Less than" and choose 1.
Hit OK.
Following the steps above, Highlight the Group 2 Critieria and use the same defined field.
Another query option is to utilize any available Dots you have.
- First You would create a Constituent Query to find the constituents identified above. Once you have this browse list then Navigate to the top toolbar and select Global>Apply Save to All. Then from the Navigator column on the left select Add Caddy Item. Select a Dot that IS NOT IN USE and add this dot to this Basic Card. When you select Save Changes the system should return a message similar to this:
- You will accept the changes. You have now ‘tagged’ all constituents that you DO NOT want to come up in your query.
- You would then build your query to select records which DO NOT have the particular Dot you selected thus returning the correct constituent records.
