Integrate Data Easily
Extracting data from business applications such as QuickBooks and making it available for other applications or for further business use has become an important need for businesses. In addition, extracting data from applications on a periodic basis for use in reporting and analysis, has been a difficult task.
The system should ideally understand the unique nature of the application or data format in consideration. It should still provide powerful extract and data conversion features. At the same time, the ease of use in getting the tool to produce useful results, should be heavily emphasized.
QXchange is a powerful data integration tool to access, integrate and manage large volumes of data from various systems, particularly QuickBooks. Extraction, transformation and loading of data are powered by QXchange with source and target database support. Find more information here.
QXchange is used as the data integration layer for both www.qcommission.com and www.easy-commission.com sales commission software applications.
QXchange is a very easy to use, data integration software. The user interface is very simple and intuitive. Users can start using the application with no training. Most of the uses that the business users are faced with, can be easily established and executed from this tool. Data can be easily mapped by just dragging and dropping them to the right areas.
QXchange is flexible, easy to use, data integration software. It can quickly and easily transfer data between many standard data formats and applications, including QuickBooks, MS Access, Excel, Comma delimited flat file, MS SQL Server, MySql, ODBC, QuickBooks, Salesforce.com and others. It allows simple and clear setup of extraction, transformation and loading instructions to exchange data between different formats. The instructions are stored and managed as profiles.
Features
QXchange boasts a tremendous number of features to make the task of integrating QuickBooks data easy.
- Extraction - Extraction module allows the specification of various data sources.
- Transformation - Transformation allows the conversion of various data items.
- Loading - Loading module allows the creation or update of output data items.
- Profiles - ETL instructions are coded and saved as profiles for re-execution.
- Data Access Plug-ins - QXchange supports access to many different data formats and applications.
- Scheduling - Profiles can be established to run at specified times.
- Logging - Errors during the ETL process are logged. At the end of the process, the errors can be reviewed.
- Formula Expression - QXChange allows the creation of formula expressions using math, string and logical operators combined with data items, variables, constants and functions.
- Standard functions - QXchange has a full complement of pre-built excel-like functions to apply against data items. Functions types include Math, String and Date.
- Filter Input data - The source data can be limited further by applying filters to the data. The filters can be complex expressions using multiple data items in combination.
- Extract from Tables/Views/ Queries - Table objects from source can be extracted from.
- Views/Query - objects from source can be extracted from.
- View Data - Any dataset object can be selected and the data from that can be immediately viewed in a datasheet view, without actually running the ETL process. The data can be copied and pasted into Excel.
- Encryption Security - Critical information such as User and Password are encrypted with Blowfish 128 bit encryption.
QXchange is architected for robustness and ease of use. The use of profiles allows the preservation of integration instructions and reuse. The user interface is largely designed to be a profile manager to manage the setup of these instructions.
The different data formats and application types are segregated from the core application and made into plug-and-play modular entities called Data Access Plug-ins (DAP). Internally the DAPs understand the unique nature and constraints of the particular application or data format, but the DAP presents a similar interface back to the engine and the user interface. This minimizes complexity for the user. It also allows the easy addition of additional DAPs to the system.
The execution engine is the controlling entity for the entire tool. It uses the profile as the instruction set and calls on appropriate modules to execute the data integration tasks. The execution engine has three sections, extraction, transformation and loading.
A scheduling module allows the selection of a single or a set of profiles to be executed at a specific time, in addition to on-demand. Scheduling can be one-time or recurring.
Data Access Plug-ins
QXchange supports access to many different data formats. These data formats can be types of files (fixed length, comma delimited, XML), type of database (Access, MySQL) or type of application (QuickBooks, MS Dynamics AX). For every data format supported, there is a separate component called a Data Access Plug-in (DAP).
This allows sophisticated data format specific operations to be done. The DAPs are also optimized for the underlying data structures. The DAPs also enforce the security required by the underlying data structure.
Text File This DAP allows access to text files. Text files can be fixed length or delimited.
- Excel - This DAP allows access to Excel files. Individual worksheets within an Excel spreadsheet can be accessed.
- MS Access - This DAP supports the Microsoft Access database. Tables and query objects can be accessed. New Tables can be created.
- XML - This DAP supports the XML file format. XML tags can be used as data item labels.
- MySQL - This DAP supports the MySQL database format.
- MS SQLServer - This DAP supports the MS SQLServer database format.
- QuickBooks - This DAP supports the QuickBooks file format. It uses the vendor supplied QuickBooks SDK to do all the access and hence is compatible with all versions supported by the SDK.
- Salesforce.com - This DAP supports the Salesforce.com hosted environment. It uses the vendor supplied AppExchange SDK and API to do all the access .
- MS Dynamics AX and GP - This DAP supports the MS Dynamics accounting applications AX and GP.
- ODBC - This DAP supports the use of ODBC connections. This will allow access to any data format supported by an ODBC driver.
- Sage MAS500 - This DAP supports the use of the MAS500 application fro Sage.
QuickBooks Integration
QuickBooks SDK
Intuit, supplies the QuickBooks SDK (Software Development Kit) to allow third party products to work with QuickBooks. The SDK ensures that:
- The business sets up the permissions for third-party software access to the data
- Provides strong error recovery and data logging to make sure data is protected
- provides standardization across various versions of QuickBooks
Authentication and Authorization
QXchange uses a digital certificate to identify itself while communicating through the QuickBooks SDK. The application and certificate information is presented to the QuickBooks administrative user to allow access to the QXchange application.
The first time QXchange connects to QuickBooks, the user should have QuickBooks up and running and should be logged in with administrative authority. The administrator allows further use of QXchange as well as permissions to access certain types of data such as, Personal and Payroll Data.
Data Access Plug-in
QXchange integration engine uses Data Access Plug-ins (DAPs) for individual application and data format integration. All the DAPs are called using a standard Application Program Interface (API). The QuickBooks SDK is wrapped inside one of these DAPs. This allows for a smooth and standard operation across all the various DAPs.
Communication
Communication between QXchange and QuickBooks is based on a client/server model. All communication messages are in a special form XML called QBXML as required by the SDK. QXchange sends a message request to QuickBooks and QuickBooks sends back a response message; the communication managed synchronously.
Supported Versions
QCommission is supports US Editions of QuickBooks 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010 & 2011 (Pro, Premier and Accountant Editions), Enterprise Solutions 4.0, 5.0, 6.0, 7.0 , 8.0, 9.0,10.0 & 11.0 of US, Canadian and UK Editions and Online edition.
QuickBooks Data
The QuickBooks SDK exposes most (but not all) of the data objects available in the QuickBooks data file. The various objects available are listed here.
Lists
The following lists are available for access:
- Account List - Accounts
- Entity Lists - Customer, Customer Job, Vendor
- Item Lists - Item Inventory, Item Non-inventory, Item Inventory Assembly (Premier Edition and above), Item Service, Item Discount, Item Other Charge
- Simple Lists - Sales Rep, Price Level
Transactional Data
Transaction objects correspond to the basic accounting entities in a business. Adding a transaction object (except a non-posting transaction object) affects the account balance of the business. The following types of transactions are supported:
- Accounts Receivable Transactions - Invoice, Credit Memo, Receive Payment
- Accounts Payable Transactions - Vendor Credit, Bill, Bill Payment Check, Bill Payment Credit Card
- Non-Posting Transactions - Purchase Order, Estimate, Sales Order (US Premier Edition and above)
- Bank Transactions and Sales Receipts - Check, Deposit, Sales Receipt
- Other Transactions - Journal Entry
Using QXchange
The QXchange engine works the same for all different DAPs. Every data transfer is set up as a profile, with a source and target data file, which can be of different data formats. Data transfer between specific objects and the mapping of source to target fields are also set up in the profile.
The profile can be executed directly or can be scheduled to be executed later. The process can be run on a local desktop or on a server.
Source Data
QuickBooks can be defined as source data and the various objects can be accessed. Field level data from the objects can be extracted and mapped to destination objects and fields in different data formats. Data from multiple objects can be extracted. Multiple profiles can be set up to extract data from various objects.
Source data can also be filtered using various expressions. Prior to executing extract the source data can be viewed to make sure the right records are getting selected.
Target Data
QuickBooks can be defined as target data and the various objects can be updated. Data can be updated to multiple objects. In some cases update may have to affect multiple data objects at the same time, for example: Invoice Header and Line.
Data can be updated in various modes: Insert, Update and Upsert. Insert expects the record being updated to be new and unique. Update expects the record to already exist. Upsert, tries to insert first and if that fails tries to update.
Special Features
The QuickBooks application and SDK has some unique features that may impede easy integration. Other integration tools may not be able to handle these challenges. QXchange has specific abilities to handle these issues.
- Qxchange can create an Invoice and connect it to a Sales Order.
- It can create a Bill and connect it to a Purchase Order.
Uses
The QXchange ability to integrate QuickBooks allows many uses. Certain limitations in QuickBooks reporting can be exceeded with this tool. Fields such as "Paid Date" on invoice are normally not available to reporting tools. Fields such as internal keys and last modified date/time are also not available directly. Using QXchange, you can access to those fields. This allows many different scenarios:
- Extract paid invoices in the last month into Excel
- Extract customer information with company names starting with "A"
- Make a copy of all available QuickBooks data in an Access Databases, for use in reporting
- Insert spreadsheet invoice data sent from branch offices into QuickBooks
- Extract Opportunities from Salesforce.com and create Customer Jobs and Sales orders in QuickBooks.
- Extract Accounts from Salesforce.com and create Customers in QuickBooks.
- Extract Invoices from QuickBooks and update opportunities in Salesforce.com
- Get orders entered from an online e-commerce system and load them into QuickBooks as Sales Orders and Invoices.
- Get customer data entered online and create Customers in QuickBooks.
- Get vendor data entered online and create Vendors in QuickBooks.
Work together in QuickBooks Hosting on Pro, Premiere, and Enterprise with clients and co-workers.
Office Outlook 2007 offers a number of new and improved features, from Instant Search to integration of tasks on the calendar.
Right Networks is the leading provider of Exchange hosting. Learn more about our hosted Microsoft Exchange 2010.
Import Transaction or List Data to QuickBooks from Excel, Databases or Text Files. Save Time by Entering Data Once.
Take control of your small business financial transactions and easily manage your cash flow, bill payments, and invoicing.
ACCTivate! delivers features that enable businesses to automate, organize & adapt for sustainable, competitive advantage.
Access Peachtree Hosting on Complete, Premium and Quantum with your team from anywhere.
Automate the entire bill payment process, saving you time and resources by streamlining approvals and more.
Automate the entire bill payment process, saving you time and resources by streamlining approvals and more.
Document Management Solution reating vendor & customer drawers inside eFileCabinet only takes seconds with integration.
Extracting data from business apps & making it available for other apps has become an important need for businesses
Office Tools Pro. is an Independent Software Vendor that utilizes Microsoft to provide high-quality resources
Internet Payroll MyPayrollHR combines a powerful yet intuitive web-based human resource and online payroll solution.
ICAT installs easily and works with QuickBooks, computing indirect rates and allocating indirect costs to contracts.
Profitably provides powerful financial analytics to growing businesses. Because it's time to own the numbers.
Legrand CRM is fast becoming the solution of choice for growing and mid-sized businesses avoiding you the high-costs.
Award-winning QuickBooks-integrated CRM to grow sales, improve cash flow, timesheets and billing, and enhance forecasting!
Integrate the power of QuickBooks with the ease of the Internet to provide a powerful document management solution.
A low cost, highly flexible way to manage payments from your customers without the hassle of double data entry.
Save time with our easy-to-use online time tracking, billing, expense reporting and project accounting solution.
Take control of your QuickBooks data to manage your business. Unlock the full potential of your information!
