2022
Code Block | ||||||||
---|---|---|---|---|---|---|---|---|
| ||||||||
USE [DBSyncDB]
CREATE TABLE [dbo].[account](
[id] [varchar](20) NULL,[Name] [varchar](50) NULL,[firstname] [varchar](50) NULL,[lastname] [varchar](50) NULL,
[phone] [nchar](20) NULL,[fax] [nchar](20) NULL,[billingstreet] [varchar](200) NULL,[billingcity] [varchar](50) NULL,
[billingstate] [varchar](50) NULL,[billingcountry] [varchar](50) NULL,[billingcode] [varchar](50) NULL,[company] [nchar](100) NULL
) ON [PRIMARY]
GO
USE [DBSyncDB]
CREATE TABLE [dbo].[invoice](
[invoice_no] [nchar](10) NULL,[customerid] [nchar](100) NULL,[item] [nchar](100) NULL,[itemdesc] [nchar](100) NULL,
[quantity] [nchar](10) NULL,[uom] [nchar](50) NULL,[salesprice] [nchar](10) NULL,[total] [nchar](10) NULL,
[billaddress] [nchar](100) NULL,[billingcity] [nchar](100) NULL,[billingstate] [nchar](100) NULL,
[billingpostalcode] [nchar](10) NULL,[billingcountry] [nchar](100) NULL,[date] [nchar](50) NULL
) ON [PRIMARY]
GO
|
Code Block | ||||||||
---|---|---|---|---|---|---|---|---|
| ||||||||
CREATE TABLE `dbsyncdb`.`account` (`id` VARCHAR(20) DEFAULT NULL,`Name` VARCHAR(50) DEFAULT NULL,`firstname` VARCHAR(50) DEFAULT NULL,
`lastname` VARCHAR(50) DEFAULT NULL,`phone` VARCHAR(20) DEFAULT NULL,`fax` VARCHAR(20) DEFAULT NULL,
`billingstreet` VARCHAR(200) DEFAULT NULL,`billingcity` VARCHAR(50) DEFAULT NULL,`billingstate` VARCHAR(50) DEFAULT NULL,
`billingcountry` VARCHAR(50) DEFAULT NULL,`billingcode` VARCHAR(50) DEFAULT NULL,`company` VARCHAR(100) DEFAULT NULL
)
ENGINE=INNODB DEFAULT CHARSET=utf8;
/*Table structure for table `invoice` */
CREATE TABLE `dbsyncdb`.`invoice` (`invoice_no` CHAR(10) DEFAULT NULL,`customerid` CHAR(100) DEFAULT NULL,`item` CHAR(100) DEFAULT NULL,
`itemdesc` CHAR(100) DEFAULT NULL,`quantity` CHAR(10) DEFAULT NULL,`uom` CHAR(50) DEFAULT NULL,`salesprice` CHAR(10) DEFAULT NULL,
`total` CHAR(10) DEFAULT NULL,`billaddress` CHAR(100) DEFAULT NULL,`billingcity` CHAR(100) DEFAULT NULL,
`billingstate` CHAR(100) DEFAULT NULL,`billingpostalcode` CHAR(10) DEFAULT NULL,`billingcountry` CHAR(100) DEFAULT NULL,
`date` CHAR(50) DEFAULT NULL)
ENGINE=INNODB DEFAULT CHARSET=utf8;
|
Code Block | ||||||||
---|---|---|---|---|---|---|---|---|
| ||||||||
CREATE TABLE "SYS"."ACCOUNT" ("id" VARCHAR(20 BYTE) DEFAULT NULL,"Name" VARCHAR(50 BYTE) DEFAULT NULL,"firstname" VARCHAR(50 BYTE) DEFAULT NULL,
"lastname" VARCHAR(50 BYTE) DEFAULT NULL,"phone" VARCHAR(20 BYTE) DEFAULT NULL,"fax" VARCHAR(20 BYTE) DEFAULT NULL,
"billingstreet" VARCHAR(200 BYTE) DEFAULT NULL,"billingcity" VARCHAR(50 BYTE) DEFAULT NULL,"billingstate" VARCHAR(50 BYTE) DEFAULT NULL,
"billingcountry" VARCHAR(50 BYTE) DEFAULT NULL,"billingcode" VARCHAR(50 BYTE) DEFAULT NULL,
"company" VARCHAR(100 BYTE) DEFAULT NULL
);
/*Table structure for table `invoice` */
CREATE TABLE "SYS"."INVOICE" ("invoice_no" CHAR(10 BYTE) DEFAULT NULL,"customerid" CHAR(100 BYTE) DEFAULT NULL,"item" CHAR(100 BYTE) DEFAULT NULL,
"itemdesc" CHAR(100 BYTE) DEFAULT NULL,"quantity" CHAR(10 BYTE) DEFAULT NULL,"uom" CHAR(50 BYTE) DEFAULT NULL,"salesprice" CHAR(10 BYTE) DEFAULT NULL,
"total" CHAR(10 BYTE) DEFAULT NULL,"billaddress" CHAR(100) DEFAULT NULL,"billingcity" CHAR(100) DEFAULT NULL,
"billingstate" CHAR(100 BYTE) DEFAULT NULL,"billingpostalcode" CHAR(10 BYTE) DEFAULT NULL,"billingcountry" CHAR(100 BYTE) DEFAULT NULL,
"date" CHAR(50 BYTE) DEFAULT NULL);
|
...
...
11.Click on Validate link for the Database adapter. If the credentials entered are accurate, you will get a pop-up message saying the "Connection Settings are Valid".
12. At this point, you have successfully validated your Database Adapter.
Panel |
---|
Quickbooks setup |
Panel |
---|
4. Click on configuration Link at the bottom of the page. A Pop-up box will appear, prompting you to open the file with "QuickBooks Web Connector (default)", click on OK button as . Click OK as highlighted below in screenshot.
5. Click on Click OK in the next window (first screenshot below) and on the next window . Then, select the option button "Yes, always; allow access even if QuickBooks is not running" and check the check box for "Allow this application to access personal data" and click on continue button . Finally, click continue (second screenshot below).
...
6. In the Access Confirmation window click on the Done button as , click Done - as shown above . And the profile will get added to your QuickBooks Web Connector and a screenshot . A screenshot of the QuickBooks Web Connector is shown below.
...
7. Please enter your DBSync password in the password section of the Web Connector and save itSave.
8. At this point, you have successfully completed configuring your Quickbooks with DBSync. You can run the integration by clicking on the Update Selected button.
Panel |
---|
Custom Account Mapping For Account And Invoice Tables |
If a user wishes to set the properties,
can be dones/he can do it from the Properties Tab
Code Block |
---|
selectSelect * from account |
5. For a rule created, set the following properties by selecting the Properties Tab.
Table : CustomerAddRq
maxbatchsize : 1
...
6. Click on the Map link from the available with options under the Rule to set the following field to field maps*.*
Target | = | Source |
---|---|---|
CustomerAddRq/CustomerAdd/BillAddress/Addr1 | = | VALUE("billingstreet") |
CustomerAddRq/CustomerAdd/BillAddress/City | = | VALUE("billingcity") |
CustomerAddRq/CustomerAdd/BillAddress/Country | = | VALUE("billingcountry") |
CustomerAddRq/CustomerAdd/BillAddress/PostalCode | = | VALUE("billingcode") |
CustomerAddRq/CustomerAdd/BillAddress/State | = | VALUE("billingstate") |
CustomerAddRq/CustomerAdd/CompanyName | = | VALUE("company") |
CustomerAddRq/CustomerAdd/Fax | = | VALUE("fax") |
CustomerAddRq/CustomerAdd/FirstName | = | VALUE("firstname") |
CustomerAddRq/CustomerAdd/IsActive | = | "true" |
CustomerAddRq/CustomerAdd/LastName | = | VALUE("lastname") |
CustomerAddRq/CustomerAdd/Name | = | VALUE("Name") |
CustomerAddRq/CustomerAdd/Phone | = | VALUE("phone") |
VALIDATEROW | = | "true" |
At this point, your DBSync is set to move Account records from your database table to Quickbooks Customer.
...
...
4. Make sure the XML Formatter section is filled up populated with the following code.
Code Block |
---|
customerid,billaddress,billingcity,billingstate,billingpostalcode,billingcountry,date, invoice_no,[item,itemdesc,quantity,salesprice] |
...
The Rule mapping can be mapped from the below following table .
Target | = | Source |
---|---|---|
InvoiceAddRq/InvoiceAdd/BillAddress/Addr1 | = | VALUE("billingaddress") |
InvoiceAddRq/InvoiceAdd/BillAddress/City | = | VALUE("billingcity") |
InvoiceAddRq/InvoiceAdd/BillAddress/Country | = | VALUE("billingcountry") |
InvoiceAddRq/InvoiceAdd/BillAddress/PostalCode | = | VALUE("billingpostalcode") |
InvoiceAddRq/InvoiceAdd/BillAddress/State | = | VALUE("billingpostalcode") |
InvoiceAddRq/InvoiceAdd/CustomerRef/FullName | = | VALUE("customerid") |
InvoiceAddRq/InvoiceAdd/InvoiceLineAdd | loop | "invoice_no/list" |
InvoiceAddRq/InvoiceAdd/InvoiceLineAdd/Desc | = | VALUE("itemdesc") |
InvoiceAddRq/InvoiceAdd/InvoiceLineAdd/ItemRef/FullName | = | VALUE("item") |
InvoiceAddRq/InvoiceAdd/InvoiceLineAdd/Quantity | = | VALUE("quantity") |
InvoiceAddRq/InvoiceAdd/InvoiceLineAdd/Rate | = | VALUE("salesprice") |
InvoiceAddRq/InvoiceAdd/IsPending | = | "true" |
InvoiceAddRq/InvoiceAdd/TxnDate | = | VALUE("date") |
VALIDATEROW | = | "true" |
At this point, you have configured your DBSync profile to integrate both the Accounts and Invoice tables that were created in your SQL Server Database. A similar procedure can be followed for other databases like MYSQL,Oracle,DB2 etc. The only thing that would be changing different, within DBSync, would be the connection strings for the respective databases.