Merges

With a ClientSpace merge, you can merge specific data from the ClientSpace system dynamically into your business documents and forms. Merges provide options to export data from ClientSpace and present the information in a highly organized manner. This functionality removes the need for manual completion of forms, such as a Request for Proposal, or Client Service Agreement.

Common uses for merges include marketing tools, proposals, personalized letters, detailed forms, labels, data analysis, and metrics. You can merge data to generate Microsoft Word documents, Microsoft Excel files, and form-fillable PDFs.

Merge Admin is where you configure the merge and maintain your documents. The Merge Admin record specifies the:

• Stored procedure that gathers the ClientSpace data.

• File template that contains the merge fields and instructions for presenting the data that is retrieved using the stored procedure.

• Type and Output Type.

Note:  You cannot delete System merges. This includes isdeveloper users.

To access Merge Admin:

1. Go to System Admin > Outputs > Merges.
   The Merge Admin dashboard opens, showing a list of your existing merges.

From here, you can Add, Edit (and view), or Delete a Merge record. However, you cannot delete a System merge record.

To add or edit a merge record:

1. On the Merge Admin Dashboard:

   •

   To add a new record, click the Add button.

   •

   To edit an existing record, locate the record and click (Jump).

   The Merge Admin Detail window displays.

   

2. Complete the applicable fields.

   Name	Type a descriptive name for this merge record.
   Template File	This is where you upload the template file. Click (Upload File). The Upload File dialog box opens. Click Choose File. Locate the file on your computer and click Open. The file name displays. For Version This File?, select one of the options: Yes, Pin New File: Default setting. This option retains all file versions and pins the most recent uploaded file. No, Replace File: Do not retain file versions. Replaces the current file. For Description, type a description of the uploaded file. Optional. For Category, select an entry from the list. Optional. Click Save.
   Description	Type a descriptive title of the merge record.
   Type	Select a file type: Word, Excel, PDF.
   Password	Optional. Type a password to secure the administration of this Merge record.
   Procedure	Select a stored procedure from the list.
   Output Type	This field is visible when Type is Word or Excel. For Type Word you can generate a Word doc or PDF, for Type Excel you can generate an Excel file or PDF.

3. Click Save.

Word Merge Proc

One of the procedures that you can select from the Procedure list is peo_merge_word_companydata. The following table outlines some of the fields available for this procedure.

Company data	Description
Related Sales Rep (fkAssignedToUserID from Org) user information	Email, Phone, Job Title, First Name, Last Name
Admin per Invoice from gen_PricingBatch	FirstCheckSupplement
WCCode1State	Full name of State
WCCode1Code	State code

Excel Merge Proc

One of the procedures that you can select from the Procedure list is peo_merge_excel_companydata. The following table outlines some of the fields available for this procedure.

Employee information (Employee Tab)
• FirstName • LastName • CensusID • luSUTAState • CompCode • AnnualPay • MiddleInitial • luPayFrequency • Address1 • Address2 • City State • ZipCode	• County • Phone • Mobile • Email • crGender • DOB • crEmploymentStatus • JobTitle • crEmploymentType • StandardHoursPerPeriod • AnnualPayPctChange
Benefits information (Employee Tab)
• HealthWaived • HealthPlanCode • luHealthCoverageLevel • HealthEffectiveDate • DentalWaived • DentalPlanCode • luDentalCoverageLevel • DentalEffectiveDate • VisionWaived • VisionPlanCode • luVisionCoverageLevel	• VisionEffectiveDate • VisionTerminationDate • LifeEffectiveDate • LifeTerminationDate • luDeductionType • luEnrollmentStatus • ContributionAmount • TotalContribution • WaitingPeriodDate • EstAnnualWages

Many other fields are available and are listed by tabbed section of the peo_merge_excel_companydata Excel report template. These include:

Contacts	Surcharges
Locations	Commissions
PricingStates	ExistingBenefitPlans
PricingCodes	OffereredBenefitPlans

 

Note: 

The Company Data merge report procedure (peo_merge_excel_companydata) sets the value in the Client Report State column using the following logic:

• Step 1: Check the Client Report State on the State Master record.

  • If checked (i.e., "True"), set Client Report State to "True"

  • If unchecked (i.e., "False"), go to Step 2.

• Step 2: Check the Client SUTA Reporting field on the State Legal Entity.

  • If checked (i.e., "True"), set Client Report State to "True".

  • If unchecked (i.e., "False"), set Client Report State to "False".

Procedure Fields

The stored procedure (prepared SQL code that can be reused) is the mechanism for retrieving data from ClientSpace. The stored procedure generates data for the merge fields. You may need to use a custom stored procedure for your merges, or you can use one from the Procedure list. For any selected stored procedure in the Procedure list, you can view the fields. This is helpful if you are not familiar with the procedure names.

To view procedure fields:

1. From the Merge Admin Detail form, in the Action Center, click Show Procedure Fields.
   The Fields dialog box opens, showing the fields of the selected procedure in Procedure.

2. To copy the fields, click Copy.
   The fields are copied to the clipboard.

3. Click Close when done.

Microsoft Word example

The following example shows a statement of work in a Word template. Word inserts your content using variables (placeholders that are replaced by text from the data source, your ClientSpace data). In the example, the placeholder syntax is represented as <<sometext>>, such as <<CompanyName>>.

Microsoft Excel example

You can use Excel to pull raw data and perform calculations. The following image illustrates an Excel template for payroll information.