Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Uploading Transactions
06-12-2013, 10:01 AM,
#1
Uploading Transactions
Is it possible to Upload GL transactions via CSV

I would like to import credit card transactions without having to each transaction manually.

Using the Journal entry screen is vary time consuming/cumbersome.

Idea
Reply
06-13-2013, 06:55 AM,
#2
RE: Uploading Transactions
No
Phil Daintree
webERP Admin
Logic Works Ltd
http://www.logicworks.co.nz
Reply
06-14-2013, 02:58 AM,
#3
RE: Uploading Transactions
I am not a big fan of no. Looks like I will have to use the DB import Utility.

INSERT INTO gltrans (type, typeno, trandate, periodno, account, narrative, amount) VALUES ( 25, '4', '2013-05-30', '2', '1010', 'PO: 5 100 - 9595 - bracelet x 950 @ 25.00', '23750' )

If it is not possible I will find a way and let you all know.

Reply
06-14-2013, 06:18 AM,
#4
RE: Uploading Transactions
Hi gregfallon1,

I have two thoughts on this topic:

1) Regarding the uploading: webERP is running on a MySQL database, hence you could use any ETL tool you want to write into the database. On my end, I'm a big fan of RapidMiner for this purpose, also for the reporting, but you could basically use any tool that is able to connect via ODBC or JDBC (for example MS Access).

2) However: If you modify any table, you need to be sure you keep the consistency and dependencies between any related tables. Usually, when you do something in webERP (or any other database driven application), not only one table is being affected, but several. If you don't keep the database consistent, you run the risk to render it completely useless.
Also: Before you do any "experiments", make sure you run the provided backup sript (.../BackupDatabase.php). For safety reasons it's recommended to download the backup, and delete it off the server immediately.

I hope this helps.
MacPhotoBiker
Just Core Business Inc.
[url]http://www.justcorebusiness.com[/url]
Reply
06-14-2013, 06:46 AM,
#5
RE: Uploading Transactions
Hello gregfallon,

Yes phpMyAdmin or the approach of MacPhotoBiker is the way to go - and the good news is that inserting records into gltrans table does not have too many dependencies and the next time any GL report is run the transactions are processed (posted) into the chartdetails.

HOWEVER, there are always at least two entries into gltrans - one for the debit and one for the credit - the sum of all gltrans for a particular transaction type and number should always be zero (double entry bookeeping 101)

Also, the type of the transaction counter maintained in systypes must be incremented so the system does not then insert another gltrans of the same number/type and you end up with a train smash.... AND if the credit card account is also set up as a bank account the banktrans should also have an entry for reconciliation purposes.

Probably wisest IMHO to write a script to do this and submit it back to the project - or sponsor me or some other developer to do it for you.
Phil Daintree
webERP Admin
Logic Works Ltd
http://www.logicworks.co.nz
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)