Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Coping with software errors - Part 2

by
9th Feb 2005
Save content
Have you found this content useful? Use the button above to save it to your profile.

In his first article, David Carter argued that the majority of errors that occur when running an accounts package will derive from hardware and network faults rather than the package itself.

But whatever the cause, there is now a problem with the accounts package since the data it holds may be corrupt. What are the commonest types of data corruption and how do you put them right?

Error One ' only one side of the double entry was posted
One common cause of data corruption is when a transaction is in the process of being updated to the files, and there is an interruption to the power supply. As a result one side of the double entry gets posted but not the other. The usual symptom of this is that, if you print off a copy of the Trial Balance, the total of the debits does not equal the total of the credits.

These days a lot of packages have 'roll-back' technology, whereby, if a transaction has not been posted completely, the data automatically gets rolled back to the state before posting started. Nonetheless, it is still worth displaying the TB to screen occasionally, and making sure that the totals of the two columns agree.

Error Two ' the transactions don't add up to the on-line balance
When you run the Trial Balance enquiry, the screen displays a list of all the nominal account balances. This display is immediate, even through each balance may be the cumulative total of thousands of transactions.

The balances appear immediately because they have already been calculated and are stored 'on-line' in the data files. Every new transaction updates them. So, for example, a new sales invoice will update the on-line balances for Sales, VAT and Debtors accounts in the Nominal ledger.

So day after day dozens or hundreds of new transactions are being entered, and each of them updates various on-line balances. But just once in a while there is a blip and the on-line balance doesn't get updated. So now the balance is wrong and it will be wrong for evermore.

In fact, incorrect on-line balances are inevitable when you think about it. Fortunately, they are nothing to worry about since the underlying transactions are OK, and that's the important thing. So if one day, when you run a report listing the individual transactions on an account and you notice that they don't add up to the account balance, don't get too worried. There's probably nothing wrong with the underlying data and it's only a minor problem.

What to Do?
Some packages have routines for automatically recalculating on-line balances. But the best thing is to ring the support department of your software supplier and discuss it with them. They will usually talk you over the phone through a 'data rebuild'. The system goes through the transaction files, finds any partial or orphaned transactions, then reconnects them to the accounts they should have updated. Having tidied up the transaction file, it then recalculates all the on-line balances. Usually this does the trick.

A Real-Life Example with TAS Books
I had a classic example of this recently with a customer who uses TAS Books. Marion the accounts lady noticed that when she went into the Nominal Transaction Enquiry of TAS and displayed a list of transactions for the year, the report total wasn't the same as the on-line balance on the TB. Half a dozen nominal accounts had the same problem.

First of all we ran the TB and checked the totals at the bottom. They balanced, so the underlying data was probably OK.

Drill down provides the answer
Next we had to work out which figure was wrong ' the total of the transactions or the account balance on the TB. Not knowing which, I pulled up the TB on the screen, found the first wrong balance and double clicked on it. TAS now displayed a window listing the underlying transactions. It was a very small window but big enough for us to scroll down to the bottom of the list and see the transaction total. It wasn't the same figure as the TB, so clearly the on-line balance had got out of sync

This is a good example of why 'drill down' ' the ability to double click on any on-line balance on the screen and display a list of the transactions that make up that balance ' is so valuable.

On to TAS Techical Support
Marion recalled that earlier in the year she had had data errors, but when they switched PCs on the network and made sure they were all the same model, the problem seemed to go away. Anyhow, now it was on to the TAS Support line to put things right.

We got through quickly. Our contact told us that there is a 'Trial Balance wizard' in TAS which runs a series of self-correcting routines. Try running this first and ring him back if there were any problems.

So we ran the wizard and it worked ' the TB now showed the correct balances. But another problem had occurred with the profit carried forward from previous years. The wizard only corrected up to 4 years back and we had 10 years data on the system.

So back to TAS again. Once again we got through quickly and this time talked to a lady called Janet. She asked for a few seconds to work out the solution and then talked us through correcting the 1999 balance by hand.

It's only when something goes wrong that you find the true mettle of your supplier. In this case everything worked perfectly ' quick pickup of the phone, quick understanding of our problem, clear instructions from Janet to solve quite a complicated problem. Well done, TAS.

Tags:

Replies (28)

Please login or register to join the discussion.

avatar
By User deleted
23rd Mar 2005 09:36

Is Software Getting It Right
To revert to the original theme

