How does Mandalay CS interface with financial systems?
The Mandalay CS system provides a facility for transferring data between systems. This is generally transactional data but can also be other details as required by the financial system. The system can also receive data from financial systems such as debtor details and credit stop indicators.
The following article explains the basic features and principals behind financial interfaces within Mandalay CS.
Overview
Transaction Export
Any transaction captured in Mandalay CS can be exported to a finance system. This could include Debtor, Internal and Cash transactions. The export process is managed by a utility called Mandalay Technologies Export (see image). This tool is loaded with a database SQL script that is used to extract the data to be interfaced to the other system. Once the database is selected from the database it is added to a text file that can then be uploaded to the financial system. This file can be any text file format, e.g. CSV, tab limited, fixed width etc.
Once the export has been run all the records included in the file are marked as exported and the export facility will no longer attempt to export this data. It is possible to re-export transactions if necessary.
This utility has a number of configuration options available to specify how the data is formatted in the file. The details on how to configure this utility are covered in a separate article.
Note: The Mandalay system does not provide any standard database to database interfacing. If this is required then a bespoke utility will need to be commissioned.
Database Script
The data is selected from the database using one or sometimes two scripts. These scripts are MS SQL select statements and are created by an experienced Mandalay consultant. The data columns configured in the script are dictated by requirements on the system being exported to. By default the system provides a standard script for exporting data in a specific format which is then overwritten with a bespoke solution if required.
The data added to the file can include two types of transaction information. It can include transaction header details and if necessary transaction line items.
Export Process
The process for exporting the data can either be manually run by an operator or automatically to a schedule. If an operator is running the export they have a few options. They can choose to see a preview of the data before the export is run. They can also specify a date range that the export is effective for, this can either be based on the transaction time stamp or the transaction effective date. In most cases no date needs to be specified as the export will only collect any transactions that have not previously been exported (i.e. only transactions created since the last export).
The automated option can only run without date ranges specified and does not allow a preview of the export before running. The schedule for the export is managed through MS Windows scheduled tasks.
Export File Specifications
Each financial system will have a requirement on what the specification of the data text file is that they can accept. This specification will included the name of the columns of each data, the type of data for each column, if the data column is mandatory and what the data represents in their system. A Mandalay consultant will use this specification along with consultation with the data administrators and finance staff to determine which data fields in the Mandalay CS database need to be included in the script for the export.
A file specification may also determine how each column of data is separated, generally this will just be a comma but can vary depending on the finance system.
Known File Specifications
Mandalay has developed interfaces to the following financial systems and in some cases has developed an implementation guide for them. If a system is not listed here Mandalay can still develop an export process as long as an import specification is provided.
- Tech One - Property & Rating (guide available)
- Civica - Authority (guide available)
- Synergy Soft (guide available)
- MYOB (most versions)
- Quickbooks
- Civica - PCS
- Pathway
Transaction Details or Summarised Data
The export facility provided by Mandalay is designed to export detailed information about transactions. By default it can not summarise data for each debtor and product for the period. The utility uses the transaction details as it needs to be able to mark the records as exported in the database. However is is possible to manage this through a 2 step process. The first step is to run the export with the script to generate the summarised data then another export is run that has a script based on the summary data but will then mark all the transaction as exported.
The choice between details and summary data is specific to each implementation. Debtors may prefer to receive an itemised invoice so they can check for discrepancies, but it may also be possible to summarise the data and then produce a detailed report to send with the invoice. Using the itemised invoice approach means less administration as no collation needs to occur between the invoice and the produced detail reports.
Data Import
Mandalay CS has another utility for importing data into the Mandalay CS database. Most administration data can be imported but it is generally used for importing debtor details exported from a financial system. This client data can include a flag to indicate that a debtor is on credit hold. With the correct setup of debtor information import into Mandalay all the debtor details can then just be managed from a single point (the finance system), removing any duplication of work.