Having selected the format for uploading/viewing your participant census data, you are ready to load your data.
Edit Census
Data can be edited directly within the census grid - to update a field, simply double-click on it and enter the new information. The system automatically saves your edits. To add a participant, enter the SSN or Employee ID number in the box above the grid and click on "Add New". You will then be able to enter data in each of the fields for the new participant. Generally however you will want to upload data into the grid. The system will try to ensure the formatting is within certain limits for certain fields. If you enter data with incorrect formatting you may see the error "Not Valid" and the field will not be updated. If you enter a date as "1/1/01", the system will update the date to the correct formatting ("1/1/2001").
Download Census
Pressing the “Download” link from within the census or from the Compliance Menu under the Census heading will bring up a pop-up box with three download options.
Current Census
In general, before uploading a census, you will need to download the blank census so that your spreadsheet will have the correct formatting. The field names must match those on our system and be in the same order. The ID number of the selected census grid also must match the number in the first column of third row of the spreadsheet (cell A3) that will appear in the downloaded spreadsheet file. This means that if you add, remove, or move a column from the selected data entry grid you generally must download a new Excel file for your census. In addition, in order for the file to be re-uploaded by the system, it must be saved in .csv format.
Another use for downloading census would be after census scrub or after other changes have been entered into the system via a portal or the "Edit Census" link. Items in the census may be updated by the system (during a data scrub - you will be shown errors if a census item is updated) or by the user. You can download the latest version of the census by clicking on "Download Census".
Note that if changes were made to the census on the system you can still get a copy of the census that was last uploaded to the system (would not include any subsequent changes made by the system or the user) by clicking on the "Print Reports" link across from the "Do Census Data Scrub" link on the Compliance Menu page. This will open a "Report Menu" screen where the last option "Download Most Recent Census" will generate a copy of the census that was last uploaded to the system.
NOTE: even if you plan to enter data on the excel sheet and upload data back to the software, you may want to consider clicking Edit Census and on the Data Entry Grid page that appears add a participant’s SSN. This will give you a drop down lists showing permissible entries if applicable as well as access to the help buttons for each column.
Download Prior Census
Clicking on the “Prior” link will generate a copy of the census prior to any changes last created by the "Do Census Data Scrub" link.
Last File Uploaded
The system saves the most recent census uploaded. Even if changes are made to the census on the system (via a data scrub or user entry), you can download a copy of the census as it was last uploaded.
Upload Census
When initially adding census data to the software it is generally easier to upload it. It is a best practice to download the grid to Microsoft Excel format, enter your data then upload the spreadsheet.
Prior to uploading the census, it is highly recommended that the data you receive from your client is reviewed for completeness and accuracy. For example, if the census has both statutory compensation and statutory from plan entry compensation, both fields must be completed for all participants.
To upload the census click the “Upload Census” link, select the file to upload, enter the total number of rows in the census file (if the last row ends on row 50, enter 50), then press “Submit”. Note: Users may enter more rows than there are census records, but not less. For example, if the last row ends on row 50, users may enter 100.
Upon uploading the census, the Spreadsheet Upload Results screen will:
- Show the number of rows selected for upload
- Show the total number of records to be added
- List participants with new records
- If no census data has been uploaded for the plan year, all records in the spreadsheet will be listed as new records.
- If the user has already uploaded data for a plan year, and needs to re-upload the data, the user will only see new records listed if additional participants were added to the census after the initial census data upload.
For first census year, indicate whether eligibility dates should be reset
If this is the first plan year on the FT William system, you will have the option to have the system clear out all of the status fields that the system calculated after the census was previously uploaded and the data scrub was run. This is a handy tool to use if invalid data, such as an incorrect termination date or hire date was previously uploaded.
Retain prior year codes
For years subsequent to the first plan year on the system, users have the option to either copy the following status fields from prior year or upload with the census:
- Ownership percentage
- Family code
- Family relationship
- Officer status
- New comparability group number
- Employee class
- Employee class other
- Note: The system defaults to “Yes” to have the prior year data pulled instead of the census data. If “Yes” selected, data for the seven fields from the prior year will overwrite data for those seven fields even if that data is in the current census being uploaded. To have the system use the data in the census, simply set the override to “No”.
Upload file name
Click the browse button to search your computer for the census file you wish to upload. Please note that the census file must have been saved as a .csv file. Also note that the format must match the format in the grid selected from the Work with Data Entry Grids screen.
Number of rows to ignore
The number of rows to ignore defaults to 5 indicating that the participant data starts on Row 6; if you have made any changes to the top of the spreadsheet you need to adjust this number accordingly. Please note that there is a number in the third row that must not be removed or changed. This number tells our system what data will be in each column.
Number of rows containing data
You must also enter the approximate number of rows containing data - you may over-estimate the number of rows but not underestimate. (Once the system reaches the number of rows you entered a blank row or an invalid Social Security/employee number, the system will stop uploading).
Submitting the file and reviewing results
After clicking on "submit", you will be taken to the "Spreadsheet Upload Results" page. If your upload was successful, the system will display the number of records added. If your upload was not successful, you will see an error message. The most common error involves not having the correct census ID number in cell A3 of the spreadsheet (see the section "Download Census" above for more information).
If you click on the "return" link from the "Spreadsheet Upload Results" page, you will be taken back to the main “Compliance Menu” page and be prompted to run the data scrub.
For some fields only a certain number of entries will be valid. These fields appear as drop-down boxes in data entry grids on the system. Upon upload, the system will try to match the text in a particular field to the correct field value. For example: the field "Officer" has two possible values: "Yes" or "No". If you upload a census with "yes" (all lower case) or even simply "y", the system should identify that the value of that field is "Yes". Of course, if there are multiple values that begin with the same letter, you will have to enter more of the beginning letters of the field to have the system recognize the correct value.
You can effectively start over for a plan year by uploading a new census. This will clear out all the "R" (regular) field types on the system for the applicable plan year end. This will also clear out any errors that appeared after a census data scrub. Master file data will not be cleared out by uploading a new census unless you are directly uploading master file information (i.e. participant’s address or earliest date of hire).
