Cancer and computers

Watching the BBC Horizon program on cancer and modern treatments, what struck me the most was that these new innovations have come about due to the power and application of modern computers. In being able to:
a) display in 3D the molecular structure of specific parts of human DNA, and drugs;
b) design at a molecular level the structure of "new" drugs and to see their fit into defective DNA structures;
c) the ability to control large robotic machines in real time in order to assist a surgeon perform an operation or to deliver radiation to a highly specific target area.

Each application is no doubt very expensive to deliver in these early pioneer days, but will surely reduce in cost as they become more commonplace.

This modern computing power has been available for sometime, but in the past has mainly been applied to military and military style applications (like modelling nuclear explosions, designing fighter planes etc.). Or to government sponsored activities like weather forecasting, communication surveillance, etc.

Now with its application in medicine it should not be long before we all benefit from improved treatments an new drugs designed and developed in a much faster timescale than previously possible become available.

A brighter future is surely empowered by the digital world.

List to file to aid debugging - forget Visual Programs

With Visual this and Visual that in the programming world it is true that people get out of the habits of doing things the "old fashioned" way! This is especially true when debugging a program. The Visual world gives you info. straight to the screen and makes it quick and easy to debug.
That is until the event you are trying to debug in something like Activate.Window when the intrusion into the program causes the event to effectively keep firing.
Or the data needed is so vast that to do it to screen achieves nothing.
It was this last situation that caused me to go back to the old ways of doing things - namely generating output to file. I had to walk through a large XML Tree structure (80,000+ nodes) and examine every node and attribute.
I found the VFP STRTOFILE() command excellent for this, as I could open the output file with a general text editor and use it's features to "find" the errant nodes and see what I was doing wrong.
The old ways never die - just fade away!

HTML code and MS Word

A useful feature that I only recently became aware of is that if you use the MS Word menu option Insert | File to import a file with a HTM/HTML file extension and it contains tagged data. MS Word renders the data in HTML format. It also does not have to have lead in Head and Body tags.

This facility can be used for outputing reports from a database application into a HTML file. Then the HTML can be inserted into MS Word for rendering and printing without having to use a browser. Plus, the data can be edited by the user - which is not possible when rendered by a browser.

Internet Telephony

When a PC is connected via broadband to the internet all day, a very cheap way of making telephone calls is via services like Skype. Not only can voice and video calls be made for free to fellow users. Skype even offers the facility to share the PC's screen, so that software problems, features, diagrams, etc. can be shared between the callers. All at no cost.
So for an SME with a large telephone bill, Internet Telephone is a way to significantly reduce those costs and improve the call experience with video and product demo's etc.

Getting rid of the Spreadsheet

Many senior finance managers that I speak to, want to get rid of the Spreadsheet from within the Financial Reporting process. Typically, from within the "Final Mile" of financial reporting.

As I detailed in my June 2011 post HTML based management reports this is entirely possible with modern systems. In fact such systems offer distinct advantages over old manual processes. Namely;
a) non-finance managers can become involved in the drafting process, writing supporting narrative, removing the reliance on finance personnel;
b) the report drafting and changing process, becomes part of the IT system;
c) none finance based numbers and quantities can be incorporated into the HTML reports based upon underlying system metrics - No. of sales orders, etc.;
d) all data and measures are drawn from a single consistent source;
e) reporting and production efficiencies can be obtained, with quicker turn-around times;
f) an audit trail of changes to numbers and narrative is automatically maintained by the system, allowing for trace back should it be required;
g) as mentioned in my post Microsoft Office Web Component graphics can be easily incorporated into the HTML reports, bringing such reports "alive";
h) being HTML based, distribution and reading of the resulting reports is readily achieved.

Overall, senior management can produce and consume more meaningful reports in a shorter time span, thereby improving overall operating results. Even the dreaded XBRL tags can be incorporated into such reports for external consumption!

Euro struggles on...

After all the rhetoric of the last few days - on all sides of the water - whether ocean or channel.

Does anyone believe that the Euro is any nearer to being rescued?

Thought not.

Democracy no longer seems important to Europe.
So what or whom does the election fixers/riggers in the rest of the world have to fear?
Sadly no one!!
So easy for them to turn around and point the finger at Europe - which can no longer be consider a democracy! Where everything is being impossed by non-elected technocrates.

What now for the Euro?

After yesterday’s summit in Brussels it is obvious that European politicians do NOT have an answer to the troubles of the Euro! Or, if they know the answer, it is not one that they are prepared to endorse. In my limited knowledge of currencies and the problems of the Euro, I believe its problems have to be addressed from 2 directions at the same time
1) impose austerity on the weak economies of the south
2) let the ECB print money to cause inflation and thereby reduce the value of the Euro in international markets

Neither one will work on its own. Both together and it stands a chance of saving the Euro. With this 2 directional approach both weak and strong countries take the pain. What we have at the moment is the imposition of the strong on the weak. With the weak taking all the punishment. (Merkels and Sarkozys attitude to Cameron, reminds one off the school bullies in the playground, not of astute politicians.) If this imbalance continues, then it is a slow drift to the inevitable break up of the Euro and ultimately the EU. For the markets will not relent in pursuing the weaknesses perceived in the Euro and its sovereign debt exposure.

As time ticks by, people in the weaker economies of Europe will become more and more restless and frustrated at the imposed austerity and lack of democracy. Military junta’s are not unknown in these countries. At some point a country will reach the tipping point when it will be less painful to leave the Euro than to remain in it. If the countries elected government doesn’t move at that point, I fear that a strong cabal of military leaders will take matters into their own hands.

When will the politicians of Germany and France ‘face the facts’ and do what everybody else can see and do what is bleeding obvious.

Demise of the Euro?

With the likelyhood of the breakup of the euro within the next months or even days. What will this mean for your business systems?
What will it mean for your business?

First we need to ascertain what the new currencies will be:
Northern Euro and Southern Euro?
Euro with New Greek drachma and New Italian Lira? May be even a Spainish and Portugese one thrown in.
Who can say at this stage!

What is certain is that new currency codes will be required at short notice along with conversations rates between the new currencies.
Will your business systems be able to cope?
When the Euro was introduced it was a slow process over several years. A breakup, should it come, will be without warning and will have to be accommodated in a few hours.

What you could be doing now - is
a) reducing your exposure to the Euro,
b) converting Euros bank balances into another currency,
c) invoicing in another currency in place of the euro,
d) trying to get suppliers to invoice in another currency,
e) investigating how to handle the new currencies and conversions of any assets and liabilites to new currencies.

Not all of this is possible as it will depend upon your customers and suppliers. Plus, exchange rates being what they are you can never be sure that a loss now want be much bigger if and when the "euro hits the fan"! Or, in fact become a profit due to market uncertainty being removed.

Therefore, I feel businesses need to be thinking about a strategy for all this uncertainty. Remember a risk managed, is far better than a risk ignored!

Report totals in the header of a data grouping

Sometimes reports call for a total of a field in the detail records. This is easy to accomplish when the total can be given at the end of the Details records. Within VFP you create a data grouping based on a specific field. Add a reporting field to the Group Footer band and set its attributes to Sum the required field. Nothing new or difficult here.

However, what about when the total of the Detail records is required in the Header Band - before the Detail records have been encountered/printed. Not so easy.

One method would be to add an additonal field to the reporting cursor to indicate a "Total Record" and at the same time via an SQL Select statement produce total values based on the group field and add these records into the reporting cursor. Then report these total records in the Header Band of the group within the report. Doing this would of course require the program to be changed to perform these tasks. Thats all well and good when you know in advance of the reporting requirement to give totals in the header band.
But how do you accomplish this when the requirement is not foreseen?
In other words, you want to be able to amend an existing report or create a variant of it without changing the routines that create the reporting cursor.

First we need a function to perform an SQL Select and summing, then to return its value. Three paramters are used
1) the field to be summed
2) the name of the field on which the Where clause or grouping is to be based
3) the value of the group whose total is required.

Hence
*********************************
FUNCTION gettotalfromcursor
LPARAMETERS p1, p2, p3
* p1 is field name to be totalled as char
* p2 is field name of group as char
* p3 is value of group field for which total is required
* report cursor is always rc - could be an additional parameter
LOCAL lrtn, ls1
DIMENSION zzztot(1)
m.ls1 = "SELECT SUM(" + p1 + ") as zzz FROM rc WHERE " + p2 + ' ='

DO case && deal with each type of variable that can be passed
CASE VARTYPE(p3) = CS_L
m.ls1 = m.ls1 + iif(p3, " .T. ", " .F. " )

CASE VARTYPE(p3) = CS_D
m.ls1 = m.ls1 + "{^" + dasheddate(dtos(p3)) + "}" && date into string format

CASE VARTYPE(p3) = CS_I
m.ls1 = m.ls1 + STR(p3, 10)

CASE VARTYPE(p3) = CS_Y
m.ls1 = m.ls1 + STR(p3, 12, 2)

CASE VARTYPE(p3) = CS_N
m.ls1 = m.ls1 + STR(p3, 16, 6)

CASE VARTYPE(p3) = CS_T
m.ls1 = m.ls1 + convertdatetimetochar(p3) && date time into string format

OTHERWISE && CS_C and others
m.ls1 = m.ls1 + '"' + p3 + '"'

ENDCASE
m.ls1 =m.ls1 + ' INTO ARRAY zzztot'

