Email Query

Robert Brown · August 2017

OK, I've looked at this 10 different ways and I'm frustrated. Here we have a lot of different email types, but the two I'm interested in is Email - Home and Email - Preferred. I'm trying to isolate records that have both on their record. Seems easy, right? Just ask RE Query to return records with Email - Home not blank and Email - Preferred not blank. Here starts the problem. Query cannot be set to look only at active e-mails. So those that have it inactive? They get returned.

Not a big deal, with? Just add a "Phone is inactive equals No" argument to each and use proper parentheses.

Nope. Even with parentheses, query is only looking for any line that has the inactive box not checked off.

I'm really frustrated. While I love the upgrade with RE moving phones into its own section separate from addresses, this is now becoming an issue.

Help?

Alicia Barevich · August 2017

Use export!

Export the Phone Number for Home Email, and export another "Phone" section for Preferred Email. Don't check the box "show inactive phones." Sort/manipulate the data in Excel, and you have your list! You can then copy the Constituent IDs from your Excel sheet into a query if you'd prefer to audit the records individually. Alternatively, you could include the Phone Import ID, and import them as a new phone type, and then use the delete phones plugin to remove them.

Jen Claudy · August 2017

This is partly why I kept the process of using Phone Types instead of the Inactive checkbox when we upgraded from 7.93. I don't like that the Inactive & Primary boxes aren't directly tied to the Phones, and I don't want to have to use Query, then Export, then Excel, then back to Query, especially for something simple where a Query would be best just from the start.

But for your situation, I think Alicia is correct, you'll need to use Export.

Steven Cinquegrana 2 · August 2017

Hi Robert,

Have you tried a Merge Query approach?

RE supports a few types including a SUB type where the end recordset has any records appearing in the sub-query subtracted.

Another approach using Merge Queries would be to create two separates ones, one for each Email Type and then OR them. With RE - and SQL - sometimes querying on two mutually exclusive criteria isn't as straightforward as it might seem.

I hope that helps.

Cheers,

Steve Cinquegrana | CEO and Principal Developer | Protégé Solutions

Alan French · August 2017

Steven Cinquegrana:

Another approach using Merge Queries would be to create two separates ones, one for each Email Type and then OR them. With RE - and SQL - sometimes querying on two mutually exclusive criteria isn't as straightforward as it might seem.

For this task, wouldn't the AND operator be better than OR? Query 1 would be Phone Type = "Email - Home" and Phone Is Inactive = No, query 2 would be Phone Type = "Email - Preferred" and Phone Is Inactive = No, and then merge them together to leave the records that are in both groups.

Robert Brown · August 2017

Alicia Barevich:

Use export!

Export the Phone Number for Home Email, and export another "Phone" section for Preferred Email. Don't check the box "show inactive phones." Sort/manipulate the data in Excel, and you have your list! You can then copy the Constituent IDs from your Excel sheet into a query if you'd prefer to audit the records individually. Alternatively, you could include the Phone Import ID, and import them as a new phone type, and then use the delete phones plugin to remove them.

Hi Alicia,

If this were just for a mailing list, I would do that no problem, still grumbling about it though. But I have to identify the records so that I can fix the issue - records are supposed to have either an email preferred or an email home. Not both. I know I can use export to identify the records, but then to take that list back to RE and search for the records individually? I'm just asking for trouble.

Robert Brown · August 2017

Steven Cinquegrana:

Hi Robert,

Have you tried a Merge Query approach?

RE supports a few types including a SUB type where the end recordset has any records appearing in the sub-query subtracted.

Another approach using Merge Queries would be to create two separates ones, one for each Email Type and then OR them. With RE - and SQL - sometimes querying on two mutually exclusive criteria isn't as straightforward as it might seem.

I hope that helps.

Cheers,

Steve Cinquegrana | CEO and Principal Developer | Protégé Solutions

Hi Steve,

The problem I've found is that RE does not let me specify the "inactive?" checkbox for the specific email I'm looking for. Even with "and" and parentheses, it does not come up properly. If I made a query were "Email preferred not blank" and "Inactive? equals no", I would also get a record that has an email preferred on it that's inactive and some other phone type that's not inactive. This is where I'm running into trouble, because the query does not give me an option to ask if each phone type is active or inactive, only if there is one at all.

Robert Brown · August 2017

Alan French:

Steven Cinquegrana:

Another approach using Merge Queries would be to create two separates ones, one for each Email Type and then OR them. With RE - and SQL - sometimes querying on two mutually exclusive criteria isn't as straightforward as it might seem.

For this task, wouldn't the AND operator be better than OR? Query 1 would be Phone Type = "Email - Home" and Phone Is Inactive = No, query 2 would be Phone Type = "Email - Preferred" and Phone Is Inactive = No, and then merge them together to leave the records that are in both groups.

Hi Alan,

Oh wait! I see the listing for "phone type" now. Yeah, I don't have to look for the "not blank" anymore, I just have to do as you suggested and use the "and" merger. That's what I need, exactly. Thank you. I've been working with this for 17 years now and I'm still learning.

Alan French · August 2017

Robert Brown:

