Attributes, Tables and Query Results

Kate Styer · August 2018

Several of our attributes are configured as tables and have multiple table entries. Many constituents have the same attribute multiple times with the same descriptions, but with different comments and dates (if they participated in a program more than once for several years, for instance). I'm trying to query on one specific attribute, and see all of the different descriptions that have been applied. However, when I output the description, comments and date for that specific attribute, it duplicates each combination of description, comments and date. Is there any way around this?

Alan French · August 2018

Yes there is a way around it - you're getting the duplicates because (I assume) your query is using fields immediately within the Attributes section (i.e. category) and also the description from within the Specific Attributes sub-section. RE doesn't make any connection between the attributes in these two sections, even though we know they are the same - for all it knows, we might WANT to select constituents with one particular attribute category but output a different attribute (as a rubbish example off the top of my head, listing the sporting interests of all constituents subscribed to the newsletter).

I guess you're using Category in order to select only the constituents who have that particular attribute? To remove the duplicates, you'll need to remove that and replace it with a field from within the Specific Attributes section for that category, so you're not using fields across multiple levels of the attribute query "tree". You could do something like 'Attribute_Name Description not blank' or 'Attribute_Name Import ID not blank' to achieve the same effect of selecting only the constituents who have an attribute with that category (the first option will not include any constituents who may have an attribute with a category but no description; the second one using import ID will).

Joanne Felci 2 · August 2018

Kate Styer:

Several of our attributes are configured as tables and have multiple table entries. Many constituents have the same attribute multiple times with the same descriptions, but with different comments and dates (if they participated in a program more than once for several years, for instance). I'm trying to query on one specific attribute, and see all of the different descriptions that have been applied. However, when I output the description, comments and date for that specific attribute, it duplicates each combination of description, comments and date. Is there any way around this?

Hi Kate,

I'm not sure I'm following exactly what you are doing/whats happening

Is it that you have an attribute, with just one description that can be added several times to a record with different dates and comments

Or is it an attribute with multiple descriptions and you are only interested in one of the descriptions and that descriptions dates and comments

For example scenario A:

Attribute = Donor Level

Description = Major

Date = 1/11/11

Comment = owns home

Attribute = Donor Level

Description = Major

Date = 7/13/15

Comment = owns boat

The results in your query would look something like this:

Attribute Donor Level Description                      Attribute Donor Level Date                      Attribute Donor Level Comments

Major                                                              1/11/11                                                 owns home

Major                                                              7/13/15                                                 owns boat

For example scenario B:

Attribute = Donor Level

Description = Major

Date = 1/11/11

Comment = owns home

Attribute = Donor Level

Description = Mid-Level

Date = 3/20/10

Comment = met to discuss org mission

Attribute = Donor Level

Description = General

Date = 9/12/08

Comment = introduced at event

The results in your query would look something like this:

Attribute Donor Level Description                      Attribute Donor Level Date                      Attribute Donor Level Comments

Major                                                              1/11/11                                                 owns home

Mid-Level                         3/20/10 met to discuss org mission

General                                                           9/12/08                                                introduced at event

Either way if you are using results from a query that has a specific attribute more than once you are always going to have duplicate rows from the output

If it is more like scenario B then I would suggest exporting the results and sorting on the description you are interested in, then you can more easily see all the comments/dates for that description on a donors record

Joanne

Kate Styer · August 2018

THANK YOU!! Both options work. Seriously, thank you so much for responding. I've been going crazy trying to fix this.

Jill Freidmutter · August 2018

One more tip that may be helpful: If you QUERY for constituents with an attribute that appears multiple times, that constituent will appear on multiple lines in the query -- once for each appearance of the Attribute. If you EXPORT that query through the EXPORT function (not query export), then you will have one line for each constituent, with any number of attributes repeated on the same line. (When you select the Attribute, Export will ask how many you want to export. I always pick a number that is one more than what I expect the max to be, to verify that it is an empty field and I have all the occurrences of that Attribute.) A query export that feeds into an Excel pivot table can provide hours of fun!

John Heizer · August 2018

