Salesforce formula with CONTAINS

Recently, I learned an interesting detail about the CONTAINS method that can be used in formula fields.  According to the inline formula help, CONTAINS is defined as follows:

CONTAINS(text, compare_text)

Checks if text contains specified characters, and returns TRUE if it does. Otherwise, returns FALSE

Let's say you needed to check for multiple values and for each, set your formula to some other value. The obvious path would be to nest your CONTAINS in a case statement, right?  Something like this, maybe:

CASE(My_Field__c

  CONTAINS(My_Field__c, 'Some Value'), 'New Value'

  CONTAINS(My_Field__c, 'Some Other Value'), 'New Value', etc...)

WRONG!  Turns out, you can't use CONTAINS with CASE, as confirmed here.  Ugh.  So, plan B, might be to use CONTAINS with a nested IF, right?  Yes, it works, but the problem you may run into is that if you are checking for many values, you may hit the maximum size for the formula field, which at the time of writing, is 3900 characters.  

So, when I was researching this, I came across this obscure knowledge article.  What caught my eye was the following:

Example 2:
a. CONTAINS("CA:NV:FL:NY",BillingState)
Will return TRUE if BillingState is CA,NV,V,L,FL:NY or any exact match of "CA:NV:FL:NY".
NOTE: when using contains with the multiple operator (:) contains then becomes equals.

The colon operator allows you to inspect many values, without the overhead of nesting IF-statements. This seems to be very well suited for checking the standard BillingState field, where values will be relatively uniform.  The key difference is the highlighted note indicating the change of function when using the operator.  In the provided example, if you had C, A, N, V, L, F, or Y, it would return true.  But, if you had California, or even CALIFORNIA, it would return false.  By contrast, if you had used a nested-if, you could have introduced some additional flexibility in finding California, CA, Cali, NoCal, SoCal, etc, sacrificing some of your character limit.  So, the takeaway for me is this: if your data is pretty uniform and structured, use the : operator, otherwise use the nested-if. 

Multiple Addresses - A Larger Question

One interesting aspect of Salesforce is the address concept.  Since Salesforce is a software (er, no-software) company, they don't really ship anything, right?  Everything is delivered via the cloud.  This perspective seems to have influenced how they model addresses.  Out-of-the-box, addresses are merely attributes of an account and contact in Salesforce.  So, for businesses who actually ship things, like widgets, to other businesses, how does this out-of-box model work?  Say, you are a widget maker and you have big customers, with many locations that consume your widgets.  How should you capture where you're selling your product and where it is sent?

Imagine you have a customer who's organization looks something like this:

• Joe's Plumbing Worldwide
• Joe's Plumbing Canada
• Joe's Plumbing America
• Joe's Plumbing New England
• Joe's Plumbing Boston
• Joe's Plumbing Hartford
• Joe's Plumbing Chicago
• Joe's Plumbing Los Angeles
• Joe's Plumbing Europe
• Joe's Plumbing France
• Joe's Plumbing England

If you were selling widgets to Joe's Plumbing, what is important for your business to capture?  Does it only matter that you are selling to "Joe's Plumbing Worldwide"?  Do you have regional team's that support Joe's Plumbing in language?  Do your team's "own" these accounts and selling into them? When you report on sales and service, do you want to measure the selling and servicing at the regional level?  Is your customer data provided or enriched by any 3rd party services?

The account concept is central to Salesforce crm and so the decisions you make around how you model your customers, is significant.  Many appexchange products and services, like address verification, assume that you use the out-of-the-box address fields.  

There are several options to support multiple addresses and each of the options I've listed below can have some variation but the important take-away is that your approach has some implications to think through.

Option 1: Use the native Account hierarchy

Option 2: Create a custom object to hold Addresses

Option 3: Denormalize shipping addresses onto Opportunities/Orders

Option 4: Add additional shipping address fields onto your Account object

