• From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Topic: Field type standardisation in Charts char => varchar

In the standard en_US-new.sql and en_US-demo.sql Charts of Accounts, there are many fields that should be varchar and not char.

Here are the set of SQL statements that can be safely executed in most recent FA v2.3 installs and can be ported to the FA v2.4 branch as well.

ALTER TABLE `0_bom` 
    CHANGE `parent` `parent` VARCHAR(20) DEFAULT '' NOT NULL, 
    CHANGE `component` `component` VARCHAR(20) DEFAULT '' NOT NULL, 
    DROP INDEX `parent`, 
    DROP PRIMARY KEY, ADD PRIMARY KEY (`parent`, `loc_code`, `component`, `workcentre_added`); 

ALTER TABLE `0_credit_status` CHANGE `reason_description` `reason_description` VARCHAR(100) DEFAULT '' NOT NULL;
ALTER TABLE `0_payment_terms` CHANGE `terms` `terms` VARCHAR(80) DEFAULT '' NOT NULL;

ALTER TABLE `0_purch_data` 
    CHANGE `suppliers_uom` `suppliers_uom` VARCHAR(50) DEFAULT '' NOT NULL, 
    CHANGE `supplier_description` `supplier_description` VARCHAR(50) DEFAULT '' NOT NULL;

ALTER TABLE `0_sales_types` CHANGE `sales_type` `sales_type` VARCHAR(50) DEFAULT '' NOT NULL;

ALTER TABLE `0_salesman` 
    CHANGE `salesman_name` `salesman_name` VARCHAR(60) DEFAULT '' NOT NULL, 
    CHANGE `salesman_phone` `salesman_phone` VARCHAR(30) DEFAULT '' NOT NULL, 
    CHANGE `salesman_fax` `salesman_fax` VARCHAR(30) DEFAULT '' NOT NULL;

ALTER TABLE `0_stock_moves` CHANGE `reference` `reference` VARCHAR(40) DEFAULT '' NOT NULL;

ALTER TABLE `0_workcentres` 
    CHANGE `name` `name` VARCHAR(40) DEFAULT '' NOT NULL, 
    CHANGE `description` `description` VARCHAR(50) DEFAULT '' NOT NULL;

ll these and more are available in my GitHub FAMods folder.

apmuthu's Website