Newby query questions

suzie deaves · May 2018

Hi, Can any one help a newby to RE out.

We have an existing mailing which I would like to able to filter records with no solicit codes out. I cant see a way of doing this..can any one advise?

Also, can anyone point me at some detailed documentation for Mailing/query so I can understand how a mailing interacts with its queries? The user guide helps with how to select options etc but doesnt help me understand how it works with the queries..

thanks-

Alan French · May 2018

Using a query with a mailing/export/report helps you to select the constituents that are being included, rather than mail everyone in the entire database.

Do you mean that you want to remove anyone that doesn't have any solicit codes at all, and retain only people who have one or more solicit codes regardless of what they are? I don't think you'll be able to do this within the mailing itself, as the filters only allow you to remove people with solicit codes, but if you use a query with the critieria "solicit code description not blank" that should work.

If you only want to retain people with specific solicit codes, you can change that to "solicit code description one of..."

suzie deaves · May 2018

Hi Alan

Thanks for the response. My problem is that we have this mailing cretaed by a previous admin and I am trying to trouble shoot an issue with it to confirm or not it is generating the correct records, with the required solicit codes filtered out. However it produces close to 10000 records many of which have no solicit code (which is correct but is making it very difficult for me to validate the records for those which do have solicit codes). If i could filter out the blank records I would have a much smaller result set with which to validate the codes being pulled through.

The only options i can see on the filter screen are to exclude solicit codes which are not blank...and i cannot see any way to manualy add another filter..

Ryan Hyde · May 2018

suzie deaves:

Hi Alan

Thanks for the response. My problem is that we have this mailing cretaed by a previous admin and I am trying to trouble shoot an issue with it to confirm or not it is generating the correct records, with the required solicit codes filtered out. However it produces close to 10000 records many of which have no solicit code (which is correct but is making it very difficult for me to validate the records for those which do have solicit codes). If i could filter out the blank records I would have a much smaller result set with which to validate the codes being pulled through.

The only options i can see on the filter screen are to exclude solicit codes which are not blank...and i cannot see any way to manualy add another filter..

If I'm understanding this correctly, then a better solution would be to create a separate query looking for anyone with one of the solicit codes you want to filter out. Then, take the query that is currently generating your mailng list and merge the two queries.

When you're looking at the query module, there's an array of buttons across the top, one of which is "merge." Click on that. In the first of the two search boxes, find the original mailing list query. In the second, find your new query that looks for all people you want removed from the mailing. Then in the dropdown menu below that, click the SUB operator. This will remove all bad records from the original list.

This is a recommended way for generating mailing lists, btw. Always have an INCLUDE query, an EXCLUDE query, and then merge those together with SUB as the operator to get a clean file. Then you use that to plug into either a mail module or an export.

Christine Cooke 2 · May 2018

suzie deaves:

Hi Alan

Thanks for the response. My problem is that we have this mailing cretaed by a previous admin and I am trying to trouble shoot an issue with it to confirm or not it is generating the correct records, with the required solicit codes filtered out. However it produces close to 10000 records many of which have no solicit code (which is correct but is making it very difficult for me to validate the records for those which do have solicit codes). If i could filter out the blank records I would have a much smaller result set with which to validate the codes being pulled through.

The only options i can see on the filter screen are to exclude solicit codes which are not blank...and i cannot see any way to manualy add another filter..

What Ryan Hyde said -- and, as a newbie you may not realize that Query is a grouping tool -- it's not designed to be a stand alone report or mailing tool. So when your query has results that show multiples of the same record that is normal.

To piggyback on Ryan's suggestions -- it could be that you have found the main query from your precedessor and not the secondary one that the first gets merged with. The second query would probably contain the solicit codes (or not codes) you are supposed to be removed/subtracted from the list. Because the merge process produces a Static query -- even though the criteria is the same everytime, you would have to do a merge and produce a new one for the new mailing in order to update the mailing list you want to send to. Makes sense I hope.

Cathleen Mai · May 2018

What format is your existing mailing? Is it a query, a mail file, an excel list?

If you have a list of Constituent IDs you could copy them into a query and then filter this query by "solicit code description not blank".

If it is a Mail file you could create a query from there, when running the mail module on the bottom it says create...output query and then filter this query.

suzie deaves · May 2018

