Follow up to annoying post
So we can’t do complex queries in Salesforce.com. But we all need to. And it’s a reporting functionality, right? I mean, if they don’t give us access to reports in the API (which they haven’t) and they don’t build a complex query builder (which they haven’t) we’re out of luck.
And then I thought about Campaigns.
After Winter ‘06 came out, Campaigns became snapshots of reports. They added the all powerful “Add to Campaign” button.
That removed the need for a bunch of Excel ping pong. Export to Excel, import to Campaign. Great for usability, but doesn’t help the complex reporting needs.
But what if they had added this button too?
Then we could create a report of all Donors, and then subtract out everyone who attended our last party. Giving us the result set we want in a Campaign where it’s available for export to Excel, can be viewed in a report, etc.
Unfortunately, I can’t add that button. There isn’t API access to reports. But I have full API access to Campaigns. So if we do the first step of putting the report results in a Campaign, then I could subtract the members of one Campaign from another, putting the resulting set in another Campaign.
We could chain together as many as we wanted. Giving results like everyone who has never given to my org, attened my last breifing meeting, and is a volunteer.
I’ve written a way to compare two Campaigns, generating a resulting set of CampaignMembers in a new Campaign. Still interested? Want to beta test?
May 2nd, 2006 at 1:12 pm
I’m new, so forgive me if I’m off mark. I’m faced with roughly this problem in another custom object, so I’m trying to figure out different alternatives.
How about using DBAmp to dump into a database? Those reports aren’t too bad to create. Is the problem that there’s no way to view the results in SFDC, that it costs money, that it’s batch, or something else?
I’m trying to figure out if it’s worth buying DBAmp or not - lots more people here know how to work with a DB than the SFDC API.
I appreciate all the tips and insight from this blog!
May 2nd, 2006 at 2:00 pm
One of the things you need to consider with DBAmp is not just the cost of the software, but also the cost to your network bandwidth when you start pushing data back and forth from salesforce to a local DB. We’ve had to resort to this solution to solve a number of problems (using the data loader, not DBAmp), and it’s definitely not a way to make friends the people on your network.
A possible caveat to using campaign member: we’ve had some trouble with updating campaignmember records, due to an obscure issue with the API. I’ll spare the gory details, but basically, there are instances where the SF application will mark a campaignmember record “invalid”, and therafter block any attempts to update the record. Unfortunately, this “invalid” flag isn’t visible through the API, and there’s no messaging telling you that the updates are being blocked. I have no idea if this would impact what you had in mind, but if it does, this is something to watch out for.
May 2nd, 2006 at 2:26 pm
Intriguing concept - I wouldn’t be a good beta tester for you at the moment, but hope you get some takers…
May 2nd, 2006 at 4:29 pm
Jeff, thanks for the tip on the campaign member bug. I haven’t run into it yet, but I’ll keep my eye out. I would love to hear the gory details, if you’re willing to share!
Peter, My main reason for not using DBAmp, or some other external data analysis solution is that the groups that I work with don’t have the technical chops to run sql server. Even ODBC to Access makes the solution a bit too complex for my liking. I know it works (I’ve used BDAmp with MSSQL linked tables–really cool) but it won’t work for my customers.
So, I’m faking it with what I’ve got, Campaigns and the API.
July 3rd, 2006 at 6:00 pm
I would be intereseted in learning more about his technique. We have been trying to find ways to flag when a contact or opportunity does not have a record on something (e.g. missing education) or does not have enough hours to receive credit (summ of multiple detial records)