2022

Page tree
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 9 Next »

Introduction
Many of the business user want to integrate QuickBooks reports with the Database object , I always come across with the one very question from a standard business user of the QuickBooks  that Can QuickBooks reports be integrated to Database ? The short answer is yes if a user wants a specific report to be sent to Database, DBSync can handle it . In this tutorial we would be learning how a data from QuickBooks P&L report is read and exported to Database using DBSync Platform. 

DataBase Setup

For this very tutorial purpose , I have chosen MYSQL as the database for writing the data which has been read from the QuickBooks Profit and Loss Report. The below is the script for you to generate the database with table and relevant columns .

CREATE DATABASE /*!32312 IF NOT EXISTS*/`qbtodb` /*!40100 DEFAULT CHARACTER SET utf8 */;CREATE DATABASE /*!32312 IF NOT EXISTS*/`qbtodb` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `qbtodb`;

DROP TABLE IF EXISTS `profitandloss`;

CREATE TABLE `profitandloss` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `COA` varchar(255) DEFAULT NULL,
  `Amount` varchar(255) DEFAULT NULL,
  `ReportTitle` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `profitandloss` */

btodb`;/*Table structure for table `customdetailreport` */DROP TABLE IF EXISTS `customdetailreport`;
REATE TABLE `customdetailreport` (  `COA` varchar(255) DEFAULT NULL,  `Amount` varchar(255) DEFAULT NULL,  `Class` varchar(255) DEFAULT NULL,  `DueDate` varchar(255) DEFAULT NULL, `Name` varchar(255) DEFAULT NULL, `Address` varchar(255) DEFAULT NULL,  `OpenBalance` varchar(255) DEFAULT NULL,  `ReportTitle` varchar(255) DEFAULT NULL,  `Memo` varchar(255) DEFAULT NULL,  `TransactionType` varchar(255) DEFAULT NULL,  `RefNumber` varchar(255) DEFAULT NULL,  `TransactionNumber` varchar(255) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

QuickBooks P&L Report To Database Import
  1. Login to DBSync with valid credentials 
  2. Create a project with name of your choice.
  3. Create connectors preferably of type DataBase and QuickBooks Desktop.
  4. Configure the QuickBooks Desktop connector which is supported by DBSync, Please refer to DBSync QuickBooks Desktop connector documentation.
  5. Configure the DataBase Connector with any DB which is supported by DBSync, please refer to DBSync DBConnector  documentation for more information. 
  6. Create a process with meaningful name like QBP&LToDB which will be easy for other users to understand .
  7. Create a workflow with valid name , Trigger and Rules as explained in latter sections .

Trigger Setup

  1. Select the data-source as QB
  2. Build a query from the Advanced Query builder which would fetch the data from the Report.

Rules Setup

  1. Add a rule with name of user choice, select the DB connector and operation as insert. 
  2. Choose an object as ProfitandLoss.
  3. Now user has to do the mapping as per the need.

Running The Sync 
As its that now the Connectors, Trigger and Rules have been set appropriately, it's time to Run sync and check whether all the data of the Profit and Loss report is dumped in to ProfitandLoss table.

Hit the Run Now button located at the top right corner of the page as shown in the above screen. If sync is successful executed it shows up in the console , in case of errors also same will be displayed in the console.

After the successful completion of sync, go to the database named qbtodb and execute the below query to check the results as shown in below screen capture.

SELECT * FROM profitandloss

  • No labels