For those of you who have read Insightful Accountant over the past years you will remember my ‘Data Detective’ stories which followed the pattern of a Sherlock Holmes style mystery. This article takes a different approach by looking at the ‘detecting’ aspects of the problem rather than ‘the story.’ Instead of starting with our own mystery, we begin today by looking at one of Agatha Christy’s stories brought to film one more time in 2017.
Kenneth Branagh portrays Hercule Poirot in the 2017 film adaptation of Murder on the Orient Express (Credits: 20th Century Fox)
I recently watched the re-make of Murder on the Orient Express1, the one in which Kenneth Branagh plays the lead character. During one episode of questioning of a witness the great detective Hercule Poirot concludes that the Countess Andrenyi had intentionally altered her passport by smudging the first character of her name so as to make her name appear as ELENA rather than HELENA. It just happens that a women’s handkerchief with a monogrammed letter ‘H’ was found at the scene of the murder.
This was certainly not the only case of mistaken identity in this detective story as the majority of the train passengers had in one way or another masked their monikers. The point I am making with this reference is the importance of a ‘proper name.’
Once Poirot had figured out the true identities of all the passengers it made it quite simple to conclude, with the other available evidence, that the murder was indeed committed by 11 of the passengers plus the train’s Conductor. Without having established the proper name of each individual it would have been almost impossible to establish their common relationship that led to the conspiracy.
By now you must have figured out that I am speaking of the importance of a ‘proper name’ and in this case the proper name of a QuickBooks Company file. To identify proper company file names, we must understand how most company files come by their names. Unlike newborns whose parents decide on their name, document it in hospital records, and finally the birth certificate; QuickBooks Company file names arise out of accident.
Most QuickBooks Company file names are created when QuickBooks users enter the ‘name’ of their company in the company information records of QuickBooks. In some cases, this may be the ‘legal name’ or the ‘doing business as’ name. It reflects the name that the users want displayed on the various QuickBooks forms like Invoices, Statements or tax forms. QuickBooks automatically uses this information to set-up the name of the Company file and appends that name with the .QBW file extension.
The problem with this approach is that nothing assures that the Company file name complies with the naming convention requirements which Intuit says are required for Company file names. While Intuit will tell you that your QuickBooks (Desktop) Company file names:
- Should contain only alphanumeric characters
- Must Not contain special characters of / , - . ( ) < > ~ ` [ ] { } ? ! @ # $ % ^ & * + =
- May contain space(s) and underscores _
- Must Not contain more than 64 characters, 4 of which are the .qbw extension
- And that the complete file path for the company file must be 256 characters or less
The set-up process in no way insures compliance. Some users may alter the file name to be compliant after initial set-up, but clearly most do not.
But even if we followed all of Intuit’s naming convention requirements the question still arises of a ‘proper name.’ This is because there are conflicts between Intuit’s naming convention requirements and the recommendations that Sybase makes for ‘Sybase database’ names.
Since Intuit uses Sybase as the database structure for QuickBooks (desktop) products, it makes sense that the more restrictive Sybase recommendations should apply including:
- Data-file names should never exceed 30 characters, including the file extension
- Data-files should have no spaces or special characters of any type including underscores
Because the database is first and foremost a Sybase-structured database we really should make every effort to comply with these more restrictive naming requirements.
Now you maybe wondering “what happens if we don’t?” That’s a great question and one that only experience can answer.
While it is possible that a company file named in a manner that ignores one or more of the naming convention requirements may open and appear to have no problems while in use by the QuickBooks application, we have learned over the years that sooner or later issues will ultimately arise that may impact performance and even data integrity.
Therefore, we (aka: 'Murph') always suggest that Company files be reduced to the least number of alphanumeric characters, without ANY special characters whatsoever, possible that can differentiate the file from any other file.
As an example, the John and Betty Hodges Irrevocable Trust for Jimmy Hodges, David Hodges and Susan Hodges should never be a QuickBooks .QBW Company file name. While it maybe the legal name of the entity you are using QuickBooks to account for, I would definitely shorten this name to nothing more than HodgesIrrevocableTrust.QBW which is plenty to describe the file. But my preference might be to simply call it HodgesTrust.QBW. In fact, I think we have definitely ‘hit the nail on the head’ if we simply call it HIT.QBW.
Remember we only need to know the ‘true identity’ of the file from the windows file name, beyond that the mystery lies therein for your detecting.
1 – a 20th Century Fox film (2017); Directed by Kenneth Branagh, Produced by Ridley Scott, Mark Gordon, Simon Kinberg, Kenneth Branagh, Judy Hofflund and Michael Schaefer; Screenplay by Michael Green; Based upon ‘Murder on the Orient Express’, the 1934 novel by Agatha Christie.