Here is an example of users having to put up with something that is clearly wrong; in contravention of all standards and yet nothing has been done about it

http://support.microsoft.com/default.aspx?scid=kb;en-us;77295

Basically Excel (well known software provider) handles CSV exports incorrectly. Microsoft acknowledges this fact, does not seem interested in fixing the problem and provides a 'work around' instead

The knock on effect of MS actions is huge - every other software provider using CSV importing needs to accommodate their error

I have heard that because most software is licenced (not sold) the Sale Of Goods Act does not apply - does anyone know if this is true

Thanks (0)
avatar
By Darwood
03rd Mar 2005 11:24

Mid Range systems
In any system design you have to make design decisions. One of those decisions is "will this work on large data sets?". I think this is really the scaleability question that has already been raised.
From what you describe it sounds like the Exchequer point-and-shoot idea only works with a small number of customers. So if the product only ever has to work with small datasets then it isn't a problem. Incidentally I think Pegasus Opera II now has a similar feature but I must admit I haven't tried it with large data sets. The SQL Server version is due out in May so it will be interesting to see how that copes too.
The higher range systems where you find the customer before displaying any information is correct design for a client/server system. The idea being that only the necessary data is transmitted over the network. This becomes more important as you deal with bigger data.
Maybe a nice compromise would be a search or filter first to get a smaller number of customers followed by the point and shoot thing.
This is one of the reasons that it is very difficult for a system to be scaled up or scaled down to reach a new market.

Thanks (0)
avatar
By User deleted
03rd Mar 2005 17:48

Adapt Point-and-Shoot for all Scenarios
I agree with David's concept of point-and-shoot as a nice way to make a selection - so the trick is to make it work in all scenarios

In order to achieve this, Darren is along the right lines with his filter suggestion thereby only returning a sub-set or records meeting the criteria. However, because it would not be practical to have a DropdownList containing hundreds (defeating the object) of records, a free format style of selection criteria could be adopted. Most databases have a command similar to 'LIKE' and by allowing wildcards before and after as well as converting the entire selection criteria to uppercase we have a pretty good simple approach - let the database do the work; its what its good at

This should get the user to where they want to be in about 2 mouse clicks, cut down the traffic and make the UI quick

Of course from this point onwards a lot depends upon the user and how refined their selection criteria is. Typing in 'a' is not selective enough

The alternative/addition for small medium sized systems is the Alphabetic Button approach (for Customers / Suppliers / Products etc) - i.e. buttons for A B C .... etc. Working on the basis that presumably the user knows the first letter of their criteria this again takes 2 mouse clicks. With 20 lines to the page this in theory covers 26 * 20 = 520 customers/suppliers, although there is a bias towards certain letters in the alphabet.

Or a combination of buttons and selection criteria would satisfy both large & small systems

With specific software example products, we again need to analyse why they have taken the approach of delivering everything when the user doesn't necessarily want it. Using alphabetic buttons is just as fast (quicker) when spanning more than one page and far more efficient

Thanks (0)
avatar
By David Carter
03rd Mar 2005 21:39

this is helpful
Thanks for educating me in this.

Yes, I think with Enterprise they have to download the entire customer or product masterfile into memory and then they display the customer/stock list. If there are too many records it all takes too long, so they allow you to set a limit - i.e you can say: show me the first 750 records. In this case the product list screen is displayed after the first 750 records have downloaded.

Darren. Take your point about thin client. I think you are suggesting as a half-way house that if the user wants to find Sweet and Co, he just types in S in the AcctNo field, the server then finds all the customer codes beginning with S and sends them down to be listed on screen for point and shoot. Is that right?

Talking about point and shoot generally, this for me is what makes small/mid-range systems so much easier than big systems. All the records are listed on the screen in front of your nose and you can just scroll up and down through them to see everything.

But with big systems all you get is a blank screen. Your data is hidden deep down in the database and you have to dig for it.


Thanks (0)
avatar
By David Carter
02nd Mar 2005 22:51

superiority of mid-range
An example of superiority of mid-range I'm thinking of is the ability to display a list of transactions or customers permanently on screen, and allow the user to point and shoot at the one he wants. Exchequer Enterprise permanently displays all customers and transactions and this is one factor that makes it such an attractive package.

You can only do this with a relatively small number of customer records as loading up a lot of records [into the grid?, is this the term?] would take too long.

