How to speed up your ERP implementation

1.  Management buy-in!!  Into the ERP system and the implementation process.  (I cannot stress this enough)

2.  Map your existing system’s database fields with that of the new systems’ as soon as possible.  Attempt to automate the ‘re-load’ of data.  Then make the data visible to users – they’ll love seeing “their” data.  (This will improve Change Management).

3.  Make key users responsible for Master File integrity.  Yup, accountability!  A pain, but worth it.

4.  Make the key processes work first – then the bells and whistles.  Document them, set target dates and track them!  Have the key users test them.

Informix table names vs Sage Line 500 table names

I’ve come to know these table names by heart (by using them over the last 13 years) –  and (still) frequently use them in sentences..

This is more for me to remember.

Informix > Sage Line 500 name:

bmhstm > BOM History
bmwodm > BOM Detail
bmwohm > BOM Header
cbbankm > Cash Bank file
cbbatchm > Cash Batches file
cbtranm > Cash Transactions file
ceaddrm > Customer (shipper_code1) Addresses
cecaldrm > (Financial) Calendar
cenatypem > Narrative Types
cevatregm > VAT Register
nlaccm > Nominal Account File
nlmastm > Nominal Master File
nltranm > Nominal (GL) Transactions
opaudm > Sales Order Audit File
opcosm > Cost Of Sales Matrix
opdetm > Sales Order Detail
opheadm > Sales Order Header
oplastprm > Sales Order Last Price
oplistm > (Sales Order) Price Lists
opsahistm > Sales History Transactions
opservm > Sales Order Services
plitemm > Purchase Ledger Items
plpaym > AP Payments
plsuppm > AP Suppliers (Creditors)
podetm > Purchase Order Detail
poheadm > Purchase Order Header
porecp2m > Purchase Order Receipts
porecpm > Purchase Order Receipts
slbankm > Customers (Debtors) Bank
slcashm > Customers (Debtors) Cash
slcustm > Customers (Debtors) Master File
slitemm > Customers (Debtors) Transactions
stallocm > Stock Allocation (to Sales Orders)
stkhstm > Stock Transactions
stkwhm > Stock Warehouse Master File
stockm > Stock Master File
stquem > Stock Batches
sysdirm > System Keys

Let me know if you need to write a report on these.


How to reduce Informix db space with Sage Line 500

Make a note of the output of onstat -d to monitor the effect of your actions.

Ensure exclusive access – preferably with Informix transaction logging off.

Informix transaction logging off:  ondblog nolog <database name>            then      onbar -b -F

As always, make sure you’ve got a decent backup before you start.

Log into Sage – into the Company you need to reduce the space of.  Exit to shell.  You should now be in the company directory by default (check System Manager > System Administration > Company Management > Working Directories).

Test a small table first, e.g.:

unload st_warehouse > stkwhm.unl

Now make sure the unload file (in this case stkwhm.unl) contains the field names at the top and data below it.  Then do

load -cD -x 10000 st_warehouse < stkwhm.unl

The ‘10000’ can be adjusted according the the number of lines loaded at a time – increase for larger tables.

Be sure to update statistics once done!  E.g:

update statistics for table stkwhm;

And lastly, remember to switch Informix transaction logging back on.

Informix transaction logging on:  ondblog buf <database name>            then      onbar -b -F


How to create a view based on Syspro’s AdmFormdata

In Syspro, it is very handy to add as many custom fields as you want – but then to report on these fields could be a pain. 

I found it easiest to create a view based on AdmFormdata (where the custom field data are stored) – and then report using only the view.

For example, if you are storing and capturing stock dimensions (Length, Width, Height, etc) – your AdmFormdata might look like this:

FormType KeyField FieldName AlphaValue NumericValue
STK A100 DIM_L    10
STK A100 DIM_W    20
STK A100 DIM_H    30
STK A100 DIM_M    40
STK A201 DIM_L    11
STK A201 DIM_W    22
STK A201 DIM_H    33
STK A201 DIM_M    44


–  ‘FormType’ indicates the data is connected to Inventory (InvMaster) and will always be ‘STK’.

–  ‘KeyField’ contains the InMaster primary key = StockCode (in this case two stock codes A100 and A201)

–  ‘FieldName’ is the (user defined) fieldname e.g. DIM_L = Length, DIM_W = Width, DIM_H = Height and DIM_M = Weight (Mass)

–  ‘AlphaValue’ not used in this case – but can be used for text/alphanumeric values.

–  ‘NumericValue’ is the field value.


Now I would create the following simple view:

CREATE VIEW StockCodeDimensions


SELECT KeyField AS StockCode,


 THEN NumericValue



 THEN NumericValue



 THEN NumericValue



 THEN NumericValue


FROM AdmFormData

WHERE FormType = ‘STK’ AND (FieldName LIKE ‘DIM%’)

GROUP BY KeyField;


 Resulting in the following table:    (SELECT * FROM StockCodeDimensions)

StockCode Length Width Height Weight
A100 10 20 30 40
A201 11 22 33 44