Kate Styer:

Several of our attributes are configured as tables and have multiple table entries. Many constituents have the same attribute multiple times with the same descriptions, but with different comments and dates (if they participated in a program more than once for several years, for instance). I'm trying to query on one specific attribute, and see all of the different descriptions that have been applied. However, when I output the description, comments and date for that specific attribute, it duplicates each combination of description, comments and date. Is there any way around this?

RE Mantra: Query is a grouping tool, not a reporting tool. Use Query to determine which Constituent records to see and then use Export to get the data out of RE.

Joanne Felci 2 · August 2018

John Heizer:

Kate Styer:

Several of our attributes are configured as tables and have multiple table entries. Many constituents have the same attribute multiple times with the same descriptions, but with different comments and dates (if they participated in a program more than once for several years, for instance). I'm trying to query on one specific attribute, and see all of the different descriptions that have been applied. However, when I output the description, comments and date for that specific attribute, it duplicates each combination of description, comments and date. Is there any way around this?

RE Mantra: Query is a grouping tool, not a reporting tool. Use Query to determine which Constituent records to see and then use Export to get the data out of RE.

John - I usually totally agree with this, but I think when you are looking for many attributes on one record it is easier to use the export from the query results as it is already formatted into rows, as opposed to a constituent export that is all columns.

Or is there an attribute export type?

Heather MacKenzie · October 2018

Joanne Felci‍ I finally had a Blackbaud someone tell me a real valid reason for never exporting directly from the query, but from through the Export module instead. First hand, she had seen it corrupt data in RE.

Since I heard this from her, my offices have moved MUCH more towards using the Export module, but I do have one that regularly still gets exported from the query... shhh! Don't tell anyone! It's because the fields I need for this report are not contained within any one single export. Go figure!

Aaron Rothberg · October 2018

Hi Heather-

Not intending to shoot the messenger here, but I have to say that answer from Blackbaud is completely rediculous, as in, worthy of redicule.

If exporting from Query were capable of corrupting data, Raiser's Edge software would never have survived in the market place for as long as it has.

The idea that "outputting" data can corrupt the tables of any database is so remote, it's not worth discussing.

The better reason to use Export instead of exporting from Query includes among other things:

Export respects; Address starts and stops, No Valid Address checkboxes, Do Not Send Mail checkboxes, Solicitor starts and stops, DNC email addresses, and a whole host of other reasons.

Joanne Felci 2 · October 2018

I can't remember - or find the post that had this information - but what is the shortcut you can use when you are in a constituent record and want to add to an attribute table

Alan French · October 2018

The shortcut is F7, there's a little reminder at the bottom of the record when you click in the field.

Joanne Felci 2 · October 2018

Thanks Alan French‍ - you are the best!

And oh yeah...look at that it is at the bottom of the screen (another sign of how unobservant I am)

Heather MacKenzie · March 2019

Aaron Rothberg:

Hi Heather-

Not intending to shoot the messenger here, but I have to say that answer from Blackbaud is completely rediculous, as in, worthy of redicule.

If exporting from Query were capable of corrupting data, Raiser's Edge software would never have survived in the market place for as long as it has.

The idea that "outputting" data can corrupt the tables of any database is so remote, it's not worth discussing.

The better reason to use Export instead of exporting from Query includes among other things:

Export respects; Address starts and stops, No Valid Address checkboxes, Do Not Send Mail checkboxes, Solicitor starts and stops, DNC email addresses, and a whole host of other reasons.

Aaron Rothberg‍, thank you for not shooting the messenger, LOL! It didn't make much sense to me either, but it was a from a teacher in an in-person class I was in who said it had actually happened to her directly, so it was finally something I could take and believe. Anytime I had asked someone from Blackbaud why, prior to that point, all I got was hemming and hawing and no real answers with any substance to them. BUT, exporting makes more sense to me now for all the other reasons you mentioned here too. Plus, I love how using the different formats can give you your data in different ways - gift vs. constituent, for example, giving you gifts all on one row or each on it's own unique row.

Attributes, Tables and Query Results

Comments

Categories