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
Obtaining a Template
Completing the Template
- Client Data
- Plan Data
Importing the Template

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:

Launch the desired Power Tool.
- For Client data, select Power Tools > Import > PensionPro Setup > Clients
- For Plan data, select Power Tools > Import > PensionPro Setup > Plans
The Power Tool begins at Step 1: Options. Select More > Download Template. A blank template will be saved to the device.
(Optional) Select More > Download 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 should 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 should 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.
ATC Bin Number	Obtained from American Trust Custody if using the ATC Paying Agent Integration.
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.
Recordkeeper Name	Must match one of the recordkeeper entities maintained by PensionPro. If a Related Data workbook was obtained, all available options will be listed on the Recordkeeper Name worksheet.

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.

Launch the desired Power Tool.
- For Client data, select Power Tools > Import > PensionPro Setup > Clients
- For Plan data, select Power Tools > Import > PensionPro Setup >Plans
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).
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.
Step 3: Review summarizes the data to be imported. Select Execute.
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.