People

Background Checks¶

This script shows the background checks requested through Protect My Ministry and MinistrySafe. The report will indicate the last date a check was run, the Approval Status, Service Code, Report Type, Label (f the church uses those), the Status of the check, and the date it was created. . If you run the script from Special Content it will run on the last search results. You have the option to add it to the blue Toolbar. Then you can run the report for an organization, Tag, or a Search Builder results. The report will include only people that have a processed date and/or a status. In other words, if a person is included in the organization or search results and they do not have a processed date or a status they will not be included in the report.

Sample Report¶

Here is what the report looks like.

https://i.tpsdb.com/2017-06-29_16-52-57.png — Background Check Report¶

SQL Code for Background Checks¶

The recommended name is BackgroundChecks

Use the following code to create the SQL Script. If you are not familiar with creating scripts, see the help article below.

See How to create a SQL Script.

--Roles=ManageApplication
;WITH backgroundchecks AS (
    SELECT
         v.PeopleId
        ,[LastCheck] = v.ProcessedDate
        ,[ApprovalStatus] = vs.Description
        ,bg.Created
        ,[CheckStatus] = (CASE bg.StatusID WHEN 1 THEN 'Not Submitted' WHEN 0 THEN 'Error'
                WHEN 2 THEN 'Submitted' WHEN 3 THEN 'Complete' ELSE '' END)
        ,[ReportType] = (CASE bg.ReportTypeID WHEN 1 THEN 'Background' WHEN 2 THEN 'Credit' ELSE '' END)
        ,[Label] = lb.Description
        ,bg.ServiceCode
    FROM dbo.Volunteer v
    LEFT JOIN dbo.BackgroundChecks bg ON bg.PeopleID = v.PeopleId
    LEFT JOIN lookup.BackgroundCheckLabels lb ON lb.Id = bg.ReportLabelID
    LEFT JOIN lookup.VolApplicationStatus vs ON vs.Id = v.StatusId
    WHERE (bg.ReportTypeID IS NULL OR bg.ReportTypeID IN (1,2))
        AND (v.ProcessedDate IS NOT NULL OR v.StatusId IS NOT NULL OR LEN(v.Comments) > 0)
)
SELECT
     b.PeopleID
    ,p.Name2
    ,b.LastCheck
    ,b.ApprovalStatus
    ,b.ServiceCode
    ,b.ReportType
    ,b.Label
    ,b.CheckStatus
    ,b.Created
FROM backgroundchecks b
JOIN dbo.People p ON p.PeopleId = b.PeopleID
JOIN dbo.TagPerson tp ON tp.PeopleId = p.PeopleId AND tp.Id = @qtagid
ORDER BY p.Name2, b.PeopleID, b.LastCheck

Latest Update

10/13/2022

Added MinistrySafe.