Identifying Organizations that were entered as Individual Records

Norah Jones

Hi all,

I recently started a new position managing the NXT Database at science center. Within the last year, they migrated from eTapestry to NXT… as I've been using the database I've noticed that a lot of companies/orgs, etc. are entered in as individual records.

I've been able convert a lot of those records to the correct type by querying records with “last names” of ‘Inc.’, ‘LLP’ and so on, but I'm still encountering records for companies that have been entered in as individuals.

I would really appreciate if anyone else has some interesting ideas on identifying these records! Thanks in advance

JoAnn Strommen

@Norah Jones Several things come to mind but all depend on how your data is entered. I don't know all the data options in eTapestry to know if there would be things like relationships.

• Do you have a specific constituent code that came in with conversion that would help? Or ID coding for orgs?
• Did the company names split info FN and LN fields? If not, query for blank FN field?
• Do you use titles for your individual records? If so, query for blank title records? I'm guessing for an org record that would be blank.
• Did you bring in relationships where you could filter on contact / contact type?

Depending on the size of the database, I'd consider query segments of individual records with name fields output so you can scan list and then click on any from query that you need to change or possibly exporting data to Excel and looking at name fields there. Take ID field and with records you want to change I think you can do a global conversion.

Just some thoughts. Best wishes with your cleanup project.

Faith Murray

@Norah Jones
JoAnn's suggestions are excellent. You'll get some false positives this way, but you can also try querying for those who give gifts through “Business Check” pay method, if this field was available in your eTapestry input data.

If you enjoy working in Excel, you can also create a query of “Keycode = Individual”, export their last name and ID, and then perform a column search in Excel for spaces. Note the empty space between words in “Blubber Co.” that does not exist in (most) individual last names.

Norah Jones

@JoAnn Strommen
Thanks so much for your suggestions!

Katherine Mannion

@Norah Jones

I'm a little jealous! I love a project where you have to think about different ways to search for the anomalies.

I like to do queries with the operator “contains.” Like, Last Name contains The. Maybe you can even do Last Name contains space (put a space in the field, not the actual word space). I'm not in a position to test that right now, but it might work. Then you'd have a query to globally convert to Org records. If you need to remove a few that shouldn't be converted, pull the query over to Query List and remove the ones you don't want.

Have fun!

Faith Murray

@Katherine Mannion
Alas! I tested the “contains (space)” in Query, and it doesn't work. You can do an “Open Constituent” search in Records using Last Name = *(space) but then you have to manually browse the search results list. That's why I suggested exporting to Excel and searching that there.