Data Migration from Quickbooks Point of Sale

February 24, 2019 20:55
56 comments

I don't expect Chris or the team to develop an entire automatic migration from QB POS - but I am posting here to see if anyone has done this and has any tips, pointers, or pitfalls to be aware of. I have about 6.5 years worth of sales data in QB POS but we are sick of paying the exorbitant licensing fees they charge for the privilege of upgrading every three years(despite minimal improvements to the software with upgrades) and are ready to migrate to a more modern POS system with more robust features and better support(thank you Chris!)

QBPOS will export Items, Customers, Vendors, Departments, and Employees to Excel, which would allow us to fairly easily set up a similar framework in PHPpos; but the most important feature, sales receipts/history, cannot be exported directly. There are other programs out there (an ODBC driver, and another custom one) which will dump this data into Excel or CSV format.

My gut is telling me that the best way to get this data into PHPPOS will be to either import it directly into the MySQL database, or use the API to do so. Any suggestions from anyone out there on where to start?

This is kind of a daunting task, but frankly the benefits of figuring this out far outweigh the headaches. If we can't pull the historical data over, then we face the reality of having to use BOTH pos systems - PHPPOS for current activity, and QBPOS for pulling historical sales data - at least until the remaining data in QBPOS is so old that it's obsolete - but frankly, that could be years.

This is made somewhat easier only because we have not ever in history accepted CC payments from within QBPOS - we have always used standalone terminals which do not communicate with QBPOS, so we do not have to worry about the complexities of handling and migrating sensitive payment card information at all.

Hopefully someone has some advice. :) Thanks in advance!

Comments

56 comments

Chris Muench
- February 25, 2019 03:44
I know Qb pos can export a lot of data; but can you share some data from reports that we can look at? Our api or database insert can be one way

0
Tynan Fox
- February 25, 2019 19:30
I think I'm going to have to use database insert. I was able to use a third-party ODBC driver to dump the entire database into an Access database which I can actually manipulate the data with.

The listing of tables and their columns is here:
https://doc.qodbc.com/qodbc/pos/TableList.php

My plan at this point is to extract the data into Excel/map the data to the appropriate PHPpos tables, save as a CSV, and then upload to MySQL.

Unless there's a better idea?

0
Chris Muench
- February 25, 2019 19:31
That should work well. All the tables are phppos_sales*. let me know if you have any questions.

0
Tynan Fox
- February 25, 2019 19:38
Do you have any reccomendations on what order to import the tables? Does it matter?

Like do I need to do Customers first, then Sales Receipts, then Deliveries? Or....?

I feel like as long as I stay out of the actual program while manipulating the database then there shouldn't be any major issues

0
Chris Muench
- February 25, 2019 19:39
It actually might be easier to use the API because it will handle putting things in right tables

0
Tynan Fox
- February 25, 2019 19:44
I was thinking that too in reality, and although I've learned a lot about php/mysql over the years, I'm not sure I have the skills to write a script to do this. I'm sure I could figure it out with enough time but I feel like I'd spend more time coding and debugging than I would creating a raw import.

I've combed through the DB schema for PHPpos in MySQL fairly thoroughly...maybe I'll try a couple of tables and see what happens

0
Tynan Fox
- February 25, 2019 21:45
Okay so after futzing around with transforming the data manually, I'm realizing that the biggest problem is not pulling the exact data needed for the tables but rather that I am developing on a Windows machine and that's not playing nicely with my Linux server.

Realizing that doing it this way will kill me if I continue on so I'm going with plan B and using the API instead.

0
Tynan Fox
- February 26, 2019 20:23
So I've written a couple PHP scripts to pipe the data through the API with some success. Can the "create_date" column in the "phppos_people" table be updated via the API? I may be splitting hairs here, but I don't want to lose that data. "You've been a valued customer since December 2013!", etc.

If not the API can it be updated via DB insert?

0
Chris Muench
- February 26, 2019 20:37
It is an undocumented feature of our API (as we don't really want people to do this); but you can pass created_at as a parameter and it will overwrite this.

0
Tynan Fox
- February 26, 2019 21:47
Do you know what the php "format" is for the Date/Time format required by the API?

I'm extracting the data alright but passing it in with "created_at" isn't working and I think it might be due to an incorrect date/time number format

0
Chris Muench
- February 26, 2019 21:48
YYYY-MM-DD

2019-01-02 for example

0
Tynan Fox
- February 27, 2019 04:53
I think I figured that out.

However now I am running into the problem with API call lockouts while running my migration script. I think you mentioned before that the default limit is 20 calls per minute but this can be overridden in the download version?

I just need to modify this for the migration and plan to change it back once in production

0
Tynan Fox
- February 27, 2019 05:12
Nevermind found it myself :)

0
Tynan Fox
- February 27, 2019 23:01
I'm having some trouble with pulling in Items via the API. I've tried passing in both the value of the "id" column and also the person_id column as the supplier_id to the API but no matter what I pick the supplier doesn't populate. Had any other reports of this or should I continue examining my code?

0
Chris Muench
- February 27, 2019 23:20
Can you post code sample (make sure you DONT put in api key or url)

0
Tynan Fox
- Edited February 28, 2019 01:02
I found the problem I believe and it's a bug report.

In /pos/application/controllers/api/v1/Items.php, you are missing any definitions for "supplier" in your private function _create_item($item_request)

