Sage Developers' Blog

Our recommended approach for deployment of new or customised Workspaces to a customer site is to create an Add-On (SDBX) package for it.
This approach makes deployment quite straight-foward. Eating our own dog-food has, however, shown that using the tool to create these packages is anything but straight-forward. To be honest, it’s quite painful and error prone [see my update on this]:

You need to remember the following files:
1 Workspace xml file(s)
2 Content Part xml file(s)
3 Content Part Info xml file(s)
4 Query xml file(s)
5 Workspace Layout file(s)

You also need to remember to set the correct file type for each file (particularly tricky for Layouts, which are actually two files and must be installed using the “resource” file type).
This is further complicated by the fact that many of the files will have the same names, and therefore folders need to be created inside the package.

In an attempt to simplify this in the short term (until we’ve included a proper solution within the desktop), I’ve created a simple application for packaging workspaces.
The tool will allow you to select any modified Workspaces you have on your system for inclusion in the package and will give you the choice of including multiple Workspaces in a single package, or generating a Workspace per package.

Note that it will only include files in your “Modifications” folder – if you want to include files from other AddOns (”Extensions”) or from the core system (”Default”) you’ll need to copy them to “Modifications” first.
The exception to this are Workspace Layout files, which will always be included if they are not one of the four defaults.

The caveats:

• You will only be able to acces this link if you are a registered Sage 200 Business Partner or Developer. Apologies if this causes you any inconvenience – if it does, please leave a comment and we’ll get in touch to find out what we can do.
• The tool will not be officially supported by Technical or Developer Support – again, please leave a comment with any questions, comments, suggestions or problems.
• The tool was created in my own time for my own convenience – and it’s been a long time since I was a professional developer… It has not had the level of engineering or Q/A rigour you would expect from an official Sage product (it isn’t one) and you should treat it with the same suspicion you would any other tool downloaded from the web!

Download the Installer

Update:

Looking back, perhaps I should have given a little more detail in my post about the purpose of this tool and the reason I developed it.

As I’m sure you’re aware, Sage 200 provides a mechanism for third party developers to package up their amendments or enhancements to the core system and deploy them across a customer’s site via a reliable and supported mechanism. This system, and the tools that enable it, were aimed squarely at our developer community and require a certain level of technical proficiency, as befits their nature.

With the introduction of our “Workspaces” technology we extended these same tools so that new or amended workspaces could be deployed via the same mechanism. Since Workspaces don’t require quite the same level of technical proficiency to develop, we have come to the conclusion that the tool is too complex for this particular application and that a simpler – more focussed – tool would be more appropriate. This is a conclusion we’ve reached over a period of time – using the tools ourselves.

I described the process as “quite painful and error prone” because that’s exactly how I’ve come to see it – Workspaces comprise a number of files and it’s very easy to miss one of them, so the whole process can take longer than it feels like it should. However improving usability is something we’re always keen on (and the approach we have in mind will be much easier than any standalone tool could ever be).  Of course, this takes time – we need to design, implement and test the improvements and then include in a scheduled release. In the meantime, we have something we’re finding helpful ourselves and we have the opportunity to give others the chance to use it too. I think it’s a great thing that we are able to do so.

Our blog is a space where we can be open, honest and do our best to help, so do continue to let us know how we’re doing.

Sometimes it can be useful to create lists that contain content that is fixed by you in the query and isn’t fetched from a data source.

A simple example of this might be creating a set of links to web-based content.  The links are fixed, but the content may change.  In the following example, the list data is first written  into a string array and then ‘Selected’ and cast to an IQueryable collection.  The only slight subtlety with this is the necessity to put this into the Queryable form and to ensure that fields are labelled and that the system can infer their type using the Select statement:

string[] Descriptions = new string[3];
//
Descriptions[0]="What is a workspace?|Bp5nPGr0ptw";
Descriptions[1]="How To Build A Sage 200 Workspace - Part 1|1wBcLZkBlxM";
Descriptions[2]="How To Build A Sage 200 Workspace - Part 2|98I7REkQTGA";
//
Descriptions.Select
(
    s=>new
    {
        Description=s.Split('|')[0],
        URL="http://www.youtube.com/v/" + s.Split('|')[1] + "&hl=en_US&fs=1&"
    }
)
.Dump();

