Uploading Payroll Data from a Spreadsheet to LCPtracker

LCPtracker provides a method for uploading payroll data to its Labor Compliance Program (LCPtracker) from a spreadsheet. A web form is provided just for this upload feature. Records that meet the minimum criteria are uploaded into the database. All of those new records are then checked by the LCPtracker’s Validation Engine, and notices are created for any of those records that need more data or corrections, allowing you to use the web interface to review and make those changes. LCPTRACKER provides a base spreadsheet with color-coded fields to help you differentiate between required, recommended, and optional fields. The second sheet in the spreadsheet is a legend defining these colors.

This upload feature is intended to be used as an interface from an accounting system to LCPtracker. If you are entering data manually, then you should use the 1. Payroll Records / Enter Records function in LCPtracker.

Generating Report

If you have not generated a spreadsheet, you can refer to our dedicated article here on how to generate a Certified Payroll Report for LCP Tracker.

Using the Spreadsheet

Copy the LCPTRACKER Upload Spreadsheet

1. Make a copy of the LCPTRACKER upload spreadsheet (LCPUpload.xls).
2. Give the copy a name that will help you remember its contents later. Since a spreadsheet may only contain entries for one project, you may want to give it a name that indicates the project and time period the spreadsheet covers (e.g. lcp-project7-091403-092803.xls).
3. Open the copy of the spreadsheet.
4. Make sure the sheet containing the payroll data in the spreadsheet is named Sheet1.
5. Make sure the header row in Sheet1 remains intact. It will start like this:
   • payroll_number week_end_date check_num ssn this_project all_projects dts_total
6. Delete any old data rows beneath the header row in Sheet1 of the spreadsheet.

Populate the Spreadsheet

Note that this step should be done by an accounting system.

1. For each employee assigned to the project to be covered by this spreadsheet, create a row in the spreadsheet for that employee by repeating steps 8-18 below.
2. The check_num should be specified here or it will not be available on reports.
3. The SSN (social security number) and/or Employee ID field must be specified or the record will be ignored. Best to fill in both.
4. The total amount the employee earned on all projects combined must be entered in all_projects or the Validation Engine will create a notice requiring you to enter it later.
5. The total amount the employee earned on this project (the one being covered by this spreadsheet) should be calculated and entered in this_project, or a notice will be created. Total paid amount paid in the current check should be entered in the total_paid field.
6. The total amount of payroll deductions withheld from the employee’s check (for all projects combined) must be entered in dts_total, or a notice will be created.
7. The fields st_hrs_date1 thru st_hrs_date7 should be filled in with the standard hours the employee worked on the project for each day of the weekly payroll cycle. Which day’s hours belongs in st_hrs_date1 will depend on your weekly payroll cycle. If you pay for Saturday thru Friday, Saturday will be “date1”. If you pay for Monday thru Sunday, Monday will be “date1”.
8. The fields ov_hrs_date1 thru ov_hrs_date7 should be filled in with the overtime hours the employee worked on the project for each day of the weekly payroll cycle. The fields ov_hrsx2_date1 thru ov_hrsx2_date7 should be filled in with the overtime hours, paid at double-time, the employee worked on the project for each day of the weekly payroll cycle.
9. Employer Payments for Vacation, Pension, Training, or Health Plans should be correspondingly recorded in ep_vac_hol, ep_pension, ep_train, or ep_haw.
10. All of the yellow fields (starting with dts_fed_tax) are various payroll deductions and do not need to be filled in unless dts_total was not provided earlier.
11. You may optionally enter any comments you desire in the prnotes field.
12. You may have to enter OtherDeductionNotes if it's required by your administrator. The field class_code should be filled with the appropriate classification code. This is the contractor accounting system code for the workers craft/classification. This code must be mapped in LCPtracker to a Prevailing Wage Craft Code. Please view under Training Materials, scroll to Contractor Support Documents and view CRAFT MATCHING (Required Function to use with Data Upload). Account owners will not allow certification without the proper craft/classification being listed.

Upload the Spreadsheet

1. Log in to LCPtracker (lcptracker.net).
2. Select 1. Payroll Records.
3. Select Upload Records
4. Select a payroll ending date from the calendar provided
5. Select a Project from the dropdown menu. Only projects that have at least one employee assigned to them are shown in this list. You must have already used the LCPtracker to add any of the employees that you want to have in your spreadsheet to your list of employees in the LCPtracker.
6. If you have hourly fringes set up in the Employee setup screen or using the Fringe Benefit Maintenance table, check the "Calculate fringes automatically" box to have system calculate those hourly values multipled by hours on payroll record.
7. Click on Browse to select the file to be uploaded.
8. Click on Upload.