Thanks for all the replies - this is helping me understand what is going but still not sure the best solution. I think that the mailing needs to be 2 seperate mailings and not 1 to achive what is being asked of me and the design is fine for one option not for 2..

I have inherited 6 queries - each of which which add to (dupliates not included) to the result set for the Mailing.

The mailing filters out several solicit codes, which works fine.

however, the problem is this:-

one SC is do not send 'a'

another is do not send 'b'

some users want- both 'a+b' (null solicit code) so thats fine

those that dont want 'a' but do want 'b' are filtered out by current settings

if I filter out 'a & b' then I lose the consitiuents that just want 'a' but not 'b' and those that want 'b' but not 'a' ....

If I produce 2 files those that do want 'a' and those that do want 'b' then I have duplicate null entries(9000+ records) for 'a+b' as I cant see anyway to remove the blanks in the second run of the query thorugh a filter....

my head hurts...

Sarah Getgood · May 2018

suzie deaves:

Thanks for all the replies - this is helping me understand what is going but still not sure the best solution. I think that the mailing needs to be 2 seperate mailings and not 1 to achive what is being asked of me and the design is fine for one option not for 2..

I have inherited 6 queries - each of which which add to (dupliates not included) to the result set for the Mailing.

The mailing filters out several solicit codes, which works fine.

however, the problem is this:-

one SC is do not send 'a'

another is do not send 'b'

some users want- both 'a+b' (null solicit code) so thats fine

those that dont want 'a' but do want 'b' are filtered out by current settings

if I filter out 'a & b' then I lose the consitiuents that just want 'a' but not 'b' and those that want 'b' but not 'a' ....

If I produce 2 files those that do want 'a' and those that do want 'b' then I have duplicate null entries(9000+ records) for 'a+b' as I cant see anyway to remove the blanks in the second run of the query thorugh a filter....

my head hurts...

I think maybe you'll find a way around it by using 'or' statements in parentheses. Where are the solicit codes being excluded? If it's at the query stage:

AND (solicit code does not equal 'a' OR solicit code does not equal 'b').

suzie deaves · May 2018

Sarah Getgood:

suzie deaves:

Thanks for all the replies - this is helping me understand what is going but still not sure the best solution. I think that the mailing needs to be 2 seperate mailings and not 1 to achive what is being asked of me and the design is fine for one option not for 2..

I have inherited 6 queries - each of which which add to (dupliates not included) to the result set for the Mailing.

The mailing filters out several solicit codes, which works fine.

however, the problem is this:-

one SC is do not send 'a'

another is do not send 'b'

some users want- both 'a+b' (null solicit code) so thats fine

those that dont want 'a' but do want 'b' are filtered out by current settings

if I filter out 'a & b' then I lose the consitiuents that just want 'a' but not 'b' and those that want 'b' but not 'a' ....

If I produce 2 files those that do want 'a' and those that do want 'b' then I have duplicate null entries(9000+ records) for 'a+b' as I cant see anyway to remove the blanks in the second run of the query thorugh a filter....

my head hurts...

I think maybe you'll find a way around it by using 'or' statements in parentheses. Where are the solicit codes being excluded? If it's at the query stage:

AND (solicit code does not equal 'a' OR solicit code does not equal 'b').

The queries do not filter on the Solicit code at all - there are other criteria though; The Mail settup runs a filter on the solicit codes from the underlying queries.

I did think of adding this to all 6 queries but it all ready takes a while to run and I think this would slow it right down....

Cathleen Mai · May 2018

You can enter your queries into segment in mail to avoid duplication.

See Christines Post here:

https://community.blackbaud.com/forums/viewtopic/158/32494?post_id=131681#p131681

suzie deaves · May 2018

Can any one point me at some more advanced documentation than the Query User Guide.. I clearly need to find out more about the power of queries.. :-)

Austen Brown · May 2018

suzie deaves:

Can any one point me at some more advanced documentation than the Query User Guide.. I clearly need to find out more about the power of queries.. :-)

Suzie - Blackbaud.com has a great library of user guides - it is not necessarily a "fun" read as they are technical guides. To find them go to Blackbaud.com > Support > How-To Documentation > Raiser's Edge 7. For your convenience here is the link to the Query & Export one... https://www.blackbaud.com/files/support/guides/re7/queryexp.pdf. If you continue to have issues, feel free to PM me. I would be happy to talk through your issue with you.

Newby query questions

Comments

Categories