<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<script type="text/javascript" src="/js/functions.js"></script>
<script src="/soap/ajax/12.0/connection.js"></script>
<script type="text/javascript">
function initPage() {
var newid = "{!$Request.newid}";
var strSQL;
var strRe;
//Redirecting after matching child case to AP case
if (newid.substring(0,3) == "a0V"){
strSQL = "Select Id, Child_Case__c from Match_Child_Case__c where Id = '" +newid +"'";
}
//Redirecting after creating child/case
if (newid.substring(0,3) == "003"){
strSQL = "Select Id from Child_Case__c where Contact__c = '" +newid +"'";
}
if (strSQL != null){
var result = sforce.connection.query(strSQL);
var records = result.getArray("records");
i=0;
while ( i<records.length) {
var rec = records[i];
if (newid.substring(0,3) == "a0V"){
strRe = rec.Child_Case__c;
}
if (newid.substring(0,3) == "003"){
strRe = rec.Id +"/e";
}
i++;
}
}
if (strRe != null){
window.parent.location.href = "/" +strRe;
} else {
//window.parent.location.href = "/" +newid;
}
}
</script>
</head>
<body onLoad="javascript:initPage();">
</body>
</html>
Wednesday, February 11, 2009
Re-directing Salesforce users to another page
I did this awhile back and know that other organizations have similar requirements. This is an S-Control, so I'll need to re-do it in Visualforce at some point, but it works for now. What my client wanted to do was this: on saving a record, re-direct the user to a record other than the one that was just saved. In some cases, this can be accomplished by overriding the Edit button to pass the record ID you want the user to land on into the retURL parameter. But that wasn't an option here, because they wanted the user to go to a record that was being created by Apex code on save. So, I created the S-Control below and pass the S-Control's ID to the retURL parameter. It obviously needs to be tweak for use in any other Salesforce instance, but this should give anyone with a similar requirement a good starting point:
Thursday, January 15, 2009
Google Maps in Visualforce
So as I go to write this, I see a project up on the Salesforce code share site for Google Maps and Earth in Visualforce. Perhaps this will still be useful to someone though, so I'll forge on. My organization tracks interviews (as a custom object) that we send our participants on and generally our Job Developers generate directions from Hopstop.com to give to them. This seemed like a good candidate for what I thought was a quick Visualforce page. I went down a few avenues trying to accomplish this, but I'll start with the path I ultimately chose and then review the options I gave up on.
What I ended up using was Google Maps for mobile devices. Why the mobile version? I wanted the maps and route in a frame because I was also including information from Salesforce about the interview. The full site was unwieldy in the frame and didn't print properly. And also because I found the parameter options for the mobile version that could easily be passed via a URL to generate the route. So here's the code that I ended up with; you'll notice some if statements in the parameters because the address used can come from two different places, but the concept should still come through:
<apex:iframe id="hopstop" height="1400px" width="600px" frameborder="true" src="http://www.hopstop.com/pda?address1=32+Broadway&county1=Manhattan&address2={!if(IsNull(Interview__c.Opportunity__r.Address__c),Interview__c.Opportunity__r.Account.BillingStreet, Interview__c.Opportunity__r.Address__c)}&county2={!if(IsNull(Interview__c.Opportunity__r.Borough__c),Interview__c.Opportunity__r.Account.Borough__c, Interview__c.Opportunity__r.Borough__c)}&language=en&mode=a&transfer_priority=1&day=1&time=50400&city1=newyork&city2=newyork&submitted=y&sid=&sub_action=" >
That frame is inside of a table with details about the interview. We're always using the same starting point since people are leaving from our office. The format for the mobile device works well for printing, the only unfortunate aspect is that the height of the frame couldn't be dynamic (100% set it to the height of the table, not the content inside the frame) so it can potentially have unnecessary space at the end.
So this ended up being pretty simple, but now for an overview of the other attempts I made just in case anyone else considers trying them. I started out playing with Hopstop.com. I was able to find some parameters that I could pass in in the URL that would set the To and From fields, but didn't actually execute the search. It was also too much having the Hopstop header, sidebar, ads and everything else included on the page that was returned, even if I could get it to execute.
Next attempt, the Hopstop API. To use the API, you have to sign up and get a key which will only work with 1 IP address. That ruled out using the API in Apex, since Salesforce could make the calls from a number of IPs and there would have been legal issues anyway. So I considered making the calls in Javascript on the Visualforce page, since it would always be called from our office here from 1 IP address. The issue there was that the API returns XML files, and Firefox (our primary browser here) has security restrictions regarding XML file transfers being called from a page on one domain to another. There's a way around this, but it involves not only changing settings in Firefox, but updating (or inserting) a config file on the user's machine. Well, we're using Salesforce so that we have a web-based application, so I wanted to avoid that method. It also just didn't seem like the best idea to be making people's browsers less secure just to display a route.
Google API, same issues as Hopstop. So I finally stumbled on the Google Mobile wiki and went with that. As is often the case when first playing with new technologies, I spent a great deal of time coming up with a rather simple solution.
What I ended up using was Google Maps for mobile devices. Why the mobile version? I wanted the maps and route in a frame because I was also including information from Salesforce about the interview. The full site was unwieldy in the frame and didn't print properly. And also because I found the parameter options for the mobile version that could easily be passed via a URL to generate the route. So here's the code that I ended up with; you'll notice some if statements in the parameters because the address used can come from two different places, but the concept should still come through:
That frame is inside of a table with details about the interview. We're always using the same starting point since people are leaving from our office. The format for the mobile device works well for printing, the only unfortunate aspect is that the height of the frame couldn't be dynamic (100% set it to the height of the table, not the content inside the frame) so it can potentially have unnecessary space at the end.
So this ended up being pretty simple, but now for an overview of the other attempts I made just in case anyone else considers trying them. I started out playing with Hopstop.com. I was able to find some parameters that I could pass in in the URL that would set the To and From fields, but didn't actually execute the search. It was also too much having the Hopstop header, sidebar, ads and everything else included on the page that was returned, even if I could get it to execute.
Next attempt, the Hopstop API. To use the API, you have to sign up and get a key which will only work with 1 IP address. That ruled out using the API in Apex, since Salesforce could make the calls from a number of IPs and there would have been legal issues anyway. So I considered making the calls in Javascript on the Visualforce page, since it would always be called from our office here from 1 IP address. The issue there was that the API returns XML files, and Firefox (our primary browser here) has security restrictions regarding XML file transfers being called from a page on one domain to another. There's a way around this, but it involves not only changing settings in Firefox, but updating (or inserting) a config file on the user's machine. Well, we're using Salesforce so that we have a web-based application, so I wanted to avoid that method. It also just didn't seem like the best idea to be making people's browsers less secure just to display a route.
Google API, same issues as Hopstop. So I finally stumbled on the Google Mobile wiki and went with that. As is often the case when first playing with new technologies, I spent a great deal of time coming up with a rather simple solution.
Friday, November 21, 2008
CEO wins Appy Award at Dreamforce 2008
So it occurs to me that I shouldn't be so humble and should announce the fact that my organization, Center for Employment Opportunities, received the "Power of Us" Appy award at Dreamforce 2008, the annual Salesforce conference. Having done the bulk of the customization in Salesforce for CEO, this was a proud moment for myself, and it was great getting some recognition for the organization as well. CEO is just starting to provide Salesforce consulting services so recognition from Salesforce that we've created a best-in-class solution goes a long way in proving to our potential clients that we have the skills and experience to help them.
For my die-hard fans, you can watch the Appy award presentation here, it's about 23 minutes in.
For my die-hard fans, you can watch the Appy award presentation here, it's about 23 minutes in.
Friday, November 14, 2008
Salesforce and Crystal Reports
And specifically how they don't play nice together. I've used Crystal Reports for many years now, and as far as reporting tools for relational databases go, I've always been happy with it. The problem being, Salesforce isn't truly a relational database and while it's great for data input, has never been well optimized for data output. Crystal has attempted to create a data connector for Salesforce, and I've been able to do a lot with it, but it's still fraught with problems. I've also come to have issues with the company itself, which is now SAP, but I'll get to that.
The problems. First off, while they give the impression that you can build a report on Salesforce data the same as you would on any other database, that's not entirely true. Like I said, Salesforce isn't really relational (it's relational-ish, perhaps?) so the standard methods of relating tables and defining filters in the Select Expert doesn't always produce what you're expecting. I haven't fully put my finger on what works and what doesn't, and I'm disinclined to do much in the way of QA for Crystal, but I often find that filters I've defined in the Select Expert have to essentially be replicated by suppressing groups or detail sections of records that shouldn't have been returned in the first place based on my criteria. Pay very close attention to results returned where your Select Expert criteria contains a lot of "and"s and "or"s, that's where I hit the most problems.
Then there are the issues with efficiency. Anyone who has developed reports on a relational database knows that complex reports can take a long time to run, hence the rise of OLAP (which I really need to learn more about). Well, if Salesforce is your data source, then expect the time to run a report to increase exponentially. When you include multiple Salesforce objects and define filters on what data to return in the Select Expert, you often end up pulling in a lot more data than you really need because of the limitations of Salesforce's SoQL language and Crystal has to then process what should be displayed. The workaround to this is to write Command statements in SoQL to pull the data from Salesforce wherever possible instead of pointing CR to multiple Salesforce objects. But part of the advantage of a tool like CR is supposed to be the GUI interface, so it's frustrating always having to write long, complex SoQL statements for reports that shouldn't really be that complicated.
Then there's the limitations of Salesforce itself that impact Crystal. Any other database, you'd have the ability to write SQL statements to do aggregate functions on the data (max, min, count, etc) and then let that processing happen on the database server itself and just return the results. But SoQL isn't that robust. A common request I get is to see the last time a participant (Contact) met with a JD, or the first time they worked transitional work after graduating from Life Skills class. I have 2 options to accomplish this. I can create a roll-up summary field in Salesforce and then use that field to pull in details about the record associated with that roll-up summary field. So in my first example, I create a roll-up summary field in the Contact record for the maximum Event date where the Event Record Type = "JD Meeting". Then if I want more information than just that date, like who the meeting was with, I have to relate the Contact table with the Event table on both the Contact Id and that roll-up summary date field. Kind of a long way to get not very far.
But even that roundabout solution for aggregates is not always an option, because the roll-up summary fields don't allow you to filter data based on another field. So in my second example, it's not enough to get the first time someone attended transitional work ever, I need to see the first time they attended transitional work after they've graduated from class, and our participants sometimes attend class multiple times over many years. I can't create a roll-up summary field for that, so my only option is to pull every single transitional work attendance record into the report, group it by participant, filter it for dates that are greater than their last class graduation (which has to happen in Crystal, and not in Salesforce, for the same reason as the roll-up summary limitation) and then only display the first record, hiding all the rest. That's a lot of data being pulled into Crystal, when I ultimately only need about 1% of it.
So I realize all of that was a bit wonky, but anyone who's done reporting should get the gist of it.
Now I have a number of reports designed in Crystal that can't run on crystalreports.com because they take so long to run that they time out. So despite this great on-demand system we have, I still have users calling me to run reports periodically, just what we were trying to get away from. Then further complicate this by crystalreports.com having unexpected outages 3 times during business hours in the last 6 months, and users running operational reports (like payroll) who can't wait for that crap, call me then. And ever since SAP took over Crystal, there's no more phone or email support, even something as catastrophic as their entire system being down has to be posted on a forum and then you just sit back and hope one of their techies is paying attention. (They claim they have email support, but don't believe it, the support people just email you and direct you to that forum). Very annoying, very unreliable, very unprofessional.
So what now? I'm still trying to figure that out. At the recent Dreamforce conference, I explored many reporting solutions. Everyone's answer is similar though- yank all of the data out of Salesforce into a data warehouse, either on your servers or theirs, and then do the reports on that. So I'm looking into the solutions that include data warehousing as part of an on-demand service so we can keep moving in the direction of cloud computing. They're all a bit young though, and I've now been burned by 2 supposed Salesforce reporting solutions (Crystal and Jasper Reports) so I'm wary about moving too quickly. I'm hoping to just hold things together long enough to let some of the bigger players test out available solutions and get them past the 1.0 stage. More to come on this I'm sure.
The problems. First off, while they give the impression that you can build a report on Salesforce data the same as you would on any other database, that's not entirely true. Like I said, Salesforce isn't really relational (it's relational-ish, perhaps?) so the standard methods of relating tables and defining filters in the Select Expert doesn't always produce what you're expecting. I haven't fully put my finger on what works and what doesn't, and I'm disinclined to do much in the way of QA for Crystal, but I often find that filters I've defined in the Select Expert have to essentially be replicated by suppressing groups or detail sections of records that shouldn't have been returned in the first place based on my criteria. Pay very close attention to results returned where your Select Expert criteria contains a lot of "and"s and "or"s, that's where I hit the most problems.
Then there are the issues with efficiency. Anyone who has developed reports on a relational database knows that complex reports can take a long time to run, hence the rise of OLAP (which I really need to learn more about). Well, if Salesforce is your data source, then expect the time to run a report to increase exponentially. When you include multiple Salesforce objects and define filters on what data to return in the Select Expert, you often end up pulling in a lot more data than you really need because of the limitations of Salesforce's SoQL language and Crystal has to then process what should be displayed. The workaround to this is to write Command statements in SoQL to pull the data from Salesforce wherever possible instead of pointing CR to multiple Salesforce objects. But part of the advantage of a tool like CR is supposed to be the GUI interface, so it's frustrating always having to write long, complex SoQL statements for reports that shouldn't really be that complicated.
Then there's the limitations of Salesforce itself that impact Crystal. Any other database, you'd have the ability to write SQL statements to do aggregate functions on the data (max, min, count, etc) and then let that processing happen on the database server itself and just return the results. But SoQL isn't that robust. A common request I get is to see the last time a participant (Contact) met with a JD, or the first time they worked transitional work after graduating from Life Skills class. I have 2 options to accomplish this. I can create a roll-up summary field in Salesforce and then use that field to pull in details about the record associated with that roll-up summary field. So in my first example, I create a roll-up summary field in the Contact record for the maximum Event date where the Event Record Type = "JD Meeting". Then if I want more information than just that date, like who the meeting was with, I have to relate the Contact table with the Event table on both the Contact Id and that roll-up summary date field. Kind of a long way to get not very far.
But even that roundabout solution for aggregates is not always an option, because the roll-up summary fields don't allow you to filter data based on another field. So in my second example, it's not enough to get the first time someone attended transitional work ever, I need to see the first time they attended transitional work after they've graduated from class, and our participants sometimes attend class multiple times over many years. I can't create a roll-up summary field for that, so my only option is to pull every single transitional work attendance record into the report, group it by participant, filter it for dates that are greater than their last class graduation (which has to happen in Crystal, and not in Salesforce, for the same reason as the roll-up summary limitation) and then only display the first record, hiding all the rest. That's a lot of data being pulled into Crystal, when I ultimately only need about 1% of it.
So I realize all of that was a bit wonky, but anyone who's done reporting should get the gist of it.
Now I have a number of reports designed in Crystal that can't run on crystalreports.com because they take so long to run that they time out. So despite this great on-demand system we have, I still have users calling me to run reports periodically, just what we were trying to get away from. Then further complicate this by crystalreports.com having unexpected outages 3 times during business hours in the last 6 months, and users running operational reports (like payroll) who can't wait for that crap, call me then. And ever since SAP took over Crystal, there's no more phone or email support, even something as catastrophic as their entire system being down has to be posted on a forum and then you just sit back and hope one of their techies is paying attention. (They claim they have email support, but don't believe it, the support people just email you and direct you to that forum). Very annoying, very unreliable, very unprofessional.
So what now? I'm still trying to figure that out. At the recent Dreamforce conference, I explored many reporting solutions. Everyone's answer is similar though- yank all of the data out of Salesforce into a data warehouse, either on your servers or theirs, and then do the reports on that. So I'm looking into the solutions that include data warehousing as part of an on-demand service so we can keep moving in the direction of cloud computing. They're all a bit young though, and I've now been burned by 2 supposed Salesforce reporting solutions (Crystal and Jasper Reports) so I'm wary about moving too quickly. I'm hoping to just hold things together long enough to let some of the bigger players test out available solutions and get them past the 1.0 stage. More to come on this I'm sure.
Wednesday, November 12, 2008
Scheduling Code in Salesforce
Update 12/15/11: It's worth noting to anyone stumbling onto this page that Salesforce has since implemented functionality internally to Schedule Apex. There are some scenarios were the code below may still make sense to implement, but this was originally a workaround to get around Salesforce's lack of scheduling for code, so be sure to research Scheduled Apex before deciding if this is useful to you. This does still make sense in cases where the code to be scheduled isn't being run on a regular, periodic basis but instead should be triggered based on criteria in your data.
-----
First off, credit where credit is due. My initial inspiration for this was work done by Steve Anderson that he shared on his blog gokubi.com, which is a great resource. What this allows you to do is both schedule code to run at particular times, and what I added on to his work is the ability to break processes into chunks that won't hit the governor limits so that large processes can be scheduled as well. I believe SF is working on providing this functionality, but until then, feel free to make use of the code below.
The anecdotal explanation: I created a custom object called Scheduled Jobs which I use to set off a workflow rule. The workflow rule is triggered by a checkbox field called "Schedule". One of the fields in Scheduled Jobs is "Next Run Datetime" and there's a workflow rule that is set to run 0 hours after that datetime. When it runs, all it does is update a "Run Job" field which sets off the Apex code. Each time a job is run, in addition to whatever code I want run, I have Apex insert a new Scheduled Job record with the Next Run Datetime and Schedule = true, so the process starts over again. Since I have a few different jobs running this way, the Scheduled Job includes fields to indicate which method to run, the time it should be set off (I store it as military time and convert it to a datetime in the code). I even have a job which deletes old Scheduled Job records so it doesn't get too cluttered.
The code, minus some irrelevent bits, is below. The intMagicNum is something I had to include because some of the methods I run have to be run in chunks otherwise they hit Apex governor limits. So, for instance, I have jobs that send out emails but I can only send out 10 emails at a time. Each time it's run, the method is tracking which users have been emailed and only emails people that haven't received it yet that day. So when it's done, it returns how many emails it actually sent and if that number = 10, the job is scheduled to run again immediately (there's generally a 15 minute delay) until everyone has been emailed (less than 10 returned).
-----
First off, credit where credit is due. My initial inspiration for this was work done by Steve Anderson that he shared on his blog gokubi.com, which is a great resource. What this allows you to do is both schedule code to run at particular times, and what I added on to his work is the ability to break processes into chunks that won't hit the governor limits so that large processes can be scheduled as well. I believe SF is working on providing this functionality, but until then, feel free to make use of the code below.
The anecdotal explanation: I created a custom object called Scheduled Jobs which I use to set off a workflow rule. The workflow rule is triggered by a checkbox field called "Schedule". One of the fields in Scheduled Jobs is "Next Run Datetime" and there's a workflow rule that is set to run 0 hours after that datetime. When it runs, all it does is update a "Run Job" field which sets off the Apex code. Each time a job is run, in addition to whatever code I want run, I have Apex insert a new Scheduled Job record with the Next Run Datetime and Schedule = true, so the process starts over again. Since I have a few different jobs running this way, the Scheduled Job includes fields to indicate which method to run, the time it should be set off (I store it as military time and convert it to a datetime in the code). I even have a job which deletes old Scheduled Job records so it doesn't get too cluttered.
The code, minus some irrelevent bits, is below. The intMagicNum is something I had to include because some of the methods I run have to be run in chunks otherwise they hit Apex governor limits. So, for instance, I have jobs that send out emails but I can only send out 10 emails at a time. Each time it's run, the method is tracking which users have been emailed and only emails people that haven't received it yet that day. So when it's done, it returns how many emails it actually sent and if that number = 10, the job is scheduled to run again immediately (there's generally a 15 minute delay) until everyone has been emailed (less than 10 returned).
Code:
trigger InsertUpdateScheduledJobAfter on Scheduled_Job__c (after insert, after update) {
Integer intReturn;
Integer intMagicNum;
List sjList = new List();
Datetime dte;
for (Scheduled_Job__c sj : Trigger.new){
if (sj.Run_Job__c == true){
//Create Site Log records for 7 business days in the future, if needed
if (sj.Method_To_Execute__c == 'CreateSiteLogs'){
intReturn = clsScheduledJobs.CreateSiteLogs(null);
//This job is run in batches of 1 site invoice (the max that can be processed
//before hitting the governor limit, sadly), so less than 1 means it's done
intMagicNum = 1;
}
//Send out Job Loss Report
if (sj.Method_To_Execute__c == 'JobLossReport'){
intReturn = clsScheduledJobs.JobLossReport(null);
//Single email limited to 10 at a time
intMagicNum = 10;
}
if (sj.Method_To_Execute__c == 'ScheduledJobCleanup'){
intReturn = clsScheduledJobs.ScheduledJobCleanup();
//This job always returns 0, shouldn't need to be re-run anytime soon
intMagicNum = 1;
}
Scheduled_Job__c new_sj = new Scheduled_Job__c(
Method_To_Execute__c = sj.Method_To_Execute__c,
Name = sj.Name,
Run_Job__c = false,
Schedule__c = true,
Time_to_Start__c = sj.Time_to_Start__c);
//If the number returned by the method is less than the Magic Number,
//create a Scheduled Job record to run it again tomorrow night
if (intReturn < intMagicNum){
//Tomorrow, 12am
dte = Datetime.newInstance(System.today().year(), System.today().month(), System.today().day()).addDays(1);
//Add on hour/minutes from Time To Start field
dte = dte.addhours(Integer.valueOf(sj.Time_to_Start__c.substring(0,2)));
dte = dte.addminutes(Integer.valueOf(sj.Time_to_Start__c.substring(2,4)));
//Sometimes if there are multiple scheduled jobs around the same time,
//SF processes them in bulk and the governor limits are hit, so make sure
//they're all spaced out at least 15 minutes
sjList = [Select Next_Run_Datetime__c from Scheduled_Job__c
where Next_Run_Datetime__c >=: dte and
Next_Run_Datetime__c <: dte.addMinutes(30) and
Schedule__c = true and Run_Job__c = false
order by Next_Run_Datetime__c DESC
LIMIT 1];
dte = (sjList.size() > 0 — sjList[0].Next_Run_Datetime__c.addMinutes(15) : dte);
//Reset Scheduled Job record to midnight of tomorrow
new_sj.Next_Run_Datetime__c = dte;
//Otherwise, there are more records to process, so create a record
//to run it again immediately
} else {
//Make sure this is offset from other jobs so they don't run in bulk
sjList = [Select Next_Run_Datetime__c from Scheduled_Job__c
where Next_Run_Datetime__c >=: System.now().addMinutes(-15) and
Next_Run_Datetime__c <: System.now().addMinutes(30) and
Schedule__c = true and Run_Job__c = false
order by Next_Run_Datetime__c DESC
LIMIT 1];
new_sj.Next_Run_Datetime__c = (sjList.size() > 0 – sjList[0].Next_Run_Datetime__c.addMinutes(15) : System.now());
}
insert new_sj;
}
}
}
Subscribe to:
Posts (Atom)