* perform the select statement
&ls1

if _tally > 0 then
m.lrtn = zzztot(1)
ELSE
m.lrtn = 9999999.99 &&default return value when no records selected
ENDIF

RELEASE zzztot && the array is no longer required

RETURN m.lrtn &&return total
*********************************

Now we need to look at the report structure.
A Report Variable is required - RptCalcdTotal
Whose "Value to Store" and "Initial value" are 0 (zero) and "Reset value based on" is set to the group field.

A reporting field is added to the Group Header Band to report this variable.

Now right click the Group Header Band and select Properties.
Now set the "Run Expression - On entry" to
setvarval(@RptCalcdTotal, gettotalfromcursor("tamt", "tbno", rc.tbno))

setvarval just assigns the value for the second parameter to the variable passed in the first parameter - it is similar to the _vfp.SetVar(fielname, value) function
TAMT is the field to be totalled
TBNO is the data grouping field
rc.tbno is the current value of the grouping field whose records are to be totalled.

That's it done.

A variant on the function would be to handle multiply fields to be used in the Where condition of the Select statement.

Under VFP Version 9 I have recently discovered another way of doing this without having pre-defined the Function to determine the Sum amount. This is how:
1) for the defined report add a new report variable (say HBTot) under menu Report Variables. Set the "Value to store" and "Initial Value" to zero.
2) Within the header band add a new field and on the field properties
General tab set the expression to HBtot. Under Format set the numeric format required. (These 2 steps are as normal for adding a report variable.)
3) Right click the header band and select Properties.
4) Click Other tab on the Group Header Band Properties dialog. Then click Edit Settings under Run-time Extensions.
5) On the resulting dialog window put BeforeBand into Execute when: attribute.
6) Click the Code Zoom button to open the Run-time extension script window.
7) Put in the following code to summate your Detail amount field via a Select statement changing the Select statement as required.
LPARAMETERS toFX, toListener, tcMethodToken,;
tP1, tP2, tP3, tP4, tP5, tP6, tP7, tP8, tP9, tP10, tP11, tP12

* this assumes a 2 pass process in creating the report
if toListener.currentpass = 1 then
local ls1
DIMENSION aaattt(1)
m.ls1 = rc.dtype
select sum(amt) as tttamt from rc where dtype = m.ls1 into array aaattt
_vfp.setvar("HBTot", aaattt(1))
release aaattt
endif
8) Close all open dialogs click OK/Save

That's it done - the total of Detail records in the Group Header Band.

HTML based Management reports

Most management reports are prepared using the ubiquitous spreadsheet. Primarily because accountants are familiar with the software and it gives flexibilty in layout and computation. Most software packages can download data to a spreadsheet or be SQL queried by them, so starting data for each month can be easily obtained. Then the commentary and insights by the various managers is added. The spreadsheet is then ready for distribution as is, or "printed" to a PDF file for distribution.

Few have considered HTML as a delivery vehicle. Yet for ease of use by a consuming manager it is possibly one of the best. As every PC and mobile device these days can read an HTML page. Not every PC or mobile device has the appropriate software to read a spreadsheet.

The problem with HTML is that "programming" knowledge is required to format a page or series of pages. Plus, how do you get the data into the HTML page?

Considering that the layout of most management reports does not change once established, putting a bit of effort into programming an HTML layout can be cost effective. Provided data can be drawn directly from the accounting and business management system. At RCL our accounting and business software packages provide this ability to draw data out of the system into an HTML document via SQL Select commands and a TableLookup function. What is more that data is not retricted to traditional accounting data; like Balance Sheet and P&L numbers; but can include any element held within the database. Possibles are: stock quantity levels, number of sales orders taken during a period, statistical departmental headcounts and vehicle numbers, even monthly management commentary can be entered, held and reported.

The HTML template document is itself held within the system. The SQL Selects and Table Lookups are evaluated prior to an HTML report being presented in an IE browser.

Having constructed an HTML management report template, the preparation of a "final" monthly report is just a question of awaiting for the monthly data to be complete and the management commentary being entered based upon "early drafts". The "early drafts" like the "final report" are just the HTML being "presented" in a browser. Being held on the system, these HTML reports are visible to all those given access. So "information control" by accountants holding "the cards close to their chests" becomes a thing of the past. As does the man hours consumed is preparing the reports.

Therefore management reports can become much more timely and relevant to the company. The approved final report when "presented" in a browser can be saved to a central server for all to subsequnetly access where ever they are in the world. Access to the underlying system not being required any more.

As my previous blog states, graphs can even be incorporated into a report to accommodate those "numerically challenged" managers that like pretty pictures!

Microsoft Office Web Component

Recently I came across the software tool Microsoft Office Web Components.
Basically it allows spreadsheets, pivot tables and graphs to be placed in HTML pages.
It is the last element - Graphs; that I will consider here.

Recognising the power of graphs in a management report, it has previously been possible to prepare a graph; say in Excel; and save it as an image file to be placed in a HTML page. But obviously, this takes time and requires co-ordination.

With Microsoft Office Web Component (OWC) and using your prefered script language - VBScript, JavaScript, etc. web pages can be built to provide management reports that automatically draw data from a background database and at the same time present a graph using that data within the HTML page. Thus, making the process as automated as possible.
With Acceptum Business Software for example it is possible to prepare a HTML page that provides the outline of a management report. Built into the HTML are SQL Select statements and Table Look Up commands that are evaluated prior to the HTML being sent to Internet Explorer. Using OWC and VBScript, graphs can be placed in the management report giving the data more vibrance. Having got the data into a temporary HTML file, it can be saved to a central folder on a server for all managers to review. This way the database is only queried once and provides a "frozen" image to which all can refer.

So lets take a look at an example web page (brackets changed to get it to display).

[BODY topMargin=1]
[script language="VBScript"]

Dim oChart1, categories, bud_get, act_ual, var_iance, c1 '***************************************************************
Sub Window_Onload
' Clear the contents of the chart workspace.
' This removes any old charts that may already exist
' and leaves the chart workspace
' completely empty. One chart object is then added.
ChartSpace1.Clear
ChartSpace1.Charts.Add
Set c1 = ChartSpace1.Constants

' Add a series to the chart.
ChartSpace1.Charts(0).SeriesCollection.Add
ChartSpace1.Charts(0).SeriesCollection.Add
ChartSpace1.Charts(0).SeriesCollection.Add
' Set the series caption (the text that appears in the legend).
ChartSpace1.Charts(0).SeriesCollection(0).Caption = "Revenue Year to Date"
categories = "Actual"

' Set the categories for the first series (this collection is zero-based)
ChartSpace1.Charts(0).SeriesCollection(0).SetData c1.chDimCategories, c1.chDataLiteral, categories

act_ual = CLng("&$TABLELOOKUP("results", "SCORECARD", "company = m.gccomp and id = 'A005' ", "")$&")
ChartSpace1.Charts(0).SeriesCollection(0).Caption = categories
ChartSpace1.Charts(0).SeriesCollection(0).SetData c1.chDimCategories, c1.chDataLiteral, categories
ChartSpace1.Charts(0).SeriesCollection(0).SetData c1.chDimValues, c1.chDataLiteral, act_ual

categories = "Budget"
bud_get = CLng("&$TABLELOOKUP("results", "SCORECARD", "company = m.gccomp and id = 'A001' ", "")$&")
ChartSpace1.Charts(0).SeriesCollection(1).Caption = categories
ChartSpace1.Charts(0).SeriesCollection(1).SetData c1.chDimCategories, c1.chDataLiteral, categories
ChartSpace1.Charts(0).SeriesCollection(1).SetData c1.chDimValues, c1.chDataLiteral, bud_get

categories = "Variance"
var_iance = CLng("&$TABLELOOKUP("results", "SCORECARD", "company = m.gccomp and id = 'A009' ", "")$&")
ChartSpace1.Charts(0).SeriesCollection(2).Caption = categories
ChartSpace1.Charts(0).SeriesCollection(2).SetData c1.chDimCategories, c1.chDataLiteral, categories
ChartSpace1.Charts(0).SeriesCollection(2).SetData c1.chDimValues, c1.chDataLiteral, var_iance

' Format the chart to have a legend
ChartSpace1.Charts(0).HasLegend = True
ChartSpace1.Charts(0).HasTitle = True
ChartSpace1.Charts(0).Legend.Position = c1.chLegendPositionTop
ChartSpace1.Charts(0).Title.Caption = "Revenue Year to Date"
'ChartSpace1.Charts(0).Axes(c1.chAxisPositionLeft).MajorUnit = 1000
set c1 = nothing

End Sub
'**************************************************
Sub Window_OnUnload
'Executed when window is closed.
Set oChart1 = nothing
Set chartspace1 = nothing
End Sub
'*************************************************
[/script]

[H2]Year To Date upto Period 6[/H2]
Year to Date Revenue is £ &$TABLELOOKUP("results", "SCORECARD", "company = m.gccomp and id = 'A005' ", "")$& [br /]
compared to Budget of £ &$TABLELOOKUP("results", "SCORECARD", "company = m.gccomp and id = 'A001' ", "")$& [br /]
giving a Variance of £ &$TABLELOOKUP("results", "SCORECARD", "company = m.gccomp and id = 'A009' ", "")$& .[br /]

