QuickBooks Desktop to QuickBooks Online App ID Mapping

Companies using QuickBooks desktop that migrate to QuickBooks Online (via the “Export to QuickBooks Online” menu option in QuickBooks desktop) can have a hard time mapping the transaction and list ids associated with their QuickBooks desktop data to their QuickBooks Online data. When the data is migrated to QuickBooks Online, these IDs are no longer available via the QuickBooks Accounting API (V3 API services used IDs that are generated inside of QuickBooks Online). This problem is now addressed in the QuickBooks Online v88 release with a new mapping service, available for all companies that migrated after the QuickBooks Online v87 release on July 2, 2015. Details of this new API are described here.

Note: Currently, this mapping API is only applicable for apps using the QuickBooks XML SDK; companies taking advantage of Intuit Data Services cannot make use of this feature at this time.

The V3 API Query service has a new searchable entity called “QbdtEntityIdMapping”.  During migration of data from QuickBooks desktop to QuickBooks Online, we now have the IDs used in desktop with the equivalent IDs created in QuickBooks Online persistent in our system. We have this mapping for most of the data that is migrated from desktop to QuickBooks Online and the query service can be used to find this data.

Let’s take an example of an Invoice created in desktop and the SDK (simplified) response. The IDs of various entities are bolded below.

<invoiceret></invoiceret>
       <txnid>4-1439202892</txnid>
       <timecreated>2015-08-10T16:04:52+05:30</timecreated>
       <timemodified>2015-08-10T16:04:52+05:30</timemodified>
       <editsequence>1439202892</editsequence>
       <txnnumber>2</txnnumber>
       <customerref></customerref>
         <listid>80000001-1431947192</listid>
         <fullname>James</fullname>
    
       <araccountref></araccountref>
         <listid>80000023-1439202874</listid>
         <fullname>Accounts Receivable</fullname>

       …

If a third-party app had this Customer, Account, and Invoice stored within their system, then post migration to QuickBooks Online, the application would need to know the new ID assigned within QuickBooks Online to make an API call. Let’s break up the response to identify their various IDs.

QuickBooks desktop ENTITY	ID from SDK
INVOICE	4-1439202892
CUSTOMER	80000001-1431947192
ACCOUNT	80000023-1439202874

 

The V3 Query API for QbdtEntityIdMapping will help in identifying these equivalent IDs after they have been saved in QuickBooks Online.

Get INVOICE 4-1439202892 in QuickBooks Online

Query:

select * from QbdtEntityIdMapping where QbdtEntityType='INVOICE' and QbdtExportableId='4-1439202892'

Response:

    <queryresponse startposition="1" maxresults="1">
        <qbdtentityidmapping>
</qbdtentityidmapping></queryresponse>
            <qboentityid>1</qboentityid>
            <qbdtexportableid>4-1439202892</qbdtexportableid>
            <qboentitytype>INVOICE</qboentitytype>
            <qbdtentitytype>INVOICE</qbdtentitytype>
    

As you can see, invoice ‘4-1439202892’ from QuickBooks desktop can be mapped to ID=1 in QuickBooks Online.

Get CUSTOMER 80000001-1431947192 in QuickBooks Online

Query:

select * from QbdtEntityIdMapping where QbdtEntityType='CUSTOMER' and QbdtExportableId='80000001-1431947192'

Response:

    <queryresponse startposition="1" maxresults="1">
        <qbdtentityidmapping>
</qbdtentityidmapping></queryresponse>
            <qboentityid>1</qboentityid>
            <qbdtexportableid>80000001-1431947192</qbdtexportableid>
            <qboentitytype>CUSTOMER</qboentitytype>
            <qbdtentitytype>CUSTOMER</qbdtentitytype>

Get ACCOUNT 80000023-1439202874 in QuickBooks Online

Query:

select * from QbdtEntityIdMapping where QbdtEntityType='ACCOUNT' and QbdtExportableId='80000023-1439202874'

Response:

    <queryresponse startposition="1" maxresults="1">
        <qbdtentityidmapping>
</qbdtentityidmapping></queryresponse>
            <qboentityid>33</qboentityid>
            <qbdtexportableid>80000023-1439202874</qbdtexportableid>
            <qboentitytype>ACCOUNT</qboentitytype>
            <qbdtentitytype>ACCOUNT</qbdtentitytype>    

From the response above, Account ‘80000023-1439202874’ from desktop can be mapped to ID=33 from QuickBooks Online.

To complete the mapping in the table from above:

 

QuickBooks desktop ENTITY	ID from SDK	ID for QuickBooks Online
INVOICE	4-1439202892	1
CUSTOMER	80000001-1431947192	1
ACCOUNT	80000023-1439202874	33

 

The above queries can even be optimized to fetch all the mappings for all the entities data (paginated) or all the mappings per entity, for example:

select * from QbdtEntityIdMapping
select * from QbdtEntityIdMapping where QbdtEntityType='ACCOUNT'

Please note this special behavior:

• There will be some missing mappings when bulk data is fetched (by QbdtEntityType or all data). This is because of complexities during migration. To ensure all the mappings are available, call the bulk API, which will provide all the mappings. For the QbdtExportableId’s for which there are no mappings, call the Query filtered by QbdtEntityType and QbdtExportableId, for example:

  select * from QbdtEntityIdMapping where QbdtEntityType=<> and QbdtExportableId=<>

• These mappings are available for all the companies that migrated after v87 (released on July 2, 2015). There is no mapping available for companies that migrated prior to v87.

Read more about these changes in the tech docs and the V88 release notes.

Questions?  Share them in the comments section below and on our developer forums.