1. Create a checklist template as shown in the image below, using the same field names.
   Note: Lookups for these fields are shown in the section below.

   

2. Open SQL Server Management Studio.
3. Open the SQL trigger shown below. It is annotated inline to explain the methods.
4. Grant Insert, Update permissions to KDSUser to the form.
5. Run the trigger and test the checklist.

Select cast(kPMPF.FirmNumber as varchar(10)) as ChoiceShortName, CAST(kPMPF.FirmNumber as varchar(10)) + ' - ' + kPMFM.FirmName as ChoiceLongName
From ChecklistInstance
INNER JOIN ChecklistTemplate on ChecklistInstance.ChecklistTemplateID = ChecklistTemplate.ChecklistTemplateID
INNER JOIN ChecklistHeaderField on ChecklistTemplate.ChecklistTemplateID = ChecklistHeaderField.ChecklistTemplateID and ChecklistHeaderField.HeaderFieldName = 'Job Name'
INNER JOIN ChecklistHeaderFieldValue on ChecklistHeaderFieldValue.ChecklistInstanceID = ChecklistInstance.ChecklistInstanceID and ChecklistHeaderField.ChecklistHeaderFieldID = ChecklistHeaderFieldValue.ChecklistHeaderFieldID
INNER JOIN kJCJM on ChecklistHeaderFieldValue.FieldValue = kJCJM.KeyID
INNER JOIN kPMPF on kJCJM.JCCo = kPMPF.PMCo and kJCJM.Job = kPMPF.Project
INNER JOIN kPMFM on kPMPF.VendorGroup = kPMFM.VendorGroup and kPMPF.FirmNumber = kPMFM.FirmNumber
Where ChecklistInstance.ChecklistInstanceID = @ChecklistInstanceID
and kPMFM.ExcludeYN = 'N'
Order by ChoiceLongName

Select cast(kPMPF.ContactCode as varchar(10)) as ChoiceShortName, kPMPM.FirstName + ', ' + kPMPM.LastName as ChoiceLongName --, kPMPF.*
From ChecklistInstance
INNER JOIN ChecklistTemplate on ChecklistInstance.ChecklistTemplateID = ChecklistTemplate.ChecklistTemplateID
INNER JOIN ChecklistHeaderField on ChecklistTemplate.ChecklistTemplateID = ChecklistHeaderField.ChecklistTemplateID and ChecklistHeaderField.HeaderFieldName = 'Job Name'
INNER JOIN ChecklistHeaderFieldValue on ChecklistHeaderFieldValue.ChecklistInstanceID = ChecklistInstance.ChecklistInstanceID and ChecklistHeaderField.ChecklistHeaderFieldID = ChecklistHeaderFieldValue.ChecklistHeaderFieldID
INNER JOIN kJCJM on ChecklistHeaderFieldValue.FieldValue = kJCJM.KeyID
---Get Firm
INNER JOIN ChecklistTemplateItemGrp on ChecklistTemplate.ChecklistTemplateID = ChecklistTemplateItemGrp.ChecklistTemplateID
INNER JOIN ChecklistTemplateItem on ChecklistTemplateItemGrp.ChecklistTemplateItemGrpID = ChecklistTemplateItem.ChecklistTemplateItemGrpID and ItemPrompt = 'Firm'
INNER JOIN ChecklistInstanceItem on ChecklistTemplateItem.ChecklistTemplateItemID = ChecklistInstanceItem.ChecklistTemplateItemID
INNER JOIN kPMPF on kJCJM.JCCo = kPMPF.PMCo
and kJCJM.Job = kPMPF.Project
and CASE when isnumeric(ChecklistInstanceItem.ItemResponse) = 1 Then ChecklistInstanceItem.ItemResponse Else -1 END = kPMPF.FirmNumber
INNER JOIN kPMFM on kPMPF.VendorGroup = kPMFM.VendorGroup and kPMPF.FirmNumber = kPMFM.FirmNumber
INNER JOIN kPMPM on kPMPF.PMCo = kPMPF.PMCo and kPMPF.FirmNumber = kPMPM.FirmNumber and kPMPF.ContactCode = kPMPM.ContactCode
Where ChecklistInstance.ChecklistInstanceID = @ChecklistInstanceID
and kPMFM.ExcludeYN = 'N'
ORDER BY ChoiceLongName

