Advanced Configuration: Send Data From an Action Item to Vista

Use the following example as a guide for creating an action item for a salary increase and pushing data from that action item to HR Resource Salary History in Vista.

Important: This topic is intended for users who are experienced with SQL and SQL testing. Customizations like these are not supported by Trimble Viewpoint's customer support team.
  1. Create a salary increase action item template like the example below. Include all fields necessary to complete the relevant form in Vista. For help, see Manage Action Item Templates.
  2. Create an SQL trigger in the [KDS-HRIM] database on the ChecklistInstanceItem table to send data to the HR Salary History form in Vista. An example SQL trigger is shown below.

Example: Create a Salary Increase Action Item Template

  1. Create a salary increase action item template such as shown in the image below, using the same field names.

  2. Open SQL Server Management Studio.
  3. Open the example SQL Trigger. It is annotated inline to explain the methods. You will also find Notes on the Example SQL Trigger below.
  4. Grant Insert, Update permissions to the form for the KDSUser.
  5. Run the trigger, and test the action item.

Notes on the Example SQL Trigger

These notes apply to the example SQL trigger shown in the following section.

ChecklistTemplateID

The checklist template ID can be found in the URL of the Action Item template.

If exists(Select 1 from inserted where ChecklistTemplateID = 285 and SubmittedBy is not null)

ChecklistInstanceItemID

The ChecklistInstanceItemID will have to be checked from the ChecklistInstanceItem table in SQL.

Pivot Values

The pivot values pull from the Action Item descriptions that are surrounded by brackets.

Note: Any spaces should be included in the brackets.
Select @EffectiveDate = [Effective Date]
,@NewRate = [New Rate]
,@NewPositionCode = [Position Code]

Example SQL Trigger

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	SQL Expert
-- Description:	Custom triggers can be dangerous and are not supported by Trimble Viewpoint
-- =============================================
CREATE TRIGGER KDS_HRHourlyIncreaseAction
   ON  ChecklistInstance
   AFTER UPDATE
AS 
BEGIN

	SET NOCOUNT ON;

	If exists(Select 1 from inserted where ChecklistTemplateID = 285 and SubmittedBy is not null)
	BEGIN
		Declare @EffectiveDate Date
			,@NewRate numeric(10,2)
			,@NewPositionCode varchar(10)
			,@HRRef int
			,@HRCo int
			,@HRRMKeyID int


		Select @HRRMKeyID = ItemResponse
		from inserted ChecklistInstance 
			INNER JOIN ChecklistInstanceItem on ChecklistInstance.ChecklistInstanceID = ChecklistInstanceItem.ChecklistInstanceID
		WHERE ChecklistTemplateItemID = 1148

		Select @HRCo = HRCo
			, @HRRef = HRRef 
		from kHRRM 
		where KeyID = @HRRMKeyID 

	   Select @EffectiveDate = [Effective Date]
		,@NewRate = [New Rate]
		,@NewPositionCode = [Position Code]
	   From 
	   (   Select replace(replace(replace(replace(replace(Left(ItemPrompt,100),',',''),')',''),'[',''),']',''),'(','') ItemPrompt    
			, ISNULL(ItemResponse, ItemComments) FormValue    
		FROM ChecklistInstance    
			inner join ChecklistTemplate on ChecklistInstance.ChecklistTemplateID = ChecklistTemplate.ChecklistTemplateID    
			inner join ChecklistInstanceItem on ChecklistInstance.ChecklistInstanceID = ChecklistInstanceItem.ChecklistInstanceID    
			inner join ChecklistTemplateItem on ChecklistInstanceItem.ChecklistTemplateItemID = ChecklistTemplateItem.ChecklistTemplateItemID    
			inner join ChecklistTemplateItemGrp on ChecklistTemplateItem.ChecklistTemplateItemGrpID = ChecklistTemplateItemGrp.ChecklistTemplateItemGrpID   
		WHere ChecklistInstance.ChecklistTemplateID = 285
						and ResponseTypeID <> 11  
						) SourceTable   
		Pivot   (max(FormValue)   
		For ItemPrompt in ([New Rate]
				,[Effective Date]
				,[Position Code]
				)   
			) PivotTable     
		
		If not exists(Select 1 from kHRSH where HRCo = @HRCo and HRRef = @HRRef and EffectiveDate = @EffectiveDate)
		Insert into kHRSH(HRCo, HRRef, EffectiveDate, Type, OldSalary, NewSalary, NewPositionCode, UpdatedYN,CalcYN)
		Values (@HRCo, @HRRef, @EffectiveDate, 'H', 0, @NewRate, @NewPositionCode,'N','N')
	END
END
GO