+ Start a Discussion


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:

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?

This is an important subject for me as well. Creating applications would take a quarter of the current time if I could simply access predefined reports in salesforce (with params of course). As it is I have to either run dozens (if not hundreds) of select statements.

I am currently running a replication job then pulls sforce data into SQL server so that I can have this functionality. I imagine joins would be LESS load on sforce servers then all these needless selects. Especially so if we were simply granted access to reports defined via the salesforce interface. The report designer already ensures that developers don't create run-away join statements that could suck cycles so why can't I select from a report?

I was contacted a year ago by a sforce rep who was conducting a use case on these subject but I gather from your post that it has not been implemented yet.

Any news on this subject sforce?
If you're curious, I wound up going with the openaccess stuff, because I had a tight deadline, but, itt does not appear that it would take too much effort to work with the stuff in the office toolkit...
I have heard this from other customers. Some customers built a daemon replication process to pick up modified cases and denormalised the accountId by writing it back into a custom field via "select account id from contact where id = 'contactId from the case"
Apart from being a slow sledgehammer solution, it didn't assign cases to the correct accounts when contacts were moved to different accounts.

When calculated fields came in, the solution was much simpler. We exposed a calculated field on the case with {!AccountId} which is only visible to the API user's profile, so the query becomes "select id from case where SSP_AccountId_Calcd__c = 'the users accountId'"

Also, in the next release we are working on a Case Account/Contact re-parenting project that will allow a Case to be related to a contact and a different account. This means the Account field will be available for Cases in the 8.0 API. So, you'll be able to issue a SOQL filter against it directly.

Mark Abramowitz