With a big system you can't offer point and shoot. The user just sees a blank screen and has to Find the customer record by entering an account code.

Thanks (0)
avatar
By Darwood
04th Mar 2005 13:10

Point and Shoot
"I think you are suggesting as a half-way house that if the user wants to find Sweet and Co, he just types in S in the AcctNo field, the server then finds all the customer codes beginning with S and sends them down to be listed on screen for point and shoot. Is that right?"
Yes pretty much. Although as JC points out just the first letter might still yield a large result set. Ideally the user could search on other fields too. How about all of the customers starting with S in Manchester for example? Then the user gets a list of those, with point and shoot as previously descibed.
The theory behind client/server is only sending the data requested over the network. Returning the entire customer list is feasible on a smaller system but could be fatal on a database with thousands or even millions of customers. You are right that being able to give the user all their data in one go (eg a complete list of all customers) is an advantage of the smaller programs but it also means they might not scale up to the larger datasets typically found in larger systems.

Thanks (0)
avatar
By User deleted
04th Mar 2005 16:02

Idea Behind LIKE ....
is that the user can enter a string of any length; obviously the greater the length the more refined the search and the fewer records returned

By using a wildcard on either side and converting to upper case we cater for searching any length/content of a string against a specified database field

Taking the search string 'oFt' (upper='OFT') would return all records with any occurance of this string anywhere in the chosen database field - i.e. Microsoft, Often, croft etc...

Whilst we are on the subject of design - could someone explain why in todays environment software still displays client codes, which are often given a greater status than the client name. Quite frankly, codes should be a behind the scene aspect and in the majority of instances not shown on screens - why do they need to be shown?

In the real world people are recognised by name and not some obscure nemonic such as GREVEANS (resticted to 8 characters). Also bearing in mind the codes are predominantly for system use, why are users allowed to enter them in the first place (and then validated for uniqueness !!!); the computer is quite capable of generating a unique code all on its own.

Then to cap it all, the software sorts customers/suppliers etc by code and not name

Thanks (0)
avatar
By User deleted
04th Mar 2005 17:21

Client Codes
Going off topic here but I will bite.
Each record in the table should have a uniqueID number. This is then used as primary key for linking to other tables such as transactions. If you have a account code at all then it should be changeable and not used for referential integrity. I agree this too should/could be generated by the system. I can see the point of an account code in some places. It can be useful for organising customers where the name doesn't do it.
Eg We have one client who wants all their retail customers to come after their trade customers. They prefix every retail customer code with ZZ and it seems to do the job for them.
I can think of other examples where we prefer to see a company name sorted on the last name rather than the first.
Eg. John Doe Advertising
Some people might prefer to have it with the D's rather than the J's
Just a thought.
I reason I think some systems fail to do this properly is because they are coming from old database design methods where the account code was the primary key.

Thanks (0)
avatar
By Darwood
28th Feb 2005 11:00

Speed or accuracy
Perhaps I should have said speed and integrity.
If you calculate totals on the fly it is slower than storing them but you always know the total is equal to the transactions.
If you store the totals then it is quicker than calculating them everytime but you can't guarantee the totals are correct (equal to the transactions).
I think scaleability means you can take an approach that works in 1 context. Perhaps small user system and use the same approach on a larger system. I don't have large scale system experience so I don't know how calculating totals on the fly would work on a large system. I think the number of transactions would obviously be a factor, and how often those calculations need to be performed. If a system is designed for just 1 market, say the SME market then scaleability might not be a factor. You would have to be certain however that the calculations never become too slow.

Thanks (0)
avatar
By David Carter
07th Mar 2005 18:50

customer codes
When QuickBooks first arrived from the US I think it had no customer codes. But they have now added this facility as an option.

You can have customer records without an account code, but as you say, an account code gives you control over how you sort the records. Most UK accountants feel happier with account codes, I think. Business users aren't bothered.

Same goes for Nominal Codes. Some systems offer alphameric codes but I think accountants are happier with numeric for sorting.

One point - the customer record should have TWO account codes. The first account code never changes and is an internal reference within the system.

The second is changeable, so that if the customer changes their name you can change their account code too. The big case a few years ago, I think, was when Mercury got taken over by Cable and Wireless. All those accounts like MERC01 needed to be changed to something like CABL01.

However, most packages only have one account code and you can't change it. So you end up with Cable and Wireless being coded as MERC01.