[object id='ChartSpace1' classid=CLSID:0002E546-0000-0000-C000-000000000046 style="width:200;height:200"][/object]
[br /]
[br /]
[H3]Explanation - Revenue Amount[/H3]
&$TABLELOOKUP("notes", "SCORECARD", "company = m.gccomp and id = 'A005' ", "")$&

[H3]Explanation - Budget Amount[/H3]
&$TABLELOOKUP("notes", "SCORECARD", "company = m.gccomp and id = 'A001' ", "")$&

[H3]Explanation - Variance Amount[/H3]
&$TABLELOOKUP("notes", "SCORECARD", "company = m.gccomp and id = 'A009' ", "")$&
[br /]
[br /]
R. Bean - Accountant[br /]
XYZ Ltd. [br /]
[HR class="pwl cblue" align=left SIZE=1]

[BR][/BODY]

Here is an image of the Html page. It is a basic report, a full management report would of course be more complex. But it gives the general idea of what can be achieved. The classid=CLSID:0002E546-0000-0000-C000-000000000046 clause of the object statement may need to vary depending on the installed version of MS OWC.

Saving all your data?

Having just got through a forced and unplanned re-install of the Operating System on my PC, it brings home to me the importance of having good backups of ALL the data required to get a PC back into a workable condition.

Taking backups of application data for a company should be standard practise, but what about all the other marginal elements that a user relies upon - are they being saved in any format. Here are a few of the things I am thinking about:-
- e-mail accounts, passwords and log-on details to e-mail servers
- e-mail folder structures
- e-mail anti-spam rules
- the e-mails themselves within the application
- web browser Favourites, cookies and passwords
- options and preferences for applications that don't hold databases etc.
- application installs taken from the web
- upgrades and new releases to applications
- registry entries
- file and folder listings
- etc.

Applications like Outlook, IE and RegEdit have built in Export/Import menu options to save/re-establish settings and user configurations. I strongly suggest that you start using them (if not already doing so) and putting the resulting exported files into a location that is backed up and archieved off of the PC.

When installing a new application or an upgrade, don't run the program from the web but save the file to a suitable folder (C:\Downloads\AppName) and include this folder is your backup/archieve.

Luckily I have been doing this, so recover went well - even so it took 2 days. The only problem I encountered was that I rely on a bit of software that MS no longer supports, thus one of the first upgrades/patches was no longer available from its website. Had to use Google to locate another source before being able to continue. Now I have added it to my backup data.

One of the things that helped the most during the recovery was a list of folders and their files. This I had started creating on a regular basis; for entirely different reasons; a couple of months back using VBScript and the File System Object. The list help me identify missing applications, folders, files and versions.

I strong recommend ensuring that if you encounter a major disk/PC problem you have the necessary information and backups to help recover from it with as little hasle as possible.
Remember, PCs are cheap when compared to the cost of re-establishing all the data they hold!

Letting a user know the computer is still working!

How to indicate to a computer user that a long or extended process is still working?

I have used Thermometers - but unless the process is really long, they just do not seem appropriate. I have tended to use Message Box's and Wait Windows, but again both of these have weaknesses. When a "Wait Window" stops appearing - that in itself does not indicate that a process is over - it could still be Looping! Message Box's call for the user to click buttons - this is not always appropriate. They might want to step away from their desk and processing will stop at the Message Box.

Then it suddenly occurred to me; why not get the command button to change the title and colour of the enclosing window! In VFP this can be done with a new class based upon the Command Button class. Only potential problem - can you call the default code for a class event; the Click event; more than once from within the events code of the object when placed on a form. Only one way to find out for sure - give it a go.

So created a New class based on the Commandbutton - called SV_CMDProcess.
Added 2 new properties to it
- SHOC (string - Hold Original form caption)
- NHOBC (numeric - hold original Background color)

Put the following code into the click event
LPARAMETERS pcallno
DO case
CASE m.pcallno = 1
this.shoc = thisform.Caption && save opening colour and caption
this.nhobc = thisform.backcolor
thisform.Caption = "P R O C E S S I N G" + Str(m.pcallno, 6)
thisform.backcolor = 255 &&RED

CASE m.pcallno > 1 and m.pcallno < 999999
IF (m.pcallno + 25) >= 16777215 THEN
thisform.backcolor = 255 && exceeding max value for white
ELSE
thisform.backcolor = thisform.backcolor + 25 && increment by arbitory value
ENDIF
thisform.Caption = "P R O C E S S I N G" + Str(m.pcallno, 6)

OTHERWISE
thisform.Caption = this.shoc && return colour and caption to original values
thisform.backcolor = this.nhobc
ENDCASE
thisform.refresh


Now when a form needs a command button to perform a process that will take 30secs to n minutes, I use this class and within its click event I can code the following:

dodefault(1) && initiate process

* process some data .....
FOR m.ix = 1 to m.Loopcount
dodefault(m.ix+1) && change colour/caption whilst processing data
ENDFOR

* end the process
dodefault(999999) && restore original colour and caption

During the processing the window shows the title "P R O C E S S I N G" followed by the number passed and the background colour starts as RED and then changes colour. This gives a visual indication to the user that processing is happening, but does not call for any action (as in a Message Box).

When finished the original caption and colour is restored.

Used alone or with WAIT Windows it provides an alternative way of communicating with the user whilst the computer is doing its work.

Being Christmas Eve as I write this - a Merry Christmas and New Year to all readers.

Flexible systems

With yet another change in VAT Rate looming at the end of the year, the importance of using a flexible accounting/ERP system that allows for the creation of new VAT codes, rates, etc. is brought to the fore.
Many 'older' systems provide little support in making such changes and allowing 'old rate' invoices and be processed at the same time as 'new rate' invoices. With the consequence that considerable effort is required around the time of actual rate change. Invoices being either to customers or from suppliers.

At least this time around many firms will have faced the problem in recent times and should be able to cope without major costs/holdups.

Inflexible systems are those that have codes and rates 'hard coded' into the program so that version upgrades are needed to meet new requirements. Where as flexible systems allow users to create and maintain entries to control it. This not only concerns tax rates, it covers all aspects of program control.
Of course with more sophisticated systems, the day to day requirements placed upon users is increased, as they need to understand more about the system and the interaction between various settings. However, this drawback is more than compensated for by the ease of changing the system when required.

My recommendation is to always look for and install the most flexible system you can find. That way you can be independent of the software supplier and still be comfortable in being able to meet the requirements of changing legislation and the overall business environment.

Planning for not being here! Death in other words

A recent family loss has suddenly brought home to me that very few of us effectively plan for not being here!

Lets assume you are an average Joe - with a partner and 2.n kids, may be a parent is still alive.

Now suddenly you are gone! You've died.
Your family are in a state of shock, bewilderment and bereaved.
Hopefully you have left a will.
But that is only a start.
Your bank accounts and credit cards are frozen.
Pension receipts will be stopped! So in the near term, money will be short.
Cars are not legally useable - in the short term - as they are in your name along with the insurance - which has now become invalid.

Yet, at the same time large funeral bills need to be incurred. Along with lots of letters to be written to organisations that they've never had to deal with before.

Do they know or know how to get to - passwords, account numbers, pin numbers, memorable words, mail and email addresses, telephone numbers, etc.
Who to contact for pensions, life insurance, mortgages, investments, ISA's, PEP's, etc.

Do they have access to joint accounts and their details?
Things that up to now you have always looked after.

Stop and think for a minute of all the things to be done at such a time.
It does not take long to come up with a long list.

How can you plan for this? In in order to reduce its impact on your family.
Start with a basic list of bank account details - codes, numbers, addresses.
During the year, add investments and pension details, as dividends are received or premiums paid out.
Give directions/details of the normal physical locations of items like keys, statements, policies, deeds, certificates. Include every day things as well as those deeds you put in the bottom of the box 25 years ago and have not been opened since!

All this sounds fairly easy to do - and it is when done in advance - BY YOU!

If you have complex affairs - investments, mortgages, share holdings, trusts etc., give a brief description of how it all hangs together and inter-relates and why you have structured it the way it is. You may have saved thousands in a complex structure. But all the savings could be eliminated by high legal fees trying to sort out and understand the situation you have created.

In to-days world the best place to create and hold this information is on a computer with Word, Excel or similar. Yet will your family know which file and directory path to access to get to all this information. So store a printed copy in a location that all family members know about.

As accounts are closed or become redundant, don't remove them from the list but note them as closed/redundant and give the date. Remember, Inheritance tax can go back 7 years.

Still think your family could handle the situation that your sudden departure has created? It may not be nice to thing of dieing, but do you really want to leave a lot of problems for your loved ones?

Start building that list and if you have spare funds put some in joint/single bank accounts that your partner/loved ones can get to when you have gone in order to tide them through those first weeks.

VFP Printing B/Fwd and C/Fwd page totals

These days it is not often that users want to print great swathes of paper of - say - all the journals posted to a ledger or even to a single ledger account.
On-line realtime systems have greatly reduced the need for such printouts.

However, I recently received a request to put Carry Forward (C/Fwd) and Brought Forward (B/Fwd) totals into a report when the number of journals to an individual account went over N+ pages.

Easy I thought. Just sum the detail amounts and put a B/Fwd in the page header and a C/Fwd in the page footer to print the interim totals. Only print the C/Fwd and B/Fwds when the interim total is not Zero by setting the 'Print When' condition to MYCBFWDTOT <> 0

