...
Database | QuickBooks |
Parent Name | Customer |
Child Name | Job |
Hierarchy | Sub level |
For Integration from QuickBooks into Database
...
- If Quickbooks Customer has hierarchy structures enabled, then the following changes have to be implemented within your DBSync mappings to facilitate hierarchy mappings.
- Let's say Avankia & DBSync are the Parent Name & Child Name in Database then Avankia & DBSync are Customer & Job in QuickBooks respectively.
- Go to the location where pdl is present and open the workflow where Customer hierarchy mapping to be done
- Based on hierarchy level the number of sequences has to be created with in the workflow.
- Example: If hierarchy level is 2 then three sequences has to be created with in the workflow
- Mappings required in the first sequence
- The validate row should contain a condition to check ParentRef field which contains Customer name so that the mapping for the sequence works
Example : IF(ISEMPTY(VALUE("ParentRef/ListID")),"true","false") - Map Name field of QuickBooks to Parent Name column in Database for the first sequence
Example : Parent Name - VALUE("Name") - Map Sublevel field of QuickBooks to Hierarchy column in Database. If Sublevel value is 0 then it refers to Parent Name
Example: Hierarchy - VALUE("Sublevel")
- Mappings required in the second sequence
- The validate row should contain the condition to check for the hierarchy level as 1
Example : IF(ISEMPTY(VALUE("ParentRef/ListID")),"false",IF(EQUALS(VALUE("Sublevel"),"1"), "true","false")) - Map Name field of QuickBooks to Child Name column in Database
Example: Child Name - VALUE("Name") - Map QuickBooks Customer Name with the Parent Name column in Database
Example : Parent Name - LSPLIT(VALUE("ParentRef/FullName"),":") - Map Sublevel field of QuickBooks to Hierarchy column in Database. If Sublevel value is 1 then it refers to first Child Name
Example: Hierarchy - VALUE("Sublevel")
- Mappings required in the third sequence
- Third and other sequence depends upon the number of Sublevel ie the hierarchy level of the Child Name
- If the hierarchy level is 2 then configure the third sequence with the following trigger and so on
Example : IF(ISEMPTY(VALUE("ParentRef/ListID")),"false",IF(EQUALS(VALUE("Sublevel"),"2"), "true","false")) - Map Sublevel field of QuickBooks to Hierarchy column in Database. If Sublevel value is 2 then it refers to second Child Name
Example: Hierarchy - VALUE("Sublevel")
...
- In DBSync platform, under Customer Workflow, click the More option within Rule section.
- Enable Update Source
- Select Update Source as Database
- Select Operations as Upsert
- Select Target object as table name (Example: customer_status_dbsync)
- Click "On Success" and following are the mapping for success criteria
- the validate row should contain the condition as "true" so that the mapping for the sequence works
- Map customer id of QuickBooks with customer id field of database
- Map newid of QuickBooks with QuickBooks id field of database
- Write QuickBooks status field as "Synced"
- Save and Close the page
- Click "On Error" and following are the mapping for error criteria
- the validate row should contain the condition as "true" so that the mapping for the sequence works
- Map customer id of QuickBooks with customer id field of database
- Write QuickBooks status field as "Not Synced"
- Save and Close the page
- Click "Save Workflow" once the above changes are made
For Integration from Database into QuickBooks
- Create a new workflow DBCustomer2QBCustomerHirearchy which is similar to DBCustomer2QBCustomer
- Select Datasource as Database
- Click switch to advanced view and add the same query as present in DBCustomer2QBCustomer
- Add where condition not equal to ParentName along with other conditions in the above query
- Save and close the Advanced Query Builder page
- Create a new rule CustomerHirearchy
- Select target customer as QuickBooks, select operation as insert and select target object as customer
- Use similar properties as given in customer insert sequence under DBCustomer2QBCustomer workflow such as batch size and primary key
- Click map and provide the mappings as given in customer insert sequence under DBCustomer2QBCustomer workflow
- Once the mappings are completed, add following changes in the Mapping window
- Target == "CustomerAddRq/CustomerAdd/ParentRef/FullName"
- Source Field == "VALUE("ParentName")"
- Save and close the rule, later save the workflow
- Similarly for customer insert rule under DBCustomer2QBCustomer workflow, in Advanced Query Builder under Query add where condition equal to ParentName along with other condition
- Once done with all the above-mentioned changes, run the sync and data will be integrated into Quickbooks with their respective hierarchies in place
- This concludes Customer hierarchy mappings to insert data from Database into Quickbooks.
Limitation
- QuickBooks can support up to five hierarchy level
...