Why we like LINQ
When we first started thinking about workspaces in mid 2008 we expected to be doing queries using SQL and showing the results in the Sage 200 desktop. After all, Sage 200 is based on a SQL Server database and naturally, our customers are only interested in data that we hold in our database. And Sage 200 is the only tool they use…right?
But then we started thinking “what about data that we don’t keep in our database such as spreadsheets or unstructured documents like emails, PDFs or word documents? And what about data from the web or from web services?” We realised that our customers have useful data held in lots of different places and that we should be looking to bring that together with Sage 200 data in a personalised simple but powerful way to give our customers a fuller, more rounded view of their business.
And once a customer has created a view they like, they should be able to see it in whatever way they want. If they are going to use the data to do tasks in Sage 200 then sure, the Sage 200 desktop is the place to show it. But maybe they want to save the view as a PDF to email to a colleague. Or maybe they want to share it with colleagues in Sage 200 CRM. Or see it on their mobile while they’re out of the office? Or use it in a spreadsheet to do some ad-hoc analysis. Obviously the views can’t look the same in all these places, but the underlying data can be the same, as can the way it is linked together.
This thinking eventually led us to select Linq as a querying language instead of SQL (try querying a web service using SQL) and to the concept of workspaces. As of the Sage 200 2009, the only place that workspaces are visible is in the Sage 200 desktop but with the 2010 release we will start realising the broader vision outlined above with workspaces on the Sage 200 mobile application. The 2011 release will see more of the vision being implemented (probably as formatted reports).
To understand how workspaces translate between the Sage 200 desktop, a mobile device and formatted reports, you first need to understand that the what you see in the Sage 200 desktop is divided into two layers.
At the bottom, more abstract layer, a workspace simply consists of a set of queries, potentially from a variety of data sources. These queries can be linked together at the data level by parent/child relationships that you can define in the workspace designer. But even without these parent/child relationships they are also linked together at the business logical level by virtue of the fact that a user (or developer) has seen fit to put them all in the same workspace. The following diagram shows this abstract view of the sample Google workspace from the Sage 200 SDK:
The top level is how the abstract definition is visualised to the customer. The data from each query is visualised as a list, a detail view or in a web browser pane. The business logical linking is visualised simply by arranging all the lists and views in the same screen. The parent/child relationships are visualised as filters that get applied to the child query when each item in the parent query is selected and by automated hyperlinks. The whole thing is specialised for the Sage 200 desktop by adding filters, column ordering and hiding, sorting and a right-click context menu.
Obviously on a mobile device there is no room to display all that data at the same time, so we keep the same abstract definition, but visualise it in a different way that is tailored to the mobile device. Again the queries are visualised as lists or detail views, but without some of the extra features that you get in the desktop. The business logical linking is visualised by collecting all the lists under a single menu item on the device. and the data level linking is visualised as a menu based drill-down.
For formatted reports, the absract workspace will be visualised using header and details sections in reports, with sub reports, drill down reports and hyperlinks.
This whole framework is made possible by Linq. Linq provides the powerful query language that can target databases, spreadsheets, web services, full text search engine indexes and the rest. It also provides a uniform querying interface that can be used to bind to windows controls in the Sage 200 desktop, mapped onto SData requests from our mobile application and built into a generic data provider for the Sage Report Designer.
There is certainly a learning curve with Linq, but hopefully this post explains why we think that it is worth getting to grips with!