For example, if you go with option 1, do your shipping records mean anything?  Should your teams own these records?  Do you need to restrict the ability to create opportunities to just the parent account? Do you want to restrict the ability to create contacts or tasks to just the parent account?  If not, is your reporting ready for a hierarchy of data to roll up?

If you like option 2, do you need to report on shipping information?  Does your address data need to be verified or enhanced by a 3rd party and if so, does that 3rd party support your custom address object?

As is usually the case, there are many ways to solve the problem.  It's a matter of figuring out what is the best fit for your business and thinking through the implications of that decision.

Salesforce Quotes

There is no doubt that the Salesforce Quote functionality is useful.  But could this object be any more specialized?  We've run into so many issues with customizations.  Here's a list of some issues or limitations I've recently come across:

1. You cannot override the standard Quote view with a custom visualforce page.  The option to override View is not available.

• I suspect that because the object is so specialized (oppty sync, PDF creation, etc) that this is not likely to change.  If you need custom quote functionality, you may need to build your own from the object up.

2. The standard Discount field is not available as a merge field in Email templates.

• As a workaround, you can create a formula field that references the out-of-box field and then use the formula field in your email template.

3. You cannot roll up list price from the line item.

• Apparently, list price is a reference to the PricebookEntry object and so it is not a field that can be summarized.  To proceed, you need to create another currency field and populate it with a workflow on the QLI based on your business requirements.

4. You can only control edit access to Sales Price from a Profile-level parameter.

• It's not so bad to have to use this parameter, since you can also put it in a permission set, but it's pretty inconsistent from the way the rest of the profile/page layouts behave.

5. If your record is locked as the outcome an approval process, you will not be able to save your pdf to the quote using the Create PDF button.

• Speaking of inconsistent... on all other objects that are locked, you can almost always add an attachment.  To get around this, I changed our approvals to unlock the record, then used validation rules to keep the QLI from being changed.

The Create PDF button on Quote

I was asked about the ability to restrict the creation of a PDF for a given quote until a couple business rules had been met.  As I thought about the solution, a couple ideas came to mind:

1. Add a record type for the ok Quotes and assign that record type a new layout that includes the Create PDF button.  Remove the Create PDF from the other page layout.
2. Modify the behavior of the existing Create PDF button to check the status first.
3. Create a new VF page to replicate the Create PDF functionality.

Given the timing and other project considerations, I opted for #2, if it was feasible.  I knew that #1 would work but I was worried about introducing a record type and then having to roll it back when another quote-related project went live.

To start, I had to figure out if I could see the code that the out-of-box button was calling to render the PDF.  With chrome it was pretty easy to inspect the element and see the code the button was calling.

With a minor bit of additional javascript, the quote's status can be interrogated first and an informational alert raised, if certain business criteria are met:

/*********************************************************************
if('{!Quote.Status}'!= 'XYZ' ) 
{
// do some business logic...
var isOk = true;  
} if(isOk) 
{ 
var pdfOverlay = QuotePDFPreview.quotePDFObjs['quotePDFOverlay']; 

pdfOverlay.dialog.buttonContents = "<input value=\'Save to Quote\' class=\'btn\' name=\'save\' onclick=\"QuotePDFPreview.getQuotePDFObject(\'quotePDFOverlay\').savePDF(\'0\',\'0\');\" title=\'Save to Quote\' type=\'button\' ><input value='Save and Email Quote' class='btn' name='saveAndEmail' onclick=\"QuotePDFPreview.getQuotePDFObject(\'quotePDFOverlay\').savePDF(\'1\');\"; title='Save and Email Quote' type='button' ><input value=\'Cancel\' class=\'btn\' name=\'cancel\' onclick=\"QuotePDFPreview.getQuotePDFObject(\'quotePDFOverlay\').close();\" title=\'Cancel\' type=\'button\' >"; 

//change this to use the correct template for your business/environment!! 
pdfOverlay.summlid = 'XXXXXXXXXXXXX'; 

pdfOverlay.setSavable(true); 

//change this to use the quote id 
pdfOverlay.setContents('/quote/quoteTemplateDataViewer.apexp?id={!Quote.Id}','quote/quoteTemplateHeaderData.apexp?id={!Quote.Id}'); 

pdfOverlay.display(); 
} 
else 
{ 
//raise an alert to let the user know about some business rule
alert('The Quote requires XYZ before the PDF can be generated.'); 
}

*************************************************************/

