• From: USA
  • Registered: 03/05/2018
  • Posts: 123

Topic: Opening credit balances

I think I've read everything in the Wiki about setting opening balances, and I get that I would need an invoice to create a normal balance on a customer/debtor (AR) account.

However, the company I'm working with is set up such that most of their customers have credit balances. I know I can create an invoice and then overpay that invoice to create a credit balance, but that seems like a lot of extra stuff just to set an opening balance on a customer (debtor) account.

Suggestions?

Background:  I'm a software developer with 10+ years of PHP and 25+ years of database experience. I am coding the conversion from the client's old system (non-SQL) to FrontAccounting, so I don't need the UI to do this. I just want to do it the right way. I am using the UI and database diffs to analyze how FA stores things.

oakstreet1's Website

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

Re: Opening credit balances

Create a Credit Note / Debit Note for each Balance Sheet Entry at the beginning of the blank current and first FA fiscal. Alternatively make a Journal entry for all the balance sheet figures on the last day for the previous fiscal, open a new fiscal year, close the old fiscal year and the old closing balances will get transferred as opening balances in the new fiscal. Note that the former will allow for allocation to Supplier/Customer but the latter will address non tracked payments / receipts only.

apmuthu's Website

  • From: USA
  • Registered: 03/05/2018
  • Posts: 123

Re: Opening credit balances

Thank you. I didn't think I could issue a credit without an invoice, but I see that I can.

I'm testing with the Test Company and I've entered a lot of stuff to analyze the transactions. Can I create an earlier fiscal year to test this, or do I need to start fresh/clean company and create the fiscal years in order?  Somewhere I saw someone say that the fiscal years had to be numbered with the earlier years having lower ID numbers.

oakstreet1's Website

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

Re: Opening credit balances

The fiscal years must be numbered in order so that closing the fiscal and purging it will nor affect the newer ones as it is ID based. If you have just one fiscal year now, then just make all fiscal year id as 2 and then create the previous fiscal year with id=1. They will appear as f_year as well in the sys_prefs table.

audit_trail.fiscal_year
fiscal_year.id
sys_prefs.name='f_year'

apmuthu's Website

  • From: USA
  • Registered: 03/05/2018
  • Posts: 123

Re: Opening credit balances

Thanks. That is good information. I'll definitely be sure to create the necessary fiscal years in the right sequence when I do the actual migration!

You are very helpful. I will be sure to make a donation towards FA as I will probably have a lot of questions, and I really appreciate the effort that goes into developing a complete accounting system. I also like the way the FA code is organized. You guys do good work.

oakstreet1's Website

  • From: USA
  • Registered: 03/05/2018
  • Posts: 123

Re: Opening credit balances

I ran into a snag when I inserted a fiscal year with a lower ID, because I inserted it with closed=0 (not closed). The UI would not allow me to enter any transactions so I hunted through the code and discovered that I needed to re-open that fiscal year, so I switched the company's fiscal_year back to the current year, logged out, changed the previous fiscal year to closed (in mysql), logged back in, opened the fiscal year in FA, re-selected that fiscal year in the settings, and I was able to enter transactions. (this is all in the Test Company, for now).

(I haven't quite deciphered the audit_trail/GL checks that determine when a fiscal_year is open.)

I was then able to enter a credit memo, switch to the current fiscal year, and see the correct opening balance.

Thanks again!

oakstreet1's Website

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

Re: Opening credit balances

There are 2 audit tables.

The audit_trail table is for recording all transaction references where certain NULL fields will indicate what was superceded. It is also used when closing a fiscal year and purging it.

The sql_trail table is to record all sql accesses as set in the debug parameters in the config.php file.

Thanks for the feedback on how you solved your issue. Will make for a good reference for yourself and for all users as well.

This project is maintained by @joe with valuable coding by @itronics, both sys admins of the FA project. They certainly deserve the donations to keep this project going. Your mite will join others in sustaining this project. Thanks once again.

apmuthu's Website