Advanced Configuration: Send Data From a Fillable PDF to Vista

If you have onboarding tasks that include fillable PDF forms, you can use the following example as a guide for creating a custom SQL stored procedure that allows you to push data from the completed PDF to the HR Resources form in Vista.

Important: This article is designed for users who are experienced with SQL and SQL testing. Customizations like these are not supported by Trimble Viewpoint's customer support team.

As an example, you can create a tool that captures information from a driver's license task and a fillable PDF version of a state W-4 form and pushes that data to the HR Resources form in Vista.

  1. Create a driver's license task like the example below. Include all fields necessary to complete the relevant form in Vista. For help, see Create Custom Tasks.
  2. Add the fillable PDF version of the state W-4 form as a task on the relevant onboarding profile. You may want to adjust the field names on the form with a PDF editor as described in the Notes on the Example SQL Stored Procedure section below.
  3. Create an SQL stored procedure for sending information to the HR Resources form. An example stored procedure is shown below.

Example: Create a Driver's License Task

  1. Create a driver's license task such as shown in the image below, using the same field names.

  2. For the License State, use the Advanced Options > Lookup option called Dropdown - States:
    Note: The lookup for the License State is shown in the following section.

Lookups

States

select State as ChoiceShortName, Name as ChoiceLongName
from Viewpoint.dbo.bHQST
order by Name

Notes on the Example SQL Stored Procedure

These notes apply to the example stored procedure shown in the following section.

OnPostInitAction

After initializing new hire records to Vista, the system will check to see if there is a procedure named ONPostInitAction and pass the parameter @OnboardID uniqueidentifier.

Pivot Values

In the ONPostInitAction procedure, the task template descriptions correspond to the pivot in brackets:

