Requests that merge accounts customers vendors classes

Merging certain types of list objects is a useful feature of QuickBooks. For example, merging accounts is useful for eliminating redundant accounts within a given account type. For example, a company file might have an expense account that tracks paper reams and a different expense account that tracks office supplies. You may want to change this to have only a single account–the office supplies account–that tracks paper reams along with other office supplies.

The QuickBooks UI solves these types of problems by allowing users to merge accounts, customers, and vendors. Beginning with qbXML spec 7.0 and QuickBooks 2008, these types of list merge operations can be performed through the SDK by using the ListMerge request. The ability to merge Classes was added in qbXML 8.0 and QB 2009.

This page describes the use of ListMerge and the work you need to do before you actually invoke ListMerge.

SDK support for merging

ListMerge is a request that allows you to merge the following:

Note

Note

ListMerge requires the company file to be opened in single user mode.

What happens in the ListMerge operation?

What happens in the merge is that one of the objects, the “merge-from” object, is merged into the other object, the “merge-to” object. For example, suppose the expense account “Paper Reams” is merged into the expense account “Office Supplies.” After the merge, there is only the “Office Supplies” account, and all transactions in the past that referenced “Paper Reams” now automatically reference “Office Supplies.” This means that balances will change in the merged-to object as it receives the merged-from balances.

When can I not do a ListMerge?

You cannot do a ListMerge if an accountant copy of the company exists–if you do, you’ll get an error. The idea is that any merges ought to be done in the accountant copy. Beginning with QB 2009 and qbXML 8.0, you can use the Account Copy Exists request to check for this before issuing the list merge request.

Can I undo or reverse a ListMerge?

No.

What must I do before merging?

The merging rules are not many in number, but they are different for accounts, customers, and vendors. The following table shows the rules applying to each:

Objects to Merge Required Pre-Merge Conditions How to Satisfy the Conditions
Accounts
  • Accounts must be of the same AccountType
  • Accounts must be at the same hierarchical level within the chart of accounts.
  • If the AccountTypes are different, you cannot proceed. You cannot change the AccountType.
  • You can change the Sublevel of one account so that it matches the Sublevel of the other account. For more information, see “Merging Accounts.”
Class No requirements NA.
Customers
  1. Only one of the customer/customer jobs can have child elements.
  2. You cannot merge a customer and a vendor.
  1. If you merge customers, only one of the customers can have customer jobs. If you merge customer jobs, only one of them can have children (sub-jobs).
  2. Cannot change this.

If merging customers and both customers have jobs, you must move jobs from the merge-from customer to the “merge-to” customer before invoking ListMerge. Or, you must make them inactive or delete them.

If merging customer jobs, only one of them may have child jobs (sub jobs). If both have children, you must move child jobs from merge-from object to the merge-to object BEFORE doing the merge. Or make them inactive or delete them.

For more information, see “Merging Customers,” below.
Vendors You cannot merge a customer and a vendor Cannot change this.
Merging accounts

To merge two accounts,

  1. Compare AccountType of the merge-from account with the AccountType of the merge- to account. If they aren’t the same, stop, you cannot continue. If the AccountTypes are the same:
    1. Compare the Sublevel of the merge-from account with the Sublevel of the merge-to account.
    2. If the Sublevels are not the same, change the Sublevel of either the merge-from account or the merge-to account so that the Sublevels match.
  2. Using the current EditSequence for each account, and their ListIDs, invoke ListMerge and check the response for success.
Comparing AccountType and changing Sublevel

The Account object contains an AccountType field. You need to compare this type before proceeding to merge accounts.

If the AccountType is the same, you next need to determine whether the accounts to be merged have the same Sublevel (yes, there is a Sublevel field in the Ret!). If Sublevels are not equal, you need to do an AccountMod on one of them to change its Sublevel–by changing the ParentRef (or removing it if a Sublevel of 0 is desired).

