Implications for FileMaker and other business application platforms
QuickBooks is a widely popular accounting management tool for small businesses. It offers an intuitive and cost-effective way for businesses to track financials, inventory, and more.
However, like all other business applications, its cloud version is quickly outpacing its desktop version. QuickBooks Online (QBO) allows SMBs to automatically adopt new features and integrate with relevant products, and team members can access accounting records and workflows from anywhere, at any time.
Unfortunately, migrating from the QuickBooks desktop version to QuickBooks Online isn’t simple. This process becomes even more complicated if you’re already integrating QuickBooks with critical business systems.
Many of our FileMaker clients are facing this exact challenge – they have critical information in FileMaker tied up to their QuickBooks data. If they transition to QuickBooks Online, they risk chaos within their systems, leading to business delays and lost revenue.
A Wrinkle in Migrating QuickBooks Desktop to QuickBooks: Critical FileMaker Data
1. Update ID Formatting
First, you need to change how your IDs are formatted in QuickBooks. We don’t know for sure why this step is necessary – simplification? Better URL references? – but it’s an important first step.
| QuickBooks Desktop ENTITY | ID from SDK | ID for QuickBooks Online |
|---|---|---|
| INVOICE | 4-1439202892 | 1 |
| CUSTOMER | 80000001-1431947192 | 1 |
| ACCOUNT | 800000023-1439202874 | 33 |
When you export and migrate from QuickBooks Desktop to QuickBooks Online, every record for every entity will be transferred from the old ID and be given a new ID. Relationships are maintained (whew!), so you’ll still see each customer’s invoices under the customer name.
If you’re concerned about how you currently reference IDs, we highly recommend fully exporting all of your QuickBooks desktop data and keeping it somewhere safe for reference.
2. Update IDs in Your FileMaker Application
That was a pretty straightforward step one, right? Here’s where things get tricky.
If you’re using a third-party tool (like FileMaker), you’ll need to change all of the IDs you’ve stored in that tool
Rematching Data
You have a few options here:
- Do nothing — ignore the past and live in the present! (Not recommended)
- Reference a 2015 blog post from Intuit and hope it remains active. (Most of the hyperlinks are now defunct)
- Take our route, which we’ve had to map out ourselves from that original blog post, through our fun challenges with this process.
If you’re going with route #3, let’s get into it. First, create a QuickBooks helper file in FileMaker. For each entity, you need to map, create a script that finds all of the records in QuickBooks Desktop. Then:
- Authenticate with QuickBooks Online
- Grant access to the QuickBooks Online file
- Initiate an API call from FileMaker to QuickBooks Online to retrieve all the mapping data for a particular entity.
QbdtEntityIdMapping API
Intuit Developer provides an overview on using the QbdtEntityIdMapping API. Learn more…
- Then parse through the response. Grab the desktop key and the QuickBooks Online key, and use that information to update.
Since you already store the desktop ID, create a new archive field I call “QuickbooksID_Desktop”. Look for that ID in the Customer table you’re trying to match for each row. If you find it, switch the IDs to the new QuickBooks Online ID. (This eliminates the need to change the ID references for all your existing QuickBooks sync work.)
Details to Consider
- The limit in a response is 1,000, so if you have more than 1,000 customers, you’ll need to do multiple queries.
- Some customers might not successfully migrate from desktop to QuickBooks Online, but that’ll be in your QuickBooks migration log. Take a thorough look to ensure all of your customers made it in your migration.
- Sales orders don’t exist in QuickBooks Online.
Next Steps in FileMaker and QuickBooks Online
If you get stuck on any of these steps or have questions, please drop a comment below or reach out to us on the FileMaker forum. If you have other applications you’d like to integrate – with FileMaker or other platforms – our development team can help. Contact us to talk with a consultant today.
