Search options

Done

Hi,
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.

Regards
Ross

Hi,
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.

Regards

Ross

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.

Regards

Ross

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.

Code has been adjusted in the extension.

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.

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.
Charles

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.

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.
THanks.

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.
Ross

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.

Thanks.
Graham

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.

Ross