Whenever people need to organize, format, and tabulate data, or calculate data with formulas, they create an Excel spreadsheet. Excel formulas provide a language for business logic than everyone understands. Almost all businesses use Excel spreadsheets for important reporting and analysis purposes because it’s easier to do that than using other business applications (like an ERP), and is readily understood by business users. But while Excel is a great way to easily apply business logic through lookups and formulas, it’s actually just a pitstop for the data. The data has to get into Excel first and then has to get back out again to where it needs to be used. That’s why being able to interface Excel with an integration tool is very powerful.
An integration platform enables data to be pulled from any source – be it a file, another spreadsheet, a database or any other application – into Excel. After being processed in Excel, the integration platform then allows the data to be moved to exactly where it needs to be – no copy-paste, no emails and no files.
Flowgear integration use cases
One example is a Flowgear customer who uses our integration platform to take data from a stand-alone Point of Sale system and re-organize it into a finance-oriented spreadsheet. This information is then sent back to Flowgear and is used to generate General Ledger entries in their accounting system. Another customer with a complex pricing requirement uses a spreadsheet to capture rules around how their pricing works and then has wrapped Flowgear around that to automate creation of Sales Orders with the correct pricing.
How Flowgear-Excel integration works
Customers create a template worksheet containing the layout and formulas they need, using real-life sample data from a second worksheet in the file. The spreadsheet is then uploaded into Flowgear and can be used at any point necessary in the integration. While many customers choose to have Flowgear build the integration for them, a number have developers or IT staff who build these integrations themselves.
Benefits
The advantage for businesses, apart from getting correct data more speedily, is that the process of building the integration is quick and allows users to check and confirm whether intermediate results and business logic are correct, because they can understand Excel formulas. This is a very efficient way to work as there is no need for the customer to brief a developer on complex business rules and the customer retains control over any changes to these rules. In a traditional software development project, users would only be able to validate the business logic and results at the end of the project – so if there was an mis-communication or mis-understanding about business logic it would only be picked up at the end.
With Flowgear’s agile integration methodology, projects that require Excel integration can be completed in less than half the time of a traditional project – that converts to lower cost and less testing.
Intelligent caching ensures that as the volume of data increases, processing stays super-efficient enabling integration workflows to be very responsive even when processing tens of thousands of rows.
Spreadsheets used for business-critical applications may need to be approved during periodic audits. Because Flowgear allows the spreadsheet formulas to be maintained as-is, the integration rules are readily understandable and therefore auditing is easier.
Next steps
Ready to find out more? We go through a practical example in Part 2!
