Census Validation (Coming Soon!) Census Validation (Coming Soon!)

Census Validation (Coming Soon!)

Overview

The Census Validation feature helps users identify potential data issues early by running validations both when a census file is uploaded and when the Validate button is selected. These validations highlight errors and warnings based on prior year data, and prior uploads in the current year, allowing users to review and address discrepancies. This ensures data accuracy while maintaining flexibility in workflow.

When Validation Occurs

Census validation is automatically triggered in the following scenarios:

  • When the user selects the Validate button
  • When uploading a census via:
    • The plan-level upload
    • Batch census upload
    • The Global Dashboard

Note:

  • Validation applies only to the primary census upload.
  • It does not apply to uploads performed through Other Imports/Exports/Reports.
  • Errors and warnings are informational and do not block the upload.

How Validation Works

  • The system compares the uploaded data against:
    • Existing current year data (if available)
    • Prior year census data (when applicable)
    • The most recent uploaded census file
  • If the plan uses Employee Number (EN) instead of SSN:
    • EN is validated in place of SSN
    • SSN formatting rules do not apply

Accessing and Running Validation

Option 1: Validate Before Upload

  1. Navigate to the census upload screen.
  2. Select your census file.
  3. Click Validate.
  4. Review any errors or warnings returned.
  5. Make corrections as needed prior to uploading.

Option 2: Validate During Upload

  1. Upload your census file through the plan, batch process, or Global Dashboard.
  2. The system will automatically perform validation.
  3. Review validation results after upload.

Understanding Validation Results

Validation results are categorized as Errors or Warnings:

Errors

Errors indicate critical data issues that should be reviewed and corrected when possible.

Error List:

  • Error: Date of birth is different than current record
  • Error: SSN is missing (if SSN field is not on census, we will not throw this error)
  • Error: SSN is in invalid format (if SSN field is not on census, we will not throw this error)
  • Error: First name is missing
  • Error: Last name is missing
  • Error: DOB is missing
  • Error: Plan compensation is greater than statutory comp (This will only be checked if Compensation_PlanCompensation is on the census that is being uploaded)
  • Error: Roth contributions were made, but Roth contributions are not permitted by the Plan.
    This will populate if a ppt has Contribution_Roth > 0 and RothElectDef = No.
  • Error: Ensure dates are valid and in the correct format for the given date field. (MM/DD/YYYY or YYYY-MM-DD)

Warnings

Warnings highlight potential concerns or unusual data conditions that may require review.

Warning List:

  • Warning: Date of termination is before the beginning of the plan year
  • Warning: Possible 402g limit excess. If the participant defers (Contribution Elective Deferrals plus Contribution Roth) amount greater than the annual limit or greater than statutory compensation, then the warning will display for that participant.
  • Warning: 2 consecutive hire dates
  • Warning: 2 consecutive term dates
  • Warning: New participants added that were not in the census in the prior year
    • Note that this warning will not be thrown if this is the first year on the system and no prior census data was uploaded.
  • Warning: Date of Hire is missing
  • Warning: Hours are blank or zero
  • Warning: Statutory Compensation is blank or zero
  • Warning: Plan Compensation is blank or zero
  • Warning: Term Reason is missing (If has term date)
  • Warning: Invalid characters in division code (* ftw Divisions template has this column).  Invalid characters are any non word characters

New Participants

  • This section will display the list of new participants added that were not in the census in the prior year
    • Participants will not be listed if this is the first year on the system and no prior census data was uploaded.

Missing Participants

  • This section will display the list of participants missing from prior year census (was in prior year census and does not have a term date)

Note:

  • Certain warnings (such as participant comparisons) are not triggered if no prior census data exists.

Validation Output File

After validation is run (either manually or during upload), the system generates a downloadable .CSV report titled “Validation Results.csv”.

File Structure

The report includes a standard header with:

  • Plan Name
  • Plan Year End
  • Report Name

Important Notes:

  • All validation results are presented in a single consolidated list (results are not separated by division).
  • Only one validation report per plan year is stored. Running validation again will overwrite the previous report.

Report Columns

The body of the report includes the following columns:

  • Row
    • The row number from the uploaded census file (participant data typically begins on row 6)
  • Last Name / First Name
    • Displays the participant’s name
  • SSN (or Employee Number)
    • Displays the participant identifier
    • SSNs are encrypted when stored
    • If Employee Number is used, it is reported instead
  • Column Header
    • Identifies the census field where the issue occurred
  • Severity
    • Indicates whether the item is an Error or Warning
  • Prior Value
    • Displays the value from:
      • The prior year census, or
      • Current stored data (when applicable)
    • If no prior or current value exists, this field is blank
  • Uploaded Value
    • Displays the value from the uploaded census file
    • Left blank if the data is missing
  • Message
    • Provides a description of the validation issue based on the validation rules

Output Behavior and Storage

  • Validation results are accessible after running validation or completing an upload.
  • The report reflects the most recent validation run for the plan year.
  • Subsequent validation runs will replace the existing report, ensuring users always see the latest results.

Best Practices

  • Run validation before uploading to proactively identify issues.
  • Use the Validation Results.csv file to quickly isolate and resolve data issues.