Have you ever seen a query output row that simply DOESN'T EXIST in a record??

Keri Barnhart · January 2019

I am baffled by a clean-up query I'm running and neither google nor forum search is being helpful. I will probably open a support ticket, but first - has anyone ever seen this happen before or know what could have caused it?

My query is looking for Suffix 1 field data that we want to store in Suffix 2 - things that we don't want printing in the default name. It's also making sure that Suffix 2 is blank, so that I can run a swap import.

(Suffix 1 is Not Blank

AND Suffix 1 is Not One Of: Jr., Sr., II, III, IV, V, VI)

AND Suffix 2 is Blank

Output: Const. ID, Suffix 1, Suffix 2, Gender, Title 1, Date Last Changed

4 separate rows show Suffix 1 = Jr. but when I open those records, there is nothing in the Suffix 1 field

The records were last changed 10/30/18, 1/2/19, and 1/9/19.

Yesterday, I did a table cleanup and merged the suffix "J." with "Jr." but only after querying to see who had the suffix assigned. One person had "J." and I manually changed it to "Jr." before running the table cleanup to merge/delete "J." I only mention this because it was my first guess - that the table cleanup bugged out.

My concern is that I'm doing a lot of massive clean-up as I adjust our name fields and Addressee/Salutations to work with a new name policy that does not use titles, and I'm trusting that the query output is actually on the record. But it looks like I can't trust that the query output is correct?? So is this a known thing that others have experienced? Is it a fluke?

Alan French · January 2019

This may be a stupid question, but are you using a dynamic query or a static one? If it's static, it might not be reflecting the data that's currently on the records.

Keri Barnhart · January 2019

Alan French:

This may be a stupid question, but are you using a dynamic query or a static one? If it's static, it might not be reflecting the data that's currently on the records.

Good question!

This is a brand new query freshly built today. It's a Constituent Query and Dynamic. My Suffix 1/2 criteria and the output items are all from the "Constituent Information" criteria tree, thus not based on relationships (I did check that none of these guys have spouses with suffixes, too, just in case).

Alicia Barevich · January 2019

I actually just recently did a similar audit in our system!

You'll want to do an inverse query:

