Query with Email and Telephone

Ray Berthelette

Hi All....I am new to Raiser's Edge, but am learning it very quickly. We are using version 7.92.5508. I am working on a query that returns a list of constituents. All that works fine. The problem is that on the Output I must include the phone number and e-mail of each constituent. If I include this as criteria, then I end up with two records for each person, one that shows the e-mail and one that shows the telephone number. Is there a way that I can have one record for each person and in that record show the e-mail address and the telephone number?


Thanks,

Ray

JoAnn Strommen

Ray, fields like phone and constituent code are considered a 'one-to-many' type and you will get multiple listings of records to display the data in those type of fields. What you want to do is use query as a 'grouping' tool - that is more the purpose of query. Put in the criteria for the query you need to filter to your group.  Then you use the Export function (not the export of the query).  For records to use in Export select your query. And now select all the fields you want to output. For a constituent export each record will be pulled one time with the output info each in a separate column.

Jen Claudy

Not in Query.  However, if you build an Export and base it on your Query, you can then add Phone Number twice, which will give you two columns.  If someone doesn't beat me to it, I'll put together some directions on how to do that.


 

Ryan Hyde

In case you get hung up on that two column approach, here's what you should do within Export:


1.) Add Phone Number to your export fields

2.) In the box that pops up, enter '1' in the upper right hand corner, and then select a single phone number type (e.g. Home Phone)

3.) Hit OK

4.) In the export fields, click all the way on the top field, which is the title of your export (or "New Constituent Export" if you have not saved the export)

5.) Follow steps 1-3 again, but this time select your email address type that you want to export


This will result in two columns each containing exactly one contact type for each constituent. It's best to do things this way (as opposed to entering 2 in the upper right hand and selecting both contact types) because you will get exactly one kind of result per column this way. If you do it the other way, you end up with contact types shuffled around between the two columns, which is useless in most scenarios.

Jen Claudy

And Ryan beat me to it!  Thanks, Ryan.

Ryan Hyde

haha, any time

John Heizer

Ray Berthelette:

Hi All....I am new to Raiser's Edge, but am learning it very quickly. We are using version 7.92.5508. I am working on a query that returns a list of constituents. All that works fine. The problem is that on the Output I must include the phone number and e-mail of each constituent. If I include this as criteria, then I end up with two records for each person, one that shows the e-mail and one that shows the telephone number. Is there a way that I can have one record for each person and in that record show the e-mail address and the telephone number?


Thanks,

Ray

Others have given you good instructions.  I'll just repeat the RE Mantra:  "Query is a grouping tool, not a reporting tool." 

Ray Berthelette

Ray Berthelette:

Hi All....I am new to Raiser's Edge, but am learning it very quickly. We are using version 7.92.5508. I am working on a query that returns a list of constituents. All that works fine. The problem is that on the Output I must include the phone number and e-mail of each constituent. If I include this as criteria, then I end up with two records for each person, one that shows the e-mail and one that shows the telephone number. Is there a way that I can have one record for each person and in that record show the e-mail address and the telephone number?


Thanks,

Ray

Thank you all for the input! It was a tremendous help and I think I know have a handle on this. Just in time too because the report requests are coming in fast. I hope to be more active in this community as time goes on and I learn more about Raiser's Edge. See you all around! Ray

Ray Berthelette

Ray Berthelette:

Hi All....I am new to Raiser's Edge, but am learning it very quickly. We are using version 7.92.5508. I am working on a query that returns a list of constituents. All that works fine. The problem is that on the Output I must include the phone number and e-mail of each constituent. If I include this as criteria, then I end up with two records for each person, one that shows the e-mail and one that shows the telephone number. Is there a way that I can have one record for each person and in that record show the e-mail address and the telephone number?


Thanks,

Ray

Again, thanks all for your help!  One little caveat that I've noticed though. It's returning all records except for the ones that have nothing for a phone number/e-mail. How do I include records if this field is blank?


Thanks,

Ray

JoAnn Strommen

Use query to group all the records you want but not based on the phone criteria. Do you have some other determining factor of what records you want to report on? Do not use phone/email as a query criteria. Get all those records in the query and then take the query to Export.

Ryan Hyde

So that has to do with your query, not your export. Just remove any criteria related to phone or email, since you're not trying to limit your group by those parameters. The info you decide to output via the Export module is independent from any of the criteria you used in your query. 

Ray Berthelette

Ray Berthelette:

Ray Berthelette:

Hi All....I am new to Raiser's Edge, but am learning it very quickly. We are using version 7.92.5508. I am working on a query that returns a list of constituents. All that works fine. The problem is that on the Output I must include the phone number and e-mail of each constituent. If I include this as criteria, then I end up with two records for each person, one that shows the e-mail and one that shows the telephone number. Is there a way that I can have one record for each person and in that record show the e-mail address and the telephone number?


Thanks,

Ray

Again, thanks all for your help!  One little caveat that I've noticed though. It's returning all records except for the ones that have nothing for a phone number/e-mail. How do I include records if this field is blank?


Thanks,

Ray

 

I think I just answered my own question! Instead of following the earlier suggestions, I added only one Phone field and in the "For each Preferred Address, enter the number of Phones to export" I put a value of 4. I then selected the "All Phones" radio button instead of selecting individual phones. This now shows all of the entries in the Phone field as well as blanks.

Ryan Hyde

Ohh, I forgot that phones are still tied to address records in 7.92. For all the pain involved with updating to 7.94+, having phone records separate from address records really does make life easier.

Ray Berthelette

JoAnn Strommen:

Use query to group all the records you want but not based on the phone criteria. Do you have some other determining factor of what records you want to report on? Do not use phone/email as a query criteria. Get all those records in the query and then take the query to Export.

Thanks JoAnn! That's what I ended up doing. I filtered it out in an Export.

Ray Berthelette

Ryan Hyde:

Ohh, I forgot that phones are still tied to address records in 7.92. For all the pain involved with updating to 7.94+, having phone records separate from address records really does make life easier.

Thanks Ryan! Yes, upgrading to 7.94+ is on my list. I've just started in this position so am trying to figure out where they are and what needs to be done!