Data Import - Clients & Plans

This article is part of a series on importing records into PensionPro. Specifically, this article focuses on Client data and Plan data. Importing Client and Plan information to create new records is accomplished by saving the necessary data in Excel workbook format, then uploading the data using Power Tools. This article will cover obtaining and completing the proper templates, and uploading those templates to PensionPro.

 

Note: A number of fields on the import template must match an established List Value. List Values are used throughout PensionPro to populate the contents of many dropdown lists. More information can be found in the article List Values.
Warning: Import Power Tools are only intended to add new records to PensionPro. They cannot be used to add additional data to, or update, existing records. In some cases, this functionality may be provided by another Power Tool (such as the Field Update category of Power Tools). For more information, refer to the article Power Tools.

 

Tier Availability: Track, Team, Business

Security Rights Required: Access Power Tools

 

Article Contents

 

 


 

Import Basics

 

Importing Client data will create the following record types:

 

  • Company: A Company record is used to collect information about any business or organization—such as addresses and phone numbers—as well as any contacts within that organization.
  • Client: A Client record is necessary for any organization with one or more Plans being managed by the firm. This record contains administration information that is not specific to a Plan, such as yearly employer data. A Client record is always linked to a corresponding Company record (although not every Company needs to be a Client!)

 

Importing Plan data will create the following record type:

 

  • Plan: A Plan record contains information useful in managing and administering the Plan, such as specifications and fee schedules.

 


 

Obtaining a Template

 

Blank templates can be downloaded directly from PensionPro. It is always best to download a fresh copy of the template whenever an import needs to be performed to ensure that it is up to date with PensionPro's current standards.

 

To obtain a copy of the import template:

 

  1. Launch the desired Power Tool.
    • For Client data, select Power ToolsImportPensionPro Setup > Clients
    • For Plan data, select Power ToolsImportPensionPro Setup > Plans
  2. The Power Tool begins at Step 1: Options. Select Moremore_options.pngDownload Template. A blank template will be saved to the device.
  3. (Optional) Select Moremore_options.pngDownload Related Data. A workbook containing useful reference data will be saved to the device.

 


 

Completing the Template

 

Both the Client and Plan templates follow the same general structure:

 

  • Row 2 contains the column headings that represent the fields to be imported.
  • Row 1 contains the categories that each field falls into. These generally represent one of the Data Views present on either the Client or Plan tab.

 

For more information on the Data Views that correspond to each category, refer to the articles The Client Tab and The Plan Tab.

 

The field headings in these templates are color-coded to denote importance, as follows:

 

  • Yellow: The field is required.
  • Orange: The field is not required, but is recommended.
  • Blue: The field is only required if other related fields are being added (for example, Address Type is required if an address is present).

 

Warning: Deleting columns from the import template, or editing the names of any column headers, will cause the import to fail.

 

 


 

Client Data

 

The following notes provide insight on completing the Client Import template; these notes may not be comprehensive of all available columns.

 

Minimum required data:

Location Name

Must match the name of one of the firm's Locations.

  • If a Related Data workbook was obtained, all available options will be listed on the Location worksheet.
Client ID Must be an alphanumeric value between 3 and 25 characters in length. The ID does not require a specific format, but each Client ID must be unique.
Client Name Each Client name must be unique.
Status

References a List Value, located under Core > General > Status.

  • If a Related Data workbook was obtained, all available options will be listed on the Status worksheet.
  •  

 

Optional data:

Client Category

References a List Value, located under Core > Client\Employer > Client Category.

  • If a Related Data workbook was obtained, all available options will be listed on the Client Category worksheet.
Search Text Can be used to define additional terms to return this record in a search; for example, a prior business name. Must be at least 3 characters in length.
Addresses

Up to three addresses can be imported.

  • Address 1, City, State, and Zip Code are required address elements.
  • State requires two (2) or three (3) characters for US state or Canadian province abbreviations, respectively.
  • Address Type is required if an address is present. This references a List Value, located under Core > General > Address Type. If a Related Data workbook was obtained, all available options will be listed on the Address Type worksheet.
  • Address is Primary is Yes/No. Only one address should be designated as primary.
  • PensionPro only supports US and Canada addresses.
Numbers

Up to two phone numbers can be imported.

  • Client Number requires ten numeric digits. No specific formatting is required. Extensions are not supported.
  • Number Type is required if a number is present. This references a List Value, located under Core > General > Number Type. If a Related Data workbook was obtained, all available options will be listed on the Number Type worksheet.
  • Number is Primary is Yes/No. Only one number should be designated as primary.
Client Notes

One Note may be added to the created Client.

  • Note Text cannot be longer than 2000 characters.
  • Note Category references a List Value, located under Core > General > Note Category.
  • Additional Notes can be imported at a later time; refer to the article Data Import - Notes.

 

 

 


 

Plan Data

 

The following notes provide insight on completing the Plan Import template; these notes may not be comprehensive of all available columns.

 

Minimum required data:

Client Name / Client ID

At least one of these fields is required to match the Plan to an existing Client in PensionPro. If both fields are used, they must reference the same Client.

  • If a Related Data workbook was obtained, all available options will be listed on the Client List worksheet.
