Checklist Lookup

Use the following Lookup parameter to connect your specific checklist to Vista.

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