Advanced Checklists - To Viewpoint

Keystyle Support Team -

This article looks at the steps necessary to take in order to create a checklist and push data back into any Vista form you desire.  This article is designed for users that are experienced with SQL.  The article below 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
  2. Lookups for these fields are included in the Appendix below.
  3. Open SQL Server Management Studio
  4. Open the attached Trigger.  Its annotated inline to explain the methods.
  5. Grant Insert,Update permissions to KDSUser to the form 
  6. 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 this parameters, users can get 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

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk