This script shows the background checks requested through Protect My Ministry. 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.
Here is what the report looks like.
SQL Code for Background Checks¶
The recommended name is
Use the following code to create the SQL Script. If you are not familiar with creating scripts, see the help article below.
--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
Update role name ApplicationReview to new name ManageApplication