Pivot   (max(FormValue)
For ItemPrompt in ([Driver's License#],[License State],[License Class],[License Expiration Date]) 
    ) PivotTable
Fillable PDF

For the fillable PDF, the names used in the JSON parsing come from the fillable fields in the PDF. It is recommended you adjust the field names in the PDF with a PDF editor if you want to make them clearer.

Select @RegularExemptions = L.[Value]
From #Louisiana L
WHERE [Name] = '6'

Select @AddExemptions = L.[Value]
From #Louisiana L
WHERE [Name] = '7'

Select @AdditionalAmount = L.[Value]
From #Louisiana L
WHERE [Name] = '8'

Example SQL Stored Procedure

Note: This example stored procedure uses Louisiana as the state.
USE [KDS-HRIM]
GO

/****** Object:  StoredProcedure [dbo].[ONPostInitAction]    Script Date: 10/5/2018 11:14:16 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		SQL Expert
-- Description:	This example shows how to parse a task and a pdf.  Customizations are not supported by Trimble Viewpoint support.
-- =============================================
CREATE PROCEDURE [dbo].[ONPostInitAction]
	@OnboardID uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

    Declare @HRRMKeyID INT
		,@HRCo INT
		,@HRRef INT
		,@PRCo INT
		,@PREmp INT
		,@DriversLicense varchar(20)
		,@DriversState varchar(2)
		,@DriversType varchar(20)
		,@DriverLicExp Date
		,@jsonText varchar(max)
		,@outMsg varchar(500)
		,@FilingStatus varchar(1)
		,@RegularExemptions int
		,@AddExemptions int
		,@AdditionalAmount numeric(10,2)
		,@NoExempt varchar(10)
		,@Single varchar(10)
		,@Married Varchar(10)

	Select @HRRMKeyID = HRRMKeyID
	from Onboard 
	where OnboardID = @OnboardID

	Select @HRCo = kHRRM.HRCo
		,@HRRef = kHRRM.HRRef 
		,@PREmp = kHRRM.PREmp
		,@PRCo = kHRRM.PRCo 
	from kHRRM 
	where kHRRM.KeyID = @HRRMKeyID 

   Select @DriversLicense = LEFT([Driver's License#],20)
	,@DriversState = LEFT([License State],2)
	,@DriversType = LEFT([License Class],20)
	,@DriverLicExp = [License Expiration Date]
   From 
   (   Select replace(replace(replace(replace(replace(Left(ItemPrompt,100),',',''),')',''),'[',''),']',''),'(','') ItemPrompt    
		, ISNULL(ItemResponse, ItemComments) FormValue    
	FROM ChecklistInstance    
		INNER JOIN OnboardTasks on ChecklistInstance.ChecklistInstanceID = OnboardTasks.OnboardChecklistInstanceID
		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 = 286   
					and ResponseTypeID <> 11  
					and OnboardID = @OnboardID ) SourceTable   
	Pivot   (max(FormValue)   
	For ItemPrompt in ([Driver's License#],[License State],[License Class],[License Expiration Date])   
		) PivotTable     


	Update kHRRM 
	Set LicType = CASE WHEN @DriversType is not null then 'DL' Else NULL END
		,LicState = @DriversState
		,LicClass = @DriversType
		,LicExpDate = @DriverLicExp
		,LicNumber = @DriversLicense
	FROM kHRRM 
	WHERE kHRRM.KeyID = @HRRMKeyID

	  -----------------------------------------State Witholdings
	IF EXISTS(	Select 1 from Onboard 
						INNER JOIN OnboardTasks on OnboardTasks.OnboardID = Onboard.OnboardID 
				WHERE OnboardBulletinID = 248 and Onboard.OnboardID = @OnboardID
			 )
	BEGIN
		select @jsonText = FileAttachment.PDFFormFieldData
		from Onboard
			INNER JOIN OnboardTasks on OnboardTasks.OnboardID = Onboard.OnboardID
			INNER JOIN Bulletin on Bulletin.BulletinID = OnboardTasks.OnboardBulletinID
			INNER JOIN BulletinResponse on BulletinResponse.AuthToken = Onboard.OnboardID
										  and BulletinResponse.BulletinID = OnboardTasks.OnboardBulletinID
			INNER JOIN FileAttachment on FileAttachment.RecordID = BulletinResponse.BulletinResponseID
										and FileAttachment.ModuleID = 129
		where Onboard.OnboardID = @OnboardID
			and Bulletin.BulletinID = 248


		SELECT v.*
		INTO #Lousiana
		FROM OPENJSON(@jsonText)
			WITH ([value] NVARCHAR(MAX) '$' as JSON) as a
		CROSS APPLY OPENJSON(a.[value])
			WITH ([Name] VARCHAR(MAX), [Type] VARCHAR(MAX), [Value] VARCHAR(MAX)) as v


		Select @RegularExemptions = L.[Value]
		From #Lousiana L
		WHERE [Name] = '6'

		Select @AddExemptions = L.[Value]
		From #Lousiana L
		WHERE [Name] = '7'

		Select @AdditionalAmount = L.[Value]
		From #Lousiana L
		WHERE [Name] = '8'

		Select @NoExempt = L.[Value]
		From #Lousiana L
		WHERE [Name] = 'NoExmp'

		Select @Single = L.[Value]
		From #Lousiana L
		WHERE [Name] = 'Single'

		Select @Married = L.[Value]
		From #Lousiana L
		WHERE [Name] = 'Married'

		SELECT @FilingStatus = CASE WHEN @NoExempt = 'True' THEN NULL
								  WHEN @Single = 'True' THEN 'S'
								  WHEN @Married = 'True' THEN 'M' 
								  ELSE NULL END

		select distinct FileStatus from kHRWI 
		IF EXISTS (SELECT 1 FROM kHRWI WHERE HRCo = @HRCo AND HRRef = @HRRef AND DednCode = 452)
		BEGIN
			UPDATE kHRWI
			SET FileStatus = @FilingStatus
				,RegExemp = @RegularExemptions
				,AddionalExemp = @AddExemptions
				,AddonType = CASE WHEN @AddExemptions = 0 THEN 'N' ELSE 'A' END
				,AddonRateAmt = @AddExemptions
			WHERE HRCo = @HRCo AND HRRef = @HRRef
		END
		ELSE
			INSERT kHRWI (
				HRCo
				,HRRef
				,DednCode
				,FileStatus
				,RegExemp
				,AddionalExemp
				,OverrideMiscAmtYN
				,MiscAmt1
				,MiscFactor
				,AddonType
				,AddonRateAmt
				,MiscAmt2
				)
			SELECT @HRCo
				,@HRRef
				,452
				,@FilingStatus
				,@RegularExemptions
				,0
				,'N'
				,0
				,0
				,AddonType = CASE WHEN @AddExemptions = 0 THEN 'N' ELSE 'A' END
				,@AddExemptions
				,0
	END 
END