Thanks (0)
avatar
By User deleted
26th Feb 2005 12:13

Design Compromised - lack of Totals
David
I am not sure that I agree with you about this. Having a certain number of totals in the system has got to be the correct approach from a design stand point

Software development goals - good data & program design, optimise performance, avoid placing excessive burden on other components in the system

Scaleability is a 'by product' of the above objectives - if everything is in place then to a large extent scaleability will follow. On the other hand if scaleability is not an issue then presumably neither are any of the underlying concepts outlined above

This opens a wider debate. Should it be up to the Accountancy Profession to challenge developers about errors? Very simply - how can an accounting product that has mis-matched totals be granted ICAEW Software Certification when it cannot do the arithmetic?

As you say, hardware is not a limiting factor - the very fact that there is a surplus of hardware power should not mean that correct design and efficient programming are compromised

The argument seems to advocate – there is 'limitless' hardware capacity therefore it is acceptable to produce inefficient software because the hardware will underwrite the software – we should not subscribe to this approach

I would also have to take issue with you over the statement about superiority of small/mid-range packages.

The boundaries have become blurred because the lower end packages are incorporating functionality traditionally reserved for larger products. Smaller packages are by definition quicker and simpler to implement in the first instance, however, they cannot offer the depth or infinite ability to customise thereafter.

This wish to 'customise' everything is driven by the user and I have to say that the accounting profession plays its part in this process. Ultimately a lot of software houses have recognised that they simply cannot cater for every possibility and have opted for the spreadsheet reporting route

The bottom line is that by ignoring scaleability one is potentially compromising the design & performance

Finally, in response to Darren, why is it a trade-off between speed and accuracy - surely they are not mutually exclusive. In this context the trade-off should surely be between ability (development effort) and accuracy

Thanks (0)
avatar
By David Carter
24th Feb 2005 20:32

why worry about scaleability?
Thanks Darren. Yes, I think both TAS and Exchequer are Pervasive SQL (used to be BTrieve). It happens that these are the only two packages I work with regularly. I don't know about faulty balances occurring in others or whether the fault is peculiar to Pervasive.

JC, is scaleability really an issue? As far as I can see as a non-techie, ever since computers were invented the developers have been forced to design systems around the limitations of the hardware. If you want sub-second response time, you can only get it if the files are designed a certain way.

For designers of big systems with zillions of transactions, hardware is still a limitation. But for us in the mid-range with only thousands or tens of thousands, it's no longer a limitation.

Mid-range systems can be designed purely for the benefit of the user and the application. The hardware tail doesn't wag the software dog any more as it it still does with big systems.

That's why small and mid-range packages are far superior to big ones in terms of ease of use and flexibility.

So don't bother too much about scaleability. It only compromises the design of the software.

Thanks (0)
avatar
By User deleted
08th Mar 2005 15:01

Account Codes .v. Primary Keys
From the discussion it would seem that all we want to do is group Customers etc. and have them sorted within that group. Surely the correct approach would be to have a 'Classification Type' (table driven) and a 'Sort Name' - on both the Customer & Supplier record

This would allow any number of user defined 'Classifications' to be set up and associated with Customers or Suppliers in any sort order chosen.

With the above in mind are Account Codes still necessary?

Products have a Product Code anyway and Nominal's could have an Order Field associated with them

Daniel I think you will probably find that the Anagram guys are only changing a 'reference' and not the Primary Key - therefore integrity (history etc) is maintained because it is never an issue

