Import Transactions (Page 1 of 7)

All values in this field on the db are singlw words.

The import file does not have a column for this but uses these two.

Person_type id (Supplier =1 Customer =2)    Person_id(Use lookup tool)

Only these 2 functions are called

journal_inclusive_tax
bank_inclusive_tax

HTH

G

I am not sure what this list of tasks is meant to achieve.

From the data being imported I would expect the extension to create the relevant enties in the S/L & P/L.

I am reporting the extension does not work as I would expect it to.

Cheers

G

No need to reinvent the wheel.

The correct journal entries are already generated in 2 other places where customer/supplier payments/invoices are entered

function write_customer_trans being one.

Cheers

G

On December 20, 2014, Geoffrey Walton wrote:

Ross

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?

--

Cheers

Geoffrey

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

Geoffrey

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. 

Ross

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.

Ross

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:
https://frontaccounting.com/punbb/viewtopic.php?id=5425

Regards,
Ap.Muthu

Dear Ap.Muthu

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

Cheers

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

There is already a README.md file in your GitHub repo. You can take the properly formatted one from mine and extend it to cover the date format aspects and other help info.

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.

Setup...Backup and Restore

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

import_transactions/includes/import_transactions.inc
line 248:
if ($person_id = !null)

should be

if ($person_id != null)

It is assigning instead of testing.

Regards,
Jorn Rikkers

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

Hi Ross,

I am looking at Geoffrey post about import transactions.
I have a bunch of transactions from the Journal Import I would need allocated to individual debtor accounts.
Where would I find these?

Have created a Wiki page for it.