Back Office > Data Export > Data Portal > MS Dynamics GP (Great Plains)

 

Description

 

This feature is used to export SwiftPOS Sales and Stock Movement transactional data to Microsoft Dynamics GP.

 

The MS Dynamic GP Export will export the following:

 

1. Sales - All Sales, Refunds, Account Charges/Payments, etc. Note : With regards to Account related transactions, the MS Dynamics GP export does not export individual Account (Debtor) specific data, but rather exports data that can be imported into MS Dynamics GP in a single Debtors Account (and NOT into individual Debtor Accounts).
2. Stock Movements - All Stock Adjustments, Damages, Receipts, Returns, Transfers, Wastage and Stocktake variances.

 

To Be Considered

 

1. Note : All assigning tabs MUST HAVE Accounts assigned. Unassigned Accounts may cause errors. The assigning tabs are:
1. Clearing Accounts
2. Sales Accounts
3. Inventory Accounts
4. Tax Accounts
2. Stock transfers between Locations that exist in different Location Groups (Venues), is supported when exporting to a single MS Dynamics GP Company file. Obviously MS Dynamics GP Accounts will need to be configured for the Locations in question.
3. For Location Groups (Venues) that have the Track Individual Location Costs option selected, transfers between Locations will be exported, with the value of the transfer calculated based on the cost of the Products in the From Location (at the time of the Transfer).

 

---

Pre-Requisites

 

1. To make use of this feature, sites will need to be registered for the General Ledger MS Dynamics GP interface. This may incur an additional charge. To confirm charges, email registrations@swiftpos.com.au.
2. In Back Office> Administration > Global Preferences > Other tab, ensure the GL Account Code Format is set in Global Preferences. The following needs to be considered when specifying the value of the GL Account Code Format.
    
1. The GL Account Code, based on the 3 sources and a GL Account Code Format value of XXXXXXXXXXXXX, will be exported as a 13 character field.
2. If the GL Account Code Format value entered is more than 13 characters long, the GL Account Code will NOT be generated.
3. If it is LESS than 13 characters, then only the number of characters specified in the GL Account Code Format will be generated. The remainder will be truncated.
4. Any hyphen characters included in GL Account Code Format value entered will result in the GL Account Code NOT being generated.
    
3. In Back Office> Administration > Location Groups (Venues) > Location Group Edit ensure the following:
    
1. Ensure the drop down Entity field is appropriately selected in the Back Office> Administration > Location Groups (Venues) > Location Group Edit.
2. Ensure that the MS Dynamics GP Data Export Path is configured for those Location Groups (Venues) from which transactions will be exported for subsequent importation into MS Dynamics GP.

 

4. In Back Office> Administration > Location Records > Location Full Edit - Ensure the following is configured for all Locations within each Location Group (Venue) from which data is to be exported:
    
1. Ensure the Department field is configured appropriately.3 characters in length.  Note : This field is mandatory when exporting data for MS Dynamics GP.
2. Ensure the Location/Site field is configured appropriately. 6 characters in length).  Note : This field is mandatory when exporting data for MS Dynamics GP.
3. Ensure that the MS Dynamics GP Accounts are configured in the Data Portal > MS Dynamics GP > Settings > Accounts tab.
   
   
1. The GL Account Code exported is sourced from the following:
    
1. GL Account - These Accounts can be either added or imported into the Accounts tab. 4 characters in length. (Represented as AAAA in the example below). Once added/imported, these Accounts can then be used to ensure all assignments/mappings are completed in the Clearing Account, Sales Account, Inventory Accounts and Tax Accounts tabs to the right of the Accounts tab.
2. Location/Site - This is configured here. 6 characters in length.  (Represented as BBBBBB in the example below).
3. Department - This is configured here.  3 characters in length. (Represented as CCC in the example below).
    

 

2. For example:
    

5. In SwiftPOS Back Office > Members > Member Classifications, if required set a Discount Expense Media against Member Classifications. This is optional and once set the Discount Expenses incurred in a sale will be exported using the GL Clearing Account the Discount Expense Media is mapped to.
6. Venues that have multiple Location Groups (Venues) and transfer Inventory/Stock between Location Group (Venues), then it is advised that the following is configured:
    