The following code snippet does a query for two hardcoded accounts, and arbitrarily picks the first as the “merge-from” account, and the second as the “merge-to” account, and adjusts the Sublevel accordingly. After the Sublevel is fixed by changing the ParentRef, we invoke AccountMod to make the changes, store the changed EditSequence, and then do the list merge.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
SessionManager.OpenConnection "", "IDN List Merge Sample"
SessionManager.BeginSession "", omDontCare
Dim ListMerge_Set As IMsgSetRequest
Set ListMerge_Set = SessionManager.CreateMsgSetRequest("US", 7, 0)
ListMerge_Set.Attributes.OnError = roeContinue

‘Do query to get latest Sublevel and EditSequence data for the Accounts:
‘We hardcode the accounts just for convenience
Dim AccountQuery As IAccountQuery
Set AccountQuery = ListMerge_Set.AppendAccountQueryRq
AccountQuery.ORAccountListQuery.FullNameList.Add "OfficeSupplies:Paper Reams"
AccountQuery.ORAccountListQuery.FullNameList.Add "Office Supplies"
Dim ListMergeAddResp As IMsgSetResponse
Set ListMergeAddResp = SessionManager.DoRequests(ListMerge_Set)

‘Process the query response: we need ListID, EditSequence, Sublevel, and
‘of course, AccountType.
Dim MyResponse As IResponse
Dim AccountRet As IAccountRet Dim ResponseType As Integer
Dim AccountRetList As IAccountRetList
Dim AccountType_MergeFrom As Integer
Dim AccountType_MergeTo As Integer
Dim Sublevel_MergeTo As String
Dim Sublevel_MergeFrom As String
Dim ListID_MergeFrom As String
Dim ListID_MergeTo As String
Dim EditSequence_MergeTo As String
Dim EditSequence_MergeFrom As String
Dim ParentRef_MergeTo As String
ParentRef_MergeTo = "empty"
Dim ParentRef_MergeFrom As String
ParentRef_MergeFrom = "empty"
Set MyResponse = ListMergeAddResp.ResponseList.GetAt(0)
If (Not MyResponse.Detail Is Nothing) Then
  ResponseType = MyResponse.Type.getValue
  If (Not ResponseType = rtAccountQueryRs)
    Then Exit Sub
  End If
End If

‘Store the values from the first account, which we arbitrarily use as the ‘merge from account.
Set AccountRetList = MyResponse.Detail
Set AccountRet = AccountRetList.GetAt(0)
ListID_MergeFrom = AccountRet.ListID.getValue
EditSequence_MergeFrom = AccountRet.EditSequence.getValue
AccountType_MergeFrom = AccountRet.AccountType.getValue
Sublevel_MergeFrom = AccountRet.Sublevel.getValue

‘If Sublevel is 0, there won’t be any ParentRef
If (Not AccountRet.ParentRef Is Nothing)
  Then
    ParentRef_MergeFrom = AccountRet.ParentRef.FullName.getValue
End If

‘Store the values from the second account, which we arbitrarily use as
‘the merge-to account.
Set AccountRet = AccountRetList.GetAt(1)
ListID_MergeTo = AccountRet.ListID.getValue
EditSequence_MergeTo = AccountRet.EditSequence.getValue
AccountType_MergeTo = AccountRet.AccountType.getValue
Sublevel_MergeTo = AccountRet.Sublevel.getValue
If (Not AccountRet.ParentRef Is Nothing)
  Then
    ParentRef_MergeTo = AccountRet.ParentRef.FullName.getValue
End If

‘Now for the checking: AccountTypes and Sublevels must match.
If (Not AccountType_MergeFrom = AccountType_MergeTo)
  Then
    MsgBox "Only accounts of the same AccountType can be merged!)"
   Exit Sub
End If

‘For convenience only, we just check for the condition where merge-from
‘has higher sublevel than merge-to. If merge-to sublevel is 1 or greater,
‘we set the merge-from ParentRef equal to the ParentRef of the merge-to,
‘as this results in same Sublevel. If merge-to has no ParentRef, we make
‘merge-from have no ParentRef either.
If (Sublevel_MergeFrom > Sublevel_MergeTo) Then If (Not ParentRef_MergeTo = "empty")
  Then
    ParentRef_MergeFrom = ParentRef_MergeTo Else
    ParentRef_MergeFrom = "empty"
End If