Darren is quite right about Primary Keys, which once set up should not be alterable and probably not compound in nature - the need for compound has diminished with SQL rather than DBase (SELECT ORDER instead of position read next). Also the type of Primary Key depends upon the level of future integration anticipated - i.e. nowadays where data from one database may need to be merged with that of another, then the Primary Keys must not clash (sequential numbering does't work); one approach to avoid this is by using guids.

David - your last para demonstrates the classic problem with using Primary Keys (Account Codes) in circumstances for which they were not designed - and why the Primary Key should not be shown on the screen. Unfortunately people interpret it in different ways and try to use it to fit their requirements - this is a software question of displaying information that should be hidden. By all means have a separate way of indexing information (see above para 1) but be crystal clear on its purpose and usage.

Recalculation of stored totals only becomes an issue if the Primary Key can be changed. At which point there are more fundamental issues at stake than out of balance totals - because it then becomes very difficult to confirm the integrity of the overall database.

Thanks (0)
avatar
By Darwood
25th Feb 2005 11:21

calculating balances
As a programmer I have used both approaches. Calulating on the fly and storing the balance.
You have a tradeoff of speed against accuracy. I do prefer to calculate on the fly where I don't think speed is going to be a major issue.

Thanks (0)
avatar
By Darwood
24th Feb 2005 14:32

Enterprise DB
I believe Exchequer Enterprise uses Pervasive SQL.

Thanks (0)
avatar
By User deleted
08th Mar 2005 12:00

2 account codes
In database design every record in a table should have a unique primary key field. This is a unique ID for that record that should never ever change. That id number can then be used to link other records from other tables. To link the customer transactions to the customer record for example. This should be an internal number. Whether or not the user ever sees this number is debatable. You then have a separate field (if desired) for the account code that can be alpanumeric if you want.

Alternatively you could use the primary key as the account code if you don't mind giving your customers meaningless numeric account codes rather than rememberable alphanumeric account codes. I have several large suppliers who give me a numeric account code. Doesn't bother me.

The problem comes when the database designer decides to use an alphanumeric account code as the primary key. NO NO NO NO NO! Don't do it! Then the user has a nightmare if he wants to change the account code.

I wonder David if you include the ability to change any alphanumeric account codes (customer/purchase/stock etc) in your system evaluations. Also of note as already discussed - does the system calculate balances on the fly or store them in the tables? If totals are stored, are recalculation utilities available?

Thanks (0)
avatar
By dclark
08th Mar 2005 10:57

Two keys
David

Interesting discussion

Anagram Encore-eBis (yes we are a dealer)

1) gives you the option of changing customer, supplier, stock and nominal index numbers (ie a supplier code from MERC01 to CABL01 or a stock code Z-9989 to TG567, etc) and all history goes with it.......two keys - one for the user and one for the system

2) the searching part also works on the LIKE basis that JC remarked upon, for every field in the search (index, description, post code, address, etc) all definable by user

Kind Regards

Daniel Clark
Ryba Macaulay Ltd
[email protected]

Thanks (0)
avatar
By User deleted
21st Feb 2005 13:47

Db Engine or Implementation of Db
David

The main hurdle with the 'Total on the fly' approach is scaleability. Yes it will work upto a certain level of transactions and depending upon the delivery mechanism - for a desktop app you can probably get away with it.

However, it will hit a ceiling (especially client/server), at which point the whole thing falls apart because computer cannot service the requests fast enough.

For example take the case of client/server and 50 users all requesting a Trial Balance on a recod set of 100,000 records spread over 200 nominal accounts (response possibly acceptable).

OK - now scale that to an ASP internet delivery with 2,500 or 10,000 users all making the same request (say on 2,000 different companies within the same Sql Server or Other database) - bearing in mind that long response times over the internet are frowned upon - ouch! Extreem maybe or is it?

Some modern databases not only allow Commit/Rollback but also have the concept of Stored Procedures (routines residing within
the database) and Triggers (actions to take on a change of field etc). With all this armoury of inbuilt functionality why (in theory) is a data re-build necessary ?

My unsubstantiated feeling is that the one common area associated with these issues is the underlying Database engine used by all these products - or the implementation of that database.

With this in mind I have had a look at the Exchequer Internet Site (only because you highlighted it) to try and determine the
database engine being used - I have probably overlooked something but I cannot find any mention of the database, although I believe it to be Btrieve.

To play 'Devils Advocate' I would suggest that we need to be looking for a solution to this issue with the underling database in use and determine whether there is any pattern between software with data re-builds and the Db engine used.

In fact put together a check list for all Software Suppliers and correlate the database engine together with mismatched Totals and a re-build data facility.

Just to start the process there are two Software Houses which I believe (could be mistaken) meet the criteria:

- Exchequer - Btrieve
- TAS - Btrieve

The only way to really get to the bottom of all this is to engage the Software Houses themselves in dialog and directly ask them these questions.

Any takers from the software Houses ??

Perhaps as part of your evaluation process you should be asking about the underlying Db engine.

Regards,

Thanks (0)
avatar
By David Carter
18th Feb 2005 23:03

Hold balances on-line or calculate at run-time?
Daniel and JC. This is an interesting discussion. I used to believe that the ideal design of a package was to have an array of on-line balances sitting on top of the transactions. When the user is making an enquiry, the on-line balances would provide an instant response.

