Sage 50 Accounts Professional XML Data Exchange with FTP
This system requires use of XML and the .Net Framework 1.1 or 2.0. The hardest part is the transformation of one type of XML to another. Anyone who has had experience with XML transformations or even EDI will probably have come across this before. It is working around limitations of Sage 50 Accounts Professional in order to electronically exchange data from one system to another. I have only just got into XML and .Net, as well as Sage. As much as I would like to learn and do this myself I simply do not have the time. A test server is set up so the application can be properly tested.
We are the supplier of the goods. Our best customer is moving away from paper-based and email orders to paperless electronic ordering. They have an FTP server setup which we can access.
The new process we have to use
What happens is, the customer deposits an XML file into the FTP folder. That XML file could be any of the following:
Purchase Order Amendment
Purchase Order Cancellation
We periodically check this FTP folder for any new of the above documents. If we find a new document, we grab the data from it and respond with the following XML file:
Purchase Order Acknowledgment (Presumably a confirmation of the order)
Any file that we deposit/upload to the FTP generates an .ack file, meaning the structure and information in the file we have just sent is correct, or a .nack file, meaning the file is incorrect, and hence there is something wrong with the file we have deposited/uploaded, and they will reject the file.
Assuming we had a response of the .ack file, we then respond either immediately or later with one of the following XML files:
At the moment we are taking and responding to orders using email, printing them off when necessary and manually entering the data into our Sage package.
Ideally I want to automatically input any data we receive directly into our Sage 50 Accounts Professional. This version of Sage supports Transaction Email, which works by attaching XML files to an outgoing email. If the receiving party also had Sage with Transaction email, they would be able to automatically input the data directly from the XML file attached in the email and update their Sage.
We have a VPS web server which I propose to use, to bridge the gap between the remote FTP and Sage Transaction Email.
Any outgoing Transaction emails from sage would be sent to the FTP via the VPS. The VPS takes the attached XML file and transforms it into the XML type required for the customers FTP. The VPS then uploads the new XML file to the FTP directory.
The VPS must also periodically check the FTP for any new files. These could be .ack or .nack files as described above or order files. If a new order file is found, the VPS must transform it to Sage XML, and then email it to our address, where it will be picked up by Sage Transaction email.
Some pointers to be aware of:
• There is no way of editing the way Sage generates its XML, hence the need for the VPS to transform it to structure required by the customer. I can, however amend any information in the email body itself.
• Sage can send and receive XML files through its Transaction Email. It cannot check remote FTP directories, again a need for the VPS.
• Emails being sent to the FTP via the VPS will be a sage reports with an attached XML file.
• Emails being received by Sage from the VPS will be in a different XML format (Transaction Email type) than the type being sent out.