I have an application that connects to QuickBooks via QBFC13, downloads the customers (just name, ID and a few other details) and all the known addresses for those customers. This all seems to work just fine, except there is no GUID or other unique identifier that I can see that uniquely identifies the address. So, for example, the customer changes an address in QuickBooks, I need to know which one changed in the application. Or, I send info to QuickBooks using an Address ID rather than an entire block of Addr1-5. I thought the address name would be unique, but it isn't and can be blank. It isn't practical or helpful to key off of the addr fields. Does this GUID even exist? Am I missing something really simple? Is there a better way to do this? The QB docs are atrocious!
The VBA used to grab the customers and their addresses follows (some code snipped for clarity).
Dim respType As IResponseType
Set respType = curResp.Type
If (respType.getValue = rtCustomerQueryRs) Then
Dim custList As ICustomerRetList
Set custList = curResp.Detail
Dim curCust As ICustomerRet
Dim i As Integer
Dim insSQL As String
For i = 0 To custList.Count - 1
insSQL = "INSERT INTO " & CustomerTableName _
& "(CustomerID, QBEditSequence, Name, PhoneNumber, EMailAddress) " _
& "VALUES " _
& "("
Set curCust = custList.GetAt(i)
If (curCust.Sublevel.getValue = 0) Then
insSQL = insSQL & "'" & curCust.ListID.getValue & "',"
insSQL = insSQL & "'" & curCust.EditSequence.getValue & "',"
insSQL = insSQL & "'" & Replace(curCust.Name.getValue, "'", "''") & "',"
If (Not curCust.Phone Is Nothing) Then
insSQL = insSQL & "'" & curCust.Phone.getValue & "',"
Else
insSQL = insSQL & "'',"
End If
If (Not curCust.Email Is Nothing) Then
insSQL = insSQL & "'" & curCust.Email.getValue & "');"
Else
insSQL = insSQL & "'');"
End If
accessDB.Execute insSQL
' Add Bill to address
If Not curCust.BillAddressBlock Is Nothing Then
If curCust.BillAddressBlock.Addr1 Is Nothing = False Then
rs.AddNew
rs!CustomerID = curCust.ListID.getValue
rs!AddressType = "B"
rs!Addr1 = curCust.BillAddressBlock.Addr1.getValue
If curCust.BillAddressBlock.Addr2 Is Nothing = False Then rs!Addr2 = curCust.BillAddressBlock.Addr2.getValue
If curCust.BillAddressBlock.Addr3 Is Nothing = False Then rs!Addr3 = curCust.BillAddressBlock.Addr3.getValue
If curCust.BillAddressBlock.Addr4 Is Nothing = False Then rs!Addr4 = curCust.BillAddressBlock.Addr4.getValue
If curCust.BillAddressBlock.Addr5 Is Nothing = False Then rs!Addr5 = curCust.BillAddressBlock.Addr5.getValue
rs.Update
End If
End If
' Add Shipping Addresses
Dim shpList As IShipToAddressList
Set shpList = curCust.ShipToAddressList
If Not shpList Is Nothing Then
Dim s As Integer
For s = 0 To shpList.Count - 1
Dim saddr As IShipToAddress
Set saddr = shpList.GetAt(s)
rs.AddNew
rs!CustomerID = curCust.ListID.getValue
rs!AddressType = "S"
rs!AddressName = saddr.Name.getValue
rs!IsDefault = saddr.DefaultShipTo.getValue
rs!Addr1 = saddr.Addr1.getValue
If saddr.Addr2 Is Nothing = False Then rs!Addr2 = saddr.Addr2.getValue
If saddr.Addr3 Is Nothing = False Then rs!Addr3 = saddr.Addr3.getValue
If saddr.Addr4 Is Nothing = False Then rs!Addr4 = saddr.Addr4.getValue
If saddr.Addr5 Is Nothing = False Then rs!Addr5 = saddr.Addr5.getValue
rs.Update
Next s
End If
End If
Next i
End If
End If
used qb a few years back and reviewed some projects from that period. mind you, this was extracted by the qb report writer inside the client that most assuredly delivers truncated record.
no predefined globally unique address idrntifier as a single field for a specific address. the customer/vendor name is unique for thier respective bill-from/to, ship-from/to but a billing address can be the same as a shipping address and other companies can, and do, use the same addresses. this is actually very common among several types of business.
so bill from 1 is a unique header/name for a vendor but the address string itself can also appear in bill from 2, bill from 3 through 5 before moving to bill from street, street 2, city, state, zip, country and all these fields roll up to create a long address string simply named bill from.
sadly, all i have are xlsxm and csv. no longer have access to qb so i can't go in for a closer look that would actually provide helpful information