How To Integrate Craft Commerce with Sage 100: Part 2
You don’t have to worry, that Hori Hori (Dig Dig) tool is on its way to you straight from the Barebones Living warehouse. We made it look easy, but trust us when we say it wasn't! Let's pick up where we left off in Part 1 and see how the discovery process unfolded.
The very first step in a Solspace discovery is to get a feel for the people involved. Any painful technical challenge on the web always seems to come down to the effectiveness of 'team'. So we started getting to know one another. We met the people at Barebones who had a stake in the success of the new platform. We met the people connected to the Sage 100 side of things, in this case a great team from one of the top 25 accounting firms in the country called Eide Bailly (https://www.eidebailly.com). They sell and service the Sage 100 platform and are the go-to for issues related to Barebones' use of the product. Happily, it was quickly established that we had a team of light-hearted but serious professionals in the mix and we knew the human element was in place for us to succeed. Now we had to make sure the technical pieces would line up...
In an ideal world, any modern API integration will go through a JSON based REST API protected by OAuth2. These are currently the standards on the web, but not every software system out there is hip to this yet. Additionally, Sage 100 is the kind of system that, like Microsoft Dynamics GP, is an on-premises system, so Sage 100 is not sitting in the cloud like a lot of other web services. Barebones' instance of it is located in their onsite data center, locked securely behind a heavy door with a scary sign on it.
So he we are, with no REST, no JSON, no OAuth2; how could we possibly get data into and out of Sage 100? The kind people at Eide Bailly came to the rescue. I can't name names, but Michelle, you know who you are! We had a very productive conversation about all of the connectivity possibilities. We learned that Sage 100 could expose a web service API, but we also heard some fear and loathing associated with it. We learned that Sage 100 could be connected to at the ODBC level where we might talk directly to the data tables, but that sounded inelegant and unpleasant. Then we learned that the most common and predictable connection method was simply to use CSV (comma separated value) files on a shared FTP server to exchange inventory and order data, etc.
There was an obvious general comfort level for the people involved as they talked about using this CSV method, which led Solspace to feel very friendly towards it as well. The team involved had used this approach before with other systems, and knew that the bugs that might crop up in such a system would be pretty transparent and easy to identify. It could be engineered in a way that could safeguard against some of the known failure points, so while it was far from our preference technically, it fired on all cylinders at the human level. And this is the power and value of the discovery cycle. You uncover the big picture and clarify the priorities, understand the current status of the situation, and then you can shape a plan around bringing the right solution to life in a way that can be maintained over the years to come.
Most of Solspace’s discovery projects include one or more proofs of concept. When we can, we like to write actual code, run it on a server somewhere and then let our clients click around to see stuff actually working. It's a great way to keep everyone honest and connected to reality. In the case of the Barebones job we were not going to be able to prove the concept of connecting Sage 100 with Craft Commerce through CSV data transfer right away, but the great thing about the solution is that we knew we could get Craft to output CSV data and we knew that we could get Sage to gobble it up and then get the data onto a shared server somewhere. But we just could not line up all of the pieces because Sage 100 was stuck in our client's data center location, and it would have been costly to get other humans involved to help us prove our concept. So we relied on our belief in the validity of the plan.
And this is how discovery turned into a plan and a proposal for the creation of the solution. Barebones looked it over, reviewed the costs and the risks and decided to go forward. We divided the Solspace team assigned to Barebones into 2 smaller teams for this job. One team focused on building the Craft system, while the other team built the Sage integration pieces. What we ended up doing, for sanity and cleverness sake, was to use Dropbox instead of an FTP server for storing and sharing files. This made things easy for the Sage 100 programmer, as they could put a Dropbox folder right on the Windows machine in their data center and the CSV files would come and go from there.
We wrote a lot of code to support what we needed to do with the CSV data; we had a file that would push new orders into Sage, and a file from Sage that told us what orders it had taken in. We had a feed from Sage that told us what orders had been put into processing, as well as an inventory feed and one more that told us when an order had shipped. We set up cron routines to sniff and check these files so that we could update Craft and the customers about the status of order and shipment events, and we established a method of archiving processed CSV files which turned out to be key. We found ourselves referring back to these historic files on a regular basis to find where orders may have gone into the system incorrectly or been missed. This made troubleshooting and customer service issues very easy to resolve.
As I write this, the system has been up and running successfully all summer and fall. Of course there were the usual post-launch bugs to be cleaned up, but thanks to the system design they were easy to see and easy to fix. Because we began with a focus on the human component and made it the priority, the client/end user was very happy with the results. More specifically, everyone involved knew what had happened throughout the development process, and knew that the system (and Solspace) were quite transparent as to where faults would lie now and in the future. This allows the Barebones in-house team to maintain their calm when issues arise, identify a probable cause, and then work together quickly and professionally to resolve them.
Why should you care about all of this? It's a great example of how Solspace addresses difficult technical and business challenges. We take an approach to web development that starts and ends with the human relationship. We leverage the power of that to accomplish otherwise very difficult tasks for our clients, and build lasting partnerships in the process.
Now go and enjoy your Hori Hori! Dig dig!