RE Query Results: 'One of' vs. 'Not one of'

Karl Craven 2

I've noticed that sometimes my RE queries don't behave with the logic I would expect. For example, I built a query to return the details of all Proposals with an Ask Date this FY, where the Status is 'Not one of' two of the options we have. Looking at the results, I noticed that some Proposals I had recently updated were not included, and should have been, based on the criteria.


When I changed the Status criteria to 'One of' all the other options, the results display correctly.


Why isn't 'Not one of A or B' the same as 'One of C, D, E, F or G'? Logically, they are the same thing.

Zane Magnuson 2

"Not One Of" is a tricky set of criteria that you have to use very carefully.


Let's say you have Constituents with the following tags


Joe Smith: A, B, C

Jill Smith: A, C

Frank Johnson: B, D

John Franklin: A, B


If you say "One of A or C", you will get Joe Smith (A, B, C), Jill Smith (A, C), and John Franklin (A, . That's easy enough.


But watch what happens when you say "Not One Of A or B": You might expect no results, but instead you'll get Joe Smith (A, B, C), Jill Smith (A, C), and Frank Johnson (B, D).


The reason for that is "Not One Of" and "One Of" are looking at each value on its own instead of as a whole. In that second example, Joe, Jill, and Frank have tags that meet the criteria - they have a tag somewhere in their record that is neither A nor B, even though they also have those A or B tags as well.


There are good uses for the "Not One Of" criteria, but if you're just trying to exclude a particular group you're better off creating two queries and doing a SUBtraction merge. You do the first query ("Query 1") of everyone possible, then a second query ("Query 2") to find all the people you want taken out of the list. Then you merge the two queries as "Query 1 SUB Query 2".

Karl Craven 2

Interesting. Yeah, I can see that in the case where a record has multiple values associated with it. But in the case of Proposal records, each Proposal only has one Status value, so it seems like the logic should still work.

Example:

Let's say I want to list all Proposals with a Status of 'Solicitation Made', 'Commitment Made', or 'Gift Made'. I don't want to list those with a Status of 'Declined' or 'Withdrawn'. In this example, these are all the possible values on a required field (there are no blanks).

Status is one of 'Solicitation Made', 'Commitment Made', or 'Gift Made'
... should give me the same results as...
Status is NOT one of 'Declined' or 'Withdrawn'
... right?

JoAnn Strommen

Not to laugh at your expense, Karl, but I did just laugh.


Logically it should work but not always when it's within RE.  I've gotten some really strange results using several of the negative criteria.  Even in some other reports.  Per BB support I spoke to "RE is functioning as designed."  Maybe the programmer way back when had faulty logic.


Hope you get what you need...thanks for the laugh and welcome to the quirks/glitches of RE.

shani traum

I've discovered that "Not one of" does not actually work on many cases.  When I called BB help, I was told, that, yeah, it's a know thing.


So, it's a known bug. 


Not helpful, I know, but there it is.


 

Joshua Bekerman bCLO bCRE

This probably also has to do with the lack of a Proposal query or export type in RE.  Are you using a Constituent query?  What output fields are you using?  A constituent query looks at all of the proposals on the constituent's record, so it does see multiple Status values.  Or something like that.  Zane could probably explain it better...

Karl Craven 2

This kind of thing is extremely worrying. Sometimes these queries form the basis of reports that are shared with our Chancellor. According to all logic, I might be confident that they are displaying the correct data, but if the database doesn't always adhere to logical assumptions then we're going to get tripped up.


How about fixing some of these 'known issues', guys? We should not have to just laugh off and accept these things - we paid a lot of money and spent a lot of time converting to this system, and I often think we may have been better off before we did so.

Joshua Bekerman bCLO bCRE

I'm not sure I would classify this as a bug, especially when it comes to querying on proposals.  I went through the same conversations and troubleshooting with Blackbaud support when querying on ratings.  If there is anything Blackbaud should be doing it is creating a query and export Proposal type, so the criteria and logic would be applied to each individual proposal (or rating) record and not all proposal records (or rating records) on the constituent record.


I'm sure this is on the Ideabank already, I'll find it and post the link.

Joshua Bekerman bCLO bCRE

Here it is, 506 votes since May 2012.  

https://rediscovery.uservoice.com/forums/137015-raiser-s-edge-discovery-topics/suggestions/2861856-make-proposals-a-record-like-gifts-that-can-be-exp

 

shani traum

It's not just proposals.  I was doing a Constituent Query trying Not One Of on a relationship attribute and another on a solicit code to name a couple, and it didn't work.


Shani

Joshua Bekerman bCLO bCRE

I imagine it would be the same issue when using a Constituent query on Relationships.  Did you try the same criteria using a Relationship type query?

John Heizer

Do the Constituents have more than one proposal?  The SQL engine is probably looking at ALL proposals and not necessarily using the Not One Of condition on each proposal individually, but at all proposals.  This is another one of those issues where one-to-many can bite you on the butt when you don't have the capability to query on the "many" side of things.  RE has to go through a lot of work to take the graphic interface of RE and turn it into valid SQL queries and sometimes it doesn't work the way we want/expect it to.  I've found that in some instances if you group criteria together within parentheses (even when you think it wouldn't be necessary), particularly when you're dealing with one-to-many issues, can sometimes make RE give you the results you are looking for (narrowing the conditions to each of the "many" rather than anywhere within the entire set).


