I run replication to my SQL server and at least one of my queries is not pulling all the rows.

This query should pull all OpportunityLineItem's shouldn't it?

select id,pubpoint__c,pp_type__c,opportunityID,pricebookentryid,productid,description,Daily_reports_email__c,serviceDate from opportunitylineitem

I havn't had a problem until a few days ago and now I can see that there are line items in salesforce that are not showing up in SQL. Many are, many are not.

Any ideas?

Dave

Screen scraping might actualy be a decent option for me. I am already doing a "snapshot" type replication as part of my solution but I don't want to grow that into a full fledged system.

Thanks

Hi, I am exploring replicating salesforce to my SQL server for the sole reason that I cannot use joins in sforce. I understand that joins can impact performance but at this point I am having to throw 200+ queries at the web service where one join would have sufficed.

(For example if my app needs to retrieve all scheduled items for today for a specific product where the opportunity is for an account in a given area I have to query "up" 4 or 5 times for each item in my initial list. If there are 100 items on the list 500 queries yuck!!)

Must be some way of comparing the relative performance impacts of 500 queries vs 1 join.

My other solution involves replicating most of the sforce database to my local SQL and throw my joins against that. It looks like allot of developers are going this route but it still seems like the long way around.

My question/suggestion is that if I could have access to "reports" in sforce then it would take care of 90% or more of my need for joins. I would simply design my report with the neccessary joins then query that report. Much like querying a view in SQL.

By exposing the reports instead of enabling joins I would get what I need and your online report designer can ensure there are no malformed and expensive joins.

So, Is there a plan to expose reports? ...soon?

Dave

When I use the Office Edition plug-in I can request a list of my custom reports and pull them directly into Excel. However, when I use the API directly it doesn't appear that reports are a supported obejct? e.g."select * from reports"

Whats the best way for me to access custom reports programmatically?

 

 

 

 

I keep getting hung up using the query result sets.  I am just so steeped in ADO I need a new model for the sForce ResultSet.

e.g. 

Set rs = sForceSession.Query("select name from account", False)
For each r in rs
    for each f in flds
         msgbox(f.name)
   next
next

the msgbox will display every field name available to an account object even tho I only asked for the "name" field in the query. Whats up with that? 

It appears as if the RS is a collection of "account" objects but I still have trouble extracting just the one field I am interested in.

I would expect to be able to do things like:

Set rs = sForceSession.Query("select name from account", False)
   -- msgbox(rs(0).fields(0).value)
or
   -- for each r in rs
          set flds = r.fields
               msgbox(flds(0).value)
or
   -- for each r in rs
          msgbox( r.fields("name") )

But everything I try comes up with a "does not support..." 

Can anyone post a quick cheat sheet for working with these result sets?

Thanks
Dave

 

 

 

 

 

 

 

 

 

 

 

 

 

Will there be any documentation for SF_MSApi.dll ?

My first test code only gets this far

Dim s As SForceOfficeToolkitLib.SForceSession
Set s = New SForceOfficeToolkitLib.SForceSession

If s.Login("xxxxxx", "xxxx", False) Then
    Set rs = s.Query("select firstname, lastname from contact", False)
End If

No problems getting this far but Im using vb6 and I can't make out how to get access to the records within the result set. I thought the whole point of COM development is that the properties and methods were thesame accross plateforms. I have tried various combinations of getRecords() or records shown in other samples but everything I try throws a method not found error.

Object Browser only shows 4 properties for a queryResultSet object (EntityType,error,erromessage,tag) The entityType returns "Contact" which I expected but I can't figure out how to get from there to the othe part of the hierchy SObject that seems to contain the fields collection.

Anyone?

 

 

 

I run replication to my SQL server and at least one of my queries is not pulling all the rows.

This query should pull all OpportunityLineItem's shouldn't it?

select id,pubpoint__c,pp_type__c,opportunityID,pricebookentryid,productid,description,Daily_reports_email__c,serviceDate from opportunitylineitem

I havn't had a problem until a few days ago and now I can see that there are line items in salesforce that are not showing up in SQL. Many are, many are not.

Any ideas?

Dave

Hmm, so here's my problem:

When a self service user comes into my client's customer portal, then I need to display all of the cases that are related to that user's account. My problem is performance. Here's the specific scenario:

SS logs in, I get their contact id and account id. Store it in the session.

The user hits the cases page. Cases are connected to accounts by ContactId. So I take the Account Id and grab all the contacts for an account. There are like 500 contacts for some of these guys. I then proceed to send off a whole bunch of API calls for each contactId. I pursued adding a whole bunch of "or ContactId='00000000AAA'" clauses to the end of the SOQL statement, and that helped alot, although, you have to know your SOQL query size limits and plan accordingly.

What I really wanted to be able to do is this this, though:

SELECT Id
FROM Case c
INNER JOIN Contact con ON con.ContactId = c.Id
INNER JOIN Account acct ON c.AccountId = acct.Id
WHERE acct.Id = 'UsersAccountId'

I know I can do this with dbamp (via a linked server) and the openaccess data provider (though it's $999 for my Win 2k3 server). What I want to know is can I just as easily work with the objects in the Sforce Office Toolkit?

I'm not really familiar with toolkit, but when I play around with it in VB.Net, I get a lot of 'Cannot be indexed because of no default property' errors. I'm starting to give up trying to find the right syntax. I guess I could also create a vb object, wrap everything up and set my default properties, but it seems there should be an easier way. Am I missing something here?

Thanks

I am not sure whether this is a problem of the COM Toolkit, my usage, or the API in general, but I'm finding it impossible to add new PriceBookEntry instances.

I am currently working on isolating the offending code so I can give a reproducable problem here, but would like to describe the symptoms, in the hope that someone has run into a similar problem and can give me a hint towards a solution.

I am using generic Excel code which uses the CreateObject function to build an object, assigns values into the field(n).value's, and then uses DoCreate to process it. This code has worked fine, unchanged, for at least 20 other object types, but is failing for PriceBookEntry.....

The failure occurs when assigning a value to, it appears, ANY of the fields in the object. I've tested it with Product2ID, Pricebook2ID, and the currency value. The result of the assignment statement, of the form:
        .....  fldobject.value="value"
is:

    "Runtime error '-2147467529 (8004005)'
    "The current field definition for Product2ID does not allow you to set a value"

As expected for the Product2ID,
              fldobject.createable = TRUE  and
              fldobject.updateable = FALSE

Any help or suggestions appreciated.

I keep getting hung up using the query result sets.  I am just so steeped in ADO I need a new model for the sForce ResultSet.

e.g. 

Set rs = sForceSession.Query("select name from account", False)
For each r in rs
    for each f in flds
         msgbox(f.name)
   next
next

the msgbox will display every field name available to an account object even tho I only asked for the "name" field in the query. Whats up with that? 

It appears as if the RS is a collection of "account" objects but I still have trouble extracting just the one field I am interested in.

I would expect to be able to do things like:

Set rs = sForceSession.Query("select name from account", False)
   -- msgbox(rs(0).fields(0).value)
or
   -- for each r in rs
          set flds = r.fields
               msgbox(flds(0).value)
or
   -- for each r in rs
          msgbox( r.fields("name") )

But everything I try comes up with a "does not support..." 

Can anyone post a quick cheat sheet for working with these result sets?

Thanks
Dave

 

 

 

 

 

 

 

 

 

 

 

 

 

Will there be any documentation for SF_MSApi.dll ?

My first test code only gets this far

Dim s As SForceOfficeToolkitLib.SForceSession
Set s = New SForceOfficeToolkitLib.SForceSession

If s.Login("xxxxxx", "xxxx", False) Then
    Set rs = s.Query("select firstname, lastname from contact", False)
End If

No problems getting this far but Im using vb6 and I can't make out how to get access to the records within the result set. I thought the whole point of COM development is that the properties and methods were thesame accross plateforms. I have tried various combinations of getRecords() or records shown in other samples but everything I try throws a method not found error.

Object Browser only shows 4 properties for a queryResultSet object (EntityType,error,erromessage,tag) The entityType returns "Contact" which I expected but I can't figure out how to get from there to the othe part of the hierchy SObject that seems to contain the fields collection.

Anyone?