• Registered: 12/08/2015
  • Posts: 3

Topic: Uploading transactions

I'm evaluating if FrontAccounting is appropriate for my business. I like what I see, but I have concerns because I need  FrontAccounting to accommodate a couple of our efficient workflows.

The business is web based. The company manufactures products by contracting with copackers for large bulk quantities that are sufficient for several months of sales. We sell the products on our website and many marketplaces, such as Amazon.

To record sales from the marketplaces, I'd like to import csv files monthly. Each record in the csv should be able to:
- Create the sale transaction, which will include product sales, shipping sales, discounts, marketplace fees, and debit either marketplaces receivables or PayPal as a bank account.
- Properly adjust inventory levels and charge cost of sales based on quantity and SKU fields.

To record most of our checking account charges, I'd like to import csv files monthly. 85% of our checking account transactions are for postage and shipping. Instead of entering each transaction individually, we prefer to import them in bulk. Each of these imported transactions should be visible to the bank reconciliation function.

To record most bank account transfers, I'd like to import csv files monthly. Each of these imported transactions should be visible to the bank reconciliation function.

Are these imports possible with FrontAccounting? Alternatively, could I import them via SQL queries?

Thanks for all advice.

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,095

Re: Uploading transactions

Whilst each of these CSVs can be imported separately in bulk, to link them together would be the challenge - which sale would map to which receipt / receivable / expense. If however, you have one combined csv having all the bank payments and sales and expenses pertaining to each transaction (one consolidated transaction per line entry in the CSV) in a single csv, you might be able to hammer out a PHP script to do it with MySQL's last_insert_id() function.

apmuthu's Website

  • Registered: 12/08/2015
  • Posts: 3

Re: Uploading transactions

Thanks for the quick reply apmuthu.

I should have mentioned that I plan to consolidate the sales reports from all sources into one CSV and/or sql query using Excel. Putting all related items into one record is easily doable.

While I'm a champ with Excel functions, DBs and VB, I do not have the skills to "hammer out a PHP script". Is that service available? How much do you think it would cost?

Steve

4 Reply by apmuthu (edited by apmuthu 12/08/2015 05:54:04 pm)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,095

Re: Uploading transactions

Actually you can do it all in excel and then paste it into any MySQL client like phpMyAdmin / SQLyog.

1. Take a backup of FA from within the Web GUI.
2. Enter a typical set of transactions from one record (row) in excel in several steps in FA in the Web GUI manually.
3. Take another backup of FA from within the FA Web GUI.
4. Compare the 2 Backups and see what has changed - tools like SQLyog can provide for database synch sql commands across databases (copies of before and after).
5. Using your Excel record's set of FA transactions SQLs, generate the SQLs using CONCATENATE for all the other records and paste them into the MySQL client.

apmuthu's Website

  • Registered: 12/08/2015
  • Posts: 3

Re: Uploading transactions

Pasting into mySQL is one of the methods that I was considering. I'm a little worried about missing something using the method above. What can go wrong, will. :-) I suspect that your single record per transaction in a CSV could be more bullet-proof. I have a small budget, but I can pay something for the script.

Could you point me to a DB schema that I can also use as a reference? That may give me more comfort in trying your latest suggestion.

Steve

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,095

Re: Uploading transactions

The ERD is available in the Wiki.

The method I outlined above is to make sure the "scripting" is right before making it into a php script - form to acquire the csv, validate it, process it.

apmuthu's Website