Worked fine - until
a) an account ended near the bottom of the page.
b) at a page throw the account just happened to total zero

For a) an account total was given. This should have cleared the the C/Fwd and B/Fwd's to zero. Unfortunately this clearing back to zero does not occur until the new account group is encountered on the next page - so the C/Fwd and B/Fwd showed the prior accounts total.

For b) no C/Fwd or B/Fwd totals were given because MYCBFWDTOT happened to be zero at the point of a page throw.

Thus a little research and a lot of testing to resolve these issues arose.
It was resolved using the VFP9 facility to run an expression when a print band (page header/footer, group header/footer, detail) is entered or exited.

First I declared a report variable MYCBFWDPRINT and set it to .F. (False)

Then the Detail band Entry Expression was set to
_VFP.SetVar('MYCBFWDPRINT', .T.)

Which sets the report variable to true when first printing a detail line. (A function call is required here as a direct Assignment Expression is not permitted.)

Then the Account Group Footer band Entry Expression was set to
_VFP.SetVar('MYCBFWDPRINT', .F.)
Which sets the report variable to false when entering the account totals print band.

Then finally in the print controls for the C/Fwd and B/Fwd amounts the Print When condition was set to just -
MYCBFWDPRINT

This solved all the problems prevously encountered by controlling printing on the value of MYCBFWDPRINT and not the MYCBFWDTOT variable.

Such Entry and Exit expressions can be used by a programmer to achieve more user friendly and flexible report layouts.

Are you backing up your data?

Had a scare this week.

Computer failed to boot – screen completely blank!

Feared that the hard drive had got screwed up.
Looked at the option of doing a data recovery from the drive and installing a new hard drive in the machine. Staggered at the current prices – any thing from £450+

Immediately saw that data – or getting it back is more costly than a basic PC.

Eventually got the PC to boot and immediately took a backup of all my data.
Used the CD Read/Writer to create a couple of CD’s – always kept putting it off before. Now I shall do it at least once a month -New Years resolution.

At 75p a disk – that’s much cheaper than £450.

Using VOIP

Still using a standard telephone whilst sitting at your computer?

One of my kids started at Uni. recently and in the Halls of Residence they have internet access and a Freewire telephone.

Freewire is basically a VOIP telephone service that allows calls to be made over the internet free of charge. We've made calls lasting over an hour at absolutely NO cost.

So if you have remote workers or clients/contacts that you call daily, look to installing VOIP at both ends and use you PC's and internet connection to reduce your phone bill.

We have found the sound and transmission quality to be just like a normal phone. No delay and not voice distortion.

So give it a go - what have you got to lose - only a phone bill!

SaaS - Software as a Service

Recently I was introduced by someone to the term SaaS or Software as a Service. It was presented as a "revolution" in the means of providing software to SME's.

Being the eternal cynic my views are
- Yes it is a new term;
- No it is not a new idea!

SaaS is basically the 1980's concept of timesharing provided by the likes of GEASCO (or was it GSISCO) during the late 70's and early 80's, but revamped to use the internet/web. The web replacing the dedicated BT telephone lines that timesharing used. Which is why in the 70's/80's it was a preserve of larger organisations who could carry the cost of the dedicated lines. With the advent of cheaper mainframes. midi's and PC's from the late 80's onwards, timesharing faded away.

Yes SaaS does allow software to be used without the headache of maintaining a system/server and it can be an economic means of using such software. But there are hidden dangers to this approach. Dangers that are very rarely addressed by the salesperson for the SaaS provider and often ignored or overlooked by the company using the SaaS system.

These dangers can be highlighted by answering the following questions:
- what happens if you loose internet connection for an extended period of time?
- what happens if the SaaS is continually attacked by a Denial of Service?
- what happens if the SaaS provider goes belly up?
- what happens if a rogue/disgruntled employee has a whole night to corrupt your data before you can contact the SaaS provider and get them barred?

The answer is simple - you cannot get to or rely on your company data!
How will this effect your company?
For most companies it would be a disaster that could at the extreme force you to go under.

Are such extended periods without access to your data likely?
Well the IT news of to-day is about the internet lines to India and the Middle East being severally disrupted due to a broken cable carrying internet traffic between Europe and Asia.

This is the crux of the problem - such downtimes/disasters cannot be forecast.

It reminds me of a situation that occured at a company I worked for in the mid 80's. It was moving from timeshare to an in-house system, with the new mainframe servers being based in the north of England some 200 miles away. Everything was set up, tested and ready for the switch over. On the morning of the switchover after a couple of hours of being live, a builder in a JCB outside the new server building decided to dig a trench and went right through the comms. cables ripping them out of the ground. It took 2/3 weeks to re-instate the cables delaying the switchover by a month. Luckily for the company it could immediately switch back to the timeshare provider to allow its business to continue. If this builder has done his trick several weeks later the switch back would not have been an option and the company would have suffered untold damage to its reputation and business.

Of course, this is why Disaster Recovery Plans are so important, no matter what size of company you are, having a Disaster Recovery Plan is essential.

Do you as a SaaS user have a Disaster Recovery Plan for your data stored at the SaaS provider? Plans and data that are independent of those provided by the SaaS provider.

With your own PCs/server it is relatively easy to backup and store company data off-site on an regular basis. It is all under you direct physical control. Getting to it or barring access to it is relatively easy.

Does the SaaS allow you to do this for ALL your data?
Not its own backups, but your backups that you can get to independently of the SaaS provider.

And having got the data, how easy will it be to get access to underlying applications in an emergency with little or no notice!

The cost of having to carry out these recovery plans will be fairly high but it will keep you in business. Without your data you will be severely hampered in keeping the business going.

When things are going well, it is easy to forget the problems that can be caused by the loss of a simple object like a telephone line.

My overall view is that many companies are attracted to SaaS because it saves time, money and having to deal with mundane things like data recovery plans. The very things that when the 'sh.. hits the fans' you will need in bucket fulls.

SaaS is fine when it works, but can be a real disaster when it does not!
For me SaaS is a disaster waiting to happen because it gives a false sense of security to the user company. Just because you use SaaS does not mean that you do not need an independent Disaster Recovery Plan for your company data.

How many SME's that currently use SaaS have Disaster Recovery Plans for the data held at the SaaS?

Was SaaS not supposed to remove the need for such things?
Well yes, but as you can see you can't!

K.P.I. - Key Performance Indicators

K.P.I. - Key Performance Indicators are those elements of financial and numeric data that are used to monitor an organisations performance, indicating whether the organisation is on target to meet its goals and ambitions.

However, this financial and numeric data is just part of the story. As business management must focus on the integration and collaboration of people, systems, processes, and information across a business, including its business partners, customers and suppliers, in order to achive the goals and ambitions it has set itself.

ERP systems provide the technology to produce summary sheets of KPIs or Dashboards. But this technology must be seen as the enabler and not the answer to business performance management. Implementing a KPI dashboard without regarding business strategy and the underlying performance management processes is doomed to failure. An appropriate organisational framework and ethos must exist to support the KPI dashboard.

The notion that critical metrics can be captured on a single (simplified) screen and all underperforming activity revealed in an instant is attractive. But the simple delivery often masks the major planning effort required to make a dashboard successful.

In the book Performance Dashboards by Wayne W Eckerson, he suggests a definition for dashboards as, Performance Dashboards let busy executives, managers and staff view the performance of key business metrics at a glance and then move through successive layers of actionable information in a carefully guided manner, so that they get the insight they need to solve problems quickly, efficiently and effectively. By helping business people keep a pulse on their business and chart progress towards meeting strategic and tactical objectives, performance dashboards are becoming powerful agents of organisational change.

Dashboard technology can be seen as a means of enabling management to communicate their goals and aims to all members of staff. Disseminating the strategy and the metrics that are being used to monitor delivery of goals and aims. As such, they bridge the gap between strategic objectives and operational behaviour and over time provide an effective means of aligning an organisation's resources with its strategy. To work effectively, dashboards must contain performance measures that are strictly aligned with strategy in order that there is not a disconnect between what is measured and what is achieved.

It is a mistake to believe KPI dashboards are easy to deliver. As the technical requirements necessary to deliver a successful KPI dashboard must; by their very nature; draw on numerous data sources. Each of these may require extensive definition, mapping and manipulation before they can be used in a repeatable and dependable manner, month after month. Similarly, it takes time to define business logic involving the treatment of data and the conditions giving rise to variances and anomalies.

A dashboard must be considered to be like the tip of an iceberg, with most of the data substance hidden out of view. Creating a KPI dashboard is strategically, operationally and technically challenging. To succeed, an implementation needs to be based on strategy and supported with good I.T. skills.

Small Print Runs for SME’s

A common problem faced by many small companies is to economically produce small quantities of high quality printed material. Here, I mean a quantity of less than 50 with over 16 pages – it could be a technical brochure, training manual, user guide, personnel policies manual, staff manual, etc.

The colour InkJet/Laser printer has helped considerably in achieving this objective. But problems still remain, with the time it takes to physically stack and bind the material in a professional manner and not just putting it into a ring binder of some sort.

Printers normally charge uneconomic prices for doing such work, especially if you are an infrequent customer.

Now a second unrelated problem. It is said ‘everybody has a book in them’. This may be true, but how do you get it published, printed and distributed?

