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

Topic: en_GB general CoA error - FA 2.4.1

27-Aug-2009 Joe Hunt
  # bank_trans_type_id removed from bank_trans in en_US-demo.sql and en_US-new.sql.

The official pkg distribution for en_GB-general.sql has the field bank_trans.bank_trans_type_id which needs to be removed.

The field budget_trans.memo_ is missing in the official distribution. It should be:

DROP TABLE IF EXISTS `0_budget_trans`;
CREATE TABLE IF NOT EXISTS `0_budget_trans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tran_date` date NOT NULL DEFAULT '0000-00-00',
  `account` varchar(15) NOT NULL DEFAULT '',
  `memo_` tinytext NOT NULL,
  `amount` double NOT NULL DEFAULT '0',
  `dimension_id` int(11) DEFAULT '0',
  `dimension2_id` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `Account` (`account`,`tran_date`,`dimension_id`,`dimension2_id`)
) ENGINE=InnoDB;

The order of the fields auto_update and inactive are switched in the currencies table.

The order of the fields cust_branch.branch_ref, debtors_master.debtor_ref, stock_category.inactive and suppliers.supp_ref needs to be corrected.

The tables dashboard_reminders and dashboard_widgets should not be in this CoA.
The deprecated table sys_types is present and needs to be removed.

The table tax_types has 

UNIQUE KEY `name` (`name`,`rate`)

in the GB CoA that is not present in the standard CoA sqls - it should be there to avoid ambiguities in select boxes.

$SysPrefs->base_sales cannot be "-1". It can be either 0, 1 or 2. Hence change the field value to be so where the field sys_prefs.name='base_sales' AND category='setup.company'.

There is no grn_clearing_act account defined in the official GB CoA.

apmuthu's Website

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

Re: en_GB general CoA error - FA 2.4.1

All these fixes are now in my FA24extensions repo.

Screenshots of available versions of extensions.

apmuthu's Website

  • gj6n68
  • Senior Member
  • Offline
  • From: UK, India
  • Registered: 02/14/2016
  • Posts: 81

Re: en_GB general CoA error - FA 2.4.1

Hello,

Just wondering if below fixes are already incorporated in 2.4.2, especially the GB CoA.

If I understand correctly, there was not GRN Clearing Acc in GB CoA before, and from looking at the files updated below, it seems GRN Clearing Acc was setup at code 1005 in updated CoA at below link.

But when I try to set up GRN clearing Account from System and General GL setup in 2.4.2, I don't see any GL option to use for GRN Clearing Acc.

Any help would be appreciated.

Thanks,
GJ

apmuthu wrote:

All these fixes are now in my FA24extensions repo.

Screenshots of available versions of extensions.

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

Re: en_GB general CoA error - FA 2.4.1

Update your FA 2.4.2 to FA 2.4.2+ by incorporating in the fixes since it's release (See #4 and #12 in the Announcements post for the release).

Only the Standard en_US new and demo CoAs are in the default install. Install your requisite Chart from the official repo and replace the sql/<Chart sql> file from my repo. The UK Chart in the official repo needs a config setting to allow alphanumeric characters in the Account Code whereas the one in my repo has prevented leading zeroes in them and works with the standard config setting.

apmuthu's Website

  • gj6n68
  • Senior Member
  • Offline
  • From: UK, India
  • Registered: 02/14/2016
  • Posts: 81

Re: en_GB general CoA error - FA 2.4.1

Hello,

Thank you for prompt reply.

I have indeed downloaded the 2.4.2 from the snap shot after all the fixes were published, I think I have the latest version as of 17/10/2017.

But, once installed, I created a few GL accounts and updated all items etc.

If I now replace sql/<Chart sql> file would it disturb everything ?

Also, all I want it a new GL account for "GRN Clearing Account", would it be easier just to create 1005 GRN Clearing Account instead of replacing the files, because when I'll replace the file, the new file won't have my unique GL accounts which I created after installation.

If I don't replace the files, would I miss out much on functionality ?

I ended up on this page after searching about 'cost of landed goods' and your last post mentions about GRN(link below).

https://frontaccounting.com/punbb/viewtopic.php?pid=28371#p28371

And last if there has been any fix about 'cost of landed goods' please let me know.

Thank you very much.

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

Re: en_GB general CoA error - FA 2.4.1

Your install has the latest FA master snapshot since it was officially last changed only on 13th Oct 2017 as on date.
The sql from my repo would be useful only for subsequent new companies created.

However, the sql file can be used in conjunction with a diff tool like WinMerge to compare the diffs with respect to your existing FA backup sql file and the latter's schema and data fields for inserts be suitably amended to incorporate any changes and then restored into FA. If there are any transactions using the Account Codes like 0xxx, then they will need to be corrected as per the one in my repo if the config file in the default install is to be left alone.

This is specific to UK, Canada and a few other Charts. The devs have yet to find the time to update their official pkg repos and till then FA Consultants can/will prey on unwary newbies who would have unsuspectingly added quite some data to their affected installs making them prime targets to be held to ransom. Re-creating the company and adding in the few transactions all over again may be the easier and advised way for many.

apmuthu's Website

  • gj6n68
  • Senior Member
  • Offline
  • From: UK, India
  • Registered: 02/14/2016
  • Posts: 81

Re: en_GB general CoA error - FA 2.4.1

Thank you for the details.

I have created Company with standard inbuilt coa but haven't done any transaction at all.

All I have done is created and modified some GLs and created few taxes and tax groups.

When I check SQL files they do not have details of any  a modification I made.

Do you think I can replace the old file with new file from your repo directly?

Creating a new company is a lot to work to be honest, both on fa and in real ha ha..

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

Re: en_GB general CoA error - FA 2.4.1

The Chart SQL files in sql/*.sql will not be touched by FA. They are used as templates to create a new company only. Hence replacing the them from my repo will not affect anything except that the next company you create will be okay to start with itself.

The backup sqls are the ones that contain your changes and they are created on the fly and are stored in company/#/backup folder. This is the one you will want to correct and synch it's schema and data with the one in my repo - use with care - and then upload and restore therefrom.

Since you do not have any transactions yet, it might be easier to create a new company from the sql in my repo and then incorporate the small changes you have made all over again in it.

apmuthu's Website

  • gj6n68
  • Senior Member
  • Offline
  • From: UK, India
  • Registered: 02/14/2016
  • Posts: 81

Re: en_GB general CoA error - FA 2.4.1

Hello again,

Assuming that stock GB CoA won't create any problem, I have decided to just carry on without disturbing anything in my current installation. I had entered too many customers, suppliers and items to have any courage to start with fresh installation.

But as @apmuthu mentioned earlier, this will leave me open to ransom in case things go wrong or if I find out something is breaking apart  big_smile big_smile big_smile big_smile

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

Re: en_GB general CoA error - FA 2.4.1

As long as you have no transactions, the lookup data like customers, suppliers and items can easily be exported and re-imported with the likes of phpMyAdmin / SQLyog. Anyway, goodluck.

You might want to synch your schema at the very least before you start entering transactions.

Take an sql dump of your database (only the structure/schema) without any data and upload it somewhere and paste the link here. Will try to provide the alter statements for the database to synch the schema to the en_US-new.sql standard as in my repo.

apmuthu's Website