There is a growing number of businesses that are using SaaS (software-as-a-service) solutions for their accounting systems, rather than installing a package on their in-house servers. Sage One is a SaaS accounting solution that is growing fast. However when it comes to keeping track of prospects and customers, many business use Salesforce, which is also a SaaS solution. So how do you integrate Sage One with Salesforce? With the release of Flowgear’s Sage One connector recently, I thought I would see how easy it would be for someone to build an integration between Sage One and Salesforce.
A basic integration to get customers in Sage One into Salesforce is quite simple, as the workflow below shows.
Integrating Sage One and Salesforce – basic example
The shared workflow can be found at flowgear.me/#s/KStZ5QX
The integration comprises only four Flowgear nodes:
- Sage One connector – which accesses the Sage One APIs
- Splitter – to split the data into chunks
- QuickMap – for mapping Sage One fields to corresponding Salesforce fields
- Salesforce connector – to access the Salesforce APIs
There is another reason for it being a simple integration – I created the workflow and I am not a developer, but I wanted to see if someone with a limited modern development background could build a simple integration workflow between the two systems. While it took me several hours with my limited skills to build the workflow, a real developer could put it together in a very short time, and make it more general purpose to both create new customers and update existing customer data.
Building the integration
The steps to build the workflow are quite straightforward.
- Create a new workflow and get the Sage One connector
- Set up the connector parameters (see this short video to see How to get Sage One company ID)
Sage One node
Sage One connector. Click to enlarge.
- Add the Sage One node and specify the parameters – in this case, Action=Get All, Module=Customer – then select the ‘Run this node’ by clicking on the gear icon.
- The activity log at the bottom of the screen should show a successful result – if there is a problem you will see ‘Error’.
Run Sage One note. Click to enlarge.
Sage One logs. Click to view.
If you scroll the activity log all the way to the right, you will see ‘Response XML <root>’ which contains the result XML from running the node.
As a test, I added the following HTML in the Parameter XML field of the connector, so only the top 5 rows were read:
<Root>
<key Name=”$top” Value=”5″ />
</Root>
- The next step of the workflow is to use the Flowgear Splitter node. The reasons for doing this are:
- unless you know Salesforce APIs well, you won’t know what the Salesforce governor limits are – the maximum number of rows it will allow to be added or updated in one pass – so splitting the data into small chunks will prevent you accidentally exceeding the governor limits;
- it allows you to monitor how the integration is working by processing the data in chunks, rather than as one big group.
- Wire the Response XML (output from Sage One) into the SourceDocum (input) of the Spitter.
- The ChunkSize controls how many records from Sage One will be processed in each pass until there is no more data to process.
- SplitXPath allows you to specify what parts of the XML you want to process; it uses the XPath syntax to navigate through elements and attributes in an XML document. As this is a case of processing all the attributes of a record, using ‘root/Results’ is the value. If you were processing only certain fields of a record the value would be ‘root/Results/fieldname’.
- For the Encapsulation value, ‘ParentAndRootNodes’ is used for the same reason as ‘root/Results’ above.
- Because you need to map the data from one system to another, you need to use the QuickMap node to map SageOne fields to Salesforce fields.
From the Splitter:
- wire the Split to the QuickMap to ensure each ‘ChunkSize’ batch of data is output to QuickMap
- wire the SplitDocumen of the Splitter (output) to the SourceXML (input) of QuickMap
Before you can get QuickMap to work, you need to create the add the Salesforce node and provide the schema, so that QuickMap can match data between Sage One and Salesforce.
- create a Salesforce connection and specify the parameters
- add the Salesforce node to the workflow
Salesforce connector
Wired up Salesforce node. Click to view
- On the Salesforce node, select the gear icon, then select ‘Choose Sample’, and select the ‘Create Account’, this populates the Salesforce account schema.
- Wire up TransformedXml in QuickMap to RequestXml in the Salesforce node
- Then click on MappingXml in QuickMap to bring up the visual mapping tool
QuickMap visual mapper. Click to enlarge
To map fields from Sage One to Salesforce, click once on the Sage One field then find the corresponding Salesforce field and click once on that.
If you’ve got this far successfully, when you run the full workflow you will see a log report like this. You can see how the Splitter iterates the processing. Don’t forget to remove the Parameter XML specification in the Sage One node before you run the workflow so that all the data is processed.
You can check your Sage One ‘List of Customers’ against the Salesforce Accounts to see how the data has been added.
This is a really simple and basic example, and you would want to have a more sophisticated workflow to ensure updates into Salesforce as well as inserts; there is an API for ‘upsert’ for this purpose. The reason for my doing this is to show that Sage One to Salesforce integration can be done by someone with fairly basic development skills.
