When we have to export invoices from Zoho CRM or to migrate any kind of data from one platform to another, it doesn’t make anyone happy. We have to evaluate the structure of the data, discover the dependencies between different data sets, explore the tables, — in other words, a lot of boring work with no space for errors. A perfect example of the Goldberg Machine, you name it.
Things get worse when we deal with relatively huge datasets, where manual control is near to impossible. You do the correct mapping, run your procedure, and hope for the best.
Zoho CRM to Zoho Books Migration Problem
While the data migration from, let’s say, Quickbooks to Zoho Books, or from Salesforce to Zoho is relatively common, the use case I want to talk about today represents the task that we don’t do very often. The data migration between Zoho CRM and Zoho Books. Normally both of the apps are doing their separate jobs just fine. Zoho CRM is responsible for customer relationship management, while Zoho Books works with the finances. Fine. After all, both of the apps are made by the same manufacturer.
There should be zero problems, but there are some. Let’s pretend that we need to export the invoices from Zoho CRM and import them into Zoho Books, with all the related Accounts, Items (Products), and their descriptions. Your first question will be the following:
How to Export Invoices from Zoho CRM?
You all know that Zoho CRM is a helluva Combine when it comes to the functionality, especially in its Enterprise edition. Among the other things, it can manage the quotes and invoices, even not being a tailored accounting software.
At first sight, we have the embedded export feature that allows us to export invoices from Zoho CRM. No problem at all, the procedure is even described in Zoho’s Help. It also can export not only the invoices, but any kind of data from other CRM modules, too.
Guess what? It is not enough.
The aforementioned export module, if used for the Invoices exports, literally, the invoices. The Invoices table, if you want a term from the relational model. You will get a perfect CSV file that comprises… right, the invoices and their Accounts. Only. No related data from the related Products tables, but we all remember that each Invoice have at least a Product or a Service.
The importer at Zoho Books wants it all: the Invoice data and the related Items a.k.a. Products / Services. So we have to get all of them together in the form of the CSV file that is required by Zoho Books.
If you ask Zoho support on that, most likely they will request the additional time, it depends on the knowledge of the particular agent. The skilled agent will explain that yes, the CRM exports only the Invoices table, but you need the Products as well. Because they are related tables.
At first sight, there are at least two ways:
- You will have to create and then merge three datasets (Invoice, Invoice Line Items, Products) as one CSV, in a way it meets all the required field in Books and import them.
- If both the CRM and the Books belong to the same Organization account and are integrated properly, you might write a Custom Function to push the CRM invoice into the Books.
The first way is relatively lengthy, as you will have to merge the large amounts of data that require some additional work. The second one is better, but what if your CRM and Books are from different Organizations? Yep, it’s not gonna work.
Additionally, you can request the full Zoho CRM data backup and use the data from it, but I don’t think this is the fastest way. And you need the appropriate user rights to download these backups.
After some research, I’ve realized that there is the third, the better way. Surprisingly, it is not described in the documentation that is available for the end-users and even for Zoho Partners (or I am just too bad in search to find it). It could be reinventing of the wheel, but the answer is the native Reports module, which is part of Zoho CRM.
Using the Reports Module for Data Migration Purposes
The priceless ability of the Reports Module is that you can design the multi-table reports, which are gathering the related data from different modules. In our case, we have to create a report that pulls the data from Invoices and Products, respectively:
Then you choose the Report Type as “Tabular Report”, it will give you the CSV where all the data will be concatenated line by line:
And don’t forget to select the fields you need using the “Report Representation” tab! The resulting CSV file, with the appropriate field mapping, will satisfy Zoho Books’ importer completely as it will contain all the necessary data.
Things to consider
- Depends on the data, your CSV might have empty lines, so it is necessary to control it before you import anything. Check everything twice, otherwise, the importer will create a huge mess in the target application.
- For some reason, Zoho CRM might lag when it exports the large data sets. If you have thousands of invoices and a well-developed Product structure, you might face the situation when you need to re-run the Report to get all the data undamaged (I don’t know why, but it happens). That is a rare circumstance, though.
- Make sure that the resulting CSV has the proper data format in its columts, use the sample CSV file from Zoho Books as an example.
- You have to migrate the Accounts from Zoho CRM into Zoho Books first because the Account Name field will be used by the importer to map the invoices properly.
- That procedure will replicate your old CRM-based invoices in Zoho Books along with the related Items.
- Keep in mind that no matter what migration method you use, the total time for that process depends on the condition and uniformity of the source data. It is not a couple of clicks, anyway. It is lengthy.
I wonder why that method has not been described in Zoho CRM help files. It utilizes the native CRM tool (the Reports module) and does not require the programming at all. I consider it as a very convenient way that allows enough control on the data migration process, and you save some time on writing the custom functions.
In my practice, the method works well even for the 5-6 MB CSV files with thousands of lines (but the importer is pretty much slow, though).