‘Now Mod the merge-from account, changing it’s ParentRef as needed ListMerge_Set.ClearRequests
Dim AccountMod As IAccountMod
Set AccountMod = ListMerge_Set.AppendAccountModRq
AccountMod.ListID.setValue ListID_MergeFrom
AccountMod.EditSequence.setValue EditSequence_MergeFrom
If (ParentRef_MergeFrom = "empty")
  Then
    AccountMod.ParentRef.FullName.SetEmpty Else
    AccountMod.ParentRef.FullName.setValue ParentRef_MergeFrom End If
End If
Set ListMergeAddResp = SessionManager.DoRequests(ListMerge_Set)

‘Process the response to the AccountMod to get the updated EditSequence.
Set MyResponse = ListMergeAddResp.ResponseList.GetAt(0)
If (Not MyResponse.Detail Is Nothing)
  Then
    ResponseType = MyResponse.Type.getValue
  If (Not ResponseType = rtAccountModRs)
    Then MsgBox "unexpected response type"
    Exit Sub
  End If
End If
Set AccountRet = MyResponse.Detail
EditSequence_MergeFrom = AccountRet.EditSequence.getValue

‘We have everything we need to do the merge, so let’s do it.
Dim ListMergeAdder As IListMerge
ListMerge_Set.ClearRequests
Set ListMergeAdder = ListMerge_Set.AppendListMergeRq
ListMergeAdder.ListMergeType.setValue lmtAccount
ListMergeAdder.MergeFrom.ListID.setValue ListID_MergeFrom
ListMergeAdder.MergeFrom.EditSequence.setValue EditSequence_MergeFrom
ListMergeAdder.MergeTo.ListID.setValue ListID_MergeTo
ListMergeAdder.MergeTo.EditSequence.setValue EditSequence_MergeTo
Set ListMergeAddResp = SessionManager.DoRequests(ListMerge_Set)

‘Take a peek at what happened
MsgBox ListMergeAddResp.ToXMLString
SessionManager.EndSession
SessionManager.CloseConnection
Merging classes

The following sample code merges two classes:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
Dim qbSessionManager As QBSessionManager
Dim msgSetRequest As IMsgSetRequest
qbSessionManager = New QBSessionManager()
qbSessionManager.OpenConnection2("", "BocaLupa", ENConnectionType.ctLocalQBD)
qbSessionManager.BeginSession("", ENOpenMode.omDontCare)

msgSetRequest = qbSessionManager.CreateMsgSetRequest("US", 8, 0)
msgSetRequest.Attributes.OnError = ENRqOnError.roeStop
Dim classMerj As IListMerge
classMerj = msgSetRequest.AppendListMergeRq classMerj.ListMergeType.SetValue(ENListMergeType.lmtClass)
classMerj.MergeFrom.ListID.SetValue("80000001-1232787304") classMerj.MergeFrom.EditSequence.SetValue("1232787304")
classMerj.MergeTo.ListID.SetValue("80000002-1232787304") classMerj.MergeFrom.EditSequence.SetValue("1231787354")

And the same thing in XML:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
<?xml version="1.0" ?>
  <?qbxml version="8.0"?>
    <QBXML>
      <QBXMLMsgsRq onError = "stopOnError">
        <ListMergeRq requestID = "0">
          <ListMergeType>Class</ListMergeType>
          <MergeFrom>
            <ListID>80000001-1232787304</ListID>
            <EditSequence>1232787304</EditSequence>
          </MergeFrom>
          <MergeTo>
            <ListID>80000002-1232787304</ListID>
            <EditSequence>1232787304</EditSequence>
          </MergeTo>
        </ListMergeRq>
      </QBXMLMsgsRq>
    </QBXML>
Merging customers

To merge two customers/customer jobs,

  1. See if both customers/jobs have children. To do this, use a customer query with a NameFilter that has its MatchCriterion set to “Contains” and the Name element set to

    the full name of the customer or customer job. This will return you the customer/ customer job and all child jobs of that customer/customer job.

  2. If both customer/jobs have children, move all the children of the move-from customer/ job to the merge-to customer/job. To do this, do a CustomerMod on each child job, changing the ParentRef from the merge-from customer/job to the merge-to customer/ job. Alternatively, you could do a CustomerMod on each child job and make it inactive by setting the IsActive field to False. Or, to be more extreme, you could do a ListDel on each child job, but that is not recommended.

  3. Using the ListIDs and recent EditSequence from merge-from and merge-to customer/ jobs, invoke the ListMerge request

