Apex Code Development You need to sign in to do that
Don't have an account?
dgrissen Need help running an update call in VB
Hi,
I am writing a sub that queries data (specifically, an aggregate sql query that returns the sum of total bookings grouped by the salesforce user id). This query returns data from a LOCAL machine.
I can't figure out how to put existing arrays of data with the sf.com ID already known into a data dictionary and then update into salesforce.com I have plenty of experience downloading data from sf.com, but am having difficulties with the the update call. The code is below, any help is greatly appreciated.
Thanks,
Darrell
I am getting an error message that says 'Type Mismatch' on this line:
Set sobjects(i) = dicObjects.Items(i)
Public Sub Update()
Dim cnxOperations As ADODB.Connection
Call ConnectOperations(cnxOperations)
Call LoginSalesforce
Dim rstMTD As ADODB.Recordset
Dim fldObject As ADODB.Fields
Dim cmdMTD As ADODB.Command
Dim strMTDsql As String
Dim qr As QueryResultSet
Dim sobj As SObject
strMTDsql = "SELECT tblBookings.SalesRepID, Sum(tblBookings.TotalDeal) AS Total FROM tblBookings INNER JOIN tblBusinessDayCalendar ON tblBookings.BookingDate = tblBusinessDayCalendar.DateID WHERE (((tblBusinessDayCalendar.MonthNumber) = " & Format(Date, "m") & ") And ((tblBusinessDayCalendar.YearNumber) = " & Format(Date, "yyyy") & "))GROUP BY tblBookings.SalesRepID;"
Debug.Print strMTDsql
Set cmdMTD = New ADODB.Command
Set cmdMTD.ActiveConnection = cnxOperations
cmdMTD.CommandText = strMTDsql
cmdMTD.CommandTimeout = 10
'Executing command to retrieve recordset
Set rstMTD = New ADODB.Recordset
Set rstMTD = cmdMTD.Execute
Set fldObject = rstMTD.Fields
Dim Count As Integer
Dim y As Integer
Dim id() As String
Dim dicObjects As New Dictionary
With rstMTD
Do While Not .EOF
dicObjects.Add rstMTD.Fields("SalesRepID").Value, fldObject
.MoveNext
Loop
End With
'updating via sforce API
Dim sobjects() As SObject
Dim i As Integer
ReDim sobjects(dicObjects.Count) As SObject
For i = LBound(sobjects) To UBound(sobjects)
Set sobjects(i) = dicObjects.Items(i)
Next
sfdc.Update sobjects, False
MsgBox "Done"
End Sub
Any advice on how to transform a ADO recordset into a format which can be used to update records in salesforce.com? Code samples?
Thanks!
Second attempt at using an update call. This time I basically just copied the update code from the foghorn_test.xls example. The only modification I made was the source of the data for updating.
The line in red is where it keeps tripping up, the error says 'Run time error 424 - object required'
I'd appreciate any advice...
Thanks,
Darrell
'Update
Public Sub RunUpdate()
Dim cnxOperations As ADODB.Connection
Call ConnectOperations(cnxOperations)
Call LoginSalesforce
Dim rstMTD As ADODB.Recordset
Dim cmdMTD As ADODB.Command
Dim strMTDsql As String
Dim qr As QueryResultSet
strMTDsql = "SELECT tblBookings.SalesRepID, Sum(tblBookings.TotalDeal) AS Total FROM tblBookings INNER JOIN tblBusinessDayCalendar ON tblBookings.BookingDate = tblBusinessDayCalendar.DateID WHERE (((tblBusinessDayCalendar.MonthNumber) = " & Format(Date, "m") & ") And ((tblBusinessDayCalendar.YearNumber) = " & Format(Date, "yyyy") & "))GROUP BY tblBookings.SalesRepID;"
Debug.Print strMTDsql
Set cmdMTD = New ADODB.Command
Set cmdMTD.ActiveConnection = cnxOperations
cmdMTD.CommandText = strMTDsql
cmdMTD.CommandTimeout = 10
'Executing command to retrieve recordset
Set rstMTD = New ADODB.Recordset
Set rstMTD = cmdMTD.Execute
Dim Count As Integer
Dim idList() As String
Dim sngNetBookingsValue() As Single
Dim x As Integer
With rstMTD
Do While Not .EOF
Count = Count + 1
.MoveNext
Loop
.MoveFirst
ReDim idList(1 To Count) As String
ReDim sngNetBookingsValue(1 To Count) As Single
'Get the ids of the objects to update
For x = 1 To Count
idList(x) = !SalesRepID
sngNetBookingsValue(x) = !Total
Debug.Print idList(x)
Debug.Print sngNetBookingsValue(x)
.MoveNext
Next x
End With
'sf.com code
Dim sobj As SObject
Dim i As Integer
Dim j As Integer
Dim fieldList As String
'Get the field list for the retrieve call
fieldList = "Id, MTDNetBookings__c"
'Retrieve the server edition of the list
Dim qrs As QueryResultSet
Set qrs = sfdc.Retrieve(fieldList, "User", idList, False)
'Put the list into a dictionary keyed on id for easy random access
Dim objects As New Dictionary
For Each sobj In qrs
objects.Add sobj.Item("Id").Value, sobj
Next
'Loop through the values updating the dictionary
For i = 1 To Count
If idList(i) <> "" Then
Set sobj = objects(idList(i))
If sobj.Item("MTDNetBookings__c").Updateable Then
sobj.Item("MTDNetBookings__c") = sngNetBookingsValue(i)
End If
End If
'sObj.Update
DoEvents
Next
Dim sobjects() As SObject
ReDim sobjects(objects.Count - 1) As SObject
For i = LBound(sobjects) To UBound(sobjects)
Set sobjects(i) = objects.Items(i)
Next
sfdc.Update sobjects, False
MsgBox "Done"
End Sub