Import Transactions (Page 4 of 7)

Looks like you will have to overwrite all your import_transaction module files. Take it from the attachment.

Hello. Once again I spoke too soon. This time the Import Transaction trial run showed up all green. I then unticked to process.  The procedure appeared to run and notified of various GL updates but when I then ran a TB the transactions were not there.  BTW after running the TB and returning to the import module it was 'greyed out' and I had to log out and back in to reaccess it.

Hello.  The messages I am sending are from Google Chrome running on KDE-Neon which is based on Ubuntu 16.04.

FA is installed on a local fileserver and accessed from Linux based workstations on the LAN.

The MySQL on the server is: MySQL Server version: 5.7.19-0ubuntu0.16.04.1 - (Ubuntu). Protocol version: 10 and PHP etc.: Apache/2.4.18 (Ubuntu), PHP version: 7.0.18-0ubuntu0.16.04.1.

Can I manually set the auto_increment field values?

@BarryHavenga: As you are using MySQL v5.7.x, the '0000-00-00' and it's timestamp counterpart are no longer accepted as an empty date unless the strict mode is turned off. There may be some PHP 7.x gotchas we need to weed out. Follow @braathwaate's instructions and see what gives.

Someone can test with PHP between 5.3 and 5.6 (both inclusive) along with MySQL between v5.0 and v5.6 and see if all is well.

Hello apmuthu message of the 07/27/2017 refers.  Where do I find "@braathwaate's instructions" to turn off strict mode in MySQL v5.7?

I am getting the following error when trying to import transactions:

"There are no customers, or there are no customers with branches. Please define customers and customer branches."

I am trying to import a general journal transaction. I have checked my account codes and they are all correct and all except one are for expenses. The one that isn't an expense is a credit card entry.

I am using FrontAccounting 2.4.2.

I installed the import_transactions extension from the program interface and then copied over these files:

import_transactions_2017-07-24.zip

Any help would be much appreciated.

Bill Dika

Hi apmuthu:

Thanks for responding.

I commented out the line as you said in import_transactions.php.

The import went through without any errors and checking the accounts afterward, all seemed OK.

It is curious to me that this function got triggered on the import since I have no customer, sales, purchase or accounts payable integrated accounts. All my accounts are straight general ledger accounts.

I have two separate Front Accounting instances on the same webserver. I upgraded both at about the same time and installed the import_transactions module together with the fixes on both instances. The first instance allowed me to import transactions without any problem. On the second instance I ran into the problem above which you kindly helped me fix. They are two separate but similar sets of books for two different businesses. Not sure why one gave me the problem and the other did not.

In any case, thank you.

Bill Dika

Hi apmuthu:

I have run into a new problem.

After using the add on successfully last month I have a new problem cropping up.

When trying to import a general journal entry list of transactions, it says that the transactions are duplicates and that I cannot enter them.

When I turned on debugging I get the following message when doing a trial run  to import transactions:

**********************
trigger_error('DATABASE ERROR : cannot add journal entry
error code : 1062
error message : Duplicate entry '0-169' for key 'PRIMARY'
sql that failed was : INSERT INTO 0_journal( `type`,`trans_no`, `amount`, `currency`, `rate`, `reference`, `source_ref`, `tran_date`, `event_date`, `doc_date`) VALUES('0','169','1239.23','CAD','1','169','','2017-11-01','','')
**********************

I checked type=0 and trans_no=169 and if I understand the mysql database correctly, this was a transaction way back in 2009. My trans_no's are over 2000 now.

Any help from you or anyone else with a safe solution to my problem would be appreciated.

Thanks.

Bill Dika

Import transactions uses the basic assumption that transaction numbers of type ST_JOURNAL (0) in the GL database "gl_trans" and the journal database "journal" are the same.   As I read the code in FA I believe this should always be true.  So when transactions are imported, the next transaction number to add is the last ST_JOURNAL transaction in the GL database plus one (see last_transno()).  This is used for both the gl_trans and journal DBs.

But if your database somehow violates this assumption, then you could get the error you experienced.

I would start by searching for the duplicate entries (e.g. 0-169) in the GL database.  You can use the mysql tool to do this.   I have to guess that the duplicate entries are not in the GL database; otherwise last_transno() would have found them and set the next transaction number past them.

Why would your database violate the assumption?   Due to some bug in FA where GL database entries were cleared but left in "journal"?

Let me know what you find out.

The 0-169 entries as you describe appear to be correct, so missing entries in gl_trans must not be the problem.

I was assuming that the error is coming from the call:

     add_journal(ST_JOURNAL, $curEntryId[ST_JOURNAL], ...

in import_transactions.   $curEntryId[ST_JOURNAL] is supposed to be 1 past the last transaction.   It should not be 169 if the last journal transaction is 2187.  You could instrument the code (add the line display_notification(...)) to try to determine why it is not  2187.   Instrument this call and in last_transno().

You could also use mysql to see if the following queries all return the same number:

select max(type_no) from gl_trans where type=0;
select max(trans_no) from journal where type=0;
SELECT counter, type, type_no, amount FROM gl_trans ORDER BY counter DESC LIMIT 1;

Well I guess you don't need to instrument the code now, as you found the bug, because all three mysql queries should be the same.   The last one is in error.

I will fix the bug in my fork and ask apmuthu to fix the master.   My first thought is to change the line in import_transactions/includes/import_transactions.inc from:

$sql = "SELECT counter, type, type_no, amount FROM " . TB_PREF . "gl_trans ORDER BY counter DESC LIMIT 1";

to

$sql = "select max(type_no) as type_no FROM " . TB_PREF . "gl_trans where type=0";

I haven't tested this, and it may not be the way I will fix it in the master, but its worth a try.

The reason you ran into this problem is because the last gl transaction in your DB happens to be a bank payment.  So another workaround is to make a dummy journal entry as the last transaction then try import transactions again.