OK a brief introduction to triggers and more importantly how I used them to overcome a formset limitation.

Triggers are events that get ‘triggered’ whenever a record in a database is updated or a new record is added to a table. From a Sage perspective it gives us control over changes made to the data and allows us control over what we do with that data.

I am currently doing a job where the only way we could deliver the required functionality was by using a database trigger. The client wanted to capture a piece of information whilst raising requisitions and have that same information available via a purchase order enquiry. The first problem was that there were no spare fields in the requisition table that could be used and using the formset editor no way of adding fields as they were all in use. What we did is to use the ‘Location Code’ field on the requisition entry screen. Now this field is actually stored in an extension table (poreqx1m) and validates the entry in this field against polocsm. The trigger was actually used to write the value from the poreqx1m table into the PO header table.

We have mentioned customer analytics before briefly. Still the theory is all well and good but what about actually mining the data and doing something with it. This tool has been under development for about a year and is being fine tuned. It works with any Sage product from Instant right up to Line 500. The basis being it scores and segments your customers to allow you to create targetted offers. The way you do this is to take a set of metrics and measure each customer against these metrics and then split the customers into groups of 10 or 25% (called deciles or quartiles).

This shows the ‘revenue decile’ (customers who have spent the most)

Then if we want to target customers in the top decile we can drill down from the chart and get those customers.

These customers could be either mail-merged or bulk emailed with a specific offer. You will also notice in the data we have the total spend the customer has made with us, the average order value and the number of days since they last ordered (from when this extract was run).

Do you want to determine just who your best customers are? The ones that buy most often or spend the most? You see the key to this information is in your accounting system. It is a commonly held belief that a customer relationship management system will generate a return on investment (ROI). Fact is CRM systems for ’service oriented’ companies can be useful. However, if you truly want to increase your profits and market your product or service in the most cost-effective way you need to segment your customer base on their previous buying history.

Your accounts system has all the information you need! And it doesn’t matter if you are a start-up running an entry level accounts package like Sage Instant or a very large enterprise running Line 500 or SAP. If you want to extract information for the purposes of segmenting and marketing to your customers then you need to be mining data from your accounting system.

What is normalisation and why should you care? Normalisation is the removal of duplicate data across tables in database. You wouldn’t store a customers complete details against each purchase order, you would store the customer details in a separate table and link your purchase order table to it via a ‘key’. Now the Sage Line 500 database (schema), to be blunt, is dreadful. It doesn’t follow any conventions, has poor normalisation and has virtually no business logic (referential integrity) in the database. In it’s defence its due mainly to the legacy of it being a product that has existed in one guise or another for many many years.

Why should you care, Line 500 works fine right? Sure it does but make sure if you have any enhancement work carried out your are not paying the supplier for their learning curve of the database. I guarantee it will be like no other database they will have worked with and no formal documentation exists which makes things harder! I’ve worked with many bespoke providers during Line 500 implementations/migrations/enhancements and time and time get asked, “What table is so and so data stored in?”

I mentioned in my last post about SQL Server reporting services (SSRS) and how you really must consider it not just as an enhancement to Sage reporting but a complete replacement. I have produced many reports that provide detailed cost centre, project costing and time booking reports the likes of which do not even exist within Line 500.

Another really great feature of SSRS is report subscriptions. Any report user can setup schedules to run reports at a certain time and have them delivered via email. This is certainly useful if you want to reduce the overhead on using the Sage database during the working day so you can get your reports delivered out of normal business hours and ready and waiting when you arrive for work!

I know that content is king but if you want to see just how much more presentable an SSRS report is compared to a Sage report take a look at this Stock Analysis Report and run the one thats in Sage and notice the difference.

Do you want to easily segment your existing customer base? How about by how often your customers buy, how recently they last bought and how much they spend? This is the essence of ‘Customer Analytics’. If you could provide highly targetted offers into existing your customer base what sort of reponse do you think you would acheive? 1%, 2% 5%?

So a common thing I get asked to do is re-coding of supplier codes in the accounts payable ledger, account codes in the general ledger, you get the idea. In fact this is easier to do with the Sage database than alot of systems because it does not enforce any business rules (referential integrity) in the database.

However the extracting, the transforming of data and then loading it back in again can be time consuming. That’s why I use the little tool which automates this and more importantly allows users with no SQL knowledge to do re-coding.

…..Click here to read more

Are you still using Sage to do all your reporting? Finding it tedious to get the information on the format that you and your users want? Then STOP! Whilst the reports deliver what they say on the tin the format is something akin to old character based UNIX and DOS systems and you have no way of exporting to say Excel to do further analysis.

The answer? Simple, use Microsoft SQL Server Reporting Services. You probably already have it as it was bundled with SQL Server 2000. You get all the tools you need to create and more importantly, deploy with no licensing issues, reports that run in standard Internet Browser and that can be exported to Excel and adobe acrobat format.

So Sage have a new product that has just been announced; Sage Line 1000. Actually it isn’t a new product at all, Line 1000 is simply a ‘bundling’ of two existing products, Sage Line 500 and Sage CRM MME. I say ‘bundled’ because (at the moment) they are two standalone products which while can sit on the same database and both can be run via the web thats where the links end. That said I’m sure this situation will change or you could get a competent development team to build in links as you need them.

Sage CRM MME is actually a very good product. I was involved with this in it’s earlier days when it was AccPac CRM and it is a very capable CRM that is visually slick and feature rich. It offers shared diaries, campaign management, workflows (called waves as I remember) and mail merging.