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

Importing Benefits Definitions/Plan

  1. One button will import the Benefit Definitions file into Benefits Definitions
    1. Benefit Definition file may contain data for multiple plans, and for global benefits as well.
    2. The file can be used for both inserting new records and updating existing records.
  2. View Settings (Benefit Plan Import File Format).
    1. File Format for Benefit Plan can be set as Vertical/Horizontal.
  3. The second button will import monthly numerical Benefit data into the Benefit Plan Gantt pages.
    1. A Benefit Plan file may contain data for multiple plans. Of course, global benefits do not have numerical data loaded. Their numerical values are obtained automatically by rolling data from plans containing those benefits.
    2. The file can be used for both inserting new records and updating existing records.
  4. Each file facility has a Preview screen which validates the file and shows errors and warnings about the file content from which you can review. It also shows which rows will be inserting new records, which will be updating existing ones. If there are no errors displayed, and after checking everything looks correct, the user can choose to Load the file. Otherwise the user must Cancel and manually correct the file.
  5. A file will not load unless completely free of inconsistencies. This is to enforce good auditing and

housekeeping. PM3 will retain internally an audit copy of each successful load file. For their own records, users should separately keep a copy of each successful file in a dedicated folder named, for example, PM3 Loaded Benefit Files. This is also the reason which PM3 will not allow a file of the same name to be loaded more than once. (As part of good housekeeping, we suggest you put a date on the filename to show when it was loaded).

  1. Rules for Load files – The loaders do extensive file checking and display results in the Preview page. However, it is helpful to list some of the rules that should be followed: Benefit Definitions
  2. Mandatory System Columns –
    1. These columns must exist in the spreadsheet
    2. They require values for each row
    3. Either PlanID or YourRef
      1. If inserting or updating a Global benefit, use PlanID = 0 and make sure the Global column is Y.
      2. If linking a global benefit to a plan, provide the PlanID or YourRef of the target plan and make sure the Global column is Y. The global benefit must already exist.
      3. If simply inserting or updating a benefit native to a plan, provide the PlanID or YourRef of the target plan, and make sure the Global column is N.
      4. BenefitName
      5. Must be unique for the plan or if global, unique within the global benefits.
    4. Optional System columns
      1. These columns do not have to appear in the spreadsheet.
      2. If they do appear, these columns do not require values for each row. Defaults for these fields are shown in brackets.
        1. Definition (empty string ‘’)
        2. OwnerFirstName AND OwnerLastName (no owner selected)
        3. Global (False)
        4. Financial (False)
        5. Positive (True)
        6. Boundary (Zero)
        7. Direction (Below)
        8. Custom1 (see *) 9.Custom2 (see *)
        9. Custom3 (see *)
      3. If an optional system column does appear in the file, then
        1. For a new record (insert), the corresponding field of the new database record will be set to the value in that spreadsheet cell; if there is no value in the spreadsheet cell, the corresponding field in the new record will be set to the default
        2. For an existing record (update), the corresponding database field of the existing record

will be set to the value in that spreadsheet cell; if there is no value in the spreadsheet cell, the existing record will be updated with the default

  1. If an optional system column does not appear at all in the file, then
    1. For a new record (insert), the default for that column will be inserted into the database
    2. For an existing record (update), the existing value for that database field remains unchanged.
  2. * Benefit Definitions may have additional Custom fields setup by Admin. Say they are for example, Source and Division. In order to make the spreadsheet values in Custom1 go into Source and those in Custom2 go into Division, customers need to contact BestOutcome to set up the association. (It only takes a minute).
  3. Since Custom fields are of many different types, Defaults will vary
    1. Alphanumeric – empty string; Date – null; Integer - 0; Boolean – false; Dropdown and Person Dropdown – not selected.
  4. Custom Columns which are
    1. Numbers can be decimals or integers.
    2. dates should in the spreadsheet cells have format set as “text”e. using the Excel Format Cells option and should be entered in ISO format i.e. yyyy-MM-dd e.g. 2018-0120.
    3. dropdowns, if not empty, must have one of the values visible in the Custom dropdown.
  5. Global Benefits do not have Custom fields so if provided in the spreadsheet file, they will be ignored.
  6. System column names must match exactly. Be careful to remove leading or trailing spaces from your spreadsheet.
  7. Columns may be in any order in the file, and additional columns may exist (and will be ignored by the loader).

 

  1. Benefit Plan (Month data)
    1. Required
      1. Either PlanID or YourRef (if YourRef is unique)
      2. Either BenefitID or BenefitName
      3. FinancialYear – using the format specified in Admin>General e.g FY2018
      4. MonthInYear – value 1...12 where 1 is first month of financial year as specified in Admin>General; suitable for custom and calendar periods
    2. Optional
      1. Budget (null)
      2. Actual (null)
      3. Forecast (null)
    3. The numeric fields allow integers or decimals. However, if Admin>General shows Currency

Multiple in Units (i.e. single pounds or euros) , the Benefits Plan rounds numbers to integers (since it is not sensible to show pence or cents) but otherwise, since the Currency Multiple is in thousands, 2 decimals are shown in the Benefits Plan

  1. The Benefit Plan loader follows the same rules as the Benefit Definition for when columns are missing or cells in present columns missing values.