Well in this age of the internet, it has an answer to this last problem – welcome to the age of Personal Publishing websites like www.lulu.com and www.bobsbooks.co.uk (just 2 of the many sites). They will print your book for you, put it on a list of titles and when someone orders will send the printed book to them and what is more you can earn income from it. All very good for all those budding authors.

And here is the answer to our first problem, of how small companies can get professionally printed and bound books at an economic price.

Create an account on your favourite Personal Publishing website, prepare your material and get it into PDF format (that is easy now with free readily available PDF software printers). Then create a project and upload the PDF file and the necessary information, before ordering your copies. No need to worry about distributing it or getting a price mark-up, you will be the only person ordering it.

This method can also help reduce costs, as you only need to order the number of copies you have an immediate requirement for. So in a stroke removing the problem of what to do with out of date printed manuals.

Due to the flexibility of this approach you can easily keep the manual up to date and order the odd copy as and when required.

Is it economic, well compared to the costs of buying top quality paper, ink cartridges/toner, etc. I have found that PP websites come out slightly more expensive. Now add in the cost of labour for time spent minding the printer and binding the material together, then the cost of the PP websites is economic and the finished article quality is much higher.


Two problems solved/resolved by the same solution!

HMRC and XBRL Annual Accounts requirement from 2010

HMRC requirement for XBRL Annual Accounts is seen by software houses and accountants as a means of imposing themselves on small businesses!

Many small businesses where very relieved when audit exemption came about. As it meant that they could finally ditch the auditor, who they saw as performing a non-beneficial service. A cost without benefits. This is clearly seen by the number of small businesses that have now dropped the audit.

Many small business people are not accountants, but that does not mean that they don't prepare their own accounts (with or without computers) for their own consumption. A majority of small businesses do just this, without recourse to third parties, in order to minimise costs and retain control of the business. If it works for them, who are we to judge otherwise.

With XBRL Annual Accounts becoming a mandatory requirement in 2010 this independence will go, as the level of complexity in XBRL is way beyond the average person to comprehend and requires a computer with appropriate software. The fact that this software does not exist at the moment, does not seem to concern anyone in a position to halt or slow down this requirement. The fact that computers are becoming an absolute necessity to run a small business due to legislative requirements, also does not seem to concern them. What they fail to realise is that a small but significant proportion of small business owners do not use or wish to use computers in their business. Their reasons do not concern us here, but it is a fact.

Some respondents to earlier comments seem to believe that XBRL will be ‘invisible’ to its users. Let me quote from a HMRC communication I have received:

"... Companies and accountants will require both accounting and tax software systems to be XBRL enabled..."
further on
"... principal benefits of streamlined financial reporting are for customers. Software vendors benefit from being able to supply updated versions of their products that support XBRL ..."
and further on
"... XBRL enables integration of accounts production and tax software and both are required by software vendor’s customers ..."

The above statements give in a nutshell why software companies like XBRL; as invisibility comes at a price.
And, if a business does not have the software or time to devote to mastering XBRL, (and few will, given that it is an annual event) they will be forced to pay a firm of accountants, or a similiar establishment, for a service that provides no real benefit to them. Just like the audit - a cost without benefits.

Being a software developer myself, I have looked in detail at how to produce full XBRL Annual Accounts from an ERP package. It is possible, but not easy given the mixed structure and flexible requirements of annual accounts. Of course the differculty will vary for each package, but overall my feeling at the moment is that most packages will make it a new chargeable module. Time will tell as to whether I'm right on this.

In all the material I have seen on XBRL every one; including HMRC; claims that there will be benefits. But nobody can give one clear example of a real benefit for a small business. Many false benefits are claimed. A real benefit; from the imposition of a new tool/procedure; is one that does not already exist using existing tools/procedures. That is, it must be a NEW benefit. And I repeat, I cannot see one clear new benefit, nor can I see one arising. The imposition of XBRL will replace, what is now a simple exercise (using anything from expensive software, through a simple spreadsheet, to a typewriter and plain paper) with tools and procedures that greatly increase costs and complexity.

Why do I focus on small businesses? It will be small business that produces over 85% of the XBRL annual accounts actually produced in a year. So yet again, it will be small business that bears the cost of the ambitions of big business, government mandarins and claims by people with vested interests. The very same groups, that many believe have in the past shown a complete disregard for the small business.

XBRL in and of itself is a useful vehicle for a limited number of organisations – my estimate is less than 250,000 worldwide. What I am against, is its imposition on businesses (over 50 million worldwide) that will bear the cost, complexity and un-usability of it all, without any form of benefit.

If other people fail to see this and get lost in the hype; as has happened many times before with software that has promised the earth and delivered a pup; so be it. But I for one do not follow the herd on XBRL.

VAT 100/101 GovTalk Gateway

Acceptum Business Software by Rowanberry Consultancy Ltd has become one of the first ERP business software packages for UK SME's to be approved by HMRC for the submission of VAT 100 and VAT 101 (EC Sales List) Forms in XML format via the Governments GovTalk Gateway.

Use of the Gateway for these returns is seen as a distinct advantage for those companies/groups that submit 3 or more returns a quarter. As no more manual form filling will be required and VAT payments can be made via Direct Debit once the company/group has registered with HMRC to use this facility. The VAT forms are submitted via a few mouse clicks once verified as being correct via the normal reporting processes within Acceptum.


Note - do not confuse these XML VAT submissions; which will ease workload; with the proposed use of XBRL for Corporation Tax and Annual Accounts mentioned is another section of this website. The latter XBRL submissions; when they become mandatory; are seen by us as an unwarranted imposition of an over complex solution to a problem that can be addressed right now using simple current technologies - namely PDF files; for the submission of supporting annual accounts by the vast majority of registered companies.

UK XBRL and GAAP Reporting

The UK Government is working towards imposing the submission of Corporation Tax Returns and accompanying Annual Accounts in XBRL format from 2010 onwards.

Yet XBRL is at this moment is an incomplete standard with very little software able to support it. But academics and large businesses are pushing it forward.
Why?
When XBRL will yield no benefit for small businesses that will be compelled to use it from 2010 onwards! And that is the majority of users, given the large number of small businesses in the UK, compared to large organisations.

Yes - large organisations and stock markets may like it; although the inconsistency of the data will produce a lot of dangerous and unstable business decisions

Yes - large accounting firms and consultancies will definitely love it, as for them it is seen as a brand new income stream

Yes - HMRC may like it as they can reduce manpower in 2010 and beyond when they make it mandatory for companies to submit accounts and Corpn Tax Returns in XBRL format.

But - for 95% of businesses (under 20 employees) this move by HMRC/Companies House to impose XBRL on them for Corporation Tax Returns and Annual Accounts will be a nightmare that will greatly increase the costs of these annual events.

If you look at the development of the XBRL standard, it does not take long to realise that big business, large software houses and large accounting firms (those driving the development of the standards) have a vested interest in making the taxonomy and standards as complex as possible. So that they become the only people who understand it and thereby stand to create a lot of money out of the use of XBRL by small business when Government bodies make its use compulsory.

What is my proof for this - take the XBRL GAAP draft standard published 15 May 2004; using the smallest font size available will still take 700 pages to print the 5 XSD/XML files that specify this standard.
In an introductory document dated 13 June 2004; I quote -
" ... the UK GAAP taxonomy was aimed at a user base which was going to be looking for, say 200-300 key elements out of a generic taxonomy of 3,500..."
That’s less than 10%.

And this for a draft standard for which the previous draft contained 1783 key elements; now at 3,486 key elements. This number will only get bigger as drafts are expanded to handle more business types.

Yet small businesses that can now produce Corpn Tax Returns and Annual Accounts in a day using tools like MS Excel will be expected - no required - to take the time and effort to understand XBRL, its structure and use. Then to troll through 3,500+ key elements looking for those 200-300 elements that they need.

This is a dream - no a nightmare!

I myself have over 20 years experience of software and its development and over 30 years in preparing accounts for large and small businesses. I understand programming, IT analysis, XML, XSL etc. so I am not new to reading such documents. Yet when I look at XBRL I am horrified by its complexity, bloat and its complete un-usability. It also lacks any consideration for the MAJORITY of users that will need to compile and build XBRL documents.

Then in addition, from all this effort, I believe, small business will receive NO benefit at all, other than to satisfy the mandatory demands of Government Departments. I even doubt that the tax inspectors will know what they are looking at when it comes to using the annual accounts submitted in such a manner. For the quality of such accounts will be poor, given the absurdity of the draft standard as it stands.

What I would like to see is some realism in the XBRL standards that are focused on producing real benefits for the majority of future users. That is the small business.
Not XBRL standards derived by those with the most to gain from their imposition on a disparate user base, that at the moment does not have a voice.

Small business must wake up to this threat before the 2010 deadline in order to avoid a major catastrophe.

E.R.P. - Enterprise Resource Planning

