Customer Hierarchy in QuickBooks and Database

Pre-Requisites

Database should have three columns to store (1) Parent Name (type: String); (2) Child Name (type: String); and (3) Hierarchy level (type: Integer).  

Integration

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 and DBSync are the Parent Name and Child Names in Database. Then Avankia and DBSync are Customer and Job in QuickBooks respectively.
• Go to the location where pdl is present. Then, open the workflow where Customer hierarchy mapping to be done.
• Based on hierarchy level, the number of sequences has to be created within the workflow.
  • Example: If hierarchy level is 2 then, three sequences have to be created within the workflow.
• Mappings required in the first sequence:
  • The validate row should contain a condition to check ParentRef field that 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").

Once the above sequence are added, Save and Close the workflow.

Status Writer

Status writer is used to insert or update the table after an integration of a record. Status writer are to be enabled for each sequence under Rules section. The status message depends upon the success or error criteria as mentioned in the mapping.

Follow the steps to add status writer:

• On DBSync platform, under Customer Workflow, click 'More option' within the 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 new ID of QuickBooks with QuickBooks ID field of database. 
  • Write QuickBooks status field as "Synced".
  • Save and Close the page.
• Click "On Error". Mapping for error criteria are as follows:
  
  • 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:

• Under DBCustomer2QBCustomer workflow, click Advanced Query Builder and add condition "ParentName equal to null" along with other condition.
• Save and close the Advanced Query Builder page and save the DBCustomer2QBCustomer workflow.
• Create a new workflow DBCustomer2QBCustomerHirearchy that is similar to DBCustomer2QBCustomer.
• Select Datasource as Database.
• Click switch to advanced view. Then, add the same query as present in DBCustomer2QBCustomer.
• Change query condition "ParentName not equal to null" along with other conditions from the above query.
• Save and Close the Advanced Query Builder page.
• Create a new rule CustomerHirearchy.
• Select target Customer as QuickBooks. Then, 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. Then, Save the workflow.
• 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 the tutorial on how to build Customer hierarchy mappings to insert data from Database into QuickBooks.

Limitation 

• QuickBooks can support up to five hierarchy levels.