All posts by Henry Dale

My name is Henry Dale and I have been helping companies to implement project-based ERP systems for over 16 years. I have been involved in numerous full lifecycle implementations, including five different shared-service projects, and dozens of individual company migrations.

Is this you?

Are you are a Senior Executive at a growing organisation with a fundamental problem that is limiting further expansion? Are your business systems — that were adequate a few years ago — no longer fit for purpose?

Is your task is to overhaul, upgrade, and modernise those systems?

Have you have taken a look at the marketplace and found dozens of different products and services, and wondered how you choose what is right for your business, and how you go about implementing it?

You need a systems mentor; someone who can help you analyse your needs, evaluate and choose one or more software packages, recruit and form a team suitable for implementing it, and guide you through the implementation process.

My philosophy is: you shouldn’t hire consultants to evaluate, choose, and implement systems for you; you should do it for yourself.

My aim is arm you with the tools and data to make your own informed choices, and then implement those choices for yourself.

Call me today for a no-strings chat about how I might help guide you through the minefield of choices; together we can help you save time and money, and arrive at an outcome that fully fits your requirements.

The basic SQL select statement

Most modern ERP and accounting information systems are built on SQL databases, and as a Systems Accountant, you must have at least a basic grasp of SQL.

The scope of even a basic grasp is beyond a single article, and there are plenty of resources already out there on the internet (for just one example, see www.w3schools.com), but here’s what you need to know:

SQL select statements

You must be able to use simple SQL select statements to query the system you are working on, in order to quickly and efficiently extract data that is useful to the many data-related tasks you perform as a Systems Accountant.

The most basic form of select statement is:

SELECT * FROM table

With this, you only need to know the name of the table you wish to query. Once you run this script, you will be presented with a list of all the field names in the table, which you can use to cut the query down to concentrate only on the data you are interested in:

SELECT field FROM table WHERE condition = value

for example:

You know your system contains a table called “jobs”, so you run the query:

SELECT * FROM jobs

which returns a large table of data. From that, you can see that the field you are interested in is called “jobname”, and you want to select it based on another field called “jobnumber”. You are now in a position to refine your select statement to something like:

SELECT jobname FROM jobs WHERE jobnumber = 1234

Although this is a trivial example, it explains the sort of process I might go through when I am feeling my way around a new database. One modification I would probably make to my initial query is to limit the number of lines it returns by using the following format:

SELECT TOP 5 * FROM jobs

This will limit the return to five rows, which is often a wise idea when dealing with a large database, or one you suspect contains large amounts of data.

There is only one way of getting to grips with select statements, and that is by using them. If you don’t already do so, you should start using SQL selects at the earliest opportunity. The select statement is non-destructive so you can play around with it as much as you want without causing damage, other than by tying up system resources with statements that return large amounts of data, and to guard against that you can use the TOP clause to limit the number of rows in the return.

Further reading:
Select (SQL)
sql select
where

Some best practice rules for system implementers and administrators

Here, in no particular order, are some general rules I consider to be “best practice”.

  1. If you are logged into a system, never leave your computer unlocked if you leave it unattended.
  2. If you are logged into a system, close all windows before you leave your computer, so you aren’t locking records that other people might want to work on.
  3. Data is out of date as soon as it is extracted from a system, so it should be used immediately or discarded. Never use an old file if you can generate the data freshly. There are always exceptions to this rule, but always bear it in mind when you look up data in old spreadsheets.
  4. Put the date in the name of extracted data files so you can see when the data was generated. Use YYMMDD format so they can be sorted by name and still come out in date order – for example 121015_10_JobHeader.xlsx is a file containing Job Header information for Company 10 that was extracted on 15 October 2012.
  5. Before importing a data file, always check its contents. If you are distracted for any appreciable length of time between checking and importing, check it again. To adapt an old engineering tip: check twice, import once.
  6. During data importing from a folder containing many files, when the import program presents a file browser for you to choose the data file, sort the list by date modified so you can positively see your file at the top of the list (assuming it is the last one that you edited, which it should be). This routine takes a few seconds at most, and the consequences of not doing this can be a lot of lost time in unravelling an unintended import.
  7. Always save source data in Excel files so that all formatting is maintained. You will understand what I mean if you have ever opened a .txt source file and had Excel remove all leading zeroes, convert sort codes to dates, convert long alpha-numeric client codes to scientific notation numbers, and so on.
  8. When filtering data in a spreadsheet with Freeze Pane on, hit Ctrl-Home to make sure that no data is obscured under the frozen area.
  9. When debugging an import file, make sure you close the error file before reimporting.
  10. Before importing changes to existing records, make a copy of the records – you can use this for two purposes:
    1. Comparing the data before and after your import to check the changes have been made correctly
    2. Recreating the original data if you cock-up and your import does things you hadn’t planned it to.

    This article will tell you more about comparing data.

  11. Immediately after running a successful import, clear the input file field. The reason for this is that the file name refers to a temporary copy of your import file, which is sitting on the server, and it is still ready to go. So if you accidentally press return, the import will run again straight away.
  12. Do NOT hide columns or lines in Excel data files – you may forget that data is hidden there and save the file as text to import (the hidden data will be saved in the text file without your knowledge), and if you copy a line and paste it elsewhere, there is a danger of field values being transposed to other fields.

Useful Custom Cell Formats

There are a few Excel cell formats that I use over and over again, but I never remember the exact syntax, so I put this page together primarily so I could look them up myself. I then added some other formats that I thought might be helpful to people just starting out in Excel.

Accounting Format

#,###.00_);[Red](#,###.00);-_)
#,###.00_);[Red](#,###.00);-_);[Red]"Invalid – numbers only "@ Continue reading Useful Custom Cell Formats

What Makes a Systems Accountant?

A Systems Accountant (SA) is a jack of all trades and a master of some; prepared to turn his or her hand to any aspect of getting a system live and keeping it running. He or she is trainer, analyst, PM, developer, runner, author, data cleaner, converter, build manager, troubleshooter, lateral thinker, problem solver, explainer, diviner, mind-reader, demonstrator, experimenter, and pioneer.

There is no particular formalised training path for the role of System Accountant, but there are essential elements for anyone wanting to become one.

Continue reading What Makes a Systems Accountant?