Create TRIGGER UpdateVistaTables
ON dbo.ChecklistInstanceItem
AFTER Update
AS
BEGIN
SET NOCOUNT ON;
Declare @RecordKeyID int, @ChecklistInstanceID int
declare @NewKeyID table (NewRecordKeyID int)
Select @RecordKeyID = ChecklistInstance.RecordKeyID, @ChecklistInstanceID = inserted.ChecklistInstanceID
From inserted
INNER JOIN ChecklistInstance on inserted.ChecklistInstanceID = ChecklistInstance.ChecklistInstanceID
Insert into CheckME(ChecklistRecordID)
Select @RecordKeyID
---Check to see if the Record Key From Vista is already on the ChecklistInstance table, if its not it means we haven't inserted it yet
if @RecordKeyID is null
Begin
------Insert into PM Notes
Insert into kPMPN(PMCo, Project, NoteSeq, VendorGroup, Firm, FirmContact, PMStatus
, AddedBy, AddedDate, ChangedBy, ChangedDate, Summary, Notes)
OUTPUT inserted.KeyID into @NewKeyID(NewRecordKeyID)
Select
kJCJM.JCCo, kJCJM.Job, ISNULL(MaxSeq,0) + 1, kPMFM.VendorGroup, kPMFM.FirmNumber, PivotTable.Contact, PivotTable.Status
, 'KDSUser', GetDate(), 'KDSUser', GetDate(), PivotTable.Summary, null
From
(
---We have values in header and in items so we union them together
Select ChecklistHeaderField.HeaderFieldName, ChecklistHeaderFieldValue.FieldValue
From ChecklistHeaderField
Inner join ChecklistHeaderFieldValue on ChecklistHeaderField.ChecklistHeaderFieldID = ChecklistHeaderFieldValue.ChecklistHeaderFieldID
INNER JOIN ChecklistInstance on ChecklistHeaderFieldValue.ChecklistInstanceID = ChecklistInstance.ChecklistInstanceID
INNER JOIN inserted on ChecklistInstance.ChecklistInstanceID = inserted.ChecklistInstanceID
UNION
Select ChecklistTemplateItem.ItemPrompt, ISNULL(ChecklistInstanceItem.ItemResponse, ChecklistInstanceItem.ItemComments)
From ChecklistTemplateItem
INNER JOIN ChecklistInstanceItem on ChecklistTemplateItem.ChecklistTemplateItemID = ChecklistInstanceItem.ChecklistTemplateItemID
INNER JOIN ChecklistInstance on ChecklistInstanceItem.ChecklistInstanceID = ChecklistInstance.ChecklistInstanceID
INNER JOIN inserted on ChecklistInstance.ChecklistInstanceID = inserted.ChecklistInstanceID
) SourceTable
---Pivot is needed to turn the rows into columns
Pivot
(Max(FieldValue)
---Listed out column names
for HeaderFieldName in ([Contact],[Firm],[Job Name], [Status], [Summary])) as PivotTable
--Joined to JCJM from KeyID saved from teh job lookup
INNER JOIN kJCJM on [Job Name] = kJCJM.KeyID
--Joined to HQCO to get vendor group
INNER JOIN kHQCO on kJCJM.JCCo = kHQCO.HQCo
LEFT OUTER JOIN kPMFM on kHQCO.VendorGroup = kPMFM.VendorGroup and PivotTable.Firm = kPMFM.FirmNumber
-----Get max NoteSeq line in order to perform the insert
OUTER APPLY (Select max(NoteSeq) MaxSeq
FROM kPMPN
WHere kPMPN.PMCo = kJCJM.JCCo
and kPMPN.Project = kJCJM.Job
) a
---Return the KeyID of the form you inserted in order to sync the update next time
update ChecklistInstance
set RecordKeyID = NewKeyID.NewRecordKeyID
from ChecklistInstance
outer apply @NewKeyID NewKeyID
where ChecklistInstance.ChecklistInstanceID = @ChecklistInstanceID
End
Else
Begin
---This step means there was already an insert so we get to update fields now
Update kPMPN
Set kPMPN.Summary = PivotTable.Summary
, kPMPN.ChangedDate = getdate()
, kPMPN.Firm = kPMFM.FirmNumber
, kPMPN.FirmContact = PivotTable.Contact
, kPMPN.PMStatus = PivotTable.[Status]
From
(
Select ChecklistHeaderField.HeaderFieldName, ChecklistHeaderFieldValue.FieldValue
From ChecklistHeaderField
Inner join ChecklistHeaderFieldValue on ChecklistHeaderField.ChecklistHeaderFieldID = ChecklistHeaderFieldValue.ChecklistHeaderFieldID
INNER JOIN ChecklistInstance on ChecklistHeaderFieldValue.ChecklistInstanceID = ChecklistInstance.ChecklistInstanceID
INNER JOIN inserted on ChecklistInstance.ChecklistInstanceID = inserted.ChecklistInstanceID
UNION
Select ChecklistTemplateItem.ItemPrompt, ISNULL(ChecklistInstanceItem.ItemResponse, ChecklistInstanceItem.ItemComments)
From ChecklistTemplateItem
INNER JOIN ChecklistInstanceItem on ChecklistTemplateItem.ChecklistTemplateItemID = ChecklistInstanceItem.ChecklistTemplateItemID
INNER JOIN ChecklistInstance on ChecklistInstanceItem.ChecklistInstanceID = ChecklistInstance.ChecklistInstanceID
INNER JOIN inserted on ChecklistInstance.ChecklistInstanceID = inserted.ChecklistInstanceID
) SourceTable
---Pivot is needed to turn the rows into columns
Pivot
(Max(FieldValue)
---Listed out column names
for HeaderFieldName in ([Contact],[Firm],[Job Name], [Status], [Summary])) as PivotTable
--Joined to JCJM from KeyID saved from teh job lookup
INNER JOIN kJCJM on [Job Name] = kJCJM.KeyID
INNER JOIN kHQCO on kJCJM.JCCo = kHQCO.HQCo
LEFT OUTER JOIN kPMFM on kHQCO.VendorGroup = kPMFM.VendorGroup and PivotTable.Firm = kPMFM.FirmNumber
INNER JOIN kPMPN on kJCJM.JCCo = kPMPN.PMCo and kJCJM.Job = kPMPN.Project and kPMPN.KeyID = @RecordKeyID
End
END
GO