CONTACT USBack to MinistryPlatform Site
Your Knowledge Base is moving on 3/25/24! Our new Help Center provides all the articles you know and love (plus so much more) in a one-stop shop. Ask your SPoC for details!

Background Check Info

 

This view shows Group Participants and the background check information for their most recent Background check

Difficulty: Advanced
Purpose: Creates a Field with the Background Check submitted date and a field with the Background Check Returned date.
Page: Group Participants

Field List

Participant_ID_Table_Contact_ID_Table.[Display_Name] AS [Display Name] 
, Group_ID_Table.[Group_Name] AS [Group Name]
, Group_ID_Table_Parent_Group_Table.[Group_Name] AS [Parent Group]
, Group_Participants.[Start_Date] AS [Joined Team] 
,(SELECT TOP 1 Background_Check_Started FROM Background_Checks BC 
     JOIN Participants P ON BC.Contact_ID = P.Contact_ID 
     WHERE P.Participant_ID = Group_Participants.Participant_ID 
     ORDER BY Background_Check_Started DESC) AS Check_Started
,(SELECT TOP 1 Background_Check_Submitted FROM Background_Checks BC 
     JOIN Participants P ON BC.Contact_ID = P.Contact_ID 
     WHERE P.Participant_ID = Group_Participants.Participant_ID 
     ORDER BY Background_Check_Started DESC) AS Check_Submitted
,(SELECT TOP 1 Background_Check_Returned FROM Background_Checks BC 
     JOIN Participants P ON BC.Contact_ID = P.Contact_ID 
     WHERE P.Participant_ID = Group_Participants.Participant_ID 
     ORDER BY Background_Check_Started DESC) AS Check_Returned
,(SELECT TOP 1 All_Clear FROM Background_Checks BC 
     JOIN Participants P ON BC.Contact_ID = P.Contact_ID 
     WHERE P.Participant_ID = Group_Participants.Participant_ID 
     ORDER BY Background_Check_Started DESC) AS All_Clear
, Group_ID_Table_Ministry_ID_Table.[Ministry_Name] AS [Ministry Name] 
, Group_ID_Table_Congregation_ID_Table.[Congregation_Name] AS [Congregation Name] 
, Group_Role_ID_Table.[Role_Title] AS [Role Title]

View Clause

To ensure the view only returns current Group Participants.

GetDate() BETWEEN Group_Participants.Start_Date 
AND ISNULL(Group_Participants.End_Date,GetDate())

Variations

Only Those In a Particular Ministry

To view only those in a specific ministry, you can use this filter. Change the highlighted number to the appropriate Ministry ID in your system.

GetDate() BETWEEN Group_Participants.Start_Date 
AND ISNULL(Group_Participants.End_Date,GetDate()) 
AND Group_ID_Table.Ministry_ID =  5
Only Those About to Expire:

To view only those about to expire: (Add the following, and change the highlighted number to the appropriate number of months after background check submission that you want the participant to show). In this example, a record will show when the date is 35 months old, so you would see it a month before it becomes 3 years old.

AND DATEDIFF(M,(SELECT TOP 1 ISNULL( Background_Check_Submitted,'1900-01-01') 
FROM Background_Checks BC JOIN Participants P ON BC.Contact_ID = P.Contact_ID 
WHERE P.Participant_ID = Group_Participants.Participant_ID),GETDATE()) >= 35
Techniques