Code samples

The following code snippets show to do the various tasks you’ll need to do, building a customer query to look for child jobs, changing the ParentRef in a child job so it points to the merge-to customer, and building the ListMerge request.

Building the CustomerQuery

Here is a snippet that builds customer query to look for child jobs for the customer job Jacobsen, Doug:Kitchen. The query will return Jacobsen, Doug:Kitchen and any child jobs because those child jobs will have Jacobsen, Doug:Kitchen within their fullname.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
SessionManager.OpenConnection "", "IDN List Merge Sample"
SessionManager.BeginSession "", omDontCare
Dim ListMerge_Set As IMsgSetRequest
Set ListMerge_Set = SessionManager.CreateMsgSetRequest("US", 7, 0)
ListMerge_Set.Attributes.OnError = roeContinue
Dim custQuery As ICustomerQuery
Set custQuery = ListMerge_Set.AppendCustomerQueryRq
custQuery.ORCustomerListQuery.CustomerListFilter.ORNameFilter.NameFilter.MatchCriterion.setValue mcContains
custQuery.ORCustomerListQuery.CustomerListFilter.ORNameFilter.NameFilter.Name.setValue "Jacobsen, Doug:Kitchen"
Dim ListMergeSampleResp As IMsgSetResponse
Set ListMergeSampleResp = SessionManager.DoRequests(ListMerge_Set)
Changing the ParentRef of a customer job

If the customer query turns up child jobs, and you have child jobs in both merge-to and merge-from customer/jobs, you need to change the ParentRef in each merge-from child job so that it points to the merge-to customer. The following code snippet shows the CustomerMod used to change this.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
SessionManager.OpenConnection "", "IDN List Merge Sample"
SessionManager.BeginSession "", omDontCare
Dim ListMerge_Set As IMsgSetRequest
Set ListMerge_Set = SessionManager.CreateMsgSetRequest("US", 7, 0)
ListMerge_Set.Attributes.OnError = roeContinue
Dim custMod As ICustomerMod
Set custMod = ListMerge_Set.AppendCustomerModRq

‘We’re modifying one of the child jobs
custMod.EditSequence.setValue"1197731900"
custMod.ListID.setValue "800000AA-1197731900"

‘Changing the ParentRef to point to the merge-to customer/job
custMod.ParentRef.ListID.setValue "1F0000-933272658"

Dim ListMergeSampleResp As IMsgSetResponse
Set ListMergeSampleResp = SessionManager.DoRequests(ListMerge_Set)
Building the ListMerge request

After any child jobs have been moved over to the merge-to customer/job, you can invoke the ListMerge request, as shown in the following code snippet:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
SessionManager.OpenConnection "", "IDN List Merge Sample"
SessionManager.BeginSession "", omDontCare
Dim ListMerge_Set As IMsgSetRequest
Set ListMerge_Set = SessionManager.CreateMsgSetRequest("US", 7, 0)
ListMerge_Set.Attributes.OnError = roeContinue
Dim ListMergeAdder As IListMerge
Set ListMergeAdder = ListMerge_Set.AppendListMergeRq
ListMergeAdder.ListMergeType.setValue lmtCustomer
ListMergeAdder.MergeFrom.ListID.setValue "80000007-1188956785"
ListMergeAdder.MergeFrom.EditSequence.setValue "1188956785"
ListMergeAdder.MergeTo.ListID.setValue "80000002-1188956165"
ListMergeAdder.MergeTo.EditSequence.setValue "1188956165"
Dim ListMergeSampleResp As IMsgSetResponse
Set ListMergeSampleResp = SessionManager.DoRequests(ListMerge_Set)
Merging vendors

There aren’t any tricks to merging vendors. Just supply the list IDs and the latest EditSequences to the ListMerge request.