Overview
This import is a flexible header import that will maintain data in these two tables:
TAX_ELIGIBILITY_YEAR
TAX_ELIGIBILITY_YEAR_CF
Standard conventions for this sort of import apply, including (but not limited to):
Everything is case Insensitive.
A blank column means “do not touch”. It does NOT mean “blank out”
The magic value <BLANK> means “save NULL to this column.”
Blank lines above the header row are skipped without error.
Each row of data is processed in a separate transaction.
If one row errors, execution proceeds to the next row.
The Data integrity layer (onchange, post process, validation) is engaged. Validation errors make the row fail.
When run at the company level, in ANY case where there is a company level mismatch, the import will fail with an error.
Tax Service Eligibility Records that should not be subject to modifications should be marked as 'Exclude from Automatic Calculations' during import
Specific Field Requirements/Sample File
1) Command Action
a. Required.
b. This must always be the first column of the import
c. This controls what the import should do with the data. Each supported command has some special rules, as given here.
d. Can take the literal values of:
i. Insert
1. Will insert a row of data.
2. Columns marked with recordIdentifier=True in the header won’t have any special behavior, in this case. Their data will just be part of the insert as normal.
3. The ASSIGNMENT_ID column is still required to have recordIdentifier=True specified, even if the command is Insert.
ii. UpdateOne
1. Used to update records in both tables.
2. One row of data can include values in both tables (don’t get confused by the word “One,” this command can update exactly one record in EACH table).
3. The record updated will be the record that matches the values for all fields marked with recordIdentifier=True in the column header. Though the ASSIGNMENT_ID column is the only column for which this setting is required, it is recommended to use other columns as well, given that one assignment will commonly have more than one tax eligibility year record.
4. If the recordIdentifier fields are insufficient (they filter the set down but find more than one matching record), the import will error.
iii. UpsertOne
1. This command will update OR insert a record, based on whether or not that record already exists.
2. It works just like the UpdateOne command, except that it will insert a record if no matches are found.
2) TAX_ELIGIBILITY_YEAR!ASSIGNMENT_ID
a. This field is required, and it is required that it have the recordIdentifier=True additional information specified in the import header row.
b. Optionally, this field can also have lookupValue=True and a lookupSource given. The following is a list of the lookup sources that are available:
i. AssignmentNumber
ii. FromEmployeeId
iii. FromNationalIdNumber
iv. FromSocialInsuranceId
v. From NationalTaxId
vi. FromPayrollId
vii. ToEmployeeId
viii. ToNationalIdNumber
ix. ToSocialInsuranceId
x. ToNationalTaxId
xi. ToPayrollId
3) TAX_ELIGIBILITY_YEAR!TAX_YEAR_ENDING
a. This field indicates the year for which the assignee is eligible for tax services.
b. This field expects a single integer value.
c. If the country uses fiscal years, then the year of eligibility is normally displayed as a two year combination (e.g. 2007/2008). The import will not accept a year in that format. Instead, just give the “ending” year (2008, in this example).
4) TAX_ELIGIBILITY_YEAR!EXCLUDE_FROM_AUTOMATIC_CALCULATIONS
a. This field indicates the whether the record should be excluded from automatic calculations.
b. This field expects a Yes/No value.
Things to Note
The following fields are Required Fields when Inserting:
ASSIGNMENT_ID
COUNTRY
TAX_YEAR_ENDING
TAX_SERVICE
When updating the ASSIGNMENT_ID, COUNTRY, and TAX_YEAR_ENDING should always be identifier fields. There is potential to cause bad data in the system by modifying the Country if for some reason the country switches between a fiscal tax year country and calendar tax year country or vice versa.
Import does not support the undo feature, nor the delete command (the only way to undo the import is to access the Tax Eligibility screen and manually delete the records).
Import format is CSV.
The first row of the import is the header row, and subsequent rows are data rows.
The first column must always be the “Command Action” column; the other columns can come in any order.
The only required columns are the “Command Action” column and the Assignment Id column.
Import can be run at the system level, or at the company level.
There is no COMPANY_ID field in this import spec; the import will error if run at the company level and given an assignment id that does not belong to that company.
After the “Command Action” column, each column in the header row should contain the unique name of the field that column will edit. The “unique name” is a combination of the full table name, an exclamation point, and then the full column name. Example: TAX_ELIGIBILITY_YEAR!ASSIGNMENT_ID.
Each header column can optionally accept additional information. To supply additional information, append a pipe character “|” followed by one of the additional options. Multiple options can be applied to a single column by separating them with more pipe characters.
Options include:
linkCF=True
Include this in the Command Action column to enable support for the custom field table. This is not needed if the import won’t include any columns from the custom field table.
recordIdentifier=True
Include this in any data column that should be used to determine which record the import should update. This has no impact when the row command is “Insert,” but is very important for updates or upserts. When the import goes to find a record to update, it will look for a record that has matching values for every column that has this specified in its header.
lookupValue=True
Include this for any column that qualifies as a lookup (that is to say, the value stored in the database is different than the value displayed on the screen). When this is specified, the data that the import should supply for the column should be the value displayed on the screen (and the database value will be looked up from that). If this header info is not specified, then the import will expect to receive the database value (i.e. the record ID or classification code) in the data row.
lookupSource=<applicable source>
Use this in conjunction with “lookupValue=True” for any field that qualifies as a lookup but might have more than one set of display values to pick from. The value supplied should indicate which lookup source to use. For this import, only one field qualifies, the ASSIGNMENT_ID field, and more information is given about that below.