E.R.P. stands for Enterprise Resource Planning; so E.R.P. software is Enterprise Resource Planning software.
So, what does this software do?
Take the activities of a typical company or 'Enterprise', its purpose can be loosely described as 'to manufacture or procure products for sale'. These products may be tangible or intangible, but basically the company must 'Plan' and 'Control' the use of its entire resource base to meet these objectives. That is what 'E.R.P. software' does, it helps the managers and staff of an enterprise to manage its resources to manufacture/procure the products it sells within one software package.
The single software package 'integrates' its elements or modules into one seamless package to control the enterprise activities. The most common activities being Purchasing, Manufacturing, Sales and Accounting (see diagram).
Lets take an example of the inter-relationship between these basic activities -
- first a customer places an order for a product, this places a demand on stock levels,
- if stock is not on hand, then manufacturing needs to produce the product, this may put a demand on purchasing,
- purchasing places an order with suppliers for the required raw materials and components,
- once acquired they are placed in stock, awaiting the start of the manufacturing process,
- manufacturing uses these stocks and along with labour and machinery produces the semi-finished or finished product,
- the product is then placed in stock, awaiting dispatch to satisfy the initial customer order,
- all of these activities have accounting impact on the business affecting suppliers, stocks, expenses, customers, etc.

E.R.P. software helps management control the business and to monitor each process, so that the limited resources of the company are used in the most economic manner.

E.R.P. software is not new, it has been around for at least 20 years, initially with large international companies. As time has progressed, the size level of companies able to install E.R.P. software has fallen, to a point now, where most companies are in a position to install some form of E.R.P. software.

What are the pitfalls to installing E.R.P. software?
The larger the company and the more functions placed on the same system at the same time increases the risk level, so if things goes wrong, it will have a dramatic impact on the financial and operating ability of the company.
However, for smaller companies, which are inherently more flexible with less complicated business processes the risks are still present, but much reduced.
Another disadvantage for large companies and E.R.P. systems is that once installed, they do present a substantial barrier to business process flexibility and change, with the result that many large companies that have installed the same E.R.P. system, end up with very similiar business processes as their competitiors and so look the same to customers and suppliers.

E.R.P. 2 the next generation.
E.R.P. 2 takes standard E.R.P. and extends it by providing for closer relationships between an enterprise and its customers and suppliers. An example of this closer relationship is that an enterprise will allow customers to directly interact with its computer system so allowing them to place orders and investigate the status of its account, outstanding deliveries and orders. Suppliers can be permitted to monitor stock levels and suggest when items need replenishing.
Due to the level of system integration needed between enterprises to achieve E.R.P. 2, this remains, for the moment, the preserve of large companies with the I.T. resources to make it possible.

I.M.S. - Inventory Management Software

Exact inventory control data is an essential part of any successful business.
Inventory Control Software provides you with accurate information on all aspects of inventory in a cost effective manner. It must provide timely and accurate information on the:
- quantity and value of goods held
- receipts of goods from suppliers
- return of goods from customers
- transfer of goods between locations
- removal for sale
- reduction of stock levels due to damage, theft, mis-counts, etc.

The software should also help you:
- improve customer service
- reduce stock levels and holding costs
- reduce stock processing costs
- change from the familiar buy-hold-sell model, to a sell-source-ship model

Inventory data must be at a level dictated by the type of stock held. This may cover: - bin location; color; size; style; lot numbers; serial numbers and expiration dates.
Run out of stock and you will lose sales. Whilst too much stock will adversely impact cash flow.
Inventory Control Software helps maximizes your benefits from 'just-in-time' inventory levels, whilst increasing warehouse productivity and reducing errors due to 'paper errors'.

Sales staff must be able to find inventory easily, using and seeing such items as:
- Fast and flexible product search facility
- Multiple part numbers - your part no; the supplier part no.; the manufacturer part no.
- Product Groups - for faster flexible searching and better reporting
- Recommended Selling Prices
- Cost per unit
- Alternative products
- Minimum order quantity
- Stock and sale unit quantities
- Associated promotion products

C.R.M. - Customer Relationship Management

is concerned about how a customer sees your business and how they interact with your business, in order to develop stronger relationships with them. Good customer relationships are at the heart of business success. One aim of CRM is to provide a cost effective seamless interface with customers no matter by what method they choose to contact you - by (mail, e-mail, web, telephone, face to face, etc.) and no matter at what lifecycle stage they have reached - initial contact, interested lead, qualified prospect, quoting, delivery, invoice, payment, or after sales service and support, followed by a re-purchase.

Improving customer support and service is a business and technology priority for nearly all businesses. Although technology is a component of CRM, it is a mistake to think about it in those terms. CRM is a strategic process that will help you better understand your customers’ needs and how you can meet those needs and enhance your profits at the same time. The strategy must bring together all the of pieces of information about customers and market trends so you can sell and market your products and services more effectively.

This may be by providing true one-to-one marketing and then gauging the effectiveness of such campaigns in real time.
By providing sales and accounting staff with a total view of all customer data and customer interactions, including complete visibility of all financial transactions and website interactions without any additional integration requirement.

CRM helps businesses use technology and human resources to gain insight into customer behaviour and the value of individual customers to the business. With an effective CRM strategy, a business can increase revenues by:
- helping sales staff close deals faster
- providing services and products that exactly met customer requirements
- offering better customer service
- cross selling products more effectively
- retaining existing customers and discovering new ones

C.P.M. - Corporate Performance Management

Many businesses desire strong profits and a cohesive work force, but take little positive action to achieve these goals.

Such desires cannot be achieved overnight.

They require training and a process of continuous improvement and refinement in order to meet the ever changing business environment.

A positive step forward is to install a Corporate Performance Management (CPM) process and ethos in the business.
Quit simply, CPM are activities to ensure that goals are consistently met in an effective and efficient manner. CPM's can be focused at various levels - at cross department business processes; at an individual department; at processes to build a product/service; at groups or teams of employees; at individual employees; etc.


Good Corporate Performance Management is based upon:
- Clear policy statements so staff at every level know what the goals are
- Clear direction to staff at every level so they know what to do to attain goals
- Mutually agreed and credible measurement criteria that makes sense to the manager and to the business
- Identifying areas of weakness and undertaking training to develop and improve such areas
- Timely feedback to managers on their measurement criteria
- Timely feedback to aid continuous process improvement and refinement
- Targeted consequences imposed consistently to develop superior performance and its maintenance
- Allowing for change at all levels as the business environment changes
- Finally, remembering to celebrate achievements


To help achive these aims, software can be used to produce the data for the measurement criteria, be they plans, budgets, forecasts or actuals. For consistent results across all measures, such software should be integrated with the E.R.P. (Enterprise Resource Planning) system used by the business.

On all the above, RCL can help, with its Acceptum Business Software E.R.P. software system which has a unquie SQL Query based Business Score Card function, allowing management to monitor business perfromance criteria whenever required. Being SQL based it can access data from any table within the system.

B.P.M. - Business Process Management

is the management of business processes that are internal or external to the business. It focuses on the integration and collaboration of people, systems, processes, and information across a business, including its business partners, customers and suppliers.

Your unique business processes are what defines your business.
They provide your business with its competitive edge.
They give it the ability to satisfy and retain customers, to maximize partnerships with other businesses, and to out-perform competitors.
It is the coordination of the numerous assets and tasks that together form your business processes. It could well be your most important business asset!

A complete review of Business Processes can innovate a business, re-energize performance and deliver increased value. A process managed business makes agile changes to direction and reduces cumulative costs across its value chain. It pursues strategic initiatives with confidence, be they mergers, consolidations, alliances, acquisitions, outsourcing or global expansion.

Business Process Management discovers what you do and then manages the lifecycle of improvement and optimization, leading directly to new or improved processes.

By its very nature, BPM can be applied to all business processes regardless of function, department, organization or industry. However, experience shows that to be successful, a business should start by solving a specific business process problem that has a clear, short term return on investment.

Broken or inefficient processes, and manual processes are often the causes of poor customer service and overall low business performance. Superior business performance depends on addressing these process problems. BPM coordinates all aspects of a process, ensuring that it is executed quickly, accurately and efficiently. It manages this execution in a manner that can be tracked (audited for compliance) and analyzed, so the process can be improved and businesses can achieve continuous process improvement.

Tangible benefits to a business implementing BPM are:
- Process transparency
- Agile and responsive business – faster, cheaper, more flexible
- Greater productivity
- Cost reduction
- Tighter control and compliance
- Improved customer service
- Connection of disparate systems.

Management Reporting

Many companies have monthly reporting routines which culminate in structured reports being printed and issued to all managers. Such monthly reports containing elements, like
- Balance Sheet,
- Profit and Loss A/c with various comparisons,
- cost centre expenditure with various comparisons,
- variance analysis of product manufacturing costs,
- labour useage, recovery and charge out rates,
- sales and marketing performance with various comparisons,
- management commentary covering the main functional areas and business performance,
- detailed reports from the ERP system.

All of this takes time to prepare and issue - normally one to two weeks.
Making the data obsolete before the report is even issued!

In to-days modern business world with advanced networks and internal web sites, the need to print physical copies of such monthly reports has largely been removed by having a sub-section of an internal web site available to all managers to access and review via a web browser, no matter where they are in the world.

To support the construction of such intranet 'management report' sites it is important that the ERP system used by the company can produce and save reports in HTML format so that they can be easily incorporated into the site. In fact on being produced by the ERP system they could be saved directly onto the web site making them immediately available. So that managers who need to add commentary have the data available on which to comment.

The automatic production of reporting elements and the removal of printing greatly reduces the man hours and elapsed time required to obtain finished reports.

Producing more timely reports makes the data far more relevant for supporting those business decisions required to change course and obtain a better outcome.

It is suggested that an outline of the management report for each period is built at the start of the financial year, so that as the year unfolds, each periods report can be constructed by the accountants and managers concerned with little to no requirement placed on IT staff.

ERP Reporting - ability to create/modify reports