Hi Steve,

The problem I've found is that RE does not let me specify the "inactive?" checkbox for the specific email I'm looking for. Even with "and" and parentheses, it does not come up properly. If I made a query were "Email preferred not blank" and "Inactive? equals no", I would also get a record that has an email preferred on it that's inactive and some other phone type that's not inactive. This is where I'm running into trouble, because the query does not give me an option to ask if each phone type is active or inactive, only if there is one at all.

Hi Robert,

I think your problem is because you're trying to use the Inactive checkbox from one level of the query nodes and then you're going into a different node (the specific phones sub-node) for your "Email preferred not blank" criteria. If you ignore the specific phones sub-node and use criteria of "phone type equals email preferred", "phone number not blank" and "Inactive equals no", I think you should get the desired result.

Melissa S Graves · August 2017

You do not have to take it back to RE individually - once you identify the trouble records in your export there are a few ways you can take a list of the IDs in your file and create a query.

1) create an import of just the ID and set the import to create a query of changed records.

2) create a query with ID = one of. copy the list if IDs from your file and go to the bottom of the "one of" list and paste it there. go back to the top and voila - all of those IDs are now in the one of criteria for your query.

Robert Brown · August 2017

Melissa Graves:

You do not have to take it back to RE individually - once you identify the trouble records in your export there are a few ways you can take a list of the IDs in your file and create a query.

1) create an import of just the ID and set the import to create a query of changed records.

2) create a query with ID = one of. copy the list if IDs from your file and go to the bottom of the "one of" list and paste it there. go back to the top and voila - all of those IDs are now in the one of criteria for your query.

Hi Melissa,

That's actually great advice that I can use in the future. I really appreciate it! This is why I really love this community. Everyone here is really amazing and helpful. Thank you again!

Jen Claudy · August 2017

One thing to be aware of when pasting lists into Query (as Melissa explained)...there is a limit of 500. So if your list is 501 Constituent IDs, you'll need to do something like:

(Constituent ID one of ...

OR Constituent ID one of...)

to get them all in one Query. The good news is, you can do as many sets of 500 as you need.

Barbara Schlichter · August 2017

Jen Claudy:

One thing to be aware of when pasting lists into Query (as Melissa explained)...there is a limit of 500. So if your list is 501 Constituent IDs, you'll need to do something like:

(Constituent ID one of ...

OR Constituent ID one of...)

to get them all in one Query. The good news is, you can do as many sets of 500 as you need.

There's a very handy add-on for having "one of" queries with an unlimited number of entries. There's a company in England call SmartTHING that has a product that does that, and it's free! It's called smartpaste. They are also a Blackbaud technology partner, so no need to worry that it'll mess with RE. Their customer service is top notch. And no, I'm not getting paid to endorse them, I just know the product is excellent, I've been using it for years.

Hope this helps someone out there!

Barbara

Denise Covington · August 2017

Also, when pasting in up to 500 constituent IDs into the Const ID one, also beware that if you only had 100 and then decide to edit the list, you will get random records dropped and the ones your tty to add may not be added. So be sure to add a new parameter OR Const ID one of "...., .....".

Cathleen Mai · August 2017

I use Splitomatic from AppOmatic (also free) which lets you split larger excel sheets into blocks of 500, but this is an even better solution. You teached me something new today! Thank You, Barbara!

Barbara Schlichter:

Jen Claudy:

One thing to be aware of when pasting lists into Query (as Melissa explained)...there is a limit of 500. So if your list is 501 Constituent IDs, you'll need to do something like:

(Constituent ID one of ...

OR Constituent ID one of...)

to get them all in one Query. The good news is, you can do as many sets of 500 as you need.

There's a very handy add-on for having "one of" queries with an unlimited number of entries. There's a company in England call SmartTHING that has a product that does that, and it's free! It's called smartpaste. They are also a Blackbaud technology partner, so no need to worry that it'll mess with RE. Their customer service is top notch. And no, I'm not getting paid to endorse them, I just know the product is excellent, I've been using it for years.

Hope this helps someone out there!

Barbara

Barbara Schlichter · August 2017

Cathleen Mai:

I use Splitomatic from AppOmatic (also free) which lets you split larger excel sheets into blocks of 500, but this is an even better solution. You teached me something new today! Thank You, Barbara!

Barbara Schlichter:

Jen Claudy:

One thing to be aware of when pasting lists into Query (as Melissa explained)...there is a limit of 500. So if your list is 501 Constituent IDs, you'll need to do something like:

(Constituent ID one of ...

OR Constituent ID one of...)

to get them all in one Query. The good news is, you can do as many sets of 500 as you need.

There's a very handy add-on for having "one of" queries with an unlimited number of entries. There's a company in England call SmartTHING that has a product that does that, and it's free! It's called smartpaste. They are also a Blackbaud technology partner, so no need to worry that it'll mess with RE. Their customer service is top notch. And no, I'm not getting paid to endorse them, I just know the product is excellent, I've been using it for years.

Hope this helps someone out there!

Barbara

You're welcome, glad I could help. The website is https://www.smartthing.org/

Barbara

Email Query

Comments

Categories