Topic: Import Transactions

I have installed and been trying out this extension.

It would appear the entries to the sales / purchase ledger depending on the person type and person id are not being processed into the db.

This means for customers, the payment does not appear in the debtor trans table and thus can not be allocated to an invoice.

Just thinking how a dummy invoice can be created.

Cheers

G

2 (edited by apmuthu 12/18/2014 05:52:20 pm)

Re: Import Transactions

Make sure that the "_ref" fields have no spaces in them. When a new contact is entered, spaces creep into the crm_persons.ref field.

Re: Import Transactions

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

Re: Import Transactions

1. Take a backup of the db.
2. Manually enter one set of transactions that you would normally want to import.
3. Take another backup of the db.
4. Compare the two say in WinMerge.
5. Now you can get the changed entries (ignore the history and last update entries in the user tables).
6. Group the entries in the order in which they were performed and match the values to the source form fields.
7. Lookup any other entries in the config.db, session and sys_prefs and other defaults tables.

Re: Import Transactions

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

Re: Import Transactions

It is only a comparison between manual data entry in FA vs. imported data via extension to see what needs to be fixed in the extension to match your needs.

Re: Import Transactions

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

Re: Import Transactions

Could you please provide what changes need to be done in the extension files for it to work?

Re: Import Transactions

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

Re: Import Transactions

There are still some extra commas in the header line where "1=supplier, 2=customer" comes in.
It's been reasonably corrected now in my GitHub repo.

Re: Import Transactions

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

Re: Import Transactions

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

Re: Import Transactions

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.

Re: Import Transactions

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.

Re: Import Transactions

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.

Re: Import Transactions

After backing up db. I was not able to retrieve it back after making a dummy sales invoice. Any idea how it goes?

Re: Import Transactions

Setup...Backup and Restore

Re: Import Transactions

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.

Re: Import Transactions

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.

20 (edited by apmuthu 04/01/2015 01:19:51 am)

Re: Import Transactions

From the PHP Manual:

The following things are considered to be empty:

"" (an empty string)
0 (0 as an integer)
"0" (0 as a string)
NULL
FALSE
array() (an empty array)
var $var; (a variable declared, but without a value in a class)

Hence it is better to make it:

      if (!empty($person_id)) 

This has been committed in my Unofficial GitHub Repo and extension file set attached herein.

Also placed the screenshots online as a zip file.

Post's attachments

import_transactions.zip 33.6 kb, 16 downloads since 2015-04-01 

You don't have the permssions to download the attachments of this post.

Re: Import Transactions

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?

Re: Import Transactions

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

Re: Import Transactions

Have created a Wiki page for it.

24 (edited by alvin 04/20/2015 05:42:56 pm)

Re: Import Transactions

I have a question,

When making your csv file the first column is reference, How do I get the number to start out with for the first row?

This would be for Journal and bank imports,

Also do you have a template for Journal Processing or a sample csv?



Please inform me,

Alvin

"The roots of education are bitter, but the fruit is sweet."  - Aristotle.

25 (edited by raddison 04/22/2015 01:15:22 pm)

Re: Import Transactions

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.

Ross