Employee Assignment Flexible Header Import - Supported Fields and Special Rules

The tables that can be inserted/updated using this import are:

Note, this import does not support importing to the Inactive field (ASSIGNMENT!INACTIVE).

Generally speaking, the import can update any field in any of these tables other than the ID field and any foreign key fields that link these tables to each other (See Employee Assignment Flexible Header Import Field Column Definition.xls for more detail on exact fields). For lookup-type fields (any field that appears as a drop-down list on the screen) the value given to the import is the full text of the value the user sees on the screen (NOT the ID or code that is looked up and stored in the database. A country or a currency field should be imported using the appropriate three-letter symbol (not the full name). A field that appears as a checkbox on the screen should be imported with a value of “Yes” if the box should be checked and “No” otherwise. Also, very few fields are required apart from the fields used to identify a record.

There are, of course, special cases. For example, if a state/province field is given, there will be an associated country field that is required (and the given state province must be a part of the given country). Also, the COMPANY_ID field of the EMPLOYEE table is an insert-only field (i.e. the value given will be used when inserting new employees, but this field will be ignored, even if present, for updates of existing employees). In one particularly important special case (the company segment data) the field name that should be defined in the header row doesn’t actually exist in the table, because a layer of business logic must be applied to determine which field should contain the given value.

The assignment record type is also a special case. There are separate fields to denote if a record is a Permanent Transfer, International Assignment, or Business Traveler. Regardless of what is configured for the company as allowed, any of these values may be set for an assignment record via this import. You should include INTERNATIONAL_RELO and/or BUSINESS_TRAVELER in your import with values of Yes/No. The PERMANENT_TRANSFER value is automatically calculated based on the values of the other two fields. Only one of these should be set to Yes per assignment record.

The Employee Assignment Flexible Header Import Field Column Definition spreadsheet highlights the fields mentioned as special cases.

The following tables are “subordinate” to the assignment table, and as such each table includes one field that is required if and only if any other fields from that table are included in the header (i.e. you can ignore the table completely, but if you choose to import any data into the table, you must include the indicated field):

1) RELOCATION_SCREEN_CONTACT and RELOCATION_SCREEN_CONTACT_CF requires the TYPE field

2) ASSIGNMENT_EMPLOYEE_CONTACT requires the LOCATION_TYPE field

3) ASSIGNMENT_MAILING_ADDRESS and ASSIGNMENT_MAILING_ADDRESS_CF requires the LOCATION_TYPE field

4) ASSIGNMENT_DEPENDENT and ASSIGNMENT_DEPENDENT_CF requires the FIRST_NAME, MIDDLE_NAME, and LAST_NAME fields (all three). Also requires BIRTH_DATE to be the first column of the DEPENEDENT table fields. That is, if you are including DEPENDENT information, the first of the related fields must be BIRTH_DATE

5) ASSIGNMENT_COMPENSATION_BONUS_CF requires both the Bonus Type and Paid Date fields

6) ASSIGNMENT_COMPENSATION_SALARY_CF requires the Salary Effective Date fields

7) COMPENSATION_DATA_REPOSITORY and COMPENSATION_DATA_REPOSITORY_CF requires the EFFECTIVE_DATE field as well as either the NAME or NAME_CODE field. Only one (NAME or NAME_CODE) can be included in each record, and none of the fields listed above (EFFECTIVE DATE; NAME and NAME_CODE) can be updated via the import. When importing an amount (AMOUNT), you must also import the currency (CURRENCY).

8) RELOCATION_POLICY_FIELD_EXCEPTION_CF requires the SYSTEM_NUMBER field and cannot be a blank field or a field that contain the text “<blank>”

9) ASSIGNMENT_DESTINATION and ASSIGNMENT_DESTINATION_CF requires the following 5 fields and they function as the record identifiers:

These fields cannot be changed by the import. Should you attempt to update, a new record will be created. The suggested workflow for changing these fields is to use the import to set the status of the old record to “Cancelled”, and create a new record with the updated values.