You might also be interested in
Replies (3)
Please login or register to join the discussion.
Good Start
This is an excellent start to a complex issue.
I agree that the Size Column (when used to define anything other than text or numbers) should only be a guide line and not a hard and fast rule.
For instance when referring to Primary Keys (i.e. Account Code = 5 etc), different packages will adopt different methods of accessing their records - this would accommodate keys ranging from numeric or text through to GUIDS (128-bit number)
A description of the type of data expected should be associated with each field (i.e. text, numbers etc.)
It would also be helpfull to have an expanded description associated with each field, so that the 'layman' can understand its purpose. Say another column for explanation
Any Primary/Secondary key items should also be identified
What is the explanation behind the field naming conventions (col 2) - presumably the first 2 characters define the source table containing the source of record (i.e. GL = General Ledger - but what are the others?). Also in this respect one needs to be quite careful because all software packages have been designed differently and tables may not always line up with the field name prefix
Maybe an additional column defining the table where one would expect the data to come from (i.e. General Ledger, Cost Centres etc) - but not a mandatory issue.
Another perspective!
I agree that it is useful to have a file with all the likely candidates for reporting included. However the software that we use (which I persistently refuse to name as we are in negotiations with them over a horrendous installation experience!) gets over many of the common problems by having only ONE transaction file. Although this is in essence the G/L, every transaction carries just about every piece of information possible with it. This results in a G/L with 141 fields, as opposed to the 45 suggested, but means that from a single source one can access just about any piece of information, including who did what and when.
The selection process is from within the software; one can select, in a split include/exclude table, which fields are displayed on the screen and in what order, or drag them into the required order by the column headings. By double clicking the column header required, the order can be pre-sorted then, at the click of the mouse, whatever is on the screen is exported into Excel in the same layout.
The main weaknesses are that it can only be done for one account at a time (for more I have to resort to Pivot Table/ External Data), and they have prescribed WHICH fields one is allowed to access.
Although there is a lot wrong with the software in question, in this respect they seem to have got it right; why have multiple transaction tables when, with modern high-speed computers, one will do?!
Naming conventions coming up
JC, I've got an article on the stocks explaining the field names. Hope to publish it soon. Prefixes mean:
TL = field from the Transaction detail record
TH = field from the Transaction header record
Others are from the relevant master records (AC = from supplier/customer master; GL from GL account master, etc).
More details in the forthcoming article.
Richard, whatever their other sins, it looks like your suppliers have gone ahead and created an analysis ready file already! But it does prove the point, doesn't it? In practice, no vendor can ever write all the reports that users might want. But as long as they make all the data available for export into Excel, the users will be able to customise their own.