Cloud Workflow
Raise an IssueJoin Community
  • Getting Started
    • Introduction
    • Integration Basics
    • Sign Up and Get a License
      • Registration for Partner Reseller Portal
    • Features and Benefits
  • DBSync Platform
    • Core Components
    • System Requirements
    • AppCenter
      • Company and User Management
    • Development Studio
    • Security Features
  • iPaaS
    • Home and Dashboard
    • Apps
    • Extend
      • Functions
    • Administration
      • User & Role Management
      • Two Factor Authentication
    • Logs
    • Remote Agent
    • Published Templates
  • Create your Workflow
    • Project
    • Automation
      • Replication Bot
    • Task
    • Flow Builder
      • Standard Functions
    • Actions
      • Flow
      • Variable
      • Transform and Write
      • IDP - Intelligent Document Processing
      • SQL
      • Restructure
      • Query
      • Debug
      • ReST
      • Conditions
        • If (Condition)
        • For (Loop)
      • Storage
        • XML
        • Dropbox
        • File
        • CSV
        • Google Drive
        • FTP
        • S3
      • Flow Management
        • Flow
        • Logs
        • Notification
        • Schedule
        • API
        • Webhook
        • Forms
    • Configuring DBSync Environment
  • Connectors
    • Amazon S3
    • Authorize.NET
    • ChannelAdvisor
    • Database
    • Dropbox
    • Xero
    • Filevine
    • FTP
    • Google Drive
    • Google Sheets
    • HubSpot
    • JIRA Cloud
    • JIRA Service Management
    • Linnworks
      • Creating the DBsync Application on Linnworks Developer Instance
    • Mailchimp
    • Microsoft Dynamics 365 Business Central
    • Microsoft Dynamics 365 Finance and Operations (D365 F&O)
    • Microsoft Dynamics CRM Online
    • Microsoft Dynamics GP
    • Microsoft Dynamics NAV
    • monday.com
    • Narvar
    • NetSuite
      • NetSuite Connector Settings
    • OpenAPI
    • PointClickCare
    • QuickBooks Desktop
    • QuickBooks Online
    • Sage Intacct
    • Salesforce
    • ShipStation
    • Shopify
    • SkuVault
    • WooCommerce
  • Template Library
    • Salesforce and QuickBooks Online Integration
    • Salesforce and QuickBooks Desktop Integration
    • Salesforce to FTP Integration
    • Salesforce and NetSuite Rest Order to Cash
    • Salesforce to Xero Integration
    • QuickBooks Online to Business Central Integration
    • QuickBooks Online to ShipStation Orders
    • QuickBooks Desktop to Shipstation Orders
    • QuickBooks Desktop to monday.com Integration
    • QuickBooks Online to monday.com Integration
    • Shopify to QuickBooks Online
    • Shopify to QuickBooks Desktop
    • Linnworks Orders to QuickBooks Online
    • Linnworks Orders to QuickBooks Desktop
    • Linnworks to Salesforce Integration
    • Salesforce Litify and QuickBooks Online
    • Salesforce Litify and QuickBooks Desktop
    • Business Central to Salesforce Integration
    • HubSpot to QuickBooks Online Order to Cash
    • HubSpot to QuickBooks Desktop Order to Cash
    • SkuVault Orders to QuickBooks Desktop
    • SkuVault Orders to QuickBooks Online
    • SkuVault and Salesforce
    • ChannelAdvisor Orders and Salesforce
    • ChannelAdvisor and QuickBooks Online
    • ChannelAdvisor and QuickBooks Desktop
    • Dynamics365 CRM and QuickBooks Online
    • Shopify Orders to Dynamics365 CRM
    • Shopify Orders to Salesforce
    • ShipStation to Dynamics 365 CRM
    • ShipStation Orders to Salesforce
    • ShipStation to QuickBooks Online Orders
    • ShipStation to QuickBooks Desktop
    • Dynamics365 CRM to Xero Integration
    • Target Recruit to QuickBooks Online
    • Target Recruit to QuickBooks Desktop
    • QuickBooks Online to Database
    • QuickBooks Desktop to Database
  • Troubleshooting
    • Common Errors & Fixes
    • Adding Trusted IP Address in Salesforce
    • Installing QuickBooks Web Connector
    • QuickBooks to Salesforce Data Migration
    • Setting Password in QuickBooks Web Connector
    • Starting QuickBooks Web Connector
    • Update Salesforce Password & Security Token in DBSync
  • Additional Resources
    • Billing Information - Auto Renewal
  • Tutorials
    • Salesforce and QuickBooks
      • Account Hierarchy in Salesforce and QuickBooks
      • Account Owner Mapping
      • Resolving Email Fields Sync Issues in QuickBooks Online
      • Mulitple Price Levels in Salesforce QuickBooks Integration
      • Multicurrency in QuickBooks to Salesforce Integration
      • Multicurrency in Salesforce - QuickBooks Desktop Integration
      • Multicurrency in Salesforce QuickBooks Online Integration
      • Product Hierarchy in QuickBooks and Salesforce
      • QuickBooks Data Extraction Queries
      • QuickBooks File Backup and Recovery Process
      • QuickBoooks Reports to Salesforce Integration
      • Recurring Transactions to Accounting System
      • Retrieve Value from custom Field of QuickBooks Online
      • Salesforce Opportunity to QuickBooks - Disabling Jobs
      • Salesforce QuickBooks Custom Mappings
        • Use Case
      • Salesforce QuickBooks Integration with Multiple QuickBooks
      • Syncing Standard Group Line Items in QuickBooks Desktop
    • Using Date Function in DBSync Integration
    • Salesforce and Database
      • Database to Salesforce Integration
    • Database to Database
      • Database to Database Integration
