• From: USA, CA, Valencia
  • Registered: 12/31/2017
  • Posts: 84

Topic: Setting up Clients importing from prior system (Quickbooks)

Ok as stated I'm importing our customers to the our new FA System running Version 2.4.1 Build 22.05.2017 just in case for database consistency. Ive been looking at the database and I believe I found all the tables needed. Let me know if i have missed anything.

0_debtors_master:  to import the clients and some basic settings
0_crm_persons: to import clients customer relations / contact info such as billing address telephone and such
0_crm_contacts: to connect the clients crm_persons table to their debtors_master id
0_cust_branch: for the clients branch info and accounts affected

Is there any other tables needed or affected when creating a client?
Also is there anyway to give the client a starting / opening balance to not have to transfer over and open invoices?

Thank You,
Jason

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

Re: Setting up Clients importing from prior system (Quickbooks)

I wrote some scripts that can aid in a Quickbooks / FA migration: https://gist.github.com/braathwaate/250e13959d437f616b6936025ecd9f56.  They are not turnkey and would require coding skill to get working on another migration.

If all you want is the customer list, you could use the qbimport.sh script that uses facebook/webdriver and selenium to remote control the FA customer entry page.   The advantage of doing an import this way is that you don't need to know anything about the FA database structure.  Its also fun to watch the import.

I did not import the invoices, so each customer started out with zero balance.   Payments after the migration were double entered in Quickbooks and FA (until the customer in both accounting systems reached the same outstanding balance; i.e. all payments from Quickbooks era invoices were received).   Payments from invoices from the Quickbooks era were entered in FA journal entry.

Post's attachments

QuickBooks2FA.zip 6.2 kb, 11 downloads since 2018-01-04 

You don't have the permssions to download the attachments of this post.
  • From: USA, CA, Valencia
  • Registered: 12/31/2017
  • Posts: 84

Re: Setting up Clients importing from prior system (Quickbooks)

Ok I'm following up for anyone in the future importing clients. I finished importing about 1000 clients directly in the database and it worked beautifully. Everything I wrote above was correct just missing one thing. The 0_crm_contacts table connects 0_cust_branch to 0_crm_persons as well as the 0_debtors_master table so you should have twice  as many entries in 0_crm_contacts as you have clients (assuming your clients are 1 branch per client like how my data was.)

@Braath Waate Thank you for the suggestion of  QuickBooks2FA.zip I decided to stay working directly with the database because I was close to done already.

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

Re: Setting up Clients importing from prior system (Quickbooks)

Good job.  If you would like to share for future users, post your process or scripts.  I am guessing you did an export in Quickbooks, then wrote some script to map and push into the FA mysql database tables.

  • From: USA, CA, Valencia
  • Registered: 12/31/2017
  • Posts: 84

Re: Setting up Clients importing from prior system (Quickbooks)

@Braath Waate

HAHA not that fancy I'll go over it now.

1: Exported all clients from Quickbooks to a CSV file (option in the clients section)

2: Opened Front Acc. and entered the first client entry from the CSV Manually (Entered Customer, Branch and CRM Sections)

3: Opened the Database to look at the tables affected and the format (I used PhpMyAdmin on my server)

4: Exported each of the tables affected to CSV in PhpMyAdmin (0_debtors_master: 0_crm_persons:0_crm_contacts: 0_cust_branch:)

5: Copy and Paste the data from your QB Clients.CSV file into each of the exported FA DB CSV files in a spreadsheet editor (I used OpenOffice/Libre Office)

6: Import the DB CSV files on your server (You can use PHP commands i.e. LOAD DATA INFILE "/home/jason/clients.csv" but I used PhpMyAdmin as it has built-in CSV import.

Et Voila c'est fini, That is my fancy import process.