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!