Consolidating constituent codes

Karen Diener 2

Hi all, I have a very simple report that I'm putting together for thank you calls to donors. No sorting, and my output is simply Primary Addressee, Home Phone, Cell Phone, and their constituent codes. The last piece is tripping up this rookie! I want all constituent codes displayed. I exported one field for constituent code, and ideally, that column would read "Parent, Alumni" or "Parent, Graduate Parent, Alumni", etc. But because i exported multiple values in one code (am I saying that right?) each person is listed on a separate row for every constituent code that they have. I've poked around a little but have no context for the formulas and how they work. So I am searching in the dark. Can someone point me in the direction for which operator / function to use? Thank you!! Karen

Melissa Graves 2

Karen Diener:
Hi all, I have a very simple report that I'm putting together for thank you calls to donors. No sorting, and my output is simply Primary Addressee, Home Phone, Cell Phone, and their constituent codes. The last piece is tripping up this rookie! I want all constituent codes displayed. I exported one field for constituent code, and ideally, that column would read "Parent, Alumni" or "Parent, Graduate Parent, Alumni", etc. But because i exported multiple values in one code (am I saying that right?) each person is listed on a separate row for every constituent code that they have. I've poked around a little but have no context for the formulas and how they work. So I am searching in the dark. Can someone point me in the direction for which operator / function to use? Thank you!! Karen

The only way you get multiple rows for one constituent due to multiple constituent codes is if you are exporting from query. Don't export from the results tab of query. There is a little "why are there duplicates in my query results?" link in blue on the results screen that explains this phenomenon. it happens with one-to-many fields where one constituent can have multiple values.

Export from Export and you will not have this issue.

Melissa Graves 2

Melissa Graves:

The only way you get multiple rows for one constituent due to multiple constituent codes is if you are exporting from query. Don't export from the results tab of query. There is a little "why are there duplicates in my query results?" link in blue on the results screen that explains this phenomenon. it happens with one-to-many fields where one constituent can have multiple values.

Export from Export and you will not have this issue.

I take back what I said about the link to "why are there duplicates in my query results?". Ignore that - I just read it and found it to be the most useless explanation and does not explain what to do when you still can not possibly eliminate duplicates - such as your scenario.

Joshua Bekerman bCLO bCRE

Karen Diener:
Hi all, I have a very simple report that I'm putting together for thank you calls to donors. No sorting, and my output is simply Primary Addressee, Home Phone, Cell Phone, and their constituent codes. The last piece is tripping up this rookie! I want all constituent codes displayed. I exported one field for constituent code, and ideally, that column would read "Parent, Alumni" or "Parent, Graduate Parent, Alumni", etc. But because i exported multiple values in one code (am I saying that right?) each person is listed on a separate row for every constituent code that they have. I've poked around a little but have no context for the formulas and how they work. So I am searching in the dark. Can someone point me in the direction for which operator / function to use? Thank you!! Karen

Hi Karen. You'll need to use a subreport for your constituent codes. I'll explain more when i get to a computer and I'm not on a phone that auto corrects"subreport" to "sombrero"

Joshua Bekerman bCLO bCRE

Josh Bekerman:
Hi Karen. You'll need to use a subreport for your constituent codes. I'll explain more when i get to a computer and I'm not on a phone that auto corrects"subreport" to "sombrero"

Okay, so I just wrote all of the following and realized that there's an easier way.  I'll post it anyway just in case it comes up with anything else (like Actions, action notes, or anywhere there are multiple one-to-many relationships in the mdb file).  Anyway, group by ConsID, then place your primary addressee, home phone and cell phone fields in the Group 1 header.  Then place your constituent code field in the details section. Note that it will list the cons code one above the other and not next to each other as mentioned in your question.  One thing you could do is go into the Section Expert and check the "Underlay following section" checkbox for the Group Header section.  This will start listing the conscodes on the same line as the Group Header fields (if that's how you want them to appear). 

Let me know if this works or if you have additional questions.

------------------------------------------------------------------------------------- 

Hi Karen.  Your data has one-to-one relationships (primary addressee, home phone, cell phone), in addition to one-to-many (cons codes).  As you've seen it's the cons codes that are causing the constituents to appear multiple times.  So, you'll need to put your one-to-many field (cons code) in a subreport, and that way your constituent will only show once while still showing all the cons codes.  Here are the general steps from what I can remember without having CR in front of me:

- Insert Subreport

- Use the same data file for the subreport as the main report

- Link to the main report using consID(?) (or there are IDKeys that the export creates in the mdb file, not sure which one to use without seeing it)

- The only thing that should be in the subreport is the ConsCode field, in the details section

- Insert the subreport in the Group header of the main report (not sure what would happen if you place it in the details section), might mess things up.  try it and see what happens.

- If things get messed up with the subreport in the details section, Group the report by ConsID and place your other fields in the Group header

- Suppress or hide the details section

- note that the conscodes will be listed one above the other and not across like you mention in your question

- Size your subreport to the height of one line, then right click and format subreport

- Click the "can grow" checkbox

 

 

Karen Diener 2

Josh Bekerman:

Okay, so I just wrote all of the following and realized that there's an easier way.  I'll post it anyway just in case it comes up with anything else (like Actions, action notes, or anywhere there are multiple one-to-many relationships in the mdb file).  Anyway, group by ConsID, then place your primary addressee, home phone and cell phone fields in the Group 1 header.  Then place your constituent code field in the details section. Note that it will list the cons code one above the other and not next to each other as mentioned in your question.  One thing you could do is go into the Section Expert and check the "Underlay following section" checkbox for the Group Header section.  This will start listing the conscodes on the same line as the Group Header fields (if that's how you want them to appear). 

Let me know if this works or if you have additional questions.

------------------------------------------------------------------------------------- 

Hi Karen.  Your data has one-to-one relationships (primary addressee, home phone, cell phone), in addition to one-to-many (cons codes).  As you've seen it's the cons codes that are causing the constituents to appear multiple times.  So, you'll need to put your one-to-many field (cons code) in a subreport, and that way your constituent will only show once while still showing all the cons codes.  Here are the general steps from what I can remember without having CR in front of me:

- Insert Subreport

- Use the same data file for the subreport as the main report

- Link to the main report using consID(?) (or there are IDKeys that the export creates in the mdb file, not sure which one to use without seeing it)

- The only thing that should be in the subreport is the ConsCode field, in the details section

- Insert the subreport in the Group header of the main report (not sure what would happen if you place it in the details section), might mess things up.  try it and see what happens.

- If things get messed up with the subreport in the details section, Group the report by ConsID and place your other fields in the Group header

- Suppress or hide the details section

- note that the conscodes will be listed one above the other and not across like you mention in your question

- Size your subreport to the height of one line, then right click and format subreport

- Click the "can grow" checkbox

 

 

I'm not very well-versed in subreports (check in with me after my October 30 training) so went with the underlay sections option. It works for now, but I do hope to end up with a constituent code field that reads something like "Parent, Alumni" or "Employee, Alumni" etc. Thanks for the help! Karen