GP Excel Template - Payroll Transaction Integration

Supports:
SmartConnect 2018+
Publisher:
eOne Solutions

GP Excel Template - Payroll Transaction Integration



Overview
Integrate payroll computer transactions directly into Dynamics GP using a pre-configured Excel template. Cut down on data entry errors by taking advantage of the built-in data connections to pull valid employees, pay codes, departments, etc. from your Dynamics GP system. Choose which company you want to integrate your transactions into by using the provided drop down field on the Excel template - as you switch companies the template will automatically update the connections that pull the accounts and other values to match the company being imported into.

The Excel template can be used on a workstation without requiring Dynamics GP or the SmartConnect interface to be installed on that machine. The template makes use of the SmartConnect web service to send any integrations through to Dynamics GP, reducing the need for each user to have full access to your accounting system. Multiple users can use copies of the Excel template at the same time without issue as well.

Details
Prerequisites
  1. Setting up Excel security (Office 2016):
    • In Excel click on the File tab and select Options
    • On the Excel Options window click in Trust Center -> Trust Center Settings
    • On the Trust Center Settings window select Macro Settings
    • ‘Disable all macros with notification’ or ‘Enable all macros’ can be selected
    • In the Trust Center Settings window select External Content
    • ‘Enable all Data Connections’ or ‘Prompt user about Data Connections’
    • Please note if ‘Disable all macros with notification’ or ‘Prompt user about Data Connections’ is selected the user must choose whether to enable this functionality or set up trusted locations and documents.
  2. The template requires the SmartConnect web service to be set up and running in the environment.
    • Refer to the main SmartConnect manual for setting up and installing the SmartConnect web service if not present in the environment.
  3. Connecting to the web service: The template uses the internet explorer settings to make a connection to the SmartConnect web services. To ensure that you can make a connection to the web service using your current windows username and password please see the following steps:
    • Open internet explorer
    • Select Tools -> Internet Options
    • Click on the Security tab
    • If accessing from the Local intranet follow steps 6 and 7
    • If accessing from the internet, select Trusted Sites from the zone and select Sites. Add the URL of the SmartConnect Web Service and click add. Click on close and follow steps 6 and 7
    • Click on Custom Level and scroll down to User Authentication -> LogonSelect Automatic logon only in Intranet zone
Installation
  1. Importing the Map
    • In SmartConnect go to Maintenance -> Import
    • Click the Select Files button and select the map file E1_EXCEL_PR_TRX.sce
    • Ensure the map shows up in the import list and that the checkbox is marked and click Import
  2. Setting up the Map
    • In SmartConnect, select the Map ID 'E1_EXCEL_PR_TRX' from the list of Maps, and click edit from the toolbar
    • In the Settings sub tab, browse to the location of the PR Transactions workbook
    • Change the Sheet name to SCValues if it is not already selected
  3. Map Security
    • The user that was used to configure the SmartConnect web service must have access to run the imported map
  4. Setting up the Template
    • Select the SmartConnectConfig sheet inside of the template
    • The Map field does not have to change as this is the ID of the map that was designed to work with the template as should have been imported into SmartConnect
    • The SmartConnect Web Service needs to be set to the URL of the web service (i.e. http://demo:5557/smartconnect.svc)
    • The data connections used to populate the data tables can be updated all at once at any time using the 'Update' button
    • The Number of Rows can be adjusted to any value that is required. Note that if it is changed while data is entered on the main sheet it will clear any data outside the new number of rows when it resizes.
    • This changes the main sheet as soon as a new value is entered. Also, this cell automatically updates as the table resizes as new rows are added dynamically.
    • Archive on Submit should be set to either TRUE or FALSE
    • If Archive Path has a path entered, the copy will attempt to be saved at that location. This does support UNC paths as well as mapped drives.
    • The SmartConnectConfig sheet can be hidden once it is setup so that it isn’t accidentally modified

Free 30 Day Trial

Free 30 Day Trial