Suffix 1 one of [options you don't want]

AND

Suffix 2 blank

For our system, we had so few suffix 2's, I didn't include that in the criteria, just the output for manual review.

Keri Barnhart · January 2019

Alicia Barevich:

I actually just recently did a similar audit in our system!

You'll want to do an inverse query:

Suffix 1 one of [options you don't want]

AND

Suffix 2 blank

For our system, we had so few suffix 2's, I didn't include that in the criteria, just the output for manual review.

We have such a huge list of titles/suffixes and Add/Sal Configs that I've got into the habit of "not one of" + "not blank" just to be super clear what my query is about! but you're right - and your reply made me test something else.

Query:

Suffix 1 is Jr.

Constituent ID is one of the 4 from above

Results: None!

Query 2:

Constituent ID is the same one I just tested, no other criteria

Output is Suffix 1 + ConstID

Results: Suffix 1 = Jr., ConstID

I am......not happy with this result.

I asked my coworker if he had ever seen this, and he found that the mystery suffixes appear in NXT! But when I look at our pre-NXT transfer db copy from 10 months ago, the same error is in place.

Dariel Dixon 2 · January 2019

Suffix/Prefix cleanup can be difficult, because those tables exist on non-constituent records as well. Make sure to check your non-constituent records as well.

I had something like this happen once before. I think it was some ghost records that had been deleted with the suffix on the record. The system still acts as if it is still on a record, even though the record no longer exists. Or something like that. It was awhile ago, and I never finished that project.

Larry Wheeler · January 2019

Could these records have been duplicate/merged in the past? Possibly such a merge could be ghosting the suffix somehow?

Keri Barnhart · January 2019

Larry Wheeler‍ The previously merged record scenario is what I'm starting to suspect, especially since we have the problem that Dariel Dixon‍ mentioned of the Title and Suffix tables being littered with entries that can't be found anywhere with positive querying (not on event participants, memberships, constituents, or relationships) but which also can't be deleted.

BUT the thing with merged records - if Record A has the field data and Record B has the field empty, the "winning" record in the merge will always get the field data, whether you pick A or B to win.

If it's a bug with a cause, I can at least be prepared for it. But without being able to identify where it's coming from, I can't even be certain that it's not causing ripples elsewhere in other fields that are maybe more important than a Suffix, and that worries me.

Anne Herbert · January 2019

Don't know if this applies, but is it possibly pulling suffix from a contact relationship for this person that has the suffix in it.

Larry Wheeler · January 2019

Keri Barnhart You are right in being concerned. My personal experience with 'ghosted' data is on a modified Comparisons and Summaries report. I have a fiscal year comparison broken into gift size ranges. I have a report for each const code which I put together in excel to make a weekly "giving to date" comparison of current and previous fiscal years by const code, by gift size.

Deleted gifts will create a row with zeroes in all amounts and gift/donor counts, until a non-deleted gift of that size range shows up.

Good luck with your research. I would be interested in your findings if there is a known reason. I have never had back-end access as we converted into a hosted environment. The responses I get are along the lines of "it's just way it is. maybe you could change your business processes?"

Keri Barnhart · January 2019

Well, I've had a "Connecting to Agent" support window open for over 30 minutes now, no idea if it's actually connected or not, so here's an update:

I was saying how it's difficult to positively query for these things, but we realized if we query on "Not one of" and include the entire list, plus "Not blank", we can then spot the data fields that are simultaneously blank and not blank (Schroedinger Fields!). This worked well - not only do we have the four Jr. Schroedinger Suffixes 1, but there's an MBA Schroedinger Suffix 1 out there, too. We tried it on the Title 1 field and found some Mr and MS - but also a very strange beast: Husband. The date added for these constituent records ranges from 13 years ago to 2 years ago, by the way.

"Husband" does not exist in our Titles table. Not at all. If it ever did, it has been years since it got Table Cleanupped out of the place. I ran the same query on Individual Relationship and found a Schroedinger Title Son, plus more Husband. Again, unlike the Jr. and MBA suffixes, Son does not exist in our Titles table.

There is a bug where cleanup of the Alias field can remove an Alias type while leaving the Alias data behind, sort of a ghostie. I've also seen this with Attributes - RE tells you that you can't delete Attribute Categories that still have data sticking around, but somehow it happens anyway.

So we suspect that at least some of what is happening is explained by incomplete or buggy Table Cleanup. I don't know why that would be the case with an MBA or Jr. suffix - both of which are the standard forms currently in the table - but it certainly could explain Son, Husband, MS, and Mr (no period) in the Schroedinger Titles category.

I have so many other more important things to be working on, but I desperately want to spend a few hours scouring the different fields that use tables or free-text to see if there are any commonalities among where the Schroedinger Field appears!

Christine Cooke 2 · January 2019

Not one of is probably what is giving you the issue. Not one of never works like it's supposed to. You would be better off doing one of and choosing all the ones you plan to relocate.

Alan French · January 2019

Aaron Rothberg:

If a constituent has the Solicit Codes of Do Not Mail and Do Not Email and you ask for constituents with a Solicit Code Not One Of Do Not Mail, you'll still see that constituent in your list because the constituent does indeed have a Solicit Code that is Not One Of Do Not Mail, it's the Do No Email code.

Aaron Rothberg‍ are you sure? This isn't how queries behave with solicit codes for us (luckily, since a lot of my queries rely on these records being excluded!). I've just tested it with a query only including my record; I got zero results when asking for Not One Of one of the two solicit codes on the record.

We're on RE 7.96 (UK, self-hosted) but I hope this behaviour doesn't differ between versions...?

Aaron Rothberg · January 2019

Hi Alan French‍ -

I stand corrected! It pays to test things before I open my mouth. ;D

Keri Barnhart · January 2019

Thanks to everyone for advice on using the Not One Of in queries - I'm pretty satisfied with how it works and typically try to avoid the various "Not" criteria where there's any ambiguity. In this particular project, One Of or Not One Of+Not Blank should have had the same result - the fact that it didn't is interesting and has revealed some quirks in the database. (Also, I admit, I would have used "One Of" except that I had got into the habit of Not One Of or Blank that day while working on salutation text, where I truly did want Not One Of to include blanks so I could set primary add/sals that were blank. I'm glad I did, since we now know to be on alert for this bug!)

Anyway since there was some surprise/skepticism

here is an example of the query criteria, results, and matching record. We have cleaned up the Jr. errors I mentioned last week, but did not clean up this MBA one, so you will only see the MBA result in the picture.

0f9c2d8d58ba580758e68c8a0b77e7f2-huge-su

6ac6c780692af22c865331f28fa71140-huge-su

Aaron Rothberg · January 2019

I must say Keri that is truly fascinating. I believe you now! How odd.

John Heizer · January 2019

Keri Barnhart‍ that is REALLY strange!!! I wish I had direct access to your database to see if there's some "junk" data in that Suffix field that keeps it from being "blank" but not having a good link to the table that holds the suffix text data.

It may seem strange and counterintuitive (particularly when using an AND operator), but in the Query criteria try putting the two line inside parenthesis. I find that sometimes the process that parses the Query GUI information into the actual SQL used can benefit from making things as obvious as possible. See if that eliminates the "bad" row.

Another thing to try is in the Query Tools -> Query options... -> Advanced Processing tab, try selecting the alternate methods and see if that makes a difference.

67fad3e53d40d2cda884abb7f26abb06-huge-ca

Keri Barnhart · January 2019

Well, my goodness, John Heizer‍ you have just given me another conundrum!

Since I fixed my previous example, I decided to use the Individual Relationships results to test your suggestion about the SQL tables and parentheses.

First, adding or removing parentheses doesn't make a difference, and neither does the Advanced Query Options tickybox.

But I was frustrated (as I always am, and always forget!) that if I run a Relationship query, I can't actually access the records from the query, the way I can with Gifts or Memberships or Event Participants. So I put the criteria into a constituent query, using the Relationship -> Individual tree. And my results list is about 9 fewer!

This might be a completely different thing I've just never had reason to learn about before, but I can't figure out how the additional folks in the Relationship Query wouldn't show up under the Constituent Query Individual Relationships. I swear that my criteria, output, sort, and Advanced settings are identical - the only difference is using Relationships vs Constituent for the query type.

Also, the first example in the relationship query (Kathleen and alexis) is definitely an individual relationship. If I change the constituent query criteria to search for Individual Relationship with the name in the record, I find her. If I get rid of the schrodinger field criteria and just put in "Ind. Relationship Title 1 = Not Blank", she still appears. (By the way, if I look at her record, the field definitely appears to be blank.)

Picture 1: comparison of the 2 queries - top is Constituent Query, bottom is Relationship Query

4a51f3a606828d4b08bc2ca39f3d47a3-huge-re

Picture 2: Constituent Query criteria to find alexis, who is a relationship for Kathleen (first result in the relationship query)

8380022126ef61e441d481d1c7198655-huge-al

Picture 3: Constituent Query results that found alexis, plus the individual relationship record - showing a mismatch on Individual Relation Title 1

379b04065f025e2e99bc82da95c11619-huge-al

Does anyone know why the Constituent and Relationship queries are getting such different results? It's probably time to try Support again, but last Friday I spent over an hour with the chat open and no change, so I have no idea if the chat support actually works anymore, but I think I definitely need to do this with examples, and not over the phone!

Aaron Rothberg · January 2019

Hi Keri-

I don't have an answer to your specific circumstance, but I did some testing and perhaps my results will help. I have a constituent that has the following relationships:

10 Individuals

7 Organizations

1 Bank

3 Schools

3 Assigned Solicitors

1 Fund

If I build a Constituent query looking at that one constituent and ask Output to give me all of the Individual Relationships, I see the same 10 relationship that I find in the constituent record.

If I build a Constituent query looking at that one constituent and ask Output to give me all of the Organization Relationships, I see the same 7 that I find in the constituent record.

If I build a Relationship query looking at that one constituent and ask Output to give me all of the Individual Relationships, I see 17. 10 of them have a Individual Relation Name of the individuals they are related to. 7 of them have no Relation Name and no Relation Code. Conversely, if I ask Output to give me all of the Organization Relationships, I also see 17, but 10 of them have no Organization Relation Name. Given the fact there are 17 relationships in total, one might reasonably conclude that Relationship queries Output all relationships, but only provide values for the Relationship Type you've asked for in your query.

I'm not sure what to say in your circumstance because unlike my tests, you have an Individual Relation First Name for each relationship in your Relationship query.

I would start by comparing your results to the actual Relationship Tab and see if you can get the number of relationships to add up.

Anne Herbert · January 2019

This is just a guess, I didn't test, but we've had constituent and relationship pull differently when the relationship checkbox for contact isn't checked. It gets pulled in the constituent but not the relationship.

Heather MacKenzie · November 2019

Keri Barnhart‍, I'm just discovering this thread now, and holy moly. RE sure has it's quirks, doesn't it? I hope you were able to complete all your cleanup stuffs by now.

Have you ever seen a query output row that simply DOESN'T EXIST in a record??

Comments

Categories