QuickBooks POS is an end-to-end solution for a retail business that enables full visibility across the entire business with seamless e-commerce integration, multichannel inventory management, contact less payment, and a lot more. It is specifically designed to automate POS functions and that also helps you in saving time as well as cost.
The user-friendliness along with customization, functionality, and accessibility makes it an ideal solution for businesses to import data from Microsoft Dynamics POS to QuickBooks POS. So, make every sale a new opportunity by importing your data to QuickBooks Point of Sale (POS) as it magnifies the speed and makes your things much easier and simple.
In this guide, we will learn more about importing data from Microsoft Dynamics POS to QuickBooks POS and its quick effective steps to do the same.
Find the Shortcuts
- About QuickBooks POS
- Extracting Data from Microsoft Dynamics POS
- Step-By-Step Guide for Exporting Data from Microsoft Dynamics POS
- SQL Queries to Get Raw Data Back from the Database
- To Get all Vendors Information, you Can Use Query as
- To Get all Departments, You Can Use Query as
- To Get all Customer information, You Can Simply Use the Query as
- For items, it is not as simple as we think. Here is the specific query that you can to retrieve all items with corresponding supplier names and category name:
- Step-By-Step Guide for Importing Records into QuickBooks POS
About QuickBooks POS
The relatively new entrant to the current industry is QuickBooks. The major benefit of QB is that it can seamlessly integrate with other QB apps, including QuickBooks Accounting, QuickBooks Pro, and sync inventory for online e-commerce sites with QB web connector.
Although I do not understand what format QuickBooks POS uses to store its data, it has a very useful tool for importing the following:
With plenty of instructions, it also contains some really useful templates. The QB POS templates for Customers, Inventory, and Vendors are an Excel spreadsheet containing one worksheet each.
Extracting Data from Microsoft Dynamics POS
Basically, there are two methods that can help you to extract data out of Dynamics POS. it includes:
- Using Inventory reports under Reports menu in the Management View/Console
- SQL Queries to get raw data back from the database.
Using either step, you can export the data to Excel spreadsheet or CSV files. If you use the Reports method for extracting your data; it is necessary to get the Bar Code Number for each product then only you can access several internal fields related to items/products in the inventory. This field is necessary when you import into QuickBooks POS because it acts as a unique identifier.
Step-By-Step Guide for Exporting Data from Microsoft Dynamics POS
Here are provided step by step instructions to export data from Microsoft Dynamics POS to QuickBooks POS:
- Initially, go to the Menu >> Tools >> Database >> Query Tool in order to get access to the Query Tool
- Next, Type SQL Query within the Query Tool Windows
- And then click Run
- Now, go to the Results tab and then Export the desired formats such as Excel, CSV, etc.
SQL Queries to Get Raw Data Back from the Database
Running SQL Queries is not supposed to be used with at least a cursory knowledge of database queries for those not versed. If you get used to it, the SQL syntax is straight forward. For example.
To Get all Vendors Information, you Can Use Query as
SELECT * FROM Supplier
This query gives you all the information related to Suppliers which you can further export to excel. The exported excel spreadsheet can be used for importing into QuickBooks POS.
To Get all Departments, You Can Use Query as
SELECT * FROM Department
To Get all Customer information, You Can Simply Use the Query as
SELECT * FROM Customer
For items, it is not as simple as we think. Here is the specific query that you can to retrieve all items with corresponding supplier names and category name:
SELECT Description, ItemLookupCode, Price, SalePrice, Cost, Quantity, ReorderPoint, SupplierID, ExtendedDescription, SubDescription1, SubDescription2,SubDescription3, BarCodeNumber, c.Name AS CategoryName, s.Name AS SupplierName FROM Item i LEFT OUTER JOIN Category c on i.CategoryID = c.ID LEFT OUTER JOIN Supplier s ON i.SupplierID = s.ID
This query provides you all the items along with their corresponding supplier names and category/department names as well.
Go to the Results window and select File >> Export. Name your exported file in a detailed manner consistent with the query. You can now choose the CSV or XLS file and then use it to import into QuickBooks POS.
Step-By-Step Guide for Importing Records into QuickBooks POS
- The very first, go to the Data Import Wizard by selecting File >> Utilities >> Import from the main menu
- A Data Import Wizard window open and then hit Next
- Now, select Vendors and then hit Next
- After that, pick Custom File and then hit Next
- Make sure, you select “Custom File” and NOT “Default Template”
- Click Browse and choose the spreadsheet file
- Select the appropriate worksheet under “Data is on Sheet”
- Tap on ‘Manage Mappings‘ afterward
- Here, between your spreadsheet columns and the QuickBooks Template, you can manually pick the mappings
- Pay careful attention to the fields “Required” highlighted in Red
- Offer a name to the map so that you can use it in the future
- Choose “Full Name Only” and “Prompt and allow me to decide.” It sets the action to be taken by QuickBooks to determine duplicates and what to do in case duplicates are identified
- The next window tells you how many vendors are in the register if there are any file processing errors, the number of vendors to be added, and the number of vendors to be updated.
QuickBooks POS is a smart, flexible, reliable, accurate and a dependable software. It can be integrated with various applications and software. Importing data into QuickBooks POS from Microsoft Dynamics can be quite a task. However, it can be easily done by following the steps above. In case you are unable to transfer data, you can contact our QuickBooks customer support team and they would assist you round the clock. They are available 24/7 to answer your questions and give a quick resolution without wasting time.