Find any field name in a MS SQL db – matching your search string (e.g. ‘perator’, as in ‘Operator’ or ‘operator’):

SELECT table_name=sysobjects.name,
column_name=syscolumns.name,
datatype=systypes.name,
length=syscolumns.length
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE sysobjects.xtype=’U’ and syscolumns.name like ‘%perator%’

ORDER BY sysobjects.name,syscolumns.colid

Change the ‘syscolumns.name’ to ‘sysobjects.name’ to search on table names.

 

Handy when learning a new system!

Herewith a list of the most commonly used Pastel Partner Table names, a short description of each and the corresponding file name.

Table Name	Description	Filename
AccountUser	Passwords, User Settings	accuser.dat
ActivityMaster	Time & Billing Activities	accactiv.dat
AnalysisCodes	Receipt Analysis Codes	accranal.dat
BankDetails	Receipting Bank Details	accbnk.dat
BICUsers	BI Centre users	ACCBIC.dat
BillingParameters	Time & Billing Parameters	accprmts.dat
BOMHeader	Bill Of Material Header	accbomh.dat
BOMLines	Bill Of Material Lines	accboml.dat
CashBookCurrency	Foreign Cash Books / Currency Balances	acccbcur.dat
CostCodes	Cost Codes	accjcode.dat
CurrencyFile	Foreign Currency	accfcurr.dat
CustomerCategories	Customer Categories	accdcat.dat
CustomerDiscount	Volume Based prices	accdisc.dat
CustomerMaster	Customer Master	AccMasD.dat
CustSuppParameters	Customer / Supplier Parameters	accprmdc.dat
DeliveryAddresses	Customer Delivery Address	accdeliv.dat
EmployeeActivities	Time & Billing Employee Activities	accempac.dat
EmployeeMaster	Time & Billing Employees	accemp.dat
Euro	Euro Code	acceuro.dat
FinancialCategories	Financial Categories	accfcat.dat
Fonts	Printer / Font Setup	accfont.dat
GAAP	GAAP	ACCGAAP.DAT
HistoryHeader	History of Document (e.g. Inv/Crn) Headers	acchisth.dat
HistoryLines	Document Lines, Inventory History	acchistl.dat
HistoryTax	Tax History	acchtax.dat
HistoryUserDesc	User (Transaction) History Descriptions	acchistu.dat
HistoryUserSetup	User History Settings	acchistd.dat
InternetBank	Internet Banking Cashbook	accbank.dat
InternetBenf	Internet Banking Beneficiaries	accbenf.dat
Inventory	Inventory Master	accprd.dat
InventoryCategory	Inventory Categories	accicat.dat
InventoryGroups	Inventory Group	accgrp.dat
InventoryLink	Store Linking	accstklk.dat
InventoryParameters	Inventory Parameters	accprmst.dat
JumpUserAccess	JumpUserAccess	ACCUSERACCESS.DAT
LedgerBalances	General Ledger Balances	ACCMASB.DAT
LedgerMaster	General Ledger Master	accmas.dat
LedgerParameters	General Ledger Parameters	accprmgl.dat
LedgerTransactions	GL, Customer, Supplier Transactions	acctrn.dat
LinkHeader	Link Header	acclinkh.dat
LinkLines	Link Lines	acclinkl.dat
MenuAccess	Menu Access	ACCMACCESS.dat
MultiStore	Multi Store	accstore.dat
MultiStoreTrn	Multi Store Transactions	accstkst.dat
Notes	Notes	accnote.dat
OpenItem	Open Items	accoi.dat
POSAudit	Point of Sale Audit	posaudit.dat
POSCashUp	Point of Sale Cashup	poscash.dat
POSPaidOuts	Point of Sale Paid Outs	pospo.dat
POSPayments	Point of Sale Payments	pospaymt.dat
POSSetup	Point of Sale Setup	accprmps.dat
PreferredSupplier	Preffered Supplier	accstkps.dat
ProcessOptions	Process Options	ACCUSROPT.DAT
ReceiptTransactions	Receipt Transactions	accrecpt.dat
ReportCategories	Report Categories	accrcat.dat
ReportExport	Report Export	accrwexp.dat
ReportWriter	Report Writer	accrepw.dat
SalesmanMaster	Salesman Master	accsale.dat
SerialMaster	Serial Master	accmassn.dat
SerialTransaction	Serial Transactions	acctrnsn.dat
SupplierCategories	Supplier Categories	ACCCCAT.DAT
SupplierMaster	Supplier Master	accmasc.dat
SystemParameters	System Parameters	accprmsy.dat
TaxBox	Tax Box	acctbox.dat
TaxDescription	Tax Description	acctdesc.dat
TaxParameters	Tax Parameters	accprmtx.dat
TimesheetHead	Timesheet Header	acctimeh.dat
TimesheetLines	Timesheet Lines	acctimel.dat
Unposted	Unposted	accprdq.dat
UserGroups	User Groups	ACCUSRGRP.DAT
UserStore	User Store	accusstr.dat

Let me know if you need to link these or do a custom Pastel report from them!

I linked a IBM Informix Dynamic Server Version 9.40.UC4 on SuSE SLES 8 server – with Microsoft SQL Server Standard (64-bit) on Windows Server 2008 R2 Standard:

1.  Get the latest IBM Informix Client SDK  from here (I used clientsdk.3.70.FC7DE.WIN) and install on your Microsoft Server.

If you are installing the 32 bit version onto a 64 bit machine, you might need to change the SysWOW64 path:   open a comand prompt from the folder where the Informix SDK install is and enter:

set PATH=C:\WINDOWS\SysWOW64\;%PATH%

2.  Add your host in C:/Windows/System32/drivers/etc/hosts (at the very bottom), e.g.  <your server IP>  <your server name>

3.  Similarly, add “sqlexec  1526/tcp#” at the very bottom of C:/Windows/System32/drivers/etc/services.  Note the # at the end.

 

Now simply call your remote db – from the MS SQL side – as follows:

SELECT * FROM [LinkedServer].[RemoteDatabase].[User/Owner].[Table]

 

I designed a web based stock take system for a client which incorporates time reducing practices.

The system was built to force users to work according to an efficient procedure – and allows the Warehouse Manager to view the progress through-out.  At any point he/she can see the areas counted (vs un-counted) and the effect on the whole (System Stock Valuation vs Counted Valuation).

We improved the system bi-annually for the last 9 years.

Let me know if you need something similar!

 

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.

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.

 

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

 

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

where 

–  ‘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

AS

SELECT KeyField AS StockCode,

MAX(CASE WHEN FieldName = ‘DIM_L’

 THEN NumericValue

 ELSE NULL END) AS Length,

MAX(CASE WHEN FieldName = ‘DIM_W’

 THEN NumericValue

 ELSE NULL END) AS Width,

MAX(CASE WHEN FieldName = ‘DIM_H’

 THEN NumericValue

 ELSE NULL END) AS Height,

MAX(CASE WHEN FieldName = ‘DIM_M’

 THEN NumericValue

 ELSE NULL END) AS Weight

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.

 

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!

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.