A Riddle, Wrapped in a Mystery; Inside an Enigma

Do you ever see inconsistent results when running unit tests in a sandbox versus running them in production?  Or, inconsistent results when running unit tests between sandboxes?  Well, the black-box that is the Salesforce unit test, just became blacker and boxier to me.

I was trying to deploy a patch to production and was going through our normal build path that passes through our full sandbox.  When attempting to deploy to the full sandbox, our automated build process failed on a test method that we had not changed in any way.

The error: ... System.TypeException: Invalid date/time: 05/05/2010 00:00 AM stack...

A colleague and I inspected the method and the class and determined that they were identical in our sandbox and production.  When we ran the individual test in our full sandbox, it failed, and when we ran it in our production environment, it passed.

What the?!

The error pointed to this fragment of code in the test: datetime.parse('05/05/2010' + ' 00:00 AM');

Not sure why it was 00:00 AM so I updated it to 12:00 AM and was able to proceed with the deployment.

I was unsettled by experience because we had been able to deploy to this sandbox two weeks earlier and had not had this test method fail.  So, we opened a case with Salesforce and asked their "premier" support to explain it.  Our first support rep reproduced the results but gave us a canned response that it wasn't a good practice to use 00:00 AM.  Fine, we said, but tell us why it passes production tests but not tests in our full sandbox and what exactly changed in our sandbox?  We went on and on like this for a couple days, escalated the case, and are now doing the same dance with another "premier" support rep.

For those of you who read this, please try this at home and let me know if you can replicate this in your sandboxes:

Create a test object called TestDate__c.  Add one custom field called SomeDate (type = date/time).

Create an apex class (api version 27, for consistency sake) with the following code:

global with sharing class TestingDate
{
    public static testMethod void testMyObj()
    {
        TESTDate__c myobj = new TESTDate__c();
        myObj.SomeDate__c = datetime.parse('05/05/2010' + ' 00:00 AM');
        insert myobj;
    }
}


Run your test.  The results we got were as follows:

NA14 (developer org): Pass
CS15: Fail
CS12: Pass

If nothing changed on CS15, how can this be?  Still waiting for an answer...

Winter 14 Highlights

Winter 14 previews are being applied to sandboxes over the next week or so.  I've gone through the release notes and pulled out some notable items.  Before getting to these items, a couple general reactions to the release notes:

• Salesforce is getting massive.  I mean, between the Sales & Service features, Chatter updates, API changes, and all of the new ".com" products like Data.com, Work.com, Desk.com, and Social.com, it's a wonder that anyone is able to keep anything straight, including Salesforce employees.
• Chatter keeps getting the lion's share of enhancements.  Since 2010, it seems that every quarterly release is stuffed with Chatter updates with a few core CRM improvements thrown in to keep the masses happy.  I've been doing Salesforce.com work since 2008 and only know of a few clients who use Chatter regularly.  I understand the academic appeal of Chatter but given the realities of email in business, can you really believe that integrating a canvas app into a Chatter stream is more valuable than providing a true M:M relationship between Accounts and Contacts or providing a better and more flexible Salesforce/Outlook/Exchange plug-in?  

Without further delay, here are some notable features coming our way in middle October with Winter 14:

User Object Sharing
I was surprised to find out that this object was not previously governed by sharing.  There are some interesting use-cases where you'd potentially need to hide/share user details.  Curious to see how this works if the org-wide default is set to Private with Chatter-enabled orgs.  Also notable, Apex managed sharing is not supported for this object.

