2022

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

Many business users wonder - Can I integrate QuickBooks reports with the Database object?  Yes, absolutely!  In this tutorial, we will learn how data from QuickBooks P&L report is read, and exported, to Database using the DBSync Platform. 

DataBase Setup

For this tutorial I selected MYSQL as the Database for writing the data that has been read from the QuickBooks Profit and Loss Report. Below is the script for you to generate the Database with tables and relevant columns .

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

USE `QuickBookstodb`;

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 the name of your choice.
  3. Create Connectors - preferably of type Database and QuickBooks Desktop.
  4. Configure the QuickBooks Desktop Connector that is supported by DBSync. Please refer to DBSync QuickBooks Desktop Connector documentation.
  5. Configure the DataBase Connector with any Database that is supported by DBSync. Please refer to DBSync DBConnector documentation for more information. 
  6. Create a Process with a meaningful name like QuickBooksP&LToDB that will be easy for other users to understand.
  7. Create a workflow with a valid name, Trigger, and Rules as explained in later sections.

Trigger Setup

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

Rules Setup

  1. Add a Rule with the name of user's choice. Select the DB Connector and Operation as an insert. 
  2. Choose an object as Profit and Loss.
  3. Next, do the mapping as per your needs.
Running The Sync 

Connectors, Trigger, and Rules have been set appropriately. Now it is time to Run sync and check whether all the data of the Profit and Loss report is dumped into Profit and Loss table.

Click 'Run Now' button located at the top right corner of the page as shown on the screen above. If sync is successfully executed it shows up in the console. However, in case of errors, it too will displayed in the Console.

After successful completion of sync, go to the database named QuickBookstodb and execute the following query to check the results as shown on following screen.

SELECT * FROM profitandloss

  • No labels