I see it in the function for "Get"/search requests, but not the one for Post requests.

0
Tynan Fox
- February 28, 2019 01:20
As a short-term fix until you can look at this, I added this line to that function and it fixed my problem:

'supplier_id'=>isset($item_request['supplier_id']) ? $item_request['supplier_id'] : $this->Supplier->get_supplier_id($category_name),

0
Chris Muench
- February 28, 2019 02:12
Thanks, will patch tomorrow and let you know if I use same solution

0
Tynan Fox
- Edited February 28, 2019 02:57
As I am trudging along through my inventory list I found another missing option.

"is_service" can be passed through the API but similar to above, private function _create_item($item_request) is missing any data to process this :)

Fixed this for me:
'is_service'=>isset($item_request['is_service']) && $item_request['is_service'] ? 1 : 0

0
Chris Muench
- February 28, 2019 14:26
This has been patched.

0
Chris Muench
- February 28, 2019 14:26
Will be released in 1 hour

0
Tynan Fox
- February 28, 2019 20:47
I just downloaded the latest hotfix release but it doesn't show any changes....

0

Chris Muench

February 28, 2019 20:49

I just downloaded it again and it did change

diff --git a/shared/phppos/htdocs/application/config/constants.php b/shared/phppos/htdocs/application/config/constants.php

index 7699f43d..70af0d74 100644

--- a/shared/phppos/htdocs/application/config/constants.php

+++ b/shared/phppos/htdocs/application/config/constants.php

@@ -94,7 +94,7 @@ defined('EXIT__AUTO_MAX')      OR define('EXIT__AUTO_MAX', 125); // highest auto

 define('NUMBER_OF_PEOPLE_CUSTOM_FIELDS',10);

 define('APPLICATION_VERSION', '16.3');

 define('ASSET_TIMESTAMP', '1550247487');

-define('BUILD_TIMESTAMP', '1551191294');

+define('BUILD_TIMESTAMP', '1551363752');

 

 /*

 |--------------------------------------------------------------------------

diff --git a/shared/phppos/htdocs/application/controllers/api/v1/Items.php b/shared/phppos/htdocs/application/controllers/api/v1/Items.php

index 4db0cfc9..8544267e 100644

--- a/shared/phppos/htdocs/application/controllers/api/v1/Items.php

+++ b/shared/phppos/htdocs/application/controllers/api/v1/Items.php

@@ -65,6 +65,7 @@ class Items extends REST_Controller {

                                        'item_id' => (int)$item->item_id,

                                        'name' => $item->name,

                                        'item_number' => $item->item_number,

+                                       'supplier_id' => $item->supplier_id,

                                        'product_id' => $item->product_id,

                                        'size' => $item->size,

                                        'expire_days' => $item->expire_days,

@@ -834,6 +835,7 @@ class Items extends REST_Controller {

                                                'cost_price'=>isset($item_request['cost_price']) ? $item_request['cost_price'] : 0,

                                                'disable_loyalty'=>isset($item_request['disable_loyalty']) && $item_request['disable_loyalty'] ? 1 : 0,

                                                'tax_included'=>isset($item_request['tax_included']) && $item_request['tax_included'] ? 1 : 0,

+                                               'is_service'=>isset($item_request['is_service']) && $item_request['is_service'] ? 1 : 0,

                                                'change_cost_price'=>isset($item_request['change_cost_price']) && $item_request['change_cost_price'] ? 1 : 0,

                                                'override_default_tax'=>isset($item_request['override_default_tax']) && $item_request['override_default_tax'] ? 1 : 0,

                                                'tax_class_id'=>isset($item_request['tax_class_id']) ? $item_request['tax_class_id'] : NULL,

cmuench@cmuench:PHP-Point-Of-Sale-Installer (master=)$

Tynan Fox
- March 01, 2019 00:50
Sorry for the delay - I had some problems with the Dev server this afternoon.

I just gave it a shot with the latest update ensuring that patch above is applied but unfortunately it's still not working. Supplier isn't being imported at all and ALL my items are being set to "is_service" = TRUE

0
Chris Muench
- March 01, 2019 00:55
You need to create a supplier using the suppliers end Point; you can’t bundle it in with an item request

0
Tynan Fox
- March 01, 2019 01:05
Whoops, nevermind about the last part. I was passing the variable wrong

0
Tynan Fox
- March 01, 2019 01:06
And I did. I created the suppliers list FIRST and then ran the API calls to create the items. I can confirm that passing the person_id of the supplier to the API does not work

0
Chris Muench
- March 01, 2019 01:27
Did you pass it is supplier_id of person_id

0
Tynan Fox
- March 01, 2019 02:40
I passed it in as "supplier_id": (person_id, as an integer)

0
Chris Muench
- March 01, 2019 03:46
I have done another release that fixes this

0

Please sign in to leave a comment.

Data Migration from Quickbooks Point of Sale

Comments

<% if (!block.description) { %> <%= block.name %> <% } else { %> <%= block.name %> <% } %>

Can't find what you're looking for?

Categories

Toggle navigation menu

<%= category.name %>

Search

<% if (!block.description) { %> <%= block.name %> <% } else { %> <%= block.name %> <% } %>

Can't find what you're looking for?

Categories

Toggle navigation menu

<%= category.name %>

Categories