A more practical scenario might be one where you need to programatically develop some fields in a query, but retrieve other fields from a data source.  In the following LINQPad snippet (for example) we compare daily sales for two calendar years.  The Sage 200 data only has information about days where we actually made sales.  If we want to compare these two series against an accurate timeline, then we need to generate our own list of days and join the series for the two years onto it:


int[] days = new int[365];
//
for(int i=0; i<365; i++){days[i]=i+1;}
//
var q1=days.Select
 (
  s=>new
  {
   Day=s
  }
 );
//
var q2=SOPOrderReturns
 .Select
 (
  s=>new
  {
   SaleDay=s.DocumentDate.Value.Day,
   Value2008=(s.DocumentDate.Value.Year==2008) ? s.TotalGrossValue :0,
   Value2007=(s.DocumentDate.Value.Year==2007) ? s.TotalGrossValue :0
  }
  )
 .GroupBy(g=>g.SaleDay)
 .Select
 (
  s=>new
  {
   SaleDay=s.Key,
   V2007=s.Sum(e=>e.Value2007),
   V2008=s.Sum(e=>e.Value2008)
  }
 );
//
var q3 =q1
  .GroupJoin
  (
   q2.DefaultIfEmpty(),
   l=>l.Day,
   r=>r.SaleDay,
   (l,r)=>new
   {
    l.Day,
    V2007=(r.Any()) ? r.First().V2007 : 0,
    V2008=(r.Any()) ? r.First().V2008 : 0
   }
  )
.Dump();


With the new System Admin tool in 2009 we have separated the concepts of menu design and restricting access to specific features.
We have allowed a user (with sufficient privileges) to amend a menu for a role, so that all members of that role will see the changes.

Adding a new menu item to all/many roles, however, is not an obvious process; the apparent approach is to add the same menu item to each role separately. This can be very time consuming for customers with lots of roles.

There is an alternative…!
Note that the approach is covered in the system help file in the topic ‘Add a new navigation group or option to all roles’ (or search for ‘Add a new navigation group’) but here we cover it in a little more detail.

Consider the scenario where the user has created a new report that they want to make available to everyone, irrespective of their role.
The overall principle we’re going to follow is to export a modified menu as an add-on package, then reimport it – which will amend the core menu template used by everyone.

1. Create the feature and target in SAA, then add the report to your own menu as usual

2. In the desktop menu designer, right click on the report and select “Export” from the context menu

3. In the wizard, “Next” past the first page then enter a path to the location where you want to create the package. It doesn’t matter where, so long as you can find it again!

4. “Next” to the last page, enter an “Add-on Name” (again, it doesn’t really matter what) and click “Generate”

5. Open the System Administration tool and go to the “Add-Ons” page
6. Right-click on the right-hand side of the screen and select “Add New Add-On”

7. Click “Next”, “Browse” to the package you just created and “Install”

The menu item has now been added to the core menu template; all you need to do now is authorise access to the feature for all roles that need it – this will determine whether the menu option actually appears for any given user.

Additional fields in Project Accounting are stored in a highly normalised way that can make them difficult to work with, especially for the kind of sorting and filtering operations that are key to workspaces. 

LINQ helps us out a little by allowing us to get straight to the collection of analysis fields associated with a project item.  The slightly tricky (and dataset specific step) is getting at a specific field by using its primary key – not elegant, but effective.  The other aspect of additional fields in project accounting that can make them tricky is that they model a number of different data types and – again – making use of a field means knowing its data type ahead of time, making queries against this schema specific to a particular dataset.

Below is a LINQ query example (built in LINQPad) of the kind of approach we can take to getting analysis codes into workspaces, and working with them effectively.  In particular, note the ‘WeeksTaken’ field is a derived field based on the actual value of the days taken additional field – something that is only really possible because we know ahead of time (and assert in the query), that this is a numeric field
PCProjectItems
    .Where(w=>w.PCProjectItemTypeID==0)
    .Select
    (
        s=>new
        {
            s.Code,
            s.Title,
            Status=s.ProjectStatus.Description,
            Region=s.PCProjectItemAnalysisFields.Where(w=>w.PCAnalysisFieldID==105957).First().PCAnalysisFieldValue.TextValue,
            DaysTaken=s.PCProjectItemAnalysisFields.Where(w=>w.PCAnalysisFieldID==105908).First().PCAnalysisFieldValue.NumericValue,
            WeeksTaken=(s.PCProjectItemAnalysisFields.Where(w=>w.PCAnalysisFieldID==105908).First().PCAnalysisFieldValue.NumericValue)/7
        }
    )
    .Dump();
 