1. Ensure the Transfer Mapping option is set to Inventory<->Clearing Account in Administration > Global Preferences > Other tab > GL Account section.
2. Ensure the Draw From, Transfer In and Transfer Out Media are mapped to an appropriate Clearing Account in the Clearing Accounts tab in Data Export > Data Portal > Microsoft Dynamics GP > Settings screen.

 

7. Ensure MS Dynamics GP is backed up before importing from SwiftPOS. In the event something goes wrong, then at least the MS Dynamics GP data can be restored from the backup.
8. It is recommended that all types of Transactions are tested and imported into MS Dynamics GP. It maybe necessary to enlist the assistance/expertise of an accountant to ensure that from the MS Dynamics GP end, all transactions are posted correctly.
9. Once testing of all types of Transactions has completed, the Dynamics GP export can be optionally included as part of the End of Day process. For more information ...
10. Follow the setup instructions below before commencing with the export of any data.

 

---

Setup

 

1. In Back Office> Data Export > Data Portal > MS Dynamics GP > Settings - Create/Import the MS Dynamics GP Accounts required to assign/map SwiftPOS combinations found in the tabs to the right. For example, Location/Media Clearing Accounts, Location/Master Group Sales Accounts, Location/Master Group Inventory Accounts, etc. The screenshot below provides an example of the basic Accounts required for an initial Export. Obviously this list can be customised and added to, to suit the requirements of individual sites and their corresponding MS Dynamics GP Account List (Chart of Accounts). Note : Account Codes added must comply with the GL Account Code Format as outlined in the Pre-requisites above. Duplicate Accounts are NOT accepted.
    

 

2. Using the Clearing Accounts, Sales Accounts, Inventory Accounts and Tax Accounts tabs to the right of the Accounts tab, assign/map the Location/Media, Location/Master Group and Location entries in the grid of each tab, to the appropriate MS Dynamics GP Accounts (as created in step 1 above). This can be done by using the Quick setup options available in the header section of the tabs, or by selecting from the drop down list of Accounts, listed on the right hand side of the grid, in each of the tabs. Note : All grid entries in each of the tabs MUST BE assigned/mapped to MS Dynamics GP Accounts.

 

---

Operation

 

1. Backup your MS Dynamics GP data.
2. Prior to exporting for the first time, it maybe necessary to ensure that all transactions to date are marked as exported. Previous years transactions may not need to be exported and therefore will need to be marked as exported. This can be done by using the Mark ALL as Exported buttons in the screen below. This will need to be done for Sales/Stock Movement transaction data.
3. Once all transactions have been marked as exported, then reset transactions for the specific Trading Dates that need to be exported to MS Dynamics GP. This can be done using the Reset buttons in the screen below. This will need to be done for both Sales/Stock Movement transaction data.
4. Export the Sales and Stock Movements data for the dates reset. The export files generated will be CSV delimited (.CSV) files, and by default files will be written to the MS Dynamics GP Data Export Path specified against each Location Group (Venue).
5. Note : Once all the setup (above) has been completed, it is highly recommended to initially export to MS Dynamics GP manually (ie. NOT as part of the End of Day). Once all problems have been identified and resolved, then it can be included as part of the End of Day process.
6. In MS Dynamics GP import the files generated by the Export. The files generated are named as follows:
1. For Sales/Stock Movement transaction data - GENERAL-YYYY_MM_DD_HH_MM_SS_SSS.csv

 

 

This screen is accessed from Back Office> Data Export > Data Portal > MS Dynamics GP > Export, and is used to facilitate the export of Sales/Stock Movement transaction data from SwiftPOS.

 

Location Groups Grid

 

• All - Select to ensure the Data in all Location Groups listed in the grid are flagged for Export. Unselect to ensure no Location Groups are selected.
• Export - Select individual Location Groups to ensure Data from those selected is Exported.
• ID/Name - Displays the Location Group ID and Name.
• LG Export Path - Displays the Export Path to which Export files will be written. These are configured in the MS Dynamics GP Data Export Path for each Location Group (Venue).
• Last Result - Displays the result of the last time Data was Exported.

 

Sales/Stock Movement Transaction Data

 

