Create SmartConnect Accounts Payable Import for Dynamics GP
SmartConnect Payables Import
· Open SmartConnect and select the 'Create' in the ribbon, this will open the 'Map Setup' window.
Want to add a caption to this image? Click the Settings icon.
· In the 'Map Setup' window enter a 'Map ID' and 'Map Description'. To create a new map enter a unique 'Map ID'. The 'Map Description' is a short description of what the map does. In this case we will use the 'Data Source Type' of 'Bulk Data Load'. The 'Bulk Data Load' type will take the source and import everything inside it.
· In the 'Data Source' drop down you can select the type of source that will be used for the map. The typical sources are CSV, TXT, or Microsoft Excel. In this case, we are going to use Microsoft Excel 2016.
· Under the 'Settings' section select the ellipses next to the 'Excel Workbook' field to open the browse window.
· Browse to the location of the file to be import and double click it to use it at the source.
· In the select the drop down error for the 'Sheet' field to select the sheet that contains the data to be imported
· You can ensure SmartConnect is able to access the file by clicking 'Validate'
· If SmartConnect can read the file you will get the 'Connection was successful' message.
· You can preview the file to ensure the proper one source is selected by clicking the 'Preview' button.
· Verify the data is correct and click 'OK' to close the window.
· Select the + in the 'Key Fields' box to add a key field. This is a requirement.
· Double click the key field to use. In this instance we are using 'Invoice Number.
· Next we need to start setting up the destination. To open the destination options click on the down arrows to the top right of the 'Destination' section. This will expand section.
· Select the drop down arrow next to 'Destination Type' to select a destination. In this case we will select 'Microsoft Dynamics GP'
· Select the ellipses next to the 'Group' field to select a group.
· In the 'Group Lookup' window you can select the type of transaction you want to create. In this case we will select 'Payables' to create an accounts payable import.
· Select the ellipses next to the 'Node Type' to select a node.
· Select the node for the transaction or information you wish to import. In this case we are going to import 'Payables Transactions'.
· Next we will create the data mapping. For an accounts payable invoice import double click 'Create payables transaction' to open the 'SmartConnect Mapping' window.
· Next is to map the fields from the source to the destination. Many fields will be direct maps for their data. You can also set constants or pull fields from GP. To set a constant look in the 'Destination Columns' section, then select 'Column Type' for the field you want to use a constant for and type the constant in the 'Column Name' column. You can see the type line of the 'Destination Columns' section is set to 'Local Constant' and 'Column Name' says '11022018AP', this will put the value '11022018AP' as the batch number. If the batch doesn't exist it will be created.
For the 'Voucher number' field we will need to pull a field from GP. Select 'Additional Columns' at the top of the window to create the GP column.
· In the 'Additional Columns' window click the 'Columns' drop down and select 'GP Rolling Column'. 'GP Rolling Column' means that the field will update as new transactions as import so that you get a unique value each time.
· In the 'Dynamics GP Rolling Column' enter a 'Name' in this case I have named it 'VOUCHERNUMBER'. In series select the series we want to pull the voucher number from, for an accounts payable import select 'Payables Management'.
· Select the ellipses next to the 'Company' field to choose a company.
· Double click the company you want to use for the voucher number.
· In the 'Document Type' drop down select 'Voucher Number' to pull a unique voucher number for each import.
· Click 'OK' to finish the process.
· To put the new GP voucher number column in the 'Voucher Number' field change the 'Column Type' to 'GP Rolling Column' then select the 'VOUCHERNUMBER' field created before.
· The rest of the fields that come directly from the source leave the 'Column Type' as 'Source Column' then select the proper source field for the destination field. As an example, the destination 'VENDORID' field will map to the source 'Vendor' field so I selected 'Vendor' in the drop down. You can see more examples below of the mapping.