• From: South Africa
  • Registered: 07/01/2017
  • Posts: 64

Topic: Import of a year of GL Transactions

Hi. Which would be the best module to achieve the import of a year's worth of GL transactions into FA? (Import transactions or Import Multiple Journal entries).

I tried the former and I get a "Green" result but nothing actually gets imported.

BTW: when invoking Import Transactions in FA 2.4.7 Theme Dynamic, the module fails to load due to a spelling mistake in the code:/modules/import_transations/import_transactions.php  rather than the required /modules/import_transactions/import_transactions.php.

I am not sure whether other themes are affected or where to fix it.

Regards Barry
  • From: South Africa
  • Registered: 07/01/2017
  • Posts: 64

Re: Import of a year of GL Transactions

I tried Import Multiple Journal Entries with debug on and got the following:

Undefined variable: trans_type in file: /var/www/html/accounts/modules/import_multijournalentries/import_multijournalentries.php at line 163
DATABASE ERROR : cannot add journal entry
error code : 1292
error message : Incorrect date value: '' for column `ppt`.`1_journal`.`event_date` at row 1
sql that failed was : INSERT INTO 1_journal( `type`,`trans_no`, `amount`, `currency`, `rate`, `reference`, `source_ref`, `tran_date`, `event_date`, `doc_date`) VALUES('0','4','3499.79','ZAR','1','GLImp1','','2018-03-01','','')

The import is complaining about the date value but I tried every permutation of date and it still does not work.

Regards Barry
  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Import of a year of GL Transactions

Wherever you have an event_date field as blank / null, it appears that your MySQL / PHP is not happy with it and the normal '0000-00-00' is not acceptable as well and it does not degrade gracefully as earlier versions of PHP/MySQL did. Set the strict date off in MySQL and restart MySQL and Apache:
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
https://www.liquidweb.com/kb/how-to-disable-mysql-strict-mode/

Otherwise, make the existing settings happy by populating the import file with some date like 1970-01-01 wherever event_date is blank and then import it.

apmuthu's Website

4 Reply by BarryHavenga (edited by BarryHavenga 10/02/2019 07:03:02 am)

  • From: South Africa
  • Registered: 07/01/2017
  • Posts: 64

Re: Import of a year of GL Transactions

Hello. I tried what you suggested and it did not work and the identical error message appears.

The specified CSV format for a Multiple Journal Entry import is:
entryid    date     reference     accountcode    dimension1    dimension2    amount    memo
........................where the date refers to the transaction date.  All the transaction dates are populated.

In the literature there is no requirement for an event date, although I see the SQL insert contains a number of additional columns.  Can you advise me how to amend the file so as to insert a dummy event_date please?

Regards Barry
  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Import of a year of GL Transactions

The said error occurs in the function add_journal() defined and called in write_journal_entries() in gl/includes/db/gl_journal.inc where the full complement of 10 arguments are used. Other functions too call it through:

add_supp_invoice() <= purchasing/includes/db/invoice_db.inc
void_supp_invoice() <= purchasing/includes/db/invoice_db.inc
add_wo_costs_journal() <= manufacturing/includes/db/work_order_costing_db.inc
handle_negative_inventory() <= includes/db/inventory_db.inc
add_exchange_variation() <= gl/includes/db/gl_db_banking.inc
add_cust_supp_revaluation <= gl/includes/db/gl_db_banking.inc
delete_this_fiscalyear() <= admin/db/fiscalyears_db.inc

None of the above functions are directly called in the extension file modules/import_multijournalentries/import_multijournalentries.php.

The file /gl/includes/db/gl_db_trans.inc is included in the above extension file which defines the function add_gl_trans() which has many more arguments but does not use the event_date parameter.

The functions called by the said extension file of importance are:

write_journal_entries() <= gl/includes/db/gl_journal.inc
add_bank_transaction() <= ** (Cannot find it defined anywhere - not even in FA 2.3.x) **

However, these functions are defined:

add_bank_transfer() <=gl/includes/db/gl_db_banking.inc
add_bank_trans() <= gl/includes/db/gl_db_bank_trans.inc

and both do not match the number and type of arguments used in the call.

The missing function add_bank_transaction() can be compared to the function write_bank_transaction() defined in gl/includes/db/gl_db_banking.inc but even this does not match the arguments used in the call.

@joe: where and how do we define the function add_bank_transaction() now?

apmuthu's Website

  • From: South Africa
  • Registered: 07/01/2017
  • Posts: 64

Re: Import of a year of GL Transactions

Hi. Can you suggest how I might address the  event_date problem please?

Regards Barry
  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Import of a year of GL Transactions

Export out a few Journal Transactions and then try to import them back with some date and number changes into a dummy install. Also try to upgrade your FA install to the latest in a test environment and see what happens.

We still need to resolve the missing add_bank_transaction() function in the core.

apmuthu's Website

  • From: USA
  • Registered: 07/16/2017
  • Posts: 276

Re: Import of a year of GL Transactions

Regarding the import_transactions extension,

I tried the former and I get a "Green" result but nothing actually gets imported.

Did you unclick the trial run box?

If so, try to import the examples (or similar) and see if that works.