"Blank ID" results for table entries

Jenelle Murray

I'm trying to clean-up the table entries in my organization's "solicit code" table, but I can't delete some entries because they are still in use. When I query on the solicit codes, there are no results (even when including inactive and deceased records).

When I go to the table entry and select "view uses", it gives me a list of constituent IDs that are all "Blank ID". Does anyone know how I can track down which constituent records these solicit codes are in use on?

JoAnn Strommen

Have gotten this frequently. Love the clean up function but wish there was a direct click link to those records.

For some I did queries looking for that info piece on relationships or other places where that field might be. Found some but not all.

Was able to resolve some of them with support. Required sending FTP of db to them and they can find the use cases.

Joe Moretti

Why not just do a query where the Constituent is blank.

Jenelle Murray

https://community.blackbaud.com/discussion/comment/315440#Comment_315440

Thanks for this suggestion, Joe. I should have mentioned I tried that as well but it returned zero records.

JoAnn Strommen

What I was told by support is that it's somewhere on a record, just not a field on the constituent record. Some are relationships, some are inactive fields. Tend not to be in 'query-able' places. I have run into this with a variety of my clean up projects.

I have 4 instances of a gift sub-type that is I've tried to delete - no one here has any idea what it is about/when used or anything. Have not been able to find with a query. Nor could support suggest where else to query. Export option here doesn't provide any data other than what is seen here.

Jenelle Murray

https://community.blackbaud.com/discussion/comment/315448#Comment_315448

That's really helpful, thank you! I'll poke around in RE and see if I can find where these fields are hiding.

Carlene Johnson

https://community.blackbaud.com/discussion/comment/315437#Comment_315437

This is good to know about! Thanks for always providing such great responses here.

Rachel Cavalier

This is one of the banes of my database managing existence 🙃I've had a few table entries over the years where I've never been able to figure out where they show up - not on a participant, not on a gift, not a constituent or relationship… a mystery!!

Karen Diener

Solicit codes are only available on a constituent record though. The only other thing I can think of is that you have some that are mapped to Consent functionality, so although the solicit code doesn't appear on a constituent record, it might be within their Consent section. I'm willing to bet that's the issue.

Carlene Johnson

https://community.blackbaud.com/discussion/comment/315463#Comment_315463

It is possible that someone created a custom field/attribute (on any type of record) and attached the Solict Code table to that custom field. (Ask my how I know! 🙄) But I agree that Consent could likely be the case here.

Karen Diener

https://community.blackbaud.com/discussion/comment/315471#Comment_315471

You can do that?? That sounds like dark magic.

Alex Wong

https://community.blackbaud.com/discussion/comment/315610#Comment_315610

Yup, it is quite useful. Any custom field using table data type can select any table as its selection. including default tables like campaign category, fund category, etc.

Patrick Manning

This might be a situation where those solicit codes existed on a record and that record was deleted, but the data was left behind in a junction table. This creates an orphaned, or "stale", record and breaks referential integrity in relational databases. It does not happen frequently but it does happen. In fact, we still have a 2 year old open case with Blackbaud where a deleted constituent record was still showing up on the soft-credit tab of gifts (btw, that soft-credit is still there after 2 years).
At my previous employer, when clients still hosted their own databases, you could use SQL Server Management Studio (SSMS) to find and clean this up.

Catherine Burns

@Jenelle Murray do you have List Management with ImportOmatic or something similar? When I have had this issue it is usually because of non-constituent records, and for List Manager you will not see them in query results. Within List Manager you can create a list from a query that looks for the solicit codes you want to remove. Even though you won't see List Manager records in the query it will still pull them into the new list so you can clean them up there.