• Location Group - Select from the drop down list the All or one of the Location Groups from which data will be exported.
• Include All unexported up to Trading Date - Select to ensure that all Transactions, up to and including the selected Trading Date, that have not been exported are exported.
• Use LG Export Paths, otherwise use - Select to set the default Export Path. Leave Unselected to use the LG Export Paths as configured in the grid above. Select the Open Folder icon to open and view the content. Note : The Open Folder icon will only be displayed if the Use LG Export Paths option is NOT selected.
• Trading Date - Select the date for which and export file is to be created. Note : Once exported, the data exported will be marked as exported. This will ensure that the same data is not exported multiple times.
• Export - Select to invoke the export. Once completed a prompt will be displayed confirming completion. A file named similar to GENERAL-2018_06_19_13_14_18_023.csv will be generated and written to the Export Path specified above.
• Reset - Select a Trading Date to ensure that General Journal related transactions for that date are flagged as NOT Exported, so that they can be exported again. Select to display the following prompt:

 

• Mark ALL as Exported - Select to ensure ALL General Journal related data in SwiftPOS is marked as exported. Note : This is usually recommended when initially configuring SwiftPOS to export to MS Dynamics GP for the first time and there is a requirement that all previous General Journal related transactions are marked as Exported.
• Note : For those Venues that have multiple Location Groups (Venues) and do Inventory/Stock transfres between them Location Group (Venues)For those Venues that  is usually recommended when initially configuring SwiftPOS to export to MS Dynamics GP for the first time and there is a requirement that all previous General Journal related transactions are marked as Exported.

 

Multi Venue (LGs) Inventory/Stock Transfers

 

To handle the transfer of Inventory/Stock between Location Groups (Venues), do as follows:
 

1. Ensure that the setup outlined HERE has been completed.
2. Ensure the checkbox opposite the appropriate Location Groups (Venues) has been selected. That is, all LGs that will be engaged in Inventory/Stock Transfers, regardless of whether they provide or receive inventory/Stock, or both.
3. Run the export for the dates the Transfers took place. If all goes well then for a single transfer from one LG to another, at a minimum, 2 GL Journals should be generated that would look something similar to the following example:
    

 

---

Import Chart of Accounts

 

For information regarding the Charts of Accounts file format, see below.

 

• Select the Import Chart of Accounts button.
• In the Open screen select the .CSV file to be imported.
• Select Open
• If an error is encountered during the Import, the following prompt will be displayed.

 

• If SUCCESSFUL, the grid in the Accounts tab will be populated with the imported MS Dynamics GP Accounts.

 

Chart of Accounts File Format

 

 

For example:

1001,Sales Bar Bulk Beer,Sales
1002,Sales Bar Bottled Beer,Sales
1003,Sales Bar Spirits,Sales
1004,Sales Bar Wine,Sales
3001,Purchases Bar Bulk Beer,COGS
3002,Purchases Bottled Beer ,COGS
3003,Purchases Bar Spirits,COGS
3004,Purchases Bar Wine,COGS

5000,Cash On Hand ,Assets

 

---

MS Dynamics GP End of Day (EOD)

 

To ensure Sales/Stock Movement transaction data is exported daily, ensure the following has been done:

 

1. Complete the steps outlined in the Pre-Requisites above.
2. Ensure the MS Dynamics GP Export option is selected in the EOD Options screen for the Location Groups (Venues) from which transactions will be exported.
3. Confirm the EOD Run Time is correctly configured.
4. Confirm the EOD Service is running.
5. The first time the End of Day runs, check the Event Logs > SwiftPOS Services > End of Day Services. An example of the entries expected to be seen in the Event Log is shown below:

 

6. Note : The first time the MS Dynamics GP Export runs successfully as part of the End of Day process, the export files generated will contain Journals for ALL Transactions that are NOT marked as Exported. This will mean that once the EOD has processed, there should not be any Trading Dates available for export, when viewed in the Export screen, for each of the Location Groups (Venues) that have the MS Dynamics GP Export option selected in the End of Day screen.

 

---

File Formats

 

The following is the file formats currently generated by Swiftpos to export to MS Dynamics GP. For an example of a export file generated, click here.

 

1. Sales/Inventory Transaction Data

 

---

Related Topics

 

1. Data Export
2. Import/Export Overview
3. Sample File

 

---