Skip to content
BestOutcome Logo
Skip to content
  • There are no suggestions because the search field is empty.

Importing Benefits Definitions/Plan

Benefit Definition File Overview

  • The Benefit Definition file can include data for:

    • Multiple benefit plans.

    • Global benefits.

  • It supports both inserting new records and updating existing ones.

  • You can choose between Vertical or Horizontal file formats using View Settings (Benefit Plan Import File Format).

Importing Monthly Numerical Data

  • The second import button loads monthly numerical benefit data into the Benefit Plan Gantt pages.

  • While plan-specific benefits may include numerical data, global benefits do not. Their values are automatically rolled up from the plans that include them.

Loading and Validating Files

  • Each import file includes a Preview screen that:

    • Validates the file.

    • Highlights errors and warnings.

    • Shows which rows will insert new records and which will update existing ones.

  • Files must be error-free before they can be loaded. If there are errors, users must cancel the import, correct the file manually, and try again.

  • This strict validation helps ensure data integrity and audit compliance:

    • PM3 retains an audit copy of each successfully loaded file.

    • Users should also store their own copies in a dedicated folder (e.g., PM3 Loaded Benefit Files).

    • File names must be unique. PM3 does not allow the same file name to be loaded more than once. Including a date in the filename is recommended.

File Loading Rules – Benefit Definitions

Mandatory System Columns (must exist in the file with values for each row):

  • PlanID or YourRef

    • Use PlanID = 0 and Global = Y to insert or update a global benefit.

    • When linking a global benefit to a plan, provide the PlanID or YourRef of the target plan and set Global = Y.

      • The global benefit must already exist.

    • To insert/update a plan-specific benefit, provide the PlanID or YourRef and set Global = N.

  • BenefitName

    • Must be unique within a plan or, for global benefits, unique globally.

Optional System Columns (not required; can be left blank or omitted):

  • Definition (defaults to empty string '')

  • OwnerFirstName and OwnerLastName (no owner selected by default)

  • Global (defaults to False)

  • Financial (defaults to False)

  • Positive (defaults to True)

  • Boundary (defaults to 0)

  • Direction (defaults to Below)

  • Custom1, Custom2, Custom3 (see custom fields section below)

If the column appears in the file:

  • For new records, the field will be populated with the value in the file, or the default if left blank.

  • For existing records, the value will be updated with the file value, or overwritten with the default if the cell is blank.

If the column is missing entirely from the file:

  • For new records, the default value will be used.

  • For existing records, the current value in the database remains unchanged.

Custom Fields (Custom1, Custom2, etc.)

  • Admins can configure custom fields (e.g., Source, Division) to map to Custom1, Custom2, etc.

    • Contact BestOutcome to set this up—it’s a quick process.

Custom Field Defaults Vary by Type:

  • Text/Alphanumeric: '' (empty string)

  • Date: null

  • Integer: 0

  • Boolean: false

  • Dropdowns/Person dropdowns: not selected

Field Input Requirements:

  • Numbers: Integers or decimals allowed.

  • Dates: Must be formatted as text in Excel and follow the ISO format (yyyy-MM-dd), e.g., 2018-01-20.

  • Dropdowns: If filled, values must match one of the dropdown options.

Note: Global benefits do not support Custom fields—any custom values for them in the spreadsheet will be ignored.

Formatting and Layout Notes

  • System column names must match exactly, with no extra spaces.

  • Column order does not matter, and additional (unrecognized) columns will be ignored by the loader.

Benefit Plan (Monthly Data File)

Required Fields:

  • Either PlanID or YourRef (YourRef must be unique)

  • Either BenefitID or BenefitName

  • FinancialYear (e.g., FY2018, based on format set in Admin > General)

  • MonthInYear: Values from 1 to 12, where 1 = first month of financial year (as per Admin settings)

Optional Fields:

  • Budget (can be left blank)

  • Actual (can be left blank)

  • Forecast (can be left blank)

Numeric fields accept both integers and decimals.

  • If the Currency Multiple is set to units (e.g., £ or €), values will be rounded to integers.

  • If set to thousands, values will show 2 decimal places.

Final Note

The Benefit Plan Loader applies the same rules as the Benefit Definition Loader regarding missing columns or values in cells.