All ERP systems come with plenty of standard reports, some useful for your circumstances, some not. But no matter how many reports there are, there always seems to be a few reports missing or reports not quite as required.

The reporting shortfall can be broken down into two main categories:
a) basic layout changes required, or the addition/removal of a small number of fields from existing reports

b) completely new reports required, some from a single data table, some requiring quite complex data structuring across two or more data tables.

To overcome these problems, ERP systems normally provide some form of report development tool. SAP for instances has its User Report Generator and ABAP programming language.

The development tool may be useable by end users with little or no IT experience, or it may require a person of considerable skill and experience. If the latter, then the running and installation costs of the software will be increased and in some circumstances even doubled (or worse).
Most systems claim to be useable by end users. This claim should be tested at some point – hopefully before committing money to the software. As not all claims are justified - I had better leave it at that.

One approach to filling the reporting gap is to use generic reporting tools like Crystal Reports or even the good old spreadsheet. These too come at a cost, they may not be of high monitory value, but the cost could well be hidden in things like data accuracy, completeness, timeliness and overall reliance on an external system to perform tasks that should be performed by the ERP system itself. Remember, that is probably one of the reasons you bought the system in the first place.

What should be avoided is using expensive IT developers or the system developer/reseller to remedy basic layout changes. Nowadays, this task should be within the scope and capability of system users and the ERP system should be capable of supporting this requirement. For example, under SAP the Report Generator is for users to create new reports. To change an existing standard report, will normally require knowledge of ABAP. Many ERP systems are sold that do not allow for standard reports to be changed by anyone! They are ‘locked’ by the system and require specialist knowledge or software to effect changes.

So when investigating an ERP system for use in your organisation, check its reporting abilities and the ability to change standard reports for category a) type changes.
Check the claims that it is user friendly.
Check on what experience and knowledge an IT person requires to create category b) type reports. Then determine the cost of that knowledge in the job market.

The answers to these questions will help you select the correct system for your SME.

Visual FoxPro - changing a Class in data grids and forms

Visual FoxPro programmers can use the VFP delivered base classes to construct forms and data grids for user data entry, reporting and viewing. The functions and tasks performed by these base classes are rudimentary. VFP programmers are encouraged to use these base classes as the starting point from which to construct their own classes in a hierarchical manner. Their own classes can greatly extend the tasks and routines performed providing an enriched programming environment that can easily be applied in a consistent manner to deliver highly functional applications to end users.

None of this is new to VFP programmers. However, there is one limitation in the Visual programming environment in respect of data grids and the addition of columns to a grid.

Under Visual FoxPro when a grid is added to a form, the programmer defines the number of columns the grid is to have. Having set the number of columns, the background VFP code actively adds the columns to the grid using the base classes of ‘Column’, ‘Header’ and ‘Textbox’ for the respective Column, Header and Text attributes. To change these base classes to the programmer defined classes; in order to use the additional functionality; under the Visual Programming environment is no small task. Especially when an application may have hundreds of data grids.

What the programmer would like to be able to do is to have the ability to define the classes for the Column, Header and Text that the grid uses when adding these elements to its structure. Unfortunately, this is not possible.

Up until recently, I have reluctantly accepted this limitation. But no more!
I have come across a method/process to overcome this limitation. What is more I could kick myself for not thinking it through and coming to the answer before now.

Most experienced VFP programmers know that the file that defines a form is in fact a normal VFP table file in disguise. Table files have the extensions
.dbf - for data and structure
.fpt - for the content of memo fields defined in the .dbf
.cdx - for defined indexes

Whereas, form files have the extensions
.scx – for the form definitions
.sct – for the content of memo fields defined in the .scx

Using the command window a form can be opened as a table with the commands:
Use “form.scx” exclusive
Browse

This shows the form.scx file as a table. Looking at the table column headings and data, it does not take long to discover that the column/field “Class” holds the class name of each object held in the form. Whilst, the column/field “ClassLoc” holds the relative address of the class library; being empty when a base class is set under “Class”.
What is more, any changes made to the tables data and saved will be reflected next time the form is opened under the Visual FoxPro programming environment.

Putting this information together into a small programming routine has allowed me to change the “Class” of every grid column text attribute in an application of over a hundred forms (most with grids) from the base class of “textbox” to my own defined class of “_textbox” which has additional programming to consistently handle, double click and right click events. Thereby making redundant my prior manual efforts of defining these events in every grid and textbox attribute in all the forms. What a productivity saver this would have been, if I had come across the solution earlier.

So what’s the small routine to save all this manual effort:

LOCAL ls1, ls2, ln1, i, lspathtemp, lnfiles
* gcnetfldr is a global holding the default drive and path
* – C:\Program Files\….
lspathtemp = gcnetfldr + "\FORMS"
* set the folder holding the forms to be changed
SET DEFAULT TO (lspathtemp)

*Step 1a
*– get the names of all SCT files in the current folder
* and change their extensions to FPT – as for a table
lnfiles = ADIR(larrayFile1, "*.sct")
FOR i = 1 TO lnfiles
ls1 = STRTRAN(larrayFile1[i,1], ".sct", ".fpt", 1, 1, 1)
RENAME (larrayFile1[i,1]) to (ls1)
ENDFOR

*Step 1b
* – get the names of all SCX files in the current folder
* and change their extensions to DBF – as for a table
lnfiles = ADIR(larrayFile2, "*.scx")
FOR i = 1 TO lnfiles
ls1 = STRTRAN(larrayFile2[i,1], ".scx", ".dbf", 1, 1, 1)
RENAME (larrayFile2[i,1]) to (ls1)
ENDFOR
RELEASE larrayFile1, larrayFile2

* Step 2
*get all the .dbf files in the current folder into an array
lnfiles = ADIR(larrayFile3, "*.dbf")

*set the complete path to the form table files
* as they are not associated with an open database
ls1 = "C:\Program Files\Microsoft Visual FoxPro 9\Acceptum\FORMS\"
* loop through the files
FOR i = 1 TO lnfiles
ls2 = ls1 + ALLTRIM(larrayFile3[i,1])
ln1 = 0
USE (ls2) IN 0 exclusive ALIAS xxx
SELECT "xxx"
* changing the Class textbox to _textbox
* and the Class Location to a relative folder path
SCAN FOR LOWER(class) = "textbox"
replace class WITH "_textbox", classloc WITH "..\..\ffc\_base.vcx"
ln1 = ln1 + 1
ENDSCAN
closetable("xxx")
Messagebox("Done " + ALLTRIM(larrayFile3[i,1]) + ;
STR(ln1),64,”Form Update”)
ENDFOR
RELEASE larrayFile3

*Step 3
* is to change the form tables back to form files
SET DEFAULT TO (lspathtemp)
lnfiles = ADIR(larrayFile4, "*.fpt")
FOR i = 1 TO lnfiles
ls1 = STRTRAN(larrayFile4[i,1], ".fpt", ".sct", 1, 1, 1)
RENAME (larrayFile4[i,1]) to (ls1)
ENDFOR

lnfiles = ADIR(larrayFile5, "*.dbf")
FOR i = 1 TO lnfiles
ls1 = STRTRAN(larrayFile5[i,1], ".dbf", ".scx", 1, 1, 1)
RENAME (larrayFile5[i,1]) to (ls1)
ENDFOR
RELEASE larrayFile4, larrayFile5

SET DEFAULT TO (gcnetfldr)
Messagebox("Complete ", 64,”Form Updated”)


And that’s it. All textbox attributes now use the programmer defined class of _textbox to provide a better and consistent approach to the application. As an aside it also reduced the size of the executable.

I now hold this code in a routine which I can run regularly after adding a new form/grid combination to my application.

Backup that data!

In the last week I have rebuilt an entire database from a few key tables over 4 months old!

Why was this necessary?

Because the user; a SME; never made a backup of its key ERP system.

So when somehow the ERP database got deleted from the server. It had no backup to go back to.

Its only recourse was to use a copy of a few files e-mailed to the system developer (yes me); to try and rebuild the entire database based upon those key tables. Yes it was done - but at a not insignificant cost.

So SME's - make sure you take regular backups and copy them to a PC or media that is not the prime server/PC.
This daily/weekly routine could save you embarrassment and money.

Be warned; PCs/Servers do
crash;
get stolen;
get infected by viruses;
deliberately get sabotaged by discontent employees;
lose data through foolish acts by novice users.

Installing an ERP system

Many companies defer installing an ERP computer system because of the disruption and risk involved in the installation process. These risks and disruptions can be minimised with good planning and preparation.

The extent of the work involved will depend on the size of the organisation involved and on its volume of clients, customers, suppliers, products, open transactions, etc. at the point of cut-over. But whatever the extent, the principles to be applied remain the same, whether the data is Master or Transactional.

These principles are to:
a) identify each entity; customer, supplier, product, account code, open sales/purchase/production orders, account balance, etc.; required to be set up in the new system.

b) identify the means of transporting data for each entity from existing systems into the new system. This normally means exporting data to an Excel spreadsheet or a data extract file useable by the new system.

c) identify the data manipulations required to change old format into new system format.

d) identify any missing data that the new system requires and determine how to acquire this data.