Note that I used  ” FieldName LIKE ‘DIM% ” in my query above.  This can simply be modified/copied to create a view for (say) ‘ANA’ if analysis codes are used to specify (eg) stock code category, colour, age group, chemical restriction, etc.


5 Tips on how to choose an ERP system

Based on recent experiences:

1.  Give yourself enough time.
Usually, companies replace/upgrade their ERP/Accounting systems only when they have to –  and the subsequent implementation date is then usually, yesterday.

In my opinion, you need at least three months to evaluate your options.   Make time, it is a big, expensive – and painfull – change.

2.  Know what you want.
Yes, sounds obvious, but it is very easy to be distracted by new bells, whistles and possibilities.  Make sure your core requirements (“pain points”) are fulfilled first – then look at the niceties.  Also, a fixed set of criteria simplifies comparisons (‘apples with apples’).

Customization/expandability in software systems is always an important criteria.

3.  Let the users help decide.
Get your key system users involved right from the start:  thoroughly sell  the reason for the upgrade/replacement, have them attend function specific demos and draw up a ‘User experience’ comparison afterwards.  Not only does this take the pressure off the (usually) sole decision maker – it also improves change management dramatically.

4.  Have a demo – and a database – installed.
If you’re a hands-on system admin (like myself) – you’ll be able to learn a lot from – and evaluate/compare very quickly – a new system, just by looking at its database structure.  First prize would be to have a working demo front-end as well, push for this!  Then test your core requirements – or have the vendor demo it for you (and your key users).

5.  Make a few calls.
A quick and cost effective method to obtain an industry opinion of your options.  What does your customers use?  Is there enought support around?

Let me know if you need help!

Sage Line 500: Debtors Aged Analysis do not balance with Debtors Control Account Trail Balance

Here’s the first test:

SELECT transaction_group, SUM(amount) amnt FROM slitemm WHERE slyear = “2013” AND period = “01” INTO TEMP slitemtmp;

SELECT transaction_group, SUM(journal_amount) amnt FROM nltranm WHERE nlyear = “13” AND trans_period = “01”  AND posting_code = <your Debtors Control Account here> INTO TEMP nltranmtmp;

SELECT s.transaction_group, s.amnt, n.amnt FROM slitemtmp s, nltrantmp n WHERE s.transaction_group = n.transaction_group AND s.amnt <> n.amnt;

Use the resulting transaction group to identify which transactions in slitemm doesn’t match the corresponding transaction in nltranm.  If the above returns nothing, then use slxrefm to check if the allocations was done correctly.

The two should rarely differ.  I had an instance where the system key LOCALDEC was (temporarily) set to 1, then changed back to 2 – resulting in a decimal difference.



Sage Line 500: Close Adjustment Period – incorrect balances error

While going through the General ledger Year End procedure – I came across the following error while running ‘Close Adjustment Period’:

“Number of Accounts with incorrect balances : 7056 see System log”

The system log shows the following (sample):

%% invalid_nldirect %% posting <your posting_code or account_code here>

%% invalid_nlsumm %% posting <your posting_code or account_code here>

%% invalid_nlstatus %% posting <your posting_code or account_code here>


Turns out that the fields direct_posting, summary_posting and status in tables nl_account (nlaccm) and nl_post_code (nlmastm) only likes Y/N in caps, not lower case – or blanks.

Pastel custom reports – easy!

An accountant friend of mine asked me to do a ‘Monthly Product Sales’ for his client working on Pastel Partner.

I’ve never seen Pastel’s database layout before – but picked it up very quickly using the ‘Pervasive SQL Control Centre’.

Within an hour I could show the (customer) their monthly sales (by units and/or values – for the last two years) in an Excel Pivot table – with the customer and/or product in the filters.

The nice thing about using Excel’s Pivot tables – is that one can easily ‘drill down’ to invoice line level.  We could immediately pick up where/when exchange rate calculations were skewd by misplaced decimal points..

The accountant was quite delighted to find out that the same report can be used on any of his clients’ Pastel databases.

Let me know if you require any Pastel custom reports (in Excel or on the web).



Regular master data in Excel – penny wise, pound foolish

Are you regularly getting ‘master data’ in Excel – and then capturing it in your accounting/ERP system – or even worse:  keeping it in Excel?  Please stop!

I’ve done some work for a debt collection company the past few months.  They receive their ‘master data’ every month in Excel.  New/old accounts, new balances, new client info, etc.  I then had to report on changes in balances (graphs) and business ‘growth’/’decline’.

The ‘quick fix’ was to handle everything in Excel:  copy-and-paste new data into a ‘summary’ workbook and then run multiple vlookups.  This method was ok at the time – since the sustainability of the business was unsure.

But this has been going on for almost a year now..  Plus, any ad hoc queries would take time (money).

If I had to do it all over – I would’ve immediately designed an integration app that would (auto) load new data into a database and run a series of scripts to (auto) generate reports.  It would’ve saved myself some time and the customer some money.

If you’re getting Excel sheets (or CSV or ‘flat’ text files) from your customers, suppliers, transporters, etc and would like to integrate it with your account/ERP system – or run an independant system:  please contact me – I’ve learnt a few lessons..