Plan Name Each Plan name must be unique.
TPA Plan ID Must be an alphanumeric value between 3 and 25 characters in length. The ID does not require a specific format, but each Plan ID must be unique.
Plan Type

References a List Value, located under Core > Plan > Plan Type.

  • If a Related Data workbook was obtained, all available options will be listed on the Plan Type worksheet.
Plan Year End The final day of a Plan Year, in MM/DD format.
Status

References a List Value, located under Core > Plan > Status.

  • If a Related Data workbook was obtained, all available options will be listed on the Status worksheet.
Service Effective On The date that the Plan's service became effective, in MM/DD/YYYY format.
Admin Type

References a List Value, located under Core > Plan > Plan Admin Type.

  • If a Related Data workbook was obtained, all available options will be listed on the Admin Type worksheet.

 

Optional data:

Client Servicing Level References a List Value, located under Core > Plan > Client Servicing Level.
On Hold Reason References a List Value, located under Core > Plan > Plan On Hold Reason.
Deactivated Date MM/DD/YYYY format. If this date is set, the Plan will be deactivated for all activity.
Combo Plan References a List Value, located under Core > Plan > Combo Plan.
MEP Master Yes/No format.
Linked Multiple Employer Plan

Requires the full name of an existing Plan that has MEP Master set to Yes.

IRS Plan Number Must be 3 alphanumeric characters in length.
Valuation Frequency References a List Value, located under Core > Plan > Valuation Frequency.
Form 5500 Type References a List Value, located under Core > Plan > Form 5500 Type.
Audited Yes/No format.
Trust ID Must be 9 numeric characters in length. This should not be an EIN.
QDIA Fund Name References a List Value, located under Core > Plan > QDIA Fund Name.
IRA Rollover Provider References a List Value, located under Core > Plan > IRA Rollover Provider.
Filing Cycle References a List Value, located under Core > Plan > Filing Cycle.
Owner(s) Only Plan Yes/No format.
Search Text Can be used to define additional terms to return this record in a search; for example, a prior business name. Must be at least 3 characters in length.
Local Directory

The path to a folder or network drive where Plan files can be accessed locally. Refer to Local Directories & Paths.

Software Plan ID Must be at least 3 characters in length.
Plan Category References a List Value, located under Core > Plan > Plan Category.
Plan Group References a List Value, located under Core > Plan > Plan Group.
Added On MM/DD/YYYY format.
1099 Payor

If used, this field must read either Client EIN or Plan TIN.

  • This option determines whether PensionPro references the EIN on the Client record or the Trust ID on the Plan record in certain situations requiring an EIN.
Projects on PSL Yes/No format. Select Yes if Plan Sponsors should be able to track the Plan's Projects on PlanSponsorLink.
Distributions on PSL If used, this field must read either Show or Hide. Select Show if Plan Sponsors should be able to track the Plan's Distributions on PlanSponsorLink.
Assets Frozen On MM/DD/YYYY format.
PBGC References a List Value, located under Core > Plan > PBGC.
Participant Statement Delivery References a List Value, located under Core > Plan > Participant Statement Delivery.
Payroll Contributions to Calculate References a List Value, located under Core > Plan > Payroll Contribution Calculation.
Payroll Frequency References a List Value, located under Core > Plan > Payroll Frequency.
Remittance Type References a List Value, located under Core > Plan > Remittance Type.
Trading Platform References a List Value, located under Core > Plan > Trading Platform.
Claim Processing Location References a List Value, located under Core > Plan > Claim Processing Location.
In House Claim Processing Yes/No format.
Medicare Reporting Required Yes/No format.
Plan Notes

One Note may be added to the created Plan.

  • Note Text cannot be longer than 2000 characters.
  • Note Category references a List Value, located under Core > General > Note Category.
  • Additional Notes can be imported at a later time; refer to the article Data Import - Notes.

 

 


 

Importing the Template

 

Once the template is complete, it can be imported via Power Tools. The import process is the same for both Client data and Plan data.

 

Note: Client data should be imported prior to importing Plan data.

 

  1. Launch the desired Power Tool.
    • For Client data, select Power ToolsImport > PensionPro Setup > Clients
    • For Plan data, select Power ToolsImportPensionPro Setup >Plans
  2. In Step 1: Options, use the File Selection box to select the import template, then select Next.
    • If an issue with the template is detected, an error message will appear in red containing a link to an error log. Selecting the link opens a new Error Log tab, which summarizes any errors (which will prevent the import) or warnings (which will not prevent an import, but may signal undesired results).
  3. Step 2: Preview will display the records as interpreted by PensionPro. Review the data, then select Next.
    • If one or more records contain issues that are preventing a successful import, an error message will appear in red containing a link to download an error log. This error log is a copy of the import template, but with any cells containing errors highlighted in red. Hovering over a highlighted cell will display a note containing the associated error message.
  4. Step 3: Review summarizes the data to be imported. Select Execute.
  5. When Step 4: Finish is reached, the data is queued for import. The user will receive two notification emails:
    • The first notification is sent immediately to confirm that the import has been queued.
    • The second notification is sent when the import has been successfully completed.