M3 HSW Brainstorm
On 21/5/9 Harrow hosted a session where 15 London & South users debated options for using
M3 in better ways for HSW work. Here are Stephen Tribbick’s notes from the day and
references to his Crystal Reports you may wish to use, after linking to your data and adjusting
for your coding structures. (Written in Crystal v8.5 for Oracle data and downloadable from
Trading name v Registered Company name
A business may have the trading name KFC on the shop-front but be run by ABC Franchise
Ltd. M3 has a field for the trading name but not one that is as uniquely identified for the Legal
Entity running that business. We have a variety of ways to record this:
1. If you insert the company name in the SEARCH tab for the property then it will be found
when doing a search of the database for them, in the same way as it is useful to add variant’s
of names in common use eg where the actual Trading Name is “THE ROYAL OAK” you can
add “ROYAL OAK” and perhaps also the previous pub name “THE OLD NAG”
2. Adding the company name as a RELATED ADDRESS allows you to give the full name
address and phone. Classify this consistently with a RELATED ADDRESS TYPE code of your
choosing eg HQ. This can easily be seen on screen, and may be usable in Crystal Reports.
But it will NOT be usable in M3 WP unless you do step 3.
3. On the property screen, pick the menu item PROPERTY INDEX / USER DEFINED
ADDRESSES. This gives you 4 possible slots for addresses that are easily accessed in WP.
In one of them type in the related address code you have used eg HQ and then click the
magnifying glass and select the relevant related address.
This will now be usable in a WP document based on the PROPERTY “application” under the
fields like USE1xxxx. So you’ll need to consistently use slot 1 or whichever for it to be useful.
One of those things where a local agreement between users is obtained eg slot 1 for main
office for the trader, slot 2 for registered office, slot 3 for keyholders and slot 4 for anything
If you want the use this address from a worksheet related WP document then repeat this step
3 but for the 4 extra slots available for that particular worksheet. The starting menu item won’t
be PROPERTY INDEX but something like COMPLAINTS EH ie whatever the worksheet
4. Note also that in the code maintenance for Task Type you can specify up to 4 linked
address types (eg HQ) that will be pre-loaded into the User Defined Address fields when the
worksheet is created. These are then available in WP. So for example, you can specify that
any Alarm Noise Worksheets get a copy of the Keyholder data held as a related address on
Closing businesses (and the licensing component)
When a commercial premise is found to have closed down then the M3 Property should be
closed by going via the menu PROPERTY INDEX / CLOSE PROPERTY
Enter a date near to when you guess it actually ceased trading and your initials. This will then
mark the record as CLOSED, remove any future programmed inspections and remove any
If the building has been demolished then that’s all you need to do.
When a new business starts, create a new PI record with identical address (so it links to the
correct gazetteer entry) and all the relevant codings. If the old business had a licence that
lives on then it is easy to move it - go into the closed record and open the licensing component,
use menu option LICENSING DETAILS / LINK then pick the new property to attach it to.
Monitoring closed properties
If you want to spot new businesses soon after they open then you’ll could to monitor planning
& building control applications, liase with Business Rates and drive by all your vacant
properties periodically. To generate a listing for that purpose you will need a Crystal Report
that lists all closed commercial premises where there isn’t an open record at the same
corporate UPRN. But this report would also give you locations where the buildings have been
demolished or merged. An extra level of sophistication can be to invent a user defined code
for what’s happened since closure, and filter out demolitions etc from your crystal listing.
Alternatively, if the property is likely to reopen for trading under new ownership in future then
you could immediately create a new PI record with identical address (so it links to the correct
gazetteer entry) and give it a trading name like “VACANT” or “AWAITING NEW OCCUPIER”.
Give this an index type of COMMERCIAL. Now it will be easy to run off an annual list of all
these “placeholder” vacant commercial properties to drive past
Businesses from home
It isn’t normal to CLOSE a domestic property record, so if a business run from home is added
directly to the domestic record (and the Index Type set to “MIXED”) then what would you do
when the trade ends or the householder moves? You could delete all the commercial codes,
but that then looses historic data. A better routine would be to keep the domestic record
entirely for the ongoing domestic use and create a separate commercial record with a status of
PART PREMISES for recording the life of that business. It can then be CLOSED without
affecting the domestic record.
Spotting errors in data
It is impossible keeping all your users consistently amending data correctly so mitsakes will
creep in. A suite of Crystal reports tracking the common problems is usefully run each quarter
or so. For example:
1: Trading name but INDTYPE not Commercial or Mixed
2. Commercial or Mixed but no trading name (showing latest action)
2a. Commercial or Mixed but no trading name (where there are no actions)
3. Commercial or Mixed but no EH Main Use
4. Commercial or Mixed but no HSW category
5. HSW Category 1-9 (ie LA enforced) but no HSW component or risk rating blank AND
no worksheet allocated
6. Commercial or Mixed and out of borough
7. Commercial or Mixed and not linked to gazetteer
8. C or M and linked to gazetteer but no GIS values
(See HSW database debugging report)
Incidentally you may wish to force users creating a new property index record to specify a main
eh property use. This is turned on under admin / system config /lpg (tick the first option)
Notes on last inspection
Before doing an inspection it is always necessary to look at the history of the business
especially points the previous inspector emphasised. These may be in action comments or
documents attached the previous inspection worksheet, or in a User Defined Text line for that
worksheet or for the property (overwritten each visit) – so tracking them down can take a while.
You may wish to ask all inspectors to consistently add a key paragraph about this on their
inspection worksheet in the DETAILS or MESSAGE before they complete that worksheet.
It is good to have a Crystal report that can quickly show or print a summary of all the actions
and worksheets attached to a property eg for when you get an ombudsman enquiry. See
Premises History report.
Use code v SIC 2007 codes used by ICC
What codes do you use for classifying your property uses? The Statistical Office publishes a
set of Standard Industrial Classification codes that are used by ICC and for various
government depts. We use the 2003 codes with some extra sub-divisions where they weren’t
precise enough for our needs. But a new set were published in 2007 and I haven’t got the
energy to change over!
Listing all commercial premises within 100m of x for a blitz
We do blitzes on a small area of the borough from time to time. So I’ve developed a few
crystal reports that list all the businesses, their basic data and most recent risk ratings. For
one of these you need to type in a list of the street codes. Another works off the GIS easting
& northing data and allows you to enter the grid reference for the centre of the patch and
specify its radius. Both reports also try to analyse the POSNREF value to group properties
according to which side of the road they’re on odds / evens and not sure. See HSW CPOE
with ratings in street Ids.rpt and HSW CPOE with ratings within x of y.rpt
Allocating risk based inspections
The Modules / Inspections Due process of allocating prioritised inspections allows you to pick
by previous risk rating and AREA. But have you classified your commercial areas in a useful
way? I wanted to be able to quickly see the team (HSW or Food or Licensing) that generally
deal with the premises AND which geographical ward they fall in. So have recently recoded all
5000 commercial premises so that the commercial area code has an initial letter for the team
then 3 letters for the ward eg FBEL for one in Belmont Ward dealt with by the Food Team.
This speeds up allocation of jobs in a tight area.
I’ve also created a few dummy officers eg HSWA = Priority A HSW visits and allocated the
year’s relevant visits to it. Real officers can then easily see what is outstanding and when they
have some capacity can reallocate some of those visits to themselves.
Allocating project worksheets
Shame you can’t pick by Property Use too. Doesn’t appear as a filter field on the inspections
due screen. And if you pick a batch of properties from the Property Access screen then you
can’t do a batch creation of Inspection Worksheets. Anyone found a quick way of creating of
VSEN or VSEH worksheets to all the Builders Merchants in the borough?
Priority activity recording
It takes a bit of setup, but M3 can readily record your special projects – Fit3, Smoking etc. You
will need to create a separate activity for each question / test / parameter. And the coded
options for the answer as well as some free text and number. Link the question to possible
answers. If you tick the box “Use in Template” then all the possible inspection activites for that
type of worksheet will be added at one – quicker than picking 5 or so activities. Just fill in the
ones you want and then “delete all incomplete” ie the unused ones.
In case you haven’t done these before, the stages are:
1. Admin / Code Maintenance / EH Inspection Activity Response – create all the possible
answers you need eg Yes, No, Red, Over 5 etc. NB These responses can be used as
possible answers to many questions.
2. Admin / Code Maintenance / EH Inspection Activity – create an item eg WFA for the first
question on Falls:
3. Specify the possible activity questions linked to a particular task using Admin / System
Config / EH / Insp EH / Insp activities
How can you more easily check you team members are keeping up with their targets? My
HSW Performance.rpt takes 20 minutes or so to run, but they shows lots of useful data for your
monthly meetings and at the end of the year.
Accident / HSW / Food requests received in period - showing latest ownership of
worksheet. Includes worksheets in Team in-trays not yet allocated to an officer.
Accident / HSW / Food request worksheets completed in period
Accidents received in the period plus work done on cases carried over from past years
showing number of cases and % meeting first response target
Accident worksheets started in period number by HSW class
Accident worksheets started in period number by latest officer allocation
Actions on accident worksheets by officer
List of all accidents with action history
HSW complaints - number received by each officer with % meeting first response target
HSW type actions by officer
HSW type actions by HSW category
HSW inspections completed - by Officer and category BEFORE the inspection
HSW inspections completed - by Class and category BEFORE the inspection
Officer performance pages - numbers of worksheets completed in period and rate per
month for selected officers
All revisit worksheets by selected officers
Open workload numbers
Notices lacking a Served Date
Overall performance for meeting the inspection targets for differing risk ratings that have
been "hard coded"
I’ve never had much success with Northgate’s annual return reports. So use a combination of
the HSW Performance.rpt described above and HSW Annual Statistics Pt 1.rpt. This shows:
Number of commercial premises by Class and Risk Rating
Number of premises by Class and Index Type
Total numbers of HSW inspection Worksheets done by Officer and previous rating
Total numbers of HSW inspection Worksheets done by Officer and new rating
Number of revisit worksheets by category
Mailshots via M3 WP or Crystal Reports
Mailshots done with M3’s WP are great, and can be done in batches with copies saved on
those multiple worksheets / properties. BUT they can be limited in the fields you can use. So
Crystal can be used for mailshots that are more complex eg Scores on the Doors certificates.