All Alumni with a Phone Number AND and Email Address

Tom Klimchak

This sounds so simple but I seem to be stuck on this one:

We have five email phone types and about seven phonenumber phone types. I want to find all the alumni who have an email address AND a phonenumber.

A simple query of “Phone type one of Email1, Email2, Email3” works.

But when add AND "Phone type one of Phone1, Cell1, Business1, Home1" into the query I get 0 results.

Pretty sure RE is trying to find a record where a single field equals BOTH phone type values, which results in a 0.

Any other way around this other than merging queries?

Alan French

Yes I think you're right that you're current query is trying to find phone numbers that simultaneously have both types, which isn't possible. If you only had a single phone and email type then you could have done “phone type one of Email, Phone” and tick the box for “each record must meet all specified criteria”, but with multiple email and phone types you'd have to add one of those criteria for every possible combo of email and phone pairs which is probably more of a faff than merging queries

If you're looking to avoid merging queries because the result is a static query, you could always use the record processing options under the tools menu to use one dynamic query as the source for another dynamic query. Still requires separate queries for the phones and the emails but I find it less of a hassle than merging, plus it's only 2 queries instead of 3.

I guess another option could be to use the fields under the Specific Phones node, which I don't normally use because I find them a bit limiting, and have something like:

(Email1 Number not blank
OR Email2 number not blank
OR Email3 number not blank)
AND (Phone1 number not blank
OR Cell1 number not blank
OR Business1 number not blank)

Tom Klimchak

Alan,

Yep, I was trying to avoid the static query situation. It seemed to work, but it was clunky…

But I also TOTALLY forgot about basing a dynamic query off another… not sure why, but I did… I set that up and it works much faster and easier. It's an extra query, but that's not really a show stopper at this point… Thank you!

Darlene LeVielle

Hello, just was reading your post and had a quick question, in your response you mention the dynamic option would be only 2 queries for each ‘type’ (phone, email) but I'm mot following why the merge would be 3? I see the initial primary and secondary with an 'AND" operator, what would be the 3rd query for the merge query scenario and also the last post noted that the dynamic query scenario does have 3 queries, I am also not following why that is in as well? Thanks!

Alan French

Hi Darlene, with a merge query you end up with 3 queries because you need the primary one, the secondary one, and then you generate a static query with the results.

With the alternate method I suggested, what you'd do is create one query for everyone who has one of the phone types, then create a second query and go into Tools > Record Processing and select your phone query as the source (so your second query is already ignoring anyone who doesn't meet the criteria from the first query before you've even added any criteria to it). Then in that second query you can specify that they must have an email address.

Darlene LeVielle

Thanks Alan, Sounds good! But I think what Tom noted was that the Dynamic method is faster but also that it needs an ‘extra’ query ( compared to what screario ?) which wasn't a show stopper but just not sure I understand that as it should be just 2 as you just noted. Thanks Again!

Alan French

Oh I see! I think Tom meant it was an extra query because he'd been hoping to do it all in one query.

Madeleine Holdsworth

You could also have a constituent query based upon the alum type. Take it into export and select each phone type/email type in the export. Export it out and filter from there.