But it does seem that on-line balances will go wrong some time even in the best-written software. For example, Exchequer Enterprise seems to be a very fine package. Nonetheless it has a "Check" option on most menus - the purpose being specifically to recalculate faulty on-line balances from the transactions.

When I do the Lab tests some developers stress that they calculate all balances at run-time from transactions. This design was originally pioneered by Sun, I think, in the early 80's.

The benefit of this is that the balances are always correct. The downside is that it takes time. As JC implies, it used to be a joke that Sun took 20 minutes to calculate a bank balance on the TB.

But nowadays, with the speed and power of the the kit and stuff like Rushmore Technology in Foxpro, it seems that it is possible both to calculate totals from transactions at run-time and to achieve acceptable response times.

My impressions as a non-programmer are that:

a) a package which calculates totals from transactions at run-time is intrinsically superior to one that holds balances on-line,

b) the technology is now available to design a package this way while still maintaining acceptable response times

Thanks (0)
avatar
By dclark
14th Feb 2005 12:19

An amendment !
JC

I'll amend what I said to include your phrase "...because of the issues of keeping Totals in step, they should be used judiciously and kept to a minimum number..."

The problem with a number of systems at the mid to lower end of the market is that they have too many Totals and it is the problems of dealing with those fields and corruptions thereof that cause most problems. Of course if your database is so slow or you've written queries badly against the data, that is another issue, but I'd begin by limiting 'Totals' in the database...........after all (although I'm not a programmer) surely the process of retriving a total request has been done to death by programmers, so should be fairly easy to get right. Are users saying adding up a list of invoices is a long process for a well written piece of code ?

Kind Regards

Daniel Clark
Ryba Macaulay Ltd
[email protected]

Thanks (0)
avatar
By User deleted
11th Feb 2005 19:22

Totals - response times & other factors
Daniel - You are right up to a point

The reason for maintaining Totals in the first place to to keep response times acceptable and in this respect it is a trade off between response and the overhead of maintaining the Totals

Because of the issues of keeping Totals in step, they should be used judiciously and kept to a minimum number

Unfortunately any accounts software that did not maintain certain Totals would simply become unusable. For example when a Summary Trial balance is requested, without Totals one could potentially have a long wait before getting the results because each individual Nominal entry would need to be fetched and added up.

To a degree one could probably get away with it using todays Database engines (Oracle, SqlServer etc) but that is using the power of the tools to cover up a questionnable design in the first place and a lot would depend on the number of ledger transaction involved - it is not a pratical proposition to read 100,000 entries every time a Trial Balance is requested

Updating Totals is potentially the easy part - downdating (editing existing entries) is where things become rather more tricky, and when one throws in a multi-user scenario where record locking is not available (i.e. ASP solutions over the internet in a stateless environment) the whole issue becomes interesting

For instance, work out the steps involved in changing a supplier part payment under cash accounting - bearing in mind that overpayment is not allowed (combination of cash & credit notes) and that the original payment could have been allocated partially/wholly to many supplier invoices, Nominal & Supplier Totals need downdating (with the old value) then updating (with the new value), individual Nominal entries need to be changed, VAT elements amended (and whether already reported)... and so on.

Underlying all this is also the fact that the application needs to handle the fact that another user may have part paid some more on one of the invoices being edited whilst it is being edited and one has the receipe for a problem

Nevertheless, whatever the scenario, by using transaction commit/rollback out of sync Totals simply should not really arise - it is an all or nothing approach; either everything is written to the ledgers or nothing

Thanks (0)
Stephen Quay
By squay
10th Feb 2005 17:04

TAS Books 2
I use the latest version 4 of TAS Books 2 in multicompany mode for all my own and my clients' book-keeping and interim accounts. I have used TAS for over 10 years and think its excellent but every version does contain a bug or two which can usually be worked around.

Most people will only use the SL, PL and CB. Most stay away from the NL. I have noticed a bug in the NL that can cause a TB not to balance. When entering a journal in NL, when on the last line a short cut is to hit F10 and TAS will calculate the balance and enter it correctly as a debit or credit and then save it. Sometimes it can be too quick for its own good and not capture the last figure. Running a TB will show an imbalance and ask whether you want to autocorrect it. It's best to say no and print a report of the journals originating from the NL only - there probably won't be many. It should be difficult to pinpoint the corrupted entry then edit it and re-save.

I regularly check my TBs and always on exiting TAS. And then I always back up that data set. Frequent back-ups should be a habit.

Thanks (0)
avatar
By Stewart Twynham
10th Feb 2005 11:38

Supplier capabilities lacking
It never ceases to amaze me how suppliers (be they "top resellers" or even the software house themselves) can get things so wrong.

Reporting and integration is often where the cracks appear. Instead of being able to rely on the reliability of the application, there are occasions when you'll want to extract data directly from the underlying database. Examples might include using products like Crystal Reports or a custom intranet to report in exactly the format that your Sales Director needs. Alternatively, you may want your CRM or MRP package (or even your website) to retrieve or update accounts information in real time.

Yet, whilst the technology is actually very straightforward - the reality is far from it.

Take something as simple as a Gross Margin report, created in Crystal by the *manufacturer* of one major accounting system for one of our customers.

Despite months of assurances from the supplier that the report was sound, the client could never get it to work correctly - often showing ridiculously high or even negative margins.

When we investigated, we discovered (using the same drill down method as above) that the report was showing transactions that weren't on the GL. This gave the game away - the system appeared to have some form of "deleted" flag for items where the item had been deleted probably prior to update. They were still on the database, they'd just never actually been posted.

A bit of trial and error later and we'd found the offending column and had filtered out the "extra" items.

But this isn't an isolated incident. I see this type of problem at least once a month - where major resellers or integrators don't really have a handle on what's going on under the bonnet. And if the people that *write* the software cannot even get it right, what hope the rest of us?!!

Stewart
[email protected]

Thanks (0)
avatar
By User deleted
10th Feb 2005 15:21

Wrap Transactions (Commit/Rollback)
Error 2
Running total balances - these should be part of the original transaction and therefore wrapped with commit/rollback, ensuring they do not become out of balance

Example of Entering a Supplier Purchase Invoice:

Start Transaction

- Write Transaction Parent records
- Write Transaction Child records

- Write Nominal/General Ledger Details
- Net to Nominal Account
- VAT to VAT Account
- Gross to Debtor/Creditor Account

- Update Nominal Totals

- Update Customer Totals

End Transaction

If any one process inside the transaction fails then nothing is written to the files. With this in mind it is very difficult for Totals to become incorrect

Of course software that does not take this approach will be prone to having 'out of balance' ledgers and one of the questions asked in any software SWAT analysis should be about this issue

Thanks (0)
avatar
By anthonyryb
10th Feb 2005 15:45

Ensure you have a copy of your data!
Computer errors are a fact of life - regardless of whether it is human error, software/hardware or virus corruption!

When errors do occur the software provider may be able to help you put things right but in the worst case scenario you could be left high and dry.

Therefore it makes sense to backup on a daily basis and have a clean copy of the database available at all times.

Online solutions such as Depositit (www.depositit.com) automatically backup your databases and also store back copies so if you ever do need to go back to a previous version you can simply pull it back down.


Thanks (0)
avatar
By David Carter
09th Feb 2005 23:23

Irony
Yes, Stewart, you will be shot. Date quickly corrected before anyone else notices.

Thanks (0)
avatar
By dclark
11th Feb 2005 16:30

Totals
Ah ha !

I think it is a valid question to ask your software provider as part of making a purchase is "does your software record totals in the database or does it just record transactions?". This can often point to potential problems

Where a package has fields in its database that carry, for example, a total of invoices outstanding for a customer as well as fields that offer the values of individual invoices, we often find errors. This is because something happens to stop full writing of records(pc crashes, poor code, etc) that will result in the list of invoices not agreeing to the total field. One field is often used for a summary report (ie Balance in NL) and the other used for a detailed report (ie Aged Debt)

Where a package only holds the values of individual invoices and merely reports on the total in a screen view or report, we rarely find errors.

Kind Regards

Daniel Clark
Ryba Macaulay Ltd
[email protected]

Thanks (0)
avatar
By Stewart Twynham
09th Feb 2005 17:25

Re: Data Corruption
Will I be shot for pointing out the irony that an article on Data Corruption appears as being published on February 10th, but it's still only February 9th!

Computer Errors are part of Life - part 2
10-Feb - Data will become corrupted in every accounts package at some time or other. David Carter asks what are the commonest types of data corruption and how do you put them right?

:)

Stewart

Thanks (0)