Like Joshua said, lacking the ability to do a Proposal Query and Export, severly limits usability.  So add your votes!!

Zane Magnuson 2

Karl Craven:

Interesting. Yeah, I can see that in the case where a record has multiple values associated with it. But in the case of Proposal records, each Proposal only has one Status value, so it seems like the logic should still work.

Example:

Let's say I want to list all Proposals with a Status of 'Solicitation Made', 'Commitment Made', or 'Gift Made'. I don't want to list those with a Status of 'Declined' or 'Withdrawn'. In this example, these are all the possible values on a required field (there are no blanks).

Status is one of 'Solicitation Made', 'Commitment Made', or 'Gift Made'
... should give me the same results as...
Status is NOT one of 'Declined' or 'Withdrawn'
... right?

Well...maybe. In the second case, it's possible that it'll also pull constituents that don't have a Proposal at all. After all, their Proposal status is neither "Declined" nor "Withdrawn".


I've run into similar issues with "Not One Of" in my experience and if I dig and dig and dig and dig, I can usually find a cause for it. And it's seldom intuitive. That's why I've gotten to a point of avoiding it almost entirely. "Not One Of" seems to work best if it's the only criteria used in a Query or one of just a small handful of criteria. The more complex a Query gets, the more likely it becomes that you'll get undesired results from it. Another option is trying a parenthetical set of "<field> does not equal <value>" criteria. It's clunky, but it replaces the "or" logic of "Not One Of" with "and" logic.

Laura Caswell

I never ever ever use negative operators in RE. I have never been able to get them to work. BB and everyone else I have ever talked to has said they dont' work as expected. I tell all new employees who will be querying not to use them and if they do use them in spite of my warnings they should expect quirky results. I use merge queries with the SUB operator to deal with "I don't want to see these results" and that always seems to work.


We have been using RE since 1999 and that is my one hard and fast rule.


Just my 2 cents.


Laura

Laura Caswell

Info Tech

Worcester State University

Worcester MA

Rachel Slager 2

Oh no, everyone seems to think this feature is broken, but I assure you it isn't!


Negative operators are an extremely useful feature.  They aren't bug-y, they are logical, but the guides for Raiser's Edge do not convey the nuance.  Zane's description above is really good, and I'm piggy-backing with a few tips.

Positive or Negative - There can be only one

If you are using the same field repeatedly in query criteria, be sure that the operator for that field is always positive or always negative.  Eg. if you have one line asking for Status --> Not One of, then if you add another line for Status you must choose does not equal, or not blank, or not between.  This rule generally holds true even if two sets of criteria are separated by parenthesis.  Think of this like a command you are giving the computer to both turn on and turn off - it's trying to execute both commands simultaneously, which is impossible.  So in the rare case where you need a specific field to be based on both negative and positive criteria, you must use merge query features.  Generally though you can get what you need with a little single query tweaking.

Not One of vs. Does not Equal

Logically, when you use positive operators you will return records that fit the criteria (all records where Attribute

Color description is one of Blue or Red).  When you use Not One Of, you are asking for records which have some other color attribute but they might also be Red or Blue, or they have no color attribute at all.  Not one Of should be interpreted as "Other Than".  If a record is both Red and another color other than Red, they will appear in results of "Not One of Red", but they won't appear in results for "Does not Equal" red.


1.  If you want your criteria to Exclude all records that are either Red or Blue, even if they are also some other color, then your Operator must be Does Not Equal.  (so you'll have 2 lines of criteria, Does not equal Red and Does not equal Blue).


2.  If you want to Exclude  records without any data, so any records without a Color Attribute, then your Operator should be Not Blank.  If the field has it's own Import ID then you should select Import ID is Not Blank.  (so you'll have a criteria line Attribute Color Import ID is Not Blank.)  In the case of Proposal Status, status doesn't have its own Import ID so you just go by the field itself (criteria line of Status Not Blank)



I hope this makes sense to someone who can explain it better.

 

Laura Caswell

Hi Rachel.


Thanks for the clarification on One of vs Not one of. And all that clarification just makes me want to stick with my "do not use 'not' in a query and merge everything" rule. Too much thinking to keep it all straight.

laura

 

Alan French

What I find most frustrating about Not One Of is that it seems to work differently depending on which field I am using.


I have a constituent record with 2 constituent codes (A and and 2 financial information types (C and D).

Querying on this record using "Constituent Code not one of A" results in zero records being returned.

Querying on the same constituent record using "Financial Information Type not one of C", yields one record: financial info type D.


I'm sure the SQL behind it is perfectly logical and working as intended, but it's not very helpful.