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.

Data Import and Export (from web sites)

Today database designers and programmers have numerous options to support when it comes to the import and export of data. Common formats supported are:
Comma Separated Values - CSV
Lotus Symphony spreadsheets
Lotus 1-2-3 spreadsheets
Excel spreadsheets
Other databases - SQL, Visual FoxPro, MS Access
XML
Common application formats - MS Word, MS OutLook
All of these have their pros and cons. However, considering them all, XML (Extensible Markup Language) has; as intended; become the most important.

This is due to its wide spread support and flexible nature. Its flexibility allows for any form of data from any database to be exported to, or imported from, an XML file.

So any modern ERP software system worth considering, must be able to import/export data into/from any of its tables. Not just a few tables chosen by the developers.

This ability to import data is very important for SME's with an e-commerce web site. As if it cannot import web site data into its ERP system without manually re-entering the data, it seriously needs to consider replacing the system with one that can. Just to avoid the errors and mistakes generated by the process of human intervention.

With a capable ERP system data from an e-commerce web site can be handled in various ways depending on whether the site is hosted or held on an internal server.

Hosted Site
Two approaches are commonly taken -
1) e-mail of the data to the company when the customer confirms the order, or
2) FTP access to an order file produced when the customer confirms the order.

1) E-mail - given a low volume of orders; less than 100 per hour; the e-mail route is considered the best approach to take.
With this approach when the customer clicks the Confirm button on the web site, the underlying program formats the data into XML format, either as a separate file that becomes an e-mail attachment or as the body contents of an e-mail. The e-mail is then automatically sent to the companies e-mail address.
Depending upon volume and required response times the e-mail address is read on a frequent basis with rules to place the e-mail(s) into a special folder. When XML attachments are used, these need to be saved into a folder on the server ready for the ERP system to process.
When the XML is in the body of the e-mail they can be read directly by the ERP system.
Having now got access to the data, the ERP system can be run manually or automatically under a timer to read the XML data and update its tables, ready for the normal sales fullfilment process to begin.
After processing each e-mail/file the system should move the source e-mail/file to an archive folder prior to deleting it from the source folder. The e-mails/files need to be available for when a processing error occurs.

2) FTP - here the ERP system can run automatically under a timer to read; on a frequent basis; the XML data files held in a web site folder, process their contents and then move the file(s) to an archive folder, prior to removing the file(s) from the web site folder, in order that they are not processed again. (An alternative approach is to rename each file as it is processed.) The file(s) need to be available for when a processing error occurs.
Once the data is imported into the ERP's tables, the normal sales fullfilment process can begin.

Internal Server Site
Many more options are available here, but the essential feature is that a folder is available to both the web server and ERP system. The web server places XML files in the folder as orders are confirmed, whilst the ERP system reads these files and updates its own tables on a regular basis and removes or renames the XML files after processing.

On all these approaches the format and data content of the XML file is dependent upon the specfics of the ERP systems and its requirements.

The only manual intervention (other than dettaching XML e-mail files) in these processes is when the user company decides to manually run the import process instead of it running automatically under a timer.

When an ERP system can export its data in XML format, the resulting file(s) can be FTP'd to the web site. Then using XSL (Extensible Scripting Lanaguage) the contents of these XML files can be easily incorporated into web pages.

So products, stock levels, customer accounts, etc. can all be made available on the web based upon the primary source (ERP system) with very little manual intervention. Thus removing the need for dual data maintenance.

An example of a web site using this approach is www.places-to-go.org here advertisors and people requesting a listing complete a form which is e-mailed to the company. Who import the data from the e-mail using Acceptum Business Software as its ERP software. This data can then be exported in XML format and then FTP'd to the web site to provide the list of attractions, which is formatted, sorted and extracted on a county basis using XSL.

XML use has opened up a whole new world for the SME allowing web sites to have the power and feel of a much larger organisation. In fact the very flexibility of SME's over monolithic large corporations gives them the opportunity to have a far better web presence than larger organisations.

SQL - LIKE and IN clauses

Reading PC World recently, I was spurred into action by an article about using the Like clause in SQL statements to report disparate values.

In MS Access the clause has a syntax of - Accounts.Acct LIKE "[ADS]*" which will extract records with the account field containing values beginning with A or D or S.
The * denoting the wild character.

With MS Visual FoxPro the syntax is not quite so neat, but can be achieved with the clause -
Accounts.acct LIKE "A%" OR Accounts.acct LIKE "D%" OR Accounts.acct LIKE "S%"
The wild character here is %.

An alternative approach, is to use the SQL IN clause, for which the above becomes
LEFT(accounts.acct,1) IN ("A", "D", "S")

Notice the use of the function LEFT() to test just the first character of field values.

We could even have - LIKE "%a%" - to report those records whose field contains an 'a'.
Or - LIKE "_a%" - to report those records whose field contains an 'a' in the second position.
With the '_' wild carding a single character.

When are these clauses used?
Not often is the obvious reply, due to a few reasons:-

1) SQL writers and users are not familiar with them, especially if they are not writing SQL statements every day.

2) The SQL clause syntax changes from one database to another.

3) Not often required to report disparate data values.

This last point is, I contend, a false assumption based on the restrictions imposed by the majority of todays ERP business software packages.
It need not be so!
As if available, they can often be used in normal business processing and coding structures.
To explain, it is best to look at a common example.
Suppose you have a company with numerous locations/branches spread throughout the country, say - Leeds, London, Glasgow, Cardiff, etc. and at each location the typical functional departments of Sales, Marketing, Manufacturing/Assembly, Accounts, Personnel, Warehousing all report to the local senior manager on a day to day basis. With central control of each functional department being maintained via head office.
On a Cost Centre coding basis we can have the location coded as
100 - London
200 - Glasgow
300 - Leeds
400 - Cardiff
...

and functional departments coded as
10 - Sales
15 - Marketing
21 - Manufacturing
22 - Assembly
30 - Warehousing
40 - Accounts
50 - Personnel
...

Combining the 2 elements together, assuming Location to be the primary element we get
10010 - London Sales
10015 - London Marketing
10021 - London Manufacturing
...
20010 - Glasgow Sales
20015 - Glasgow Marketing
20021 - Glasgow Manufacturing
...
30010 - Leeds Sales
30015 - Leeds Marketing
...

Such a coding structure using the standard From/To selection range can easily report on a Location basis.

But how do head office functional departments see the costs of their own departments spread across the company?
With the use of SQL LIKE and IN clauses!

Under Visual FoxPro the SQL LIKE clause for all for Accounts Depts. at all locations would be written as
CostCentre.code LIKE "___40"
The '_' wild cards a single space, so 3 are required to wild card the location element.

Being able to use such powerful SQL features can remove a lot of restrictions imposed on coding structures and similiar data elements.

So when reviewing ERP software packages for potential use/purchase, look for the flexibility to be able to use such SQL features when reviewing data and drawing reports.

You may not thing you need it today, but who knows what tomorrow brings.

First Entry

Rowanberry Consultancy Ltd offers Acceptum Business Software from its web site for UK SME's looking to use an ERP system.

One major advantage of an ERP system is that it provides information on a real-time basis. So management can always be sure that the data reported by the system is the current state of affairs for the entered data.
A disadvantage is caused by management not understanding or appreciating the phrase '... for the entered data.', believing that the data is entirely correct, when in fact the data is incomplete in some way. Making decisions on such incomplete data can cause problems, even severe financial loss.