Foundation Software is well known in the construction industry, and it so happens, that one of our Salesforce clients uses this system. In order to eliminate duplicate entries and keep the data flowing seamlessly, our team was tasked with integrating the systems.
The client’s service team will use Salesforce Field Service and the back office team will be on Foundation Software. Work Orders will be created and managed in Salesforce Field Service, and once a work order is completed, Invoices in Foundation will be created. Our team pulled out their integration checklist and started reaching out to the clients:
Q) Is this cloud or on-premise?
A) Oh, it’s on the cloud.
Q) Great! Is there an API or a package that is already built and available on the AppExchange?
A) No, there is no API or a package that you can use.
Upon further research, it turns out that the software is a thick client, and it was running in a hosted VM. No API, No Package. The first thought was to use RPA since there is no API. The software is running in a VM, the only way to use RPA was to have an RPA Agent running in the same VM to facilitate the robot. This would require Foundation Software to install the RPA Agent in the client’s VM. We approached the Foundation team with some hesitation (more like trepidation), fully expecting them to laugh in our faces and tell us to go pound sand. Yep, no surprise there, that call went exactly as expected. But, they were nice enough to offer alternatives:
1) Reads - knock yourself out, you will have read-only, ODBC access to the SQL backend.
2) Writes - limited to certain objects/tables using their FSI Importer tool, a Windows application. A little quirky, but once you get a hang of it, you’re on your way. We only saw a way to Insert rows, no updates; at least with the version we got.
Armed with this new knowledge and tool, our team embarked on a quest to conquer this integration task. Long story short, we now have a solution. We deployed a Windows service on the client’s network that uses ODBC to connect to the Foundation SQL database to read Customers, Contacts (just the one in the Customer object), Sites, Jobs, Accounts Payables, and AP Line Items to populate data in Salesforce. To send data to Foundation, a custom component was developed that creates import files from Salesforce; fully formatted, FSI-Importer-ready CSV files for Customers, Sites, Jobs, Invoices, and Invoice Line Items to import into Foundation.
1) The operations team should be able to export new Customers, Sites, Work Orders, Invoices, and Line Items on-demand, filtered by created date.
2) When exporting invoices, consider only Work Orders in “Invoiced” status.
3) In cases where the field service team needed to purchase parts/materials etc., an Accounts Payable invoice (and line items) is created in Foundation, which needed to be incorporated into the Field Service Work Order in Salesforce, and subsequently passed on to the AR Invoice (and line items) in Foundation, along with markup.
4) Implement a customized markup calculation based on line item unit price for spare parts and consumables used on the job. Automate markup calculation based on this in the Work Order Line Item and sync the Extended Price to Foundation.
5) Configurable exclusion criteria - exclude syncing certain Account Payable Line Items into Salesforce that are outside the specified G/L code list.
6) Foundation’s text fields are limited to 30 or 40 characters, trim data coming out of Salesforce to ensure they will be synced to Foundation without errors.
7) Specific naming criteria and formatting requirements for the Work Order name in Salesforce - make it a combination of Job Description, Site name, WO# & Job Type. This will help the operations team to easily and quickly identify the Job in Foundation.
The system is currently live, and the client is using it to run their field service business. We also know the client’s back office team is very happy with the solution and the productivity increases, well, let’s call it what it is - elimination of manual, duplicate entries in both systems, which was error-prone. The level of satisfaction of the back office team was calculated to be extremely high, since “If you were here, I would give you a hug, thank you for saving me time” couldn’t be translated to a numeric score. After delivering this solution and drunk on high praise, our crack team got to investigate what could be done to automate the final, manual step of importing CSV files using FSI Importer. I am proud to say they succeeded! I will not disclose the solution here, alas, we are in the services business and it is bad business practice to give away valuable intel. You can get a peek at that cool solution and a lot more detail on how we can help you with this or any other integration project by reaching out to our awesome sales team at sales@appshark.com