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. 

Payroll Transaction Entry window Paste button

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.

UPR Transaction Paste Validation Report displaying warning messages.

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.

I hope you enjoy this feature!  Continue to check back to the Microsoft Dynamics GP October 2021 - Feature Blog Series Schedule page to review upcoming blog posts related to our new features and other helpful resource links.

Isaac Olson

Microsoft Support