Converting People Records to Businesses Records

  • By Michael Garrison 2 Years Ago

Rock, unlike many other systems, keeps businesses separate (at least in the UI) from person records. One of the most obvious benefits to this system, is that a business can have an actual person record linked to it as the contact for that business, without affecting that person's personal contribution statement. (The business can be accessed from the person's profile by use of the Known Relationship box on their profile)

But what about your imported data?

It's extremely likely that your businesses were imported to regular Person records. And there can be quite a few (and they may have financial contributions already attached to them, perhaps with scanned check images and the like), so it can be prohibitive to re-create the businesses from scratch.

Take heart: you're not alone! Fortunately some of the brightest members of our community have run into this already and blazed the trail- and having benefitted from their advice and guidance, I'm here to share with you what I learned and how I successfully converted my own imported business records.

Step 1: Identifying the Records

Hopefully your people were imported with Connection Statuses already- and hopefully there's one just for businesses. This is how my data was- in my case the "Business" connection status had an ID of 525, so this is what I'll be using in my examples throughout this article. Be sure to replace that wherever appropriate.

Perhaps your businesses are marked with tags, or being a part of a group, etc. Really, the easiest way to do this operation is to have them in a unique Connection Status- so I recommend using the bulk update function on Data Views or groups to move your businesses into their own Connection Status if you're using an alternate means of business identification.

If you don't know what the ID number is for the connection status you're using to identify businesses, it can be found by going to General Settings on the Admin menu, clicking Defined Values and selecting Connection Status, then hovering your mouse over the Business connection status: a tooltip will appear informing you of the numerical ID of that status.

Getting the ID of the target Connection Status

Step 2: Verifying the Record Readiness

Your business records need to be in a family of their own- if they have been placed in a family with other people (perhaps the business owner), you need to move them into their own family by using "Edit Family" and then clicking the "Move to new family" button beneath their picture.

They also need to be set to "Combine Giving" with their family (which again, nobody else should be in). If they're set up to not combine giving, or if they're set to an individual contribution group, you need to set them back to combine giving with their "family" once they're in their own family of one.

To check both of these items at once, you can use the following query in Admin Tools -> Power Tools -> SQL Command:

SELECT p.[Id] 'PersonId', p.[FirstName] + ' ' + p.[LastName] 'Name', p.[GivingId], g.[Id] 'FamilyId', (SELECT count(*) FROM [GroupMember] WHERE [GroupId]=g.[Id]) 'FamilySize'  
    FROM [Group] g
        LEFT JOIN [GroupMember] gm ON g.[Id]=gm.[GroupId]
        LEFT JOIN [Person] p ON p.[Id]=gm.[PersonId]
    WHERE g.[GroupTypeId]=10 --family
        AND p.[ConnectionStatusValueId]=525 --'business' connection status !!CHANGE THIS VALUE TO MATCH YOUR SETUP!!

After you change the number '525' above to the Connection Status ID number to match the ID of your identifying connection status, this will give you a list of everyone you're getting ready to convert to businesses. It will list their family number and their giving group (which should both match except that their giving group is prefixed with a 'G'), and also list their family size (which should be 1).

This also gives you a preview of what the business name will be after we run the conversion- if some of the names don't look right you can edit them either now or later.

Manually checking each row can be tedious however, so once you make sure that these results list the correct individuals, you can use this slightly modified query to list JUST the individuals you need to look deeper into (again, be sure to change the 525 number to the correct ID for your installation):

SELECT p.[Id] 'PersonId', p.[FirstName] + ' ' + p.[LastName] 'Name', p.[GivingId], g.[Id] 'FamilyId', (SELECT count(*) FROM [GroupMember] WHERE [GroupId]=g.[Id]) 'FamilySize'  
    FROM [Group] g
        LEFT JOIN [GroupMember] gm ON g.[Id]=gm.[GroupId]
        LEFT JOIN [Person] p ON p.[Id]=gm.[PersonId]
    WHERE g.[GroupTypeId]=10 --family
        AND p.[ConnectionStatusValueId]=525 --'business' connection status !!CHANGE THIS VALUE TO MATCH YOUR SETUP!!
        AND (
            p.[GivingId] != 'G' + CONVERT(varchar(10),g.[Id]) --Not combining giving with their "family"
            OR (SELECT count(*) FROM [GroupMember] WHERE [GroupId]=g.[Id]) > 1 --Not alone in their "family"
        )

If the first query returns a correct list of individuals and the second query returns no results, your records are ready to go.

Step 3: Running the Conversion

Your business records probably do some creative juggling of their names- first, last, both, etc. But Business records in Rock only use the Last Name. So we'll combine First Name and Last Name together for the final Business name. Edit August 2016: The community also found that there shouldn't be a leading "space" character in the business name, and that it also needs to stay under 50 characters, so our final Last Name is determined by the following: CAST(RTRIM(LTRIM([FirstName] + ' ' + [LastName])) AS VARCHAR(50))

We also need to null out things like the Record Status Reason, Connection Status, Birthdays, Gender, Marital Status, Anniversary, Photo, Graduation Year, etc.

And it's always advisable to be able to identify the records you've edited in bulk later on- just in case. So we'll use the SystemNote field in the Person database table. However, since other routines may have already filled something into this field, I recommend simply appending your note to the end of whatever is already in there. In my case, ALL of my business records had a System Note indicating the date they were imported by Excavator, so I simply added a newline (CHAR(13)+CHAR(10)) followed by my note. You may want to edit the below query to check whether there's anything already in SystemNote (and only add the line break if there is) or you may decide it doesn't matter.

So with no further ado, here is the query to paste into SQL Query which will convert the correct Person records to Business records, keeping any contributions which may have already been attributed to the record. Once again, be sure to edit the last line as appropriate in the very likely event your Business ConnectionStatusValueId isn't 525.

UPDATE [Person]  
    SET LastName=CAST(RTRIM(LTRIM([FirstName] + ' ' + [LastName])) AS VARCHAR(50))
        ,RecordTypeValueId=2
        ,RecordStatusReasonValueId = NULL
        ,ConnectionStatusValueId = NULL
        ,TitleValueId = NULL
        ,FirstName = NULL
        ,NickName = NULL
        ,MiddleName = NULL
        ,BirthDay = NULL
        ,BirthMonth = NULL
        ,BirthYear = NULL
        ,Gender = 0
        ,MaritalStatusValueId = NULL
        ,AnniversaryDate = NULL
        ,SuffixValueId = NULL
        ,PhotoId = NULL
        ,GraduationYear = NULL
        ,SystemNote=[SystemNote] + CHAR(13)+CHAR(10) + 'Converted to Business record on 7/20/2016'
WHERE [ConnectionStatusValueId] = 525 --!! Change this number to match your ConnectionStatusValueID!!  

That's it! Now all that's left (only slightly tongue-in-cheek) is going through each business record, linking the business contact into it, and (likely) editing the name of the business, if they aren't quite accurate. But these are just nice things to have done, not absolute essentials.

But hopefully this saved you the effort of re-creating your business records and moving their transactions over to the new record =)


@mikejed
Spark Development Network
Flagstaff, AZ

Michael Garrison recently left his job in Architecture to become one of the "new guys" at Spark (the "Core Team"), but he's still helping out Christ's Church of Flagstaff and other non-profits with tech needs in his off-hours, trying to make computers do what computers do best, so that people are freed to do what we do best: relate with people!