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.

Today I found that the Sage Line 500 table plpaym (P/L Proposed Payments) uses the item and our_reference field in the first line of the table (where seq = ” “) as date fields (must be dd/mm/yy format) to validate posting to the right Calendar period.. (!)

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).

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..

The following is a useful query for extracting the current (active) promotional prices from a Sage Line 500 price list table (oplistm) – where (say) ‘MN’ is the main selling Finished Goods warehouse in stockm and all prices should be shown as inclusive of VAT (14%):

select p.price_list, p.product_code, s.long_description, round(s.price*1.14,2) normal_price,
CASE
WHEN p.vat_inclusive_flag = “Y” THEN p.price
WHEN p.vat_inclusive_flag = “N” THEN round(p.price*1.14,2)
END promo_price,
p.price_start_date,
p.price_end_date
FROM oplistm p, stockm s
WHERE p.product_code = s.product AND s.warehouse = “MN”
AND p.sequence_number <> ”  ”
AND p.price_start_date <=today
AND p.price_end_date >= today
ORDER BY p.product_code, p.price_start_date;

You’re busy with a Cash Batch and your PC goes off or you’re disconnected from the server for some reason.  Now when you want to re-access the batch – it is locked?

From the database side:  DELETE FROM cblockm WHERE batch_no = <your_batch_no>;

For years I’ve had trouble finding the correct Informix table (name) used by Sage Line 500’s Data Dictionary – until I discovered this nifty trick:

Note:  Requires one to have Demo/Test company installed – accessing a database differently named to the live/production db.  I work in a SuSE Linux Enterpise Server (SLES) environment.

1.  Access the desired Sage screen/window.  (e.g.   if you’d like to know which table stores Sales Order Processing order detail – then go into any example order’s detail screen/window). 

2.  Open another session, log in as Infomix and run ‘onstat  -g sql’.  This will show all current database(s) threads.  Most of these will (might) relate to the production db’s processes – so look for the process with the Demo/Test db’s name in it.  Make a note of the process number (integer number on the left).

3.  As Informix, run ‘onstat -g sql <process number>’  (e.g.  ‘onstat -g sql 16342’) and look for the table name in the output (usually after ‘from’). 

Let me know if problems!

I could not believe how easy this was!

1.  Set up an ODBC link to your MySQL database.

2.  In MS Access, right-click on the (source) table – ‘Export’ – ‘ODBC Database’ – and choose ‘Machine Data Source’ – select your Data Source Name – OK!

Export to Mysql via ODBC

Thats it!

Sure, it takes time to load (around 20 lines per second – depending (mostly) on your connection speed) – so only use for small tables – but still very convenient!

If you need to do larger tables – rather Export to Text or Excel (and save as .csv) – and use LOAD INFILE – but be prepared to change (among other things) the date format.

I managed at least two company-wide stock takes every year for the last 15 years.  Here’s a few tips I’ve learned to reduce stock take time:

1.  Break a large warehouse up into smaller, manageable stocktake ‘areas’.   Have stock take teams take responsibility for ‘their’ area’s accuracy and speedy completion.

2.  Which area holds the highest stock value?  Which has the most number of lines?  Use this info to assign the number and quality of people to stock take teams.

3.  Identify count vs. system differences (within each area) as quickly as possible – and have the responsible teams re-check!

4.  Find a way to monitor and control the progress of counting in the individual areas – and combine this info to get an overview of the whole stock take progress.

5.  Incorporate stock value into the above system – thereby allowing one to continuously see the ‘Nett effect’ of the whole stock take.  What is the overall ‘loss’ or ‘gain’ – and which area contributes the most toward this?

Let me know if you need assistance!

It was a struggle. Here’s the shortcut:

1. Download the IBM Informix Client SDK3.50 – I used version 3.50.FC8 – for operating system Windows (64bit).
I found that the Infomix Connect 2.90 (choose the 64 bit option) also works – but you’ll have to run it as Administrator on Windows 7.

2. Edit c:/Windows/System32/drivers/etc/services and add

sqlexec 1526/tcp #

on the last line. Note the hash(#) at the end of the line.

3. Edit c:/Windows/System32/drivers/etc/hosts and add

(your Informix server IP) (your Informix server name)  e.g

12.345.67.89      informixserver.com     #

on the last line.  Again, note the hash(#) at the end of the line.

NB:  Make sure you define your host (e.g informixserver.com) in SetNet32!  (part of IBM SDK install)

Note:  If Windows 7 complain that you you’re not allowed to save or make changes to the file ‘services’ or ‘hosts’ – try opening Notepad as Administartor (Swift + Right-click) – or even copy the contents of the (say) hosts, rename the old file (hosts_old) and past into a new file (hosts).

4. Create a file (say) your_data_source.dsn in C:/Users/YourMachine/AppData/Roaming/Microsoft/Queries with contents

[ODBC]
DRIVER={IBM INFORMIX ODBC DRIVER}
DATABASE=your_informix_database
HOST=informixserver.com
SRVR=your_server
SERV=sqlexec
PRO=olsoctcp
CLOC=en.US.CP1252
DLOC=en_US.819

Note the ‘informixserver.com’ host as specified in point 3 – and Setnet32 – above!

OR

Go Start > Control Panel > Adminstrative Tools > Data Sources (ODBC)  to add a User DSN or System DSN  (both work).  Or access via C:\Windows\SysWOW64\odbcad32.exe

5. Now the datasource should appear in MSQuery!