Powered by GitBook
On this page
  1. Tutorials
  2. Salesforce and QuickBooks

QuickBooks Data Extraction Queries

PreviousProduct Hierarchy in QuickBooks and SalesforceNextQuickBooks File Backup and Recovery Process

Last updated 4 months ago

CtrlK

QuickBooks Queries

Knowledge of QuickBooks and QuickBooksXML is required for mapping and creating a process for integration with QuickBooks.

QuickBooks Desktop" adapter/connector provides interfaces for:

  • Reader: reading from QuickBooks with an SQL like query.

  • Writer: writing into QuickBooks.

  • Metadata query: navigating through QuickBooks objects.

You can build the queries of QuickBooks or QuickBooks Desktop Adapter from the Reader Section of the Process Definition. You can build queries similar to SQL, which includes the straightforward query.

Reader Query Examples:

Customer Query

The following is a query to extract all Customers in batches of 100 that have been changed since the last successful run of the process.

Select * from Customer where Customer.MaxReturned=100 and Customer.FromModifiedDate=$last_success_run
  • MaxReturned suggests how many records are to be retrieved from QuickBooks.

  • FromModifiedDate suggests the filter for selecting Customers that are edited or created after the previous sync run.

  • $last_success_run is a process-specific variable that gets set up whenever the ETL process is successfully completed.

Refer the filters from the QuickBooksXML. This is available in DBSync On-Premise version under this DBSync2\src\www\WEB-INF\conf folder. The XML Structures of all QuickBooks Objects are specified in this file.

ItemService Query

The following is a query to extract all ItemService in batches of 100 that have been changed since the last successful run of the process.

Select * from ItemService where ItemService.MaxReturned=100 and ItemService.FromModifiedDate=$last_success_run

Other items - such as ItemNonInventory, ItemInventory, and ItemDiscount - are not supported in QuickBooks Online Edition.

Complex query with a join

select * from Invoice,Customer where Invoice.MaxReturned=100 and Invoice.ModifiedDateRangeFilter.FromModifiedDate=$last_success_run
and Invoice.IncludeLineItems=true and Invoice.OwnerID=0 join on Invoice.CustomerRef.FullName=Customer.FullName

The above query joins Invoice and Customer QuickBooksXML responses into one XML. As a result, the transformation engine can avoid multiple processes. This is done to optimize data flow to and from QuickBooks.