(154 replies, posted in Modules Add-on's)

Hi Jorn,
Yes I agree. Thanks for that. The code on lines 634 and 651 need to be changed to include filtering 'type' in the SELECT statement.   

Regarding the journal entry I will do a few tests when I have the time.


(154 replies, posted in Modules Add-on's)

For Bank Statement Processing:
Check the bank statement checkbox below the drop down box.
Your csv must have the two amount columns. One for deposits. One for payments.
Which is typical of any bank statement.

Remember this is an adapted Bank Statement csv file that you are importing.

So the assumption is:
1. Download your bank statement from your bank in csv format.
2. Incorporate additional columns in this spreadsheet according to the template.
3. Complete your account code designations to the transactions in your bank.csv including tax codes.
4. Import the csv.

So you do the all account code and vat code designations for each line or transaction on the bank statement before you import.

The first few columns of the spreadsheet/csv must be identical to your bank statement.
The last few columns incorporate account and tax allocation.

So this spreadsheet/csv that you have created has your account and vat allocations in addition to the bank statement.

Essentially you are doing your account and vat  code designations before the import. Which I personally find much easier than any other commercial software packages available on the market. Complete all your account code designations to each transaction before you do the import.

Process the same csv twice.
Once with Deposit Processing.
Next with Payment Processing.

Hope that clears a few things up.


(154 replies, posted in Modules Add-on's)



(154 replies, posted in Modules Add-on's)

I have made the adjustments.


I have updated the extension Import Transactions to incorporate Sales Orders / Invoices.  A spreadsheet template is available for both credit invoices and cash invoices.  Invoices may be multiline or singleline. The templates use a combination of service and product items.  Each invoice / order is defined by the reference number used.



(154 replies, posted in Modules Add-on's)

I have updated the extension to import Sales Orders and Invoices.  The code is available on my github repo.

Multiline or singleline invoices can be imported. Spreadsheet templates are available under the templates folder for both multiline and single line invoices.

Invoices can be either cash or credit. The payment id lookup tool should be used to determine what number you will associate with either cash or credit invoices.  For cash invoices I have used the payment id of 4, and for credit invoices I have arbitrarily used 1 instead of 2 or 3.


(154 replies, posted in Modules Add-on's)

I have adapted the extension to import multi line sales orders, and sales invoices from a spreadsheet. Invoices are either cash sale or credit sale.

The extension is working fine but I am getting a Cart: Read warning (yellow) whilst importing the Direct Sales Invoices.  The sales orders import fine. 

I do not want to rewrite the Cart Class simply to avoid a warning.

I am using the attached adapted British coa with a service item 001 and a product item 002. One customer connected to branch one and a second customer connected to branch 2.

Any ideas would be appreciated. See attached files please.




(154 replies, posted in Modules Add-on's)

Hi Alvin,
A template for Journal Processing is available under FA->Banking and General Ledger->Import Transactions->Import type: Journal Processing->Display->Display Help Notes (checkbox).

A template for Deposit/Payment  Processing is available under FA->Banking and General Ledger->Import Transactions->Import type: Deposit/Paymenl Processing->Display->Display Help Notes (checkbox).

If looking in folders on your server,    ...\www\frontaccounting\modules\import_transactions\templates.

RE: How do I get the number to start out for the first row?
Have a look at the Journal template and you will see the pattern required. Basically use the same reference alphanumeric (Numbers and letters) for all journal debits and credits that belong to the particular journal.

ie.   Reference  Date                  Description                             Amount      Code       Taxtype Dim1  Dim2  PTid  Pid
       JNL1            01/12/2014    Bad debts using account           222.24    8100    0       D1         D2       
       JNL1            01/12/2014    Debtors Control using account -222.24    1100    0       D1         D2      2      2

So, in the above, I have used JNL1 for both lines.  Here we have 2 lines for 1 journal and -222.24 === 222.24

In other examples you might sometimes have, for example, 5 lines in 1 journal so maybe having 4 debit lines (positive amounts) and one credit line (minus amount). The minus amount total's absolute amount must equal all the positive amounts added up. So all the 4 debit lines (positive amounts) added up will equal the one credit (minus) line amount. eg. 50 first line, 60 second line, 100 third line, 50 forth line === minus 260 fifth line. All lines must have the same reference eg. JNL2 for all 5 lines.

The software creates a new journal everytime the positive amounts equal the negative amounts. If there are several positive amounts one after the other, it will add these up until it finds a minus amount (absolutely ABS) equaling the positive amount.
So make sure your positives equal your minus amounts.

So for Journals, use your own reference number system. For Deposit and Payment Processing preferably use a reference number that corresponds to your source document.

Additionally, remember your account codes must correspond with your chart of accounts that you use. The templates use British chart of account codes.



(154 replies, posted in Modules Add-on's)

Hi Graham,
Journal processing under the Import Transactions extension is currently the equivalent of a General Journal not a Debtors Journal or a Creditors Journal that may be found in other accounting software packages.

You have probably noticed under FA...Banking and General Ledger...Journal Entry there is no provision for creating a journal matched to a specific customer or supplier.

So account codes relative to the General Ledger should only be used here. The Debtors Control account and Creditors Control account are General Ledger accounts and can be used in the extension. The person_id eg. 1 for Joe Bloggs and person_type_id eg. 2 for Customers (Supplier =1) are currently simply used to extract information ie. customer name from the database and put it into a memo. Admittedly this does not serve any real purpose currently. Perhaps I should rename Journal Processing to General Journal Processing. This implies the exclusion of Debtor and Creditor processing. 

I will be adapting the extension later to accomodate the various types of Journal processing eg. Debtors, Debtors returns, Creditors Creditors returns, Bad Debts all under specific names where specific types can be allocated to a whole batch of similar single line transactions. The advantage of having batches of similar transactions is that the minus sign representing a credit is avoided.

Currently you can allocate supplier payments using the extensions Payment Processing not Journal Processing. Allocate receipts using the extensions Deposit Processing. The Sales Ledger is updated and the amount(s) await to be allocated under FA/Sales/Allocate Customer Payments or Credit Notes. This is where you will find them.

And the Purchases Ledger is updated and the amount(s) await to be allocated under FA/Purchases/Allocate Supplier Payments or Credit Notes. This is where you will find evidence of the success of the import.




(154 replies, posted in Modules Add-on's)

Hi Ross,
I tried to use the frontaccounting import_transactions extention, but I found a bug in the code.

line 248:
if ($person_id = !null)

should be

if ($person_id != null)

It is assigning instead of testing.

Jorn Rikkers

Hi Jorn,
Agreed. Thanks. Will inform the team at Front Accounting.


(154 replies, posted in Modules Add-on's)

Import Transactions: I have put up a 12 step carousel of screenshots on my website for those that might be interested  http://www.bbqq.co.uk/index4.php/site/how001. I will put something up on the wiki in due course as well.

Ross Addison's Import Transactions extension will import bank statements in csv format. FA account codes, VAT codes, Suppliers, and Customers have to be allocated to each line of the bank statement. A trial run feature is available. See the spreadsheet templates provided.

Code has been adjusted in the extension.

FA...Banking and General Ledger...Maintenance...Import Transactions (extension)....Journal Processing

If you do not have the extension,    login to Company 0,  or your first company that you setup (which you should keep neutral for setting up additional companies),  FA...Setup...Install/Activate extensions...Import Transactions.  In the dropdown box FA... Setup...Install/Activate extensions...Extension:....SELECT 'Activated for' ... the relevant company (which should not be company 0). Activate this extension for this company. Logout of Company 0. Login to the relevant company.

Use the template under FA...Banking and General Ledger...Maintenance...Import Transactions...Journal Processing...Display...Display Help Notes...Point 6...Template

(Remember, if you are not using British chart of accounts, to change the account codes used in this template. )

Use the 'Trial check' before performing the import to make sure that you are happy with the overall process.

NB: You cannot process bank related journals using this extension. So no transaction using a bank account code will be allowed using this extension. You must use Import Transactions...Deposit/Payment Processing for this purpose.


(11 replies, posted in Banking and General Ledger)

You can now import bank statements in csv format using the Import Transactions extension.

Using the extension:
1. Download Bank statement from your bank in csv format.
2. Add additional columns to this csv for reference, (coa) account numbers, vat code (if transaction includes vat/gst), using the provided template, and supplier or customer ids.
3. 'Trial run' on this adusted csv to validate existence of account codes, and supplier/customer ids.
4. If successful Trial run then uncheck 'trial run' checkbox and import the adjusted csv.

Now Using Front Accounting:
Allocate imported customer receipts (Sales...Allocate customer payments) and supplier payments (Purchases....Allocate supplier payments) against appropriate invoices.

So before the csv can be imported, correct account code matching, vat code matching, and supplier/customer matching must be
made to each line of the bank statement in csv format.


(154 replies, posted in Modules Add-on's)

Setup...Backup and Restore


(154 replies, posted in Modules Add-on's)

On 26/02/2015 Charles wrote:
I must apologize but I am trying to use your module and am having a little difficulty.
The account is a new account added and for some reason doesnt show in the chart_master
in the mysql database. Is this an easy fix?

Skipped header. (line 1 in import file 'ofx.csv') --Line 2 --DATABASE ERROR : could not
retreive the account name for sql that failed was : SELECT account_name from 0_chart_master
WHERE account_code=''

On 27/02/2015 Ross Wrote:
Hi Charles,
A couple of things you can check:

Make sure the account code that you have used does not have any leading zeros. eg. 0010. 0010 in a spreadsheet will be saved as 10 in your csv if the spreadsheet column is in number format. The spreadsheet column should be in text format before you save it in csv format. So check your csv by opening up in notepad. That is a common mistake. Check that all your account codes in your csv are in fact available in your general ledger.

Check that your spreadsheet columns correspond to the template column headers.  Hopefully these tips will help. This type of error is normally related to an incorrectly formatted csv file.

On 27/02/2015 Charles wrote:

Thank you. I was able to sort most of it. Is it possible to import transactions for a credit card? I keep getting an Error: Credit amounts represented by negative amounts being entered. Check csv file is correct. I do have the credit card linked to a bank account and I can enter the items manually to get the negative balance.

On 27/02/2015 Ross wrote:
This error is related to Deposit or Payment Processing. (Line No 218 Import_transactions.php). No credit amounts represented by negative amounts should be entered in a spreadsheet if you are constructing a spreadsheet for either Deposit or Payment Processing csv's with the 'Bank Statement format ie. two columns' checkbox checked.

So all amounts used in either of the two columns should be positive. This is sensible since a bank statement has two columns.  Deposit amounts, or money coming in, are normally in the right column. Payment amounts, or money going out of the bank, are normally in the left column.  Negative amounts are normally used only if a statement is using one amount column. The negative representing a benefit or credit to the client.

In journal processing however, only one amount column is used. Each journal debit must have a journal credit. So a debit is represented by a positive amount. A credit is represented by a negative amount. This is for interest sake only since you have selected Deposit or Payment processing and if you do not have accounting knowledge you would want to stay clear of Journal Processing.

I suspect that you have downloaded a bank statement or credit card statement that has one column. Check how the total balance is changing. If the overall balance is going up with a negative amount in the column, then in this instance the negative will represent a deposit. So you will have to construct two separate amount columns in your spreadsheet to conform with the bank statement format.

Put all the negative amounts (probably just a few to top-up your credit card)  under the Deposit (right) column. Put all the positive amounts under the Payment (left amount column).   

A credit card statement normally has one amount column. The negative amounts (with a minus sign) represent a deposit into your credit card account effectively crediting your account. The positive amounts represent a payment off your card. So put all deposits (negative amounts) into the right amount column of your spreadsheet, and put all payments (positive amounts) into the left amount column of your spreadsheet. Of course all amounts should be positive now since you have two separate columns as required under 'Bank statement format' checkbox.

Check the help template located on the main page of Import Transactions to verify the type of format required. Remember you will have to process this csv once with  'Deposit Processing' and a second time with 'Payment Processing' (or visa versa) since it is assumed you have checked or ticked 'Bank Statement format' and therefore are now required to have two columns in the spreadsheet as opposed to one singular column in DepositProcessing.csv and one singular column in Paymentprocessing.csv. If you have 'Bank Statement format' checked or ticked you are effectively combining normally separate csv files Deposit and Payment processing into one Bank Statement spreadsheet. Selecting Deposit processing will ignore the payment column, and selecting Payment processing will ignore the deposit column.

Additionally, you will need to setup a credit card bank account under FA which it appears you have done already. In the event that you have not, choose a number close to the current bank account normally 1200; using the British Chart of Accounts (coa). Preferably linking the account to current liabilities as opposed to current assets.

I hope these few tips have helped further. Thanks for the request and I apologise for the rather lengthy explanation.


(154 replies, posted in Modules Add-on's)

I am in the process of upgrading my website and will get to developing some additional help notes in due time using the suggested Front Accounting templates.


(154 replies, posted in Modules Add-on's)

On 2015-02-10, Graham wrote

Hi Ross,
I am trying to utilise your built in module to post transactions for
which I have completed the spreadsheet. The test run appears to
work, but when I try click un-tick (i.e. post to database), it runs,
and displays summary but the database actually isnt populated.
Please can you advise.

On 2015-02-10 Ross wrote

Hi Graham,
Please make sure that you have the latest updates to the extension.
This was a bug a while ago. To be absolutely sure that you have the correct extension go to my extension which I am hosting on Github.
https://github.com/rossaddison/import_transactions or 
Send me an email if you encounter any further problems.

On 2015-02-12, Graham wrote

Hi Ross,

Thanks for getting back to me.
I think it is something you could consider updating in the "how-to". Essentially, the issue was my FA was set to accept dates mmddyyyy. Your schedule looks

for it as ddmmyyyy. I tested changing both FA or the template (in Excel) to different types and I discovered that as long as they agree in terms of format

the entries seem to import correctly.


On 2015-02-12, Ross wrote

Hi Graham,
Thank you for your efforts and poignant observations. Yes under FA...Banking and General Ledger....Import_transactions...Display...Company Settings: Lookup...User Selected Date format....DDMMYYYY must correspond to the date format that is used in the csv file.

This setting, as you probably realize,  is retrieved from your companies  FA...Setup....Display Setup...Dateformat and Separators.

For our forum: In addition, as you have probably read, I always check my csv file for a 4 character-length year ie. YYYY as opposed to YY, as mentioned in the help notes under Import Transactions, after I have saved my spreadsheet into csv format. Also watch out for account codes that begin with zero's that are being used in your spreadsheet as these can be lost if the spreadsheet column is not in text format.

I will add an additional note to the Display...Display Help note in my extension on Github:
https://github.com/rossaddison/import_transactions for this purpose and CC this through to Apmuthu, Joe, and Janusz.

Your observations are appreciated and I will draw up a 'README' file which I will add to the repository.



(154 replies, posted in Modules Add-on's)

I have removed the comma between (supplier =1 , customer=2). Now reads (supplier =1 customer=2). So spreadsheet should open up csv template more amicably. You will also notice that I have changed the last line to reflect an example code of 1001 which is stock as opposed to 0010 furniture which has leading zeros and might be dropped if the account code column is not in text format when the user saves their spreadsheet back into csv.

Thanks for your particularness smile


(154 replies, posted in Modules Add-on's)

On December 20, 2014, Geoffrey Walton wrote:


I have seen some advice to contact the extension author to report bugs.

I have downloaded this extension in the last few days.

The extension imports a file that includes customer and supplier numbers, so I assume it will import sales and purchase transactions.

The import correctly creates the journal and GL entries but no entry in the sales or purchase ledgers.

So for a payment received from a customer the Sales Ledger does not get an entry and thus the customer balance is not reduced and the payment allocated to invoices.

Is this expected behaviour?




On December 21, 2014  1:20 am  5 KB Ross wrote:


Yes. Currently the extension does not allocate payments and receipts to the Purchases Ledger (individual Supplier accounts) and Sales Ledger (individual Customer accounts) respectively.  The Control accounts are updated in the General Ledger with the Customer and Supplier id.

I will integrate supp_trans_db.inc's  function write_supp_trans  (Purchases Ledger) and the cust_trans_db.inc's function write_cust_trans (Sales Ledger) into the include's function bank_inclusive_tax. This will mean that we will have an additional column in the csv for $supp_reference (Purchases Ledger) and $Branch_No (Sales Ledger) along with suitable validation.

Thank you for your input. 


December 27, 2014  2:02 pm  48 KB

Hi Geoffrey,

As an attachment you have the latest updates to import transactions.

Work done:
1. Integration of supp_trans_db.inc's  function write_supp_trans  (Purchases Ledger) and the cust_trans_db.inc's function write_cust_trans (Sales Ledger) into the include/ import_transactions.inc function bank_inclusive_tax.

So the Sales Ledger is updated and the amount(s) await to be allocated under Sales/Allocate Customer Payments or Credit Notes.
And the Purchases Ledger is updated and the amount(s) await to be allocated under Purchases/Allocate Supplier Payments or Credit Notes.

2. Templates adjusted to include the extra column Branch_id. Suppliers evidently do not have a Branch_id and so $supp_reference is not needed in the csv file.

3. spreadsheet_headers.html (saved lazily from spreadsheet_headers.doc as spreadsheet_headers.html) help file has been updated as well so users can familiarise themselves with the csv structure from the website.

Your feedback is appreciated.


On December 28, 2014  2:03 pm 2 KB AP.Muthu wrote:

Dear Ross,

Please review fixes to your import_extensions files in the FA Extension pkg listed at:


Dear Ap.Muthu

Thank you for the tidy up. I will update my code  on github as well. https://github.com/rossaddison/import_transactions