Copy and Paste Payroll Transactions from Excel is a Human Resource and Payroll feature added to Microsoft Dynamics GP in the October 2021 release.
In Microsoft Dynamics GP you can now copy and paste payroll transactions from Excel straight into the Payroll Transaction Entry window similar to our General Ledger and Payables Transactions Copy and Paste features. This is a great feature which makes it easier for those who may have an external time keeping system that can export data to Excel in the required format for pasting or for those who might currently be using Integration Manager to enter a larger payroll transaction.
TIPS AND TRICKS:
Baseline testing showed best results with 1000 or less transaction lines. 2000 transaction lines processed within about 8 minutes and will vary depending on the environment. For those with larger numbers eConnect may be a better solution, otherwise it is best practice to break up the data and paste it into separate smaller batches for best results. As you can see in our examples below if you did 4 batches of 1000 transactions it could take 8-12 minutes total. Versus doing 4000 all in the same batch could take 20+ minutes.
Performance can vary widely due to environmental factors and the number of 3rd Party Add-ins installed on top of our code. Copy and Paste works when you have security to the core Dynamics GP window.
Here are some baseline testing examples on SQL Server with Local Install of Microsoft Dynamics GP without any customizations or add-on products.
| Transaction Count | Time Elapsed |
| 500 | 1 minute |
| 1000 | 2-3 Minutes |
| 2000 | 6-8 Minutes |
| 3000 | 10-12 Minutes (extrapolated) |
| 4000 | 20+ Minutes (extrapolated) |
Leaving any fields that you wish to default, don’t include those columns when you copy your range, such as departments, position or dates can help speed up the process. For example if you are using the default position on the employee, don’t put it in the excel sheet as it will be redundant and slow down the import process. Example below:
SUMMARY OF FEATURE:
Below is a quick overview of this feature. For full documentation on this feature and other new features please see the Microsoft Dynamics GP Resource Directory.
A new Paste button will be visible in the Payroll Transaction Entry window. To view this window go to the Transactions menu, point to Payroll, and click on Transaction Entry.
TIP: If the Paste button does not appear, verify that your user security set to the Microsoft Dynamics GP version of the Payroll Transaction Entry window, not the HRM Solutions or any other alternate window. Also make sure that your batch does not contain any previously entered transactions as you cannot add to existing transaction data, best practice would be to create a new batch id.
Once you have selected a batch and the Paste button is available, you can copy data from a table (excluding the column headers) using the following table format.
| Employee ID | Trx Type | Code | Amount | Pay Rate | Department | Position | Date From | Date To |
| BARB0001 | Pay Code | HOUR | 40.00 | 20.00 | SPTS | CSP | 05/12/2027 | 05/17/2027 |
| ACKE0001 | Pay Code | SALY | 8 | SPTS | CSP | 05/14/2027 | 05/14/2027 | |
| BUCH0001 | Deduction | EPU | 50.00 | 05/12/2027 | 05/12/2027 | |||
| CHEN0001 | Pay Code | HOLI | 8 | 05/12/2027 | 05/12/2027 |
If any Validation errors or warnings occur, the UPR Transaction Paste Validation Report will print and display the errors that it has identified, and no data will Pasted. The validation report will not print if there are no errors or warnings.
Warning messages such as ‘WARNING – The pay rate amount cannot be edited.’ will appear on the Payroll Transactions Paste Validation Report but will not prevent the data from pasting. Default pay rates would be used in this situation instead of the rates on the Excel spreadsheet.
Defaults:
- The Amount, Pay Rate, Department, and Position fields will default from the Employee’s setup records if left blank.
- Date From and Date To will default with the values in the Payroll Transaction Entry window if left blank.
- Benefit and Deduction Trx Types will use the Amount field for the dollar amount or percentage that you paste. Pasting a blank amount will use the default value from the Employee’s setup for that code.
- Salary pay codes will always use a Payroll Salary Adjustment Type of Reduced Hours, and will reduce the employee’s salary by the number of hours entered in the Amount column of the Excel spreadsheet.
- Columns that are not available in the Excel Format to be copied will use the defaults for the employee from the user interface. For example, State and Local Tax will default from the Employee Tax Maintenance window and cannot be pasted just like they would when manually keying the a new transaction line.
Formatting Tips:
| Column | Description | Required | Accepted Values |
| A | Employee ID | Yes | 15 Character Max; Must exist in company; |
| B | Trx Type | Yes | Pay Code, Deduction, or Benefit; |
| C | Code | Yes | 6 Character Max; Must be assigned to employee; |
| D | Amount | No | Number must be between 0 and 9999.99; Cannot be negative; |
| E | Pay Rate | No | Number must be between 0 and 999999999.99; Decimal Places must follow Payroll Setup. Must be blank for Salary, Benefits and Deductions; |
| F | Department | No | 6 Character Max; Must exist in company; |
| G | Position | No | 6 Character Max; Must exist in company; |
| H | Date From | No | MM/DD/YYYY Date format; |
| I | Date To | No | MM/DD/YYYY Date format; |
Additional Notes:
This feature will update Human Resources Attendance information if you are using that for tracking instead of Payroll.
This feature is not compatible with Web Client or Analytical Accounting at this time.
Are you interested in Microsoft Dynamics GP? Contact us first. CAL Business Solutions 860-485-0910 or support@calszone.com
By CAL Business Solutions, Connecticut Microsoft Dynamics GP Partner, www.calszone.com
Read the original post at: https://community.dynamics.com/gp/b/dynamicsgp/posts/microsoft-dynamics-gp-october-2021—copy-and-paste-payroll-transactions-from-excel