and the results are as follows (using Demodata):

As currently implemented, the workspace infrastructure only allows simple relationships to be established, in particular each content part can only be filtered by the value of exactly one field from exactly one other content part.

This is fine for the majority of situations, and often helps when thinking about breaking down a problem into master lists and detail lists.  Sometimes, however it would be nice to be able to filter a list by parameters from more than one parent list.

Consider the following scenario:  Starting with a list of customers, I want to be able to select a customer and see all of the stock items they have bought and then perhaps drill in further by selecting a stock item and seeing which sales orders were placed to buy this particular item.

The list of customers is pretty straightforward, and we can reuse an existing content part to do this.

The list of stock items is initially a little trickier, but starting a query from SOPStandardItemLinks quickly gives us both the stock item data and customer information to filter on, for example:

return cxt.SOPStandardItemLinks
    .Select
    (
        s =>new
        {
            CustomerID=s.SOPOrderReturnLine.SOPOrderReturn.CustomerID,
            StockItemID=s.StockItem.ItemID,
            ItemCode=s.StockItem.Code,
            Name=s.StockItem.Name,
            LineQuantity=s.SOPOrderReturnLine.LineQuantity,
            LineTotalValue=s.SOPOrderReturnLine.LineTotalValue
        }
    )
    .GroupBy
    (
        t=>new{t.CustomerID,t.StockItemID,t.ItemCode,t.Name},
        (t,x)=> new
        {
            t.CustomerID,
            t.ItemCode,
            t.StockItemID,
            t.Name,
            Qty=x.Sum(a=>a.LineQuantity),
            Total=x.Sum(a=>a.LineTotalValue),
            CustomerItemKey=t.CustomerID.ToString()+"#"+t.StockItemID.ToString()
        }
    );


Note that we have grouped the initial select in order to give a unique list of items with summary values, rather than a list of all purchase of all items.  The other important thing to note is that we have created a new key field ‘on the fly’ that includes both the customer ID and the stock item ID.  The point of doing this is that we can then build the third list of sales orders with the same dynamically created key and use this field to tie the two content parts together.  In this way, the third list gives us orders for a particular customer AND item.

This approach won’t solve all of the scenarios where you need to filter a list by more than one filter parameter, but it’s a useful approach for situations like this one where we’re drilling down to detail by more than one field at a time.

return cxt.SOPStandardItemLinks
    .Select
    (
        s=>new
        {
            OrderID=s.SOPOrderReturnLine.SOPOrderReturnID,
            StockItemID=s.ItemID,
            DocumentNo=s.SOPOrderReturnLine.SOPOrderReturn.DocumentNo,
            DocumentDate=s.SOPOrderReturnLine.SOPOrderReturn.DocumentDate,
            DocumentType=s.SOPOrderReturnLine.SOPOrderReturn.DocumentStatus.Name,
            GrossValue=s.SOPOrderReturnLine.SOPOrderReturn.TotalGrossValue
            CustomerItemKey=s.SOPOrderReturnLine.SOPOrderReturn.CustomerID.ToString()+"#"+s.ItemID.ToString()
        }
);

We provide a query editor in Sage 200 where you can write new queries right in the Sage 200 desktop, and for simple or moderate queries this works fine and is very convenient. For very complex queries however, the lack of  features like code-autocompletion means that there are better tools for the job. We may do some improvements to the query editor for the 2010 release, but it still likely to have some gaps compared to the likes of Visual Studio. Long term we want to provide a graphical query designer that can be used in our new report designer that is coming with v2010, but we are not there yet.

So in the meantime, what is the best way to create new Linq queries?

Well, one way you can do it is in Visual Studio – just make a new console or windows application, add a reference to our accounts or CRM model assembly and write the query. We have a Visual Studio project in the Sage 200 R&D team that contains many of the more complex queries that sit behind our out-of-the-box workspaces. We use this mainly for unit testing though, rather than for active query development.

The most popular tool in our team for query development is undoubtedly LinqPad. This is one of the best development aids I have ever used and it’s free too! Actually while there is a free option, we have paid the fee to activate the code auto-completion (a.k.a. Intellisense) feature. As someone or other put it: “It’s like turning the light on in a pitch black room”.

LinqPad is a tool for quickly developing and executing code snippets, queries or even small programs in a very quick and easy way. It let’s you do it in VB.Net, C#, regular SQL or entity SQL (eSQL). You can point it at any SQL Server database and it will generate a Linq to SQL model for you amazingly quickly, or you can point it at an existing Linq to SQL model or an entity framework model. You can add references to your own assemblies and code against them too. It really is an extremely flexible tool.