Approval Emails with Comments
This is one of those "duh" features that has taken a backseat to Chatter.  Finally, you can add the approver's comments to an approval email notification without having to build some trigger or vf-based email.

Embed a report within a standard detail layout
I would also categorize this under "duh".  Finally, remove the link to the report and just put the report in the layout.  Much better, though it seems there are some limits, such as, only 2 charts per layout.

Sandbox Updates
Configuration-only sandboxes are being renamed to "Developer Pro" and will have storage limits bumped up from 500 MB to 1 GB.  Developer sandboxes will continue to be called "Developer" but will also get a bump in storage from 10 MB to 200 MB.

Developer Console
I do most of my development in Eclipse but if I'm away from my laptop, I use the developer console as it is a vast improvement over the standard code editor.  A couple highlights here:

Have you ever had a complex process to debug?  Well, you probably ran into a log limit, right?  Just before discovering the source of your headaches, you see text like "Log limit reached"... ugh!  You can now override log levels for a specific trigger or class.  So you could turn logging down by default and then turn it up on the class or trigger where you suspect the problem resides.  Sweet!

One new feature that caught my eye was this debug tool in the console.  It looks like it will graphically display the order of execution.  Very nice!

Visualforce Updates
VF has some HTML 5 updates that should prove useful.  One that stood out to me was the new <apex:input> tag.  You can specify the type by using a "type" attribute.  The browser, using HTML 5 standards, should render the input based on the type.  One use case where this should help is with input dates.  If you needed to provide a date input, without binding it to a Salesforce object's field, you had to do some hokey workaround to get the date picker to display.  Now, you should be able to just do something like <apex:input type="date">.

There is also support for a "list" attribute to add to the input tag.  It looks like this should provide visualforce with some autocomplete capability that you could previously do with some jquery magic.  For picklists, we'd typically generate a list in the controller, then bind the list to VF through selectlist/selectoption tags.  I guess we'd use the input tag and list attribute in cases where we'd need the autocomplete on and where we wouldn't need some of the selectlist/selectoption features like displaying but disabling values.

Apex Changes
The maximum number of code statements has changed from being a flat number to CPU time-based.  Previously this was 200k, which is suprisingly easy to hit, when you have some nested loops.  The new limit is based on whether you are synchronous or asynchronous (batch Apex).  They've advertised this as removal of a limit, but in reality, they've just changed how the limit is computed.  Seems like it'll be harder for a developer to anticipate CPU utilization.

The new Database.getUpdated() method allows you to pass in the object, and the start and end date/time and will return to you a list of updated records.  That should be useful!

There is a new BusinessHours static method that allows you to pass in a date/time and an id to a Business Hours object to see if something is within the business hours and to fetch when the next available business hours start.  That should be handy.

Other
Environment hub?!  I need to see screen shots to determine the actual utility, but it potentially sounds useful. Particularly if you are an admin for multiple orgs with multiple instances.

***

When the changes are actually applied to my personal sandbox, I'll do some mockups of some of the new VF stuff.  Stay tuned.

Salesforce Sandbox Instances

A while back, I was on a client site that was affected by a sandbox outage that lasted over 3 days. Unfortunately, both of our full copy sandboxes were on this instance and a good number of our developer/configuration sandboxes were as well.  Needless to say, we were greatly affected by the outage and when we asked about migrating some of our sandboxes to another instance to reduce the risk for our team, we were told that it was not possible.  Today, again, there is another major outage, this time on CS15, and again, most of our development, testing, and full copy sandboxes are affected. It seems crazy to me that customers who seek the cloud for protection against this type of outage do not have the ability to specify that they want their sandboxes distributed across multiple instances.  If we have a break/fix situation, we are in a difficult situation given that our development path is dependent on CS15 being operational.  Customers should be allowed to spread their developer sandboxes across instances to reduce the impact of another outage.

Vote for the idea here.