3rd Party Integration
This provides information relating to the swiftpos Microsoft Dynamics GP General Ledger 3rd Party Integration. To access select the Microsoft Dynamics GP menu option from the Data Portal menu.
Subjects ▼
About ▲ ▼
What Is It? ▲ ▼
The swiftpos MS Dynamic GP export is a General Ledger interface that allows Venues to export Sales and Stock Movement data to their Microsoft Dynamics GP General Ledger. The MS Dynamic GP Export will export the following:
What Is Required? ▲ ▼
- 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.
What To Consider? ▲ ▼
- Assigning/Mapping Tabs
- Chart Of Accounts Format
- Export File Formats
- GL Account Code Exported
- GL Account Code Format
- MS Dynamics GP Accounts
- Stock Transfers
- Track Individual Location Costs
Assigning/Mapping Tabs ▲ ▼
- All assigning/mapping tabs MUST HAVE Accounts assigned. Unassigned Accounts may cause errors.
Field Name | Description | Data Type | Max Length |
Account | The MS Dynamic GP Account | Text | 10 |
Description | The description of the Account | Text | 50 |
Type | The type of Account. For example, Assets, Expenses, Sales, COGS, etc | Text | 20 |
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
- Sales/Inventory Transaction Data
Field Name | Description | Data Type |
Batch Number | SPGPYYYYMMDD | Text |
Account | AAAABBBBBBCCC
Where AAAA is the MS Dynamic GP Account, BBBBBB is the Location/Site and CCC is the Department |
Text |
Line Description | AAAAAAAAAAAAAAAAAAA; BBBBBBBBBBBBBBBBB
Where AAAA is the MS Dynamic GP Account Description by default. However for Sales Accounts AAAA is the Product Group. BBBB is the Location Name. |
Text |
Debit Amount | The amount debited to the Account | Decimal |
Credit Amount | The amount credited to the Account | Decimal |
Date | DD/MM/YYYY - The Trading Date | Date |
InterCompanyDB | The Location Group's selected Entity | Text |
InterCompanyTransaction | True/False | Text |
- GL Account Code Exported - The GL Account Code exported is sourced as follows:
- 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.
- Location/Site - This is configured HERE. 6 characters in length. (Represented as BBBBBB in the example below).
- Department - This is configured HERE. 3 characters in length. (Represented as CCC in the example below).
Department Override - To override the default Location specific Department, a Department override can be configured against Master Groups and will be used instead of the Location specific Department when exporting.
- Create a special Master Group (for example, Game Day) and specify an override Dept code (3 characters and represented as CCC in the example below) for it. The code specified here will be used to override the default Location's Department Code when generating the GL Account for export. Override Dept Codes ONLY have to be configured for those Master Groups that require it. All other Master Groups can be left blank. In which case they will default to the Location specific Department.
- Create a special Product Group (for example, Game Day), to which specific Products can be assigned.
- Assign the specially created Product Group to the specially created Master Group.
- Assign (using the Report Group field) the required Products to the specially created Product Group.
- Example
- MS Dynamics GP Accounts - These are configured in the Accounts tab and can consist of one of the following:
- GL Account - These are the Accounts that are added by importing the Chart of Accounts (COA). They are 4 characters in length and are then used as the first 4 characters of the GL Account generated when exported.
- MS Dynamics GP Accounts - These are configured in the Accounts tab and can consist of one of the following:
MS Dynamics GP Account | Location/Site | Department | GL Account Exported | Comments |
AAAA | BBBBBB | CCC | AAAABBBBBBCCC |
Where AAAA is the MS Dynamics GP Account from the COA Import. BBBBBB is the Location/Site value configured against each Location. CCC is the Department value configured against each Location. |
3161 | 003BGL | RES | 3161003BGLRES | |
31612 | 003BGL | RES | 31612003BGLRE |
The GL Account will be generated for export but will most probably cause an error when importing into MS Dynamics GP. |
3161 | 003BG | RES |
The GL Account will not be generated for export in these two cases, as they do NOT comply with the GL Account Code Format. |
|
3161 | 003BGL | RE |
- GL Account Code Exported continued ...
- Department continued ...
- Example continued ...
- MS Dynamics GP Accounts continued ...
- Alternative Account - These Accounts are added (via the Accounts Tab) to ensure an alternate GL Account is generated when exporting. These Accounts can be one of the following lengths:
- MS Dynamics GP Accounts continued ...
- Example continued ...
- Department continued ...
Length | Format | Location/Site | Department | GL Account Exported | Comments |
4 | XXXX | BBBBBB | CCC | XXXXXBBBBBBCCC |
Where XXXX, XXXX-YYYYYY and/or XXXX-YYYYYY-CCC is the MS Dynamics GP Account added as an Alternative Account. The hyphen characters must be included. These will be removed when generating the GL Account for export. BBBBBB is the Location/Site value configured against each Location. CCC is the Department value configured against each Location. |
11 | XXXX-YYYYYY | 003BGL | RES | XXXXYYYYYYRES | |
15 | XXXX-YYYYYY-ZZZ | 003BGL | RES | XXXXYYYYYYZZZ |
- GL Account Code Exported continued ...
- Example
Length | Alternate Account | Location/Site | Department | GL Account Exported | Comments |
4 | 8540 | 003BGL | RES | 8540003BGLRES |
Once an Alternative Account has been added (via the Accounts Tab), they need to be assigned/mapped to the appropriate Accounts, that is via the Clearing Accounts, Sales Account, Inventory Accounts and Tax Accounts Tabs.
Also, A Product requiring Alternative Accounts to that of the Accounts currently assigned/mapped to the Product's Location/Master Group combinations (in the Tabs in the Settings screen), a separate Master Group will need to be created and the Product assigned to it. Also, once the Master Group has been created ensure the Alternative Account has been created and the Location/Master Group combinations (in the Tabs in the Settings screen) are correctly assigned/mapped. |
11 | 8540-045ENT | 003BGL | RES | 8540045ENTRES | |
15 | 8540-045ENT-ZZZ | 003BGL | GDY | 8540045ENTGDY |
The GL Account Code is based on the 3 sources and a GL Account Code Format value of XXXXXXXXXXXXX, will be exported as a 13-character field.
- If the GL Account Code Format value entered is more than 13 characters long, the GL Account Code will NOT be generated.
- 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.
- Any hyphen characters included in GL Account Code Format value entered will result in the GL Account Code NOT being generated.
These Accounts are created/imported to assign/map to swiftpos Location/Media, Location/Master Group combinations and swiftpos Locations in the tabs to the right.
Notes ...
swiftpos does not require the whole of the MS Dynamics GP Account list to be setup, as only a small subset of these is actually used by the MS Dynamics GP Export feature.
As a guide and at a minimum the Accounts needed to be available/configured are as follows:
- Asset Type Accounts
- Undeposited Funds - An account to which Cash payments/refunds from sales made can be posted.
- Clearing Account - An account to be used as a default account for the assigning/mapping of swiftpos Location/Media combinations.
- Accounts Receivable/Trade Debtors - An Account to which Customer/Member Account Charges/Payments can be posted.
- Inventory - Accounts to which Stock Sales generated transaction data can be posted.
- Liability Type Accounts
- Tax - Accounts to which GST Collected and Paid can be posted.
- Revenue Type Accounts
- Sales - Accounts to which income/revenue from sales made can be credited.
- Expense Type Accounts
- Direct Costs Type Accounts
- Cost of Goods Sold (COGS) - Accounts to which the Cost of Goods from sales can be posted.
- Expense Type Accounts
- Stock Adjustments - Accounts to which Stocktake and Stock Adjustments (Receipts, Adjustments, Damaged Goods, Returns, Promotions and Wastage) generated transaction data can be posted.
- Unders & Overs - An Account to which variances in POS Terminal Cash Offs can be posted.
- Direct Costs Type Accounts
Notes ...
For sites with multiple Locations, they may choose to create Location specific Accounts. Obviously this list will vary from Venue to Venue and will also be dependent on the Venue's Chart of Accounts (Account List). it is recommended to liaise with Accountants to ensure the Accounts required are configured correctly.
- 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.
Track Individual Location Costs ▲ ▼
- 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).
When Is It Used? ▲ ▼
Used when Venues want to export swiftpos Sales and Stock Movement transactional data to Microsoft Dynamics GP. The MS Dynamic GP Export will export the following:
- Sales - All Sales, Refunds, Account Charges/Payments, etc.
Notes ...
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).
- Stock Movements - All Stock Adjustments, Damages, Receipts, Returns, Transfers, Wastage and Stocktake variances.
How To ▲ ▼
- Configure For End Of Day Processing
- Export
- Export Multi Venue (LGs) Inventory/Stock Transfers
- GET STARTED
- Import Chart Of Accounts
How To Configure For End Of Day Processing ▲ ▼
To ensure Sales/Stock Movement transaction data is exported daily, ensure the following has been done:
- Complete the steps outlined in What Is Required above.
- 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.
- Confirm the EOD Run Time is correctly configured.
- Confirm the EOD Service is running.
- The first time the End of Day runs, check the Event Logs > swiftpos Services > End of Day Services.
Example of the entries expected to be seen in the Event Log
How To Export ▲ ▼
-
Before exporting for the first time, or before testing the exporting of data:
-
Backup your MS Dynamics GP data.
-
- Before exporting in production for the first time:
-
It may be 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 options in the Export screen. This will need to be done for Sales/Stock Movement transaction data.
-
-
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 options in thein the Export screen. This will need to be done for both Sales/Stock Movement transaction data.
-
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).
Notes ...
Once all the above steps have been completed, it is highly recommended to initially only manually export data, and NOT as part of the End of Day process Once all problems have been identified and resolved, then it can be included as part of the End of Day process.
-
In MS Dynamics GP import the files generated by the Export. The files generated are named as follows:
-
For Sales/Stock Movement transaction data:
GENERAL-YYYY_MM_DD_HH_MM_SS_SSS.csv
-
How To Export Multi Venue (LGs) Inventory/Stock Transfers ▲ ▼
To handle the transfer of Inventory/Stock between Location Groups (Venues), do as follows:
- Ensure that the setup outlined HERE has been completed.
- 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.
- 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:
- 4 bottles of wine with a total cost of $20.00 was transferred from the B&G Bottle Shop location in LG 0 to the LC Conference Room Bar location in LG 1
- GL Journal generated after exporting for the From LG 0:
Batch Number,Account No,Line Description,DebitAmount,CreditAmount,Date,InterCompanyDB,InterCompanyTransaction
SPGP20231024,6301001BGLBSH,Inventory - Beverage; B&G Bottle Shop,0,20,2023/10/24,PANTH,TRUE
SPGP20231024,8652001BGLBSH,Inter Venue Transfers; B&G Bottle Shop,20,0,2023/10/24,PANTH,TRUE (Clearing Account Entry)
- GL Journal generated after exporting for the To LG 1:
Batch Number,Account No,Line Description,DebitAmount,CreditAmount,Date,InterCompanyDB,InterCompanyTransaction
SPGP20231024,6301104LGCCON,Inventory - Beverage; LC Conference Room Bar,20,0,2023/10/24,PANTH,TRUE
SPGP20231024,8652104LGCCON,Inter Venue Transfers; LC Conference Room Bar,0,20,2023/10/24,PANTH,TRUE (Clearing Account Entry)
- GL Journal generated after exporting for the From LG 0:
- 4 bottles of wine with a total cost of $20.00 was transferred from the B&G Bottle Shop location in LG 0 to the LC Conference Room Bar location in LG 1
How To GET STARTED ▲ ▼
- In Back Office
-
- In Global Preferences, In the Other tab, ensure the GL Account Code Format is set. Refer HERE when specifying the value of the GL Account Code format.
- In Location Group Edit ensure the following:
- Ensure the drop-down Entity field is appropriately selected.
- Ensure that the MS Dynamics GP Data Export Path is configured for those Location Groups (Venues) from which transactions will be exported.
- In Location Full Edit ensure the following is configured for all Locations within each Location Group (Venue) from which data is to be exported:
- Ensure the Department field is configured appropriately. 3 characters in length. This field is mandatory.
- Ensure the Location/Site field is configured appropriately. 6 characters in length. This field is mandatory
- Ensure the Post Financial Data option is selected.
- In the Data Portal, ensure that the MS Dynamics GP Accounts are configured in the Accounts tab. Refer HERE for information on how the GL Account Code that is exported is sourced.
- In 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.
- Venues that have multiple Location Groups (Venues) and transfer Inventory/Stock between Location Group (Venues), then it is advised that the following is configured:
- Ensure the Transfer Mapping option is set to Inventory<->Clearing Account in Administration > Global Preferences > Other tab > GL Account section.
- Ensure the Draw From, Transfer In and Transfer Out Media are mapped to an appropriate Clearing Account in the Clearing Accounts tab.
- In the Accounts tab of the Settings screen:
- Create or import Accounts required to assign/map to swiftpos combinations found in the tabs to the right of the Accounts tab. Creating Accounts can be done by selecting the Add Row option and importing Accounts can be done by selecting the Import Chart of Accounts option in the Accounts tab. Refer to the Accounts tab for an example of the Accounts required for an initial Export. Obviously, this example list of Accounts can be customised and added to, to suit the individual requirements of Venues.
-
- In the Assignment/Mapping tabs found 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/imported 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. Refer HERE for examples of the assignments/mappings required for one Location
IMPORTANT
It is highly recommended that ALL grid entries in ALL assignment/mapping tabs are assigned/mapped to a MS Dynamics GP Account. Use the Exclude from Export Account (listed as the first entry in the Account drop down lists in the assigning/mapping tabs) to ensure specific Location/Media, Location/Master Group and Location entries are excluded from being exported.
Caution must be taken when using the Exclude from Export Account, as it can potentially result in unbalanced Journals being generated and these will NOT successfully import into MS Dynamics GP.
- Once all assignments/mappings have been completed, testing of the exporting of swiftpos Sales and Stock Movement transactional data to Microsoft Dynamics GP can commence.
- 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.
- It is recommended that all types of Transactions are tested and imported into MS Dynamics GP. It may be necessary to enlist the assistance/expertise of an accountant to ensure that from the MS Dynamics GP end, all transactions are posted correctly.
- 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. Refer HERE for more information.
- Refer HERE for information on How To Export.
How To Import Chart of Accounts ▲ ▼
-
Select the Import Chart of Accounts option.
-
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.
- If an error is encountered during the Import, the following prompt will be displayed.
Screens ▲ ▼
Export Screen ▲ ▼
To access select the Export menu option in the Microsoft Dynamics GP menu in Data Portal.
Used to export Sales and Inventory related data from swiftpos for import a Venue's Microsoft Dynamics GP General Ledger.
Location Groups Section
- 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 Section
- 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.
Notes ...
The Open Folder icon will only be displayed if the Use LG Export Paths option is NOT selected.
Notes ...
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:
- Location Group - Select from the drop down list the Location Group for which data will be reset.
- Trading Date - Select the Trading Date for which General Journal related transactions will be reset. Resetting is limited to a single Trading Date at a time.
Notes ...
When resetting and exporting Journals again, be sure that they DO NOT result in duplicate Journals when imported into MS Dynamics GP.
- Mark ALL as Exported - Select to ensure ALL General Journal related data in swiftpos is marked as exported. 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.
Notes ...
For those Venues that have multiple Location Groups (Venues) and do Inventory/Stock transfers 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.
Settings Screen ▲ ▼
To access select the Settings menu option from the Microsoft Dynamics GP menu in Data Portal.
- Accounts
- Assignment/Mapping Tabs
Used to create/import a list of MS Dynamics GP Accounts that will be used by the Export feature to assign/map to swiftpos Location/Media, Location/Master Group combinations and swiftpos Locations in the tabs to the right.
Notes ...
swiftpos does not require the whole of the MS Dynamics GP Account list to be setup, as only a small subset of these is actually used by the MS Dynamics GP Export feature.
Notes ...
Account Codes added must comply with the GL Account Code Format as outlined HERE. Duplicate Accounts are NOT accepted.
- Delete Row - Select to delete the currently selected row.
- Import Chart of Accounts - Select to Import the Chart of Accounts (COA) from a CSV file.
Example of the Accounts required for an initial Export.
End of article ▲