Individual query on relationship type

Suzie Capps · August 2017

Hi folks,

I may be missing something obvious here (sorry!). Is it possible to pull a query which only shows individuals who do not have any relationships of a particular type?

I have had a request to pull a list of all our alumni (constituent code = alumni) who do not have any 'professor' (a relationship type) relationships on their record.

Thanks in advance for any advice

Ryan Hyde · August 2017

When you say relationship 'type,' do you mean relation code? That's the area in the lower right hand corner of the a relationship record where you pick a relation code and a reciprocal relation code.

If so, yes, in your query go to Relationships > Individuals > Relation code, and select "does not equal" and "professor." Or would this be the "recip relation code" for these records? I always get those confused. Check a record who you know has a "professor" relationship on their constituent record and see if it's in "relationship" or "reciprocal." Whichever is the case, that's that's the criteria you would select "does not equal" "professor" for.

Suzie Capps · August 2017

Ryan Hyde:

When you say relationship 'type,' do you mean relation code? That's the area in the lower right hand corner of the a relationship record where you pick a relation code and a reciprocal relation code.

If so, yes, in your query go to Relationships > Individuals > Relation code, and select "does not equal" and "professor." Or would this be the "recip relation code" for these records? I always get those confused. Check a record who you know has a "professor" relationship on their constituent record and see if it's in "relationship" or "reciprocal." Whichever is the case, that's that's the criteria you would select "does not equal" "professor" for.

That is what I have at the moment - but unfortunately the query result brings up everyone who has at least one relationship that is not "professor" - so a lot of them have got a professor relationship (in addition to other relationships). Even when supressing duplicates, there are still many people that have professor relationships appearing in the output.

Alan French · August 2017

Are you using a constituent query or a relationship query? A relationship query would give you those people with other relationships, whereas a constituent query shouldn't.

Suzie Capps · August 2017

Alan French:

Are you using a constituent query or a relationship query? A relationship query would give you those people with other relationships, whereas a constituent query shouldn't.

I've tried using a Constituent query and an Individual query - both of which are having the same problematic results.

Ryan Hyde · August 2017

Ah, I see, yes that would be expected. If I have three relationships and two of them do not have the code "professor," then indeed I would show up in the results even though I do have one relationship that is "professor."

So what you need is two queries. The first one, the one you've already written, is your "include query." And actually you could simplify it - the only criteria you actually need is Constituent Code = Alumni.

Your second query will be an "exclude" query. This second query will simply say Relationships > Individuals > Relation code = "Professor." So you're grabbing anyone who does in fact have a relationship code of "professor."

Then you're going to merge the queries, which is a button you'll find out in the query module, just to the left of "delte." Your primary inqury is the "include," your secondary query is the "exclude" and your operator is SUB.

So you're taking ALL alumni and subtracting out ALL records who have a professor relationship. The resulting static query will have the correct results.

Suzie Capps · August 2017

Ryan Hyde:

Ah, I see, yes that would be expected. If I have three relationships and two of them do not have the code "professor," then indeed I would show up in the results even though I do have one relationship that is "professor."

So what you need is two queries. The first one, the one you've already written, is your "include query." And actually you could simplify it - the only criteria you actually need is Constituent Code = Alumni.

Your second query will be an "exclude" query. This second query will simply say Relationships > Individuals > Relation code = "Professor." So you're grabbing anyone who does in fact have a relationship code of "professor."

Then you're going to merge the queries, which is a button you'll find out in the query module, just to the left of "delte." Your primary inqury is the "include," your secondary query is the "exclude" and your operator is SUB.

So you're taking ALL alumni and subtracting out ALL records who have a professor relationship. The resulting static query will have the correct results.

Perfect! Thank you

Alan French · August 2017

Ryan Hyde:

Ah, I see, yes that would be expected. If I have three relationships and two of them do not have the code "professor," then indeed I would show up in the results even though I do have one relationship that is "professor."

I'm not seeing the same behaviour here - I've just created a query for people who do not have a Spouse relationship type and a second query for constituents who do have a Spouse relationship, and I'm getting zero overlap between the two even though we definitely have married constituents who have other individual relationship types on their records.

Ryan's suggestion of merge queries is a good one; at least you can be sure who's included or not! Personally I tend to use them as a last resort though, as I find static queries a pain in the butt sometimes

Ryan Hyde · August 2017

Welcome!

Ryan Hyde · August 2017

Alan, are you looking at relationship code or are you querying on relationship code or "this individual is the spouse"?

Alan French · August 2017

I'm querying on relationship code. I also tried it with organisation relationship type of "Current Employer" and got the same thing - the record wasn't included in the results even if it had other relationships of different types.

Ryan Hyde · August 2017

Yeah, seeing the same thing on my end actually.

Maybe different version of RE being used?

That or it's dark magic. I'm not sure which is more likely, honestly.

Joanne Felci 2 · August 2017

Ryan Hyde:

Yeah, seeing the same thing on my end actually.

Maybe different version of RE being used?

That or it's dark magic. I'm not sure which is more likely, honestly.

dark magic...most definitely dark magic...

Christine Cooke 2 · August 2017

Ryan Hyde:

Ah, I see, yes that would be expected. If I have three relationships and two of them do not have the code "professor," then indeed I would show up in the results even though I do have one relationship that is "professor."

So what you need is two queries. The first one, the one you've already written, is your "include query." And actually you could simplify it - the only criteria you actually need is Constituent Code = Alumni.

Your second query will be an "exclude" query. This second query will simply say Relationships > Individuals > Relation code = "Professor." So you're grabbing anyone who does in fact have a relationship code of "professor."

Then you're going to merge the queries, which is a button you'll find out in the query module, just to the left of "delte." Your primary inqury is the "include," your secondary query is the "exclude" and your operator is SUB.

So you're taking ALL alumni and subtracting out ALL records who have a professor relationship. The resulting static query will have the correct results.

And because someone can have multiple relationships -- you should be Exporting that list. Query is a grouping tool 99% of the time, not reporting or export

Individual query on relationship type

Comments

Categories