Query - Constituent code output (only 1 displaying)

Usamah Shaban 2

When I set a criterion for constituent code (say, equals Alumni), and output constituency codes, it only outputs the specified constituent code and ignores any other that the constituent may have. Is there a way to fix this?


I already know about the export option - this is not the solution I'm looking for.

Ryan Hyde

Fascinating question! I had never noticed this behavior before, but I can confirm it.


So, here's what you do.


1.) Create a query looking for your one constituent code and save it.

2.) Open a new constituent query

3.) Go to Tools > Query Options > Record Processing

4.) Check the box for "Select from Query" and find the query you saved in step 1

5.) Hit OK

6.) Output constituency code and maybe constituent ID


Presto, you have all constituentcy codes for people who have that one code on their record. It's round about, but it works.

John Heizer

Usamah Shaban:

When I set a criterion for constituent code (say, equals Alumni), and output constituency codes, it only outputs the specified constituent code and ignores any other that the constituent may have. Is there a way to fix this?


I already know about the export option - this is not the solution I'm looking for.

RE Mantra:  Query is a grouping tool, not a reporting tool.  A Constituent Query is designed to return a group of Constituents, not report on their Constituent Codes.


That said, try using Primary Constituent Code "is one of" instead of "equals."  Also, it will make a difference if you select Primary Constituent Code (there can be only one primary, and RE uses from/to date ranges and the order on Bio1 to determine which is the primary) or Constituency as your criteria and output.

Ryan Hyde

Though I think what he wants is one line per constituent code for some reason, and there could be lots of reasons for this. Since there's no "constituent code export," you'd need to use just a constituent export and then you'd get one code per column, and wrangling that into one code per row would be time consuming, unless you know how to use R or something like it.


 

John Heizer

Ryan Hyde:

Though I think what he wants is one line per constituent code for some reason, and there could be lots of reasons for this. Since there's no "constituent code export," you'd need to use just a constituent export and then you'd get one code per column, and wrangling that into one code per row would be time consuming, unless you know how to use R or something like it.

S

Selecting "Constituency" instead of "Primary Constituent Code" as the Query output will get the desired results.

Ryan Hyde

Well, not always. For this to work, all of the constituents who have a constituency of Alumni would have to have this as their primary constituency code. If that's the case, then absolutely this method would work better than my method (e.g. set criteria to look at Primary Constituency Information and set output to look at just the Constituency field), but if anyone has Alumni as a code that is not primary, then this won't work because they won't qualify for the criteria. So it depends on how clean and consistent your data is, and whether or not 'primary constituentcy code' is something you worry about at all. In my database, it is not. I don't even require constituency code on new records. But of course everyone org is different.

Katherine Mannion

Ryan Hyde

Hahahaha! I wish I could award this "best answer"!


I just asked my co-worker, "Wanna laugh at a bunch of nerds? (i'm one of the nerds)"

Bob Wiebe

Ryan Hyde:

Fascinating question! I had never noticed this behavior before, but I can confirm it.


So, here's what you do.


1.) Create a query looking for your one constituent code and save it.

2.) Open a new constituent query

3.) Go to Tools > Query Options > Record Processing

4.) Check the box for "Select from Query" and find the query you saved in step 1

5.) Hit OK

6.) Output constituency code and maybe constituent ID


Presto, you have all constituentcy codes for people who have that one code on their record. It's round about, but it works.

Very interesting.  I am now wondering what other common situations would benefit from using the Record Processing approach on a selected query.

Ryan Hyde

I would say the utility of this approach is pretty limited. It's a good approach to take, though, when trying to find what % of X = Y. Let's say I want to know how many of my walk-a-thon participants also give to direct mail, attend our gala, and have a planned gift, and I want the separate percentages for each. I would write a query that looks for all participants, and then I would write a query for each of those other three categories, and I would connect the participantation (not participant - this is still a constituent query) to the other three via record processing. 


There are of course other ways to get this number, but I think it's a step or two more efficient to do it this way. 


I'll say that Usamah's initial question on this thread is the first situation I've come across that absolutely required the use of record processing, though I'm sure other scenarios do exist.