e) identify data used in existing systems that is 'dropped' by the new system; and decide how important this data is to the organisation and how any functional gap caused by this loss, is to be covered. This should have occurred when the new system was selected. But it should be re-examined now that more information on the new system will have been obtained in the interim period.
This is the point of greatest risk, for if some 'must have' business function appears not to be covered by the new system, it can become a 'show stopper'. Or at best, delay the implementation timeframe, whilst alternatives are investigated and assessed.

f) identify all actions that need to be performed on existing systems before the data is transferred across to the new system. Examples of this would include:- data clean-up, removal of old records, payment of all open items to suppliers, fulfilment of as many customer/production orders as possible.

g) whenever possible, coincide the switch to the new system with a financial year end, as it is always much easier. When this is possible, always import closing balance into the old year on the new system and do a 'year end roll' into the new year so that monthly/period movement reports are not compromised by cutover data.

h) identify new hardware and software requirements of the new system and the acquisition path and any delivery/installation delays.

i) collate the work required from the above points and assign to individuals along with developing a timeframe for each task. Critical points and data constrictions need to be identified so that timeframes are not delayed and additional resources can be focused in the right place when tasks are actually performed.

For a small organisation these tasks may only take a day or two to complete and not require significant testing prior to doing it for real. In larger organisations, test runs will be required to gain insights into exact data requirements and loading sequences. Whatever the size of an organisation, planning the installation process and preparing data in advance, goes a long way to making an easy and smooth systems installation.

ERP Business Systems and Inter-Departmental Communications

Telesales takes an order and the customer specifies that they wish the ordered items to be packed and delivered in a special one-off manner. The customer is important to your business, so you decide to accept the conditions and make a small charge.

Now the problem begins - How does the telesales person communicate these instructions to the warehouse manager and delivery manager in a timely and consistent manner?

What happens when accommodating such customer requests forms or becomes a distinguishing factor of your business, to the extent that they form a substantial part of your business.

The use of inter-departmental e-mail is one way to solve this communication problem. But its major disadvantage is that the instructions are not held in or connected with the original sales order in the business system. And when the number of orders requiring special treatment grows to a level beyond a few, the chances of these special instructions being missed when an individual order is picked/packed/despatched is increased. To a level that is an embarrassment to the company when things go wrong.

The only effective alternative is have an ERP Business System that provides for telesales to record such instructions in records relating to the sales order and to have such records available to and changeable by, any other department. Only in this way are the notes directly linked with the sales order and thereby visible at all times to every department that needs to see them. What is more the use of an external system is eliminated making the secure backup of such notes part of the normal backup routine of the main ERP Business System.

One major advantage of a ‘General Notes’ facility as described above, is that it can be used for lots of other inter-departmental communications, like - telesales to credit control/accounts; sales to purchasing department.

A business system that provides for General Notes to be held for each of the primary business transaction documents (Sales Orders, Purchase Orders, Time Recordings, Job Costings, Production Orders, etc.) will be much more flexible, responsive and useable than a system that does not provide such facility.

See Acceptum Business Software on RCL Systems web site.

Transport/Delivery Restrictions and ERP Business Software

In to-days world of delivery lorry/van restrictions, that can be on - access times, vehicle weight/size, congestion charge zones, etc. How does a supplier organisation keep up to date, or even retain the necessary data on these restrictions, as imposed on individual customer delivery points?

The majority of older ERP/business systems struggle to hold such data in a manner or format convenient to the supplier and able to be seen and set by multiple departments that need or have access to such data.

A good ERP/business system will provide for this data to be held, accessible and changeable by each of the departments involved in the delivery process.

Part of the sales forces task in to-days environment should be to collect and enter the initial assessment of such restrictions into the business system when taking an order. Either, at an individual sales order level, or better still at the customer delivery point level. Then later on in the Sales Order completion process, the warehouse/delivery department can use this information to pack the product in such a manner as to meet weight/size restrictions and schedule a delivery time within the allowable time window.
This data should also be available for update by the Transport/Delivery department when a driver reports back that the data needs to be changed for some reason.

The complexity of this delivery restriction data at its simplest level need only be a text memo attribute held at the sales order or delivery address level. For other organisation more complex data structures may be required holding such details as lorry size, maximum cargo weight and size dimensions, etc.

If delivery forms a problem for an organisation at the moment then this problem will only grow in the years to come as more restrictions and congestion charge zones are imposed, making it all the more important that such data is held in the main business system.

Visual FoxPro - Corrupt Tables due to header record count

Since Version 8 of VFP programmers have been able to set how tables are validated when they are opened.

The level of validation performed is via the command
SET TABLEVALIDATE TO [nlevel]

For a full descriptions of [nlevel] see the VFP 8/9 Help system.

The default value for [nlevel] is 3; which is described in Help as:

Check .dbf header before saving append operation to disk and modifying header.

This means that the default action is to validate the table header record count on opening and on update.

What happens when the record count in the header does not match the actual number of records in the file?

The program returns an error message

2091 - Table "name" has become corrupted. The table will need to be repaired before using again.

This unexpected message appears to be quite terminal to recovering the VFP table! As no 'REPAIR' command is available nor is any advice given on what to do next.

How come this message is generated in the first place!
a) it could be due to a corruption caused by bad programming, system crashes etc.
b) it would appear that some virus checking programs do not release/unlock the tables header record correctly at a time when VFP is writing a record to the table and this causes the header record count to become out of sync. with the number of records held in the table.
The timing of these events appears to be crucial in causing the error.
So for 1,000 records written to a table it may only occur once.

How do you recover the table and its contents when this happens?

First you need to have an environment under which the table is NOT validated when it is opened. This can be achieved with the command -
SET TABLEVALIDATE TO 0

0 being - Do not perform table validation.

Now the table can be opened, you need to reset the header record count to the correct value. The structure of a table file can be found in the Help system at "Table File Structure".
This shows that the record count number is held in the first record at positions 4-7 byte offset and is held in hexidecimal format.

Resetting to the correct value is not straight forward, as commands like

RECCNT()
and
COUNT ... TO

only report back the invalid number in the header record. As does the code

i = 0
scan
i = i + 1
endscan

i only contains the number of records held in the header.
Most likely the table will hold more records at the end of the table that are not reached.

In order to regain these records 3 actions need to be taken, in outline they are:

1) set the header record count to a very high number

2) PACK and REINDEX the table

3) reset the header record count to the actual number of records held.

This can be done with code something like (a log file is used to record the actions taken):

Local nHdlr, i, lslog, ltn
...
...
...
CLOSE TABLES ALL

* create/open log file
nHdlr = FOPEN('compact_repair.log', 11)
IF nHdlr = -1 then
nHdlr = FCreate('compact_repair.log')
ELSE
FSEEK(nHdlr,0,2) && attempt to get to end of file
ENDIF
lslog = "**** Started " + TTOC(DATETIME(),1) + " ****"
FPUTS(nHdlr, lslog)

ADBOBJECTS(aTabs, "Table")
=ASORT(aTabs)

FOR i = 1 TO ALEN(aTabs)
* when tables not validated, any records
* after the headers record count are deleted
* so we need to check/reset the record count
* in the header before doing a PACK
* gdatabasename is a global for the database name
ltn = gdatabasename + "!" + aTabs(i)
* set rRecCount in header to a high value
resettabheader(ALLTRIM(aTabs(i)), nHdlr, .T., .F.)
USE (ltn) IN 1 EXCLUSIVE
PACK
REINDEX
USE IN 1
* 2nd call resets RecCount in header to correct
* value and logs changes
resettabheader(ALLTRIM(aTabs(i)), nHdlr, .F., .T.)
ENDFOR
USE IN 1
...
...
...


The procedure "resettabheader" is coded as:

PROCEDURE resettabheader
LPARAMETERS ptab, nloghdlr, psethigh, plog

LOCAL ls1, ls9, lslog, nHdler, lfn
LOCAL ln1, ln2, ln3, ln4, nRC, nCount as integer
nCount = 0
ls9 = ".dbf"
IF psethigh then
* set record count to high number *
* maximum record count allowed in VFP
nCount = 999999999
ELSE
* not setting high value, so count the
* number of records using Scan
* which automatically stops when the
* end of the table is reached
USE (gtn) IN 1
SCAN
nCount = nCount + 1
ENDSCAN
USE IN 1
ENDIF

* open current table file using low level
* file commands and reset the record counter
* gcnetfldr and gcdatafolder are globals
* that hold the path data to the current database
lfn = gcnetfldr+"\"+gcdatafolder+LOWER(ptab)+ls9
nHdler = FOPEN(lfn, 2)
IF nHdler = -1 then
lslog = "File not found - " + lfn
ELSE
* convert our record count number to hex format
ln4 = FLOOR(nCount / 16777216)
nRC = nCount - (ln4 * 16777216)
ln3 = FLOOR(nRC / 65536)
nRC = nRC - (ln3 * 65536)
ln2 = FLOOR(nRC / 256)
ln1 = nRC - (ln2 * 256)
lslog=lfn+" contains "+STR(nCount)+" records."
* move file pointer to the 4th byte
FSEEK(nHdler, 4)
ls1 = CHR(ln1) + CHR(ln2) + CHR(ln3) + CHR(ln4)
* write record count number to the table file
FWRITE(nHdler, ls1, 4)
* low level file close
FCLOSE(nHdler)
ENDIF
* output log data
IF plog then
FPUTS(nloghdlr, lslog)
ENDIF

RETURN
***************

Warning - you use this code at your own risk!

After records have been recovered in this manner, the validity of the table and its records MUST be verified.