• From: Australia
  • Registered: 11/15/2017
  • Posts: 9

Topic: Bookings in previous financial years

Hello everybody,

I just installed FrontAccounting 2.4.3 and wanted to do some bookings in the previous financial year (As usual in Australia this would be from 01/07/2016 to 30/06/2017).

The previous year is not closed and I set it to the active year in my company setup.

However I still get the error message "The entered date is out of fiscal year or is closed for further data entry".

I also tried setting the permission flag to allow booking in non-closed financial years, no change.

I might have to add that I have to play around quite a bit until I got the Australian Financial years set up correctly and now have them set up until 30/06/2021.

Anything I miss here?

Thanks for your help.

Cheers,

Thomas

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: Bookings in previous financial years

Check that you have not closed the GL Transactions in Banking & General Ledger, Closing GL Transactions.

/Joe

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

Re: Bookings in previous financial years

Setup => Company Setup => Active Financial year => choose the previous fiscal year. When making an entry for the previous financial year make sure the transaction date is in the previous financial year. If some core settings / permissions change in a session, logout, clear cache and login again.

apmuthu's Website

  • From: Australia
  • Registered: 11/15/2017
  • Posts: 9

Re: Bookings in previous financial years

As already mentioned in my original post the previous financial year is not closed and I set it as active in the company setup.

I also cleared my cache before re-login, no change.

It feels like there is a bug somewhere. I don't know if it has something to do with the chart of accounts I am using. I initialised it with an Australian one. But I also realised that some of the configured accounts in system setup (Retained Earnings and Profit/Loss Year) were not exiting, so I had to create them.

Any Ideas?

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

Re: Bookings in previous financial years

Take a backup of your database and compare the schema with the sql/en_US-new.sql and see if the missing fields / tables are in place. Which Chart of Accounts did you start with - AU general or AU service? The latter is  quite modified in terms of the table data but they both have similar schemas.

apmuthu's Website

  • From: Australia
  • Registered: 11/15/2017
  • Posts: 9

Re: Bookings in previous financial years

Thanks apmuthu for your reply.

I am using the general AU Chart of Accounts.

I did a backup and tried to compare using text compare. However, there are plenty of differences, mainly in character set and collations.

What kind of differences are you looking for? What is the best way to compare?

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

Re: Bookings in previous financial years

Missing fields, field type definitions and sizes and order of fields, missing tables, presence of obsolete tables, etc.
Use WinMerge to compare.

Alternatively, load both original schemas without their data into two separate databases and do a schema compare possibly in SQLyog / phpMyAdmin or any other online tool that compares schemas - some listed in this forum as well.

The easiest way forward is to use the sql/en_US-new.sql Chart and then alter what you want to suit AU requirements.

apmuthu's Website

  • From: Australia
  • Registered: 11/15/2017
  • Posts: 9

Re: Bookings in previous financial years

Hello,

Finally I got around to do that difference, there are quite a few differences, particularly columns in en_US-new.sql which do not exist in my database.

I have a html file with the report of that comparison. Can I upload this somewhere here?

Thanks.

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

Re: Bookings in previous financial years

Better still - which was the original Chart of Accounts you used to start with?
Did you install any extensions that caused these differences?

You can upload anywhere (delete all insert statements especially for the users table) - just your schema and the differences with en_US-new.sql and paste the link here.

apmuthu's Website

10 Reply by jegentom (edited by jegentom 11/20/2017 01:30:16 pm)

  • From: Australia
  • Registered: 11/15/2017
  • Posts: 9

Re: Bookings in previous financial years

I had it directly created with this chart of accounts (The AU general).

I did generate the dumps for comparison without data, so the file does not show any data only structure. I have uploaded the file here: https://accounting.perscare.com.au/files/textfile_diff_result.html

jegenco_fracct is my accounting (created with the AU general chart of account). jegenco_sandbox is the en_US-new.sql.

Thanks

Post's attachments

TextFile Diff Report - 2017-11-19 23 35 07.zip 18.5 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.
  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Bookings in previous financial years

Looks like there is more than meets the eye.
There are some default data necessary for FA to run - reflines, security_roles, etc..
We need to eliminate all AUTO_INCREMENT=XXX phrases and all collation information from the base sql.
I will now proceed to make a real standard SQL for AU and revert.
Since you already have data, you may not be able to benefit from the newer dispensations in reflines unless you are willing to start the new references midway or from scratch.

apmuthu's Website

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

Re: Bookings in previous financial years

The en_AU general 4 digit chart of accounts corrected for FA 2.4.3 has been committed to the FA24extensions repo.

@itronics: can re-create the pkg for it.

apmuthu's Website

  • From: Australia
  • Registered: 11/15/2017
  • Posts: 9

Re: Bookings in previous financial years

Hello,

I am happy to start from scratch again ... I did not do too much setup yet and I messed around quite a bit with the financial years.

When I re-install will it download the new version of the AU general chart of accounts  automatically or do I have to do something?

Thanks

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

Re: Bookings in previous financial years

No. It will download only the old version and install that - but never mind that. Just download the new version from the FA24extensions repo and overwrite the downloaded file in your sql folder.

Then you can choose the Chart to use for creating your new company.

@itronics: please refresh the pkg repo with this new file from my repo linked herein.

apmuthu's Website

  • From: Australia
  • Registered: 11/15/2017
  • Posts: 9

Re: Bookings in previous financial years

Hi apmuthu,

I have done a re-installation. The problems with the default data I originally had have disappeared.

However, I am still unable to book in a previous year. Active year is set to the previous one and it is not closed. Booking in the current year works now (a thing which did not work previously either).

Does it maybe have anything to do with the way I had to set up the financial years to accommodate the Australian fiscal year (July to June)? I had to do the following:

- Year 01/01/2016 to 31/12/2016 was already there
- Added short year 01/01/2017 to 30/06/2017
- Added normal year 01/07/2017 to 30/06/2018 and set as active year
- Closed and deleted year 01/01/2016 to 31/12/2016
- Closed and deleted year 01/01/2017 to 30/06/2017
- Added normal year 01/07/2016 to 30/06/2017

The last one is the one I want to create bookings in. I set that to active for that purpose.

Thanks for your help. There is a new comparison report on https://accounting.perscare.com.au/files/textfile_diff_result_20171122.html

jegenco_fracct is again my current installation and jegenco_sandbox the en_US-new.sql one.

Thanks

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

Re: Bookings in previous financial years

I was not aware of the AU fiscal year you stated when I prepared the chart.

When you install such a chart, before you input any data into the installation, manually (phpmyadmin) correct the fiscal_year table so that the appropriate date ranges are there for each fiscal and they must be in serial order and no id should be missing either.

Australian Fiscal Year

I have changed the insert statement in the chart to be:

INSERT INTO `0_fiscal_year` VALUES (1, '2016-07-01', '2017-06-30', 0);

The additional key for the tax_types table UNIQUE KEY `name` (`name`,`rate`) is essential to avoid duplicate entry of tax types that populate select drop down boxes. Hope the devs will add it upstream. All AUTO_INCREMENT=XXX have been removed. Some comments have been added from later upstream chart updates.

Committed

Download the updated chart here.

apmuthu's Website

  • From: Australia
  • Registered: 11/15/2017
  • Posts: 9

Re: Bookings in previous financial years

Hello

Now it works fine ... thanks a lot for your support.

Thomas

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

Re: Bookings in previous financial years

The latest AU general chart will always be here. Just right click the "Raw" button and choose to save it. There have been some updates in it after the previous release too, to accommodate the new barcoding option.

apmuthu's Website