Anyway, enough plugging. Here is how you can use LinqPad to develop queries for Sage 200 workspaces based on the Sage 200 accounts model…

First you need to point LinqPad at the Sage 200accounts data model. To do this, click Add Connection in the top left of the screen:

Then, in the Data Context drop down, select Custom Linq to SQL DataContext.

In the Path to Custom Assembly browse to the folder where the Sage 200 desktop is installed (usually C:\Program Files\Sage\Sage200) and then to the DomainModels sub-folder. In that folder there are two assemblies. Select Sage.Accounting.DataModel.dll (the other one is the CRM Linq to SQL model).

Next, in the Full Name of Typed DataContext edit box, click Choose and select Sage.Accounting.IM.AccountsModel. This is the type that is the main entry point into the model.

To complete the form, you have to fill in the SQL database connection properties for the database you want to query. Once you have deployed the query in the Sage 200 desktop, the system will automatically select the proper connection string based on the current company that the user has selected. When you’re using LinqPad, you have to provide that yourself. The completed form should look something like this:

When you OK this LinqPad will list all the entities in the model on the left hand side of the screen. You can expand this to see all the properties of each entity. Here is a screenshot showing the some of the properties of SOPOrderReturnLine. Notice that it includes association properties that can get the SOPOrderReturn that the line belongs to, and the collection of SOPAllocationLines belonging to the line. This ability totraverse complex object graphs is a key feature of Linq.

With the model in place the next step is to choose what kind of query you want and to tell LinqPad that you want to use the Sage 200 account model you just added. To pick the model click “Use AccountsModel” in the top right corner of the screen. I usually select “C# statements” for my quey language, but you can also use VB.Net or SQL. Experiment. See what you prefer. Be aware though that only C# Statement will work in the Sage 200 Query Editor.

You are now ready to start developing queries against the Sage 200 accounts model. But before you do, you might want to add references to your assemblies or other assemblies. Remember that Linq queries are just .Net code and LinqPad allows you to import any .Net assembly. If you want to do that, hit F4 and browse to the assembly you want. You can also add namespaces (like a using in C#) or you can just refer to your types using their namespace qualified name (e.g. Sage.Accouting.IM.AccountsModel is the namespace qualified name for AccountsModel).

If you activate the auto-completion feature in LinqPad, you get the familiar intellisense type behaviour as you type:

Here is an example of a query that gets the favourite product for each customer. This post is not about the intricacies of Linq so I won’t explain the structure of the query here. However, the ability of LinqPad to quickly execute the query as it was being built up made it very easy to build the relatively complex nested Select to pull out the favourite item. Notice at the bottom of the query the use of the Dump() extension method. This causes LinqPad to output the results of the query to the output window. Once the query has executed, as well as seeing the results, you can look at final lambda expression that was sent to the Linq to SQL provider, the resulting SQL and the IL code (if you like that kind of thing) for the query. The SQL is pretty useful for performance tuning queries. LinqPad also helpfully puts the query execution time at the bottom of the screen – again useful for performance tuning.

Once you are happy with the query you can cut and paste it into the Sage 200 query editor. But there are a couple of small differences between the query editor and LinqPad that mean you have to make a some minor changes before the query editor will run the query.

First, LinqPad infers the data context, so you can type things like var q = SLCustomerAccounts. The query editor does not do that so you have to explicitly prefix the SLCustomerAccounts property with the context lie this var q = cxt.SLCustomerAccounts.

Second, to get the output form the query in LinqPad you use Dump(). In the query editor, you return the IQueryable that you want to execute instead.

After putting the customer favourites query into the query editor and making these adjustments, you can preview the results:

LinqPad has a load more features that will  increase your productivity and reduce your stress when developing Linq queries for Sage 200 workspaces. Hopefully this post has given you a good flavour of the tool and why we like it some much in our team!

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!

What is a workspace?
A Sage 200 workspace is a linked collection of content parts that display data drawn from any sources that are collaborating with your Sage 200 system.

What problems do workspaces solve?

• Workspaces help you to get the information you need out of the system in the form that you want it
• They let you take action on the data you can see
• They can present data in interesting ways
• They can use data from many different data sources – not just the Sage 200 suite databases

Here’s a short video showing a typical workspace in action: