T -4: 'Open' transactions
In your old system, you'll have 'Open' Sales Orders and Purchase Orders, and perhaps even Sales Quotes. You need to identify and categorize each transaction type.
| # | From Quickbooks | From another system |
|---|---|---|
1 | Non-posting open transactions: Analyze and categorize Sales Orders, Purchase Orders, and Sales Quotes. | Non-posting open transactions: Analyze and categorize Sales Orders, Purchase Orders, and Sales Quotes. |
null | ||
2 | Opening non-posting open transactions: You now need to 'restore' the 'Historical Transactions', specifically the Sales Order, Purchase Order transactions you identified and categorized. Follow the 'How do I restore Historical Transactions' instructions below. | Import open non-posting transactions: Determine REF# settings. Populate and check the Import Transactions template. Import and check. |
null |
How much 'historical' data should I migrate?
The easy answer is 24 months*. In our experience, this is the minimum required for forecasting. You might disagree, it's your call. What you don’t really want to do is fill your new system with too much old data. If it’s not needed, don’t move it.
*by default the Quickbooks Transformer imports 24 months of data.
In the above section, I covered some of the challenges of retaining access to data in your old system. If you can affordably keep access then do it until you don’t need it.
How do I categorize my Quickbooks 'Open' Sales Orders?
Objectives:
Identify 'Open' Sales Orders, and categorize them as 'Not Invoiced' and 'Partially Invoiced'.
Adjust Item 'Qty's on Partially Invoiced Order data to reflect what still needs to be shipped.
Add all 'Open' Orders to the 'Upload Transactions' sheet, and upload them into your system.
First, you need to identify 'Open' Sales Orders.
Go to Quickbooks>Reports>Sales>Open Sales Orders by Item
Make sure you set the 'Dates' on this report to 'ALL'.
The report lists Item(s) by Sales Order(s) by Customer(s). Only 'Open' Sales Orders appear in this report. You need to categorize your Sales Orders as 'Not Invoiced' or 'Partially Invoiced'. Here's how:
Export the report to an Excel worksheet.
This will be your 'Open Sales Order migration tracking sheet. It's a good idea to name it as such with a date and time.
Open the Excel worksheet.
Notice the columns Item, corresponding Sales Order 'Num', Qty, and Invoiced.
Now work through the list of 'Open' Sales Orders and categorize each one.
Not Invoiced
If you have Excel skills, you can sort the sheet by Sales Order 'Num'. This will group all of your Items by Sales Order 'Num'. This will make the following exercise easier, and you're preparing your Sales Order data for import into Salesorder.com.
In the 'Qty' column, you see the total number of Items ordered per Sales Order together with the corresponding 'Num'. If the Invoiced column contains '0', the Items on the corresponding Sales Order, the Item Qty on this Order has not been Invoiced.
Check the rest of the Items on this Order, if all of the 'Qty' values are '0'. Record this Sales Order in the 'Not Invoiced' category.
Focus on collating all of your Sales Orders that are 'Not Invoiced'. Group them together in the sheet. These will be imported as 'Open Sales Orders, not invoiced.
Partially Invoiced
The remaining Sales Orders are 'Partially Invoiced'. Your next task is to rationalize the remaining Items on Sales Orders that have not been Invoiced.
The Customer for each order will have received the 'Partial Invoice'. You will have also shipped the stock on these partial orders. (You saw the warning at the start of this workbook right?).
Before you do this next step, I'll explain the strategy. You need a complete record of the original partially shipped Sales Order in your new system. This will include what is still to be fulfilled and what has been fulfilled (invoiced).
The quickest and safest way to do this in QuickBooks, print a copy of the Partially Invoiced Order. Using the Attachments action you'll link or attach this PDF to the imported Partial Order.
The data in rows for the Partially Invoiced Sales Orders need to be edited in your 'Open Sales Order migration tracking sheet.'
Copy and paste all of the Partially Invoiced Sales Order data to another sheet in your worksheet. It's critical you have a record of the original export and the categorization of each Sales Order. Name this sheet ' Partially Invoiced Orders'.
Your objective is to import the residual data including any notes, dates, etc into your new system as a new Sales Order with the same 'Num' or Ref# (Salesorder Transaction ID).
The original report exported the 'Qty' and 'Invoiced' values. Subtract the Invoiced values from the 'Qty' and change the 'Qty' to the result. This will be the Qty by Line Item on the new Sales Order in Salesorder.com.
When you've completed this for every Partially Invoiced Sales Order, you will have a new group of 'Open' Sales Orders to import into your new system.
When you import these Sales Orders into your new system, check they are correct and then upload the attachments onto each corresponding Sales Order.
One more thing, when you import a Sales Order with a 'Num' from Quickbooks into Salesorder.com using the 'Upload Transactions' template, the Num will appear on the 'Origin' tab in the 'External Cart Mapping' field.
If you have any questions, mail support@salesorder.com. We are here to help!
How do I categorize my Quickbooks 'Open' Purchase Orders?
See this article - How to categorize Quickbooks 'Open' Purchase Orders.
I've got Purchase Orders for multiple warehouses, what should I do?
Sites?: Where are the items on the purchase order going to be received, e.g. which geographical location, or which warehouse?
When you import these types of purchase orders they will be set to the default ‘Site’ in Salesorder. There’s a ‘Site’ column on the newly created/imported purchase order.
You’ll need to set up Sites if you’re using or not using the warehouse management system (WMS). If you’ve only one physical warehouse or using a 3PL (third-party logistics vendor) then chances are you’ll only need one site.
If you’re using multiple sites then go set up them up. Go to ‘Sites’ in helpwith.salesorder.com. When you’ve done this make sure you fill in the site column on the import spreadsheet. You’ll find this ‘Upload Transactions’ spreadsheet under Setup>Import Data in Salesorder.
More about the ‘Upload Transactions’ spreadsheet soon.
If the line items on the Purchase Order have been shipped and any payment has been made, then they are FOB (Freight on board). This means they are your stock, but have not yet been received in your physical warehouse. They are ‘in transit’.
If you’re using the Salesorder WMS, it’s best practice to set up the Location(s) to track the in-transit stock. You can find full details on how to configure Locations at helpwith.salesorder.com. Quick tip, you should set the Location to not pickable.
If you’re not using the WMS, and you need a second ‘not pickable’ site. Call this ‘In Transit’. Create this and receive the stock there. Your stock will be correct, but because all of your Sales Orders will set the Site column to the default ‘SIte 1’ then you’ll avoid allocating stock from the ‘In Transit’ site.
One more thing, before you go live you’ll do a stock count to confirm your stock levels. Make sure you include the ‘In Transit’ stock in the count.
Study and use the ‘Upload Transactions’ sheet to get your Purchase Orders into your new system. Any discrepancies in stock levels will most probably be caused by doing a double receive on the Line Items in the Purchase Orders. Do a stock adjustment according to the math.
How do I use the Upload Transactions template?
See Migrate Transactions.
How do I restore Historical Transactions
This task can only be carried out if you are logged into your Salesorder system as master administrator.
Find the Purchase Order or Sales Order Document you want to set to a status of 'Open' (from Historical).
Go to Actions>Restore
Save the Document.
The Document status will be set to 'Open'.
See Key Field Mapping to check the original reference numbers are correct.
Last updated