Advanced Checklists - To Vista

This article outlines the steps necessary to take in order to create a checklist and push data back into any Vista form you desire. The article is designed for users that are experienced with SQL. The article covers the basic steps and shows an example wiring up the form PM Notes.

  1. Create a Checklist template. Include all fields necessary to fill in the form you desire in Vista.
  2. Create a trigger in the [KDS-HRIM] database on the ChecklistInstanceItem or ChecklistHeaderFieldValue table to push data into Vista.

Creating the PM Notes Checklist Sync

  1. Create a checklist template as shown below with the same names.
    Note: Lookups for these fields are included in the Appendix below.
  2. Open SQL Server Management Studio.
  3. Open the attached Trigger. 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.

    Appendix 1 - Lookups

    These lookups demonstrate the use of a parameter @ChecklistInstanceID which is always passed to the lookups. Using these parameters, users can connect to the specific checklist instance. This allows for dependent columns as shown below.

    Project Firms

    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

    Firm Contacts

    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

    SQL Script:

    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