Fun With SOQL: Cubed Results

GROUP BY CUBE is an awesome feature of SOQL that nobody talks about. It is a very handy tool when you need to create complex visualforce reports because you can get aggregate results for different grouping of values in a single SOQL call.

Let's say that you want to create a graphical summary of the Opportunity object, and need to get the count and total of the Opportunity amounts of every combination of Stage, Type, and Lead Source. Well, you're in luck, because with Just one "cubed" SOQL call you can get enough data to fill a visualforce page with interesting charts:



The above statement returns:

• The count and subtotal for every combination of Stage, Type, and Lead Source (That's as many rows as the number of Stage picklist values times the number of Type picklist values times the number of Lead Source picklist values).
• The count and subtotal for each two field combination where the values of the third field does not come into consideration (all two field combinations).
• The count and subtotal for each field value regardless of the value in the other two fields (for each of the three fields).
• The count and total for all the values.

Here is how this works:

• The GROUP BY CUBE section determines which field values get aggregated.
• The GROUPING() in the SELECT section provides a way for you to evaluate what subtotal each row corresponds to - a zero in that function means that the row is grouped by the specified field, and a one means that it doesn't (seems counter intuitive, but that's how it works). See example of that below.
• The aggregate results in the SELECT provide the data. A nice addition here could be using Account for one of the grouped results and use COUNT_DISTINCT(Account) to get count of unique accounts as opposed to unique opportunities.
• The ORDER BY section is very important because it helps you process the data. By sorting the GROUPING() functions first, the results come up in a predictable order that you can take advantage of in an Apex loop.

Here is an example of how you can harvest the cubed results:

You can find GROUP BY CUBE in the documentation here.

Read More

Change Sets 101

Say you're a new Salesforce Admin and you need to make changes to your org.  You're already following best practices by making changes to your sandbox and not production (right?) and everything is working the way it should.  Now it's time to push those changes to production, but how?

Change Sets

The process is simple enough:

1. Create an Outbound Change Set
2. Add Components to that Change Set
3. Upload the Change Set to another sandbox (you can do that) or production
4. Validate and Deploy the Inbound Change Set in the org the Change Set was uploaded to

Before any of that can be done, you need to establish a Deployment Connection between your sandbox and your production instance which will allow you to upload Outbound Change Sets to Production for deployment.

NOTE:  You can establish Deployment Connections between two sandboxes as well for those times you need to push changes from one sandbox to another.  The rest of this post assumes you're pushing from a sandbox org to a production org.

Create Deployment Connection

1. First, within your production instance, click on your name at the top of the page, and then click on "Setup"
   On the left-hand side, under "App Setup," expand "Deploy" and click on "Deployment Connections"
   
   
   
   
   
   
   
2. Find the name of your sandbox and click on the "Edit" link next to it
   
   
   
   
   
   
3. Check off the box that says "Allow Inbound Changes" and then click on the "Save" button
   
   
   
   
4. The next thing to do is to create an Outbound Change Set within your sandbox. For this scenario, the change set will include a page layout that I want to deploy within my production instance as well as a new field that I added to the object within the sandbox. One change set, two components, one deployment. Nice!

Create Outbound Change Set

1. Within your sandbox, click on your name at the top of the page again and then click on the "Setup" link
   
2. On the left-hand side, navigate to "App Setup" --> "Deploy" and then click on "Outbound Change Sets"
   
   
   
3. Click on the "New" button to create a new Outbound Change Set
   
   
   
4. Provide a "Name" and "Description" for your change set.
   
   
   
5. Click on the "Save" button.
   
6. Within the "Change Set Components" section, click on the "Add" button.
   
   
   
   
   
   
   
7. First, let's add the new field. Change the "Component Type" dropdown to "Custom Field"
   
   
   
   
8. Next, find and select any fields that you also need to deploy that are not currently found in production.
   
   
   
9. Click on the "Add to Change Set" button at the top of the page. Here's a quick tip -- make sure that you use the "Add To Change Set" button after you select something. Changing the component letter, page, or "Component Type" will clear any previous selections.
   
   
   
10. Now it is time to do the same for the page layout. Change the "Component Type" to "Page Layout, find and select your new layout, and then click on "Add To Change Set" again.
    

Your change set should have two components in it now.

Upload a Change Set

1.  To push your Outbound Change Set to the Production instance for deployment, click on the "Upload" button.

2.  Choose your Production instance and then click on "Upload"

3.  Within a few minutes you should receive an email, letting you know that your Change Set was uploaded to the production instance. Keep in mind that you don't always have to push from a sandbox to production. You can go from sandbox to sandbox or even production to sandbox.

Validation and Deployment

Once you do receive that email, it is time to deploy it.

1. Within the org you've uploaded your Change Set to, click on your name at the top of the page again and then click on the "Setup" link
   
2. On the left-hand side, navigate to "App Setup" --> "Deploy" and then this time click on "Inbound Change Sets"
   
   
   
3. Click on your change set. Here you will be able to see information about the change set, including its components, any details, and its deployment history. You also have two options; "Validate" and "Deploy."
   
   
   
4. Clicking on "Validate" goes through all of the actions required to deploy the change set without actually deploying it. This is always a safer bet as you can learn which components will be new, overwritten, or deleted. Sometimes you learn things from a validation that you didn't expect and can save yourself from making mistakes. 
   
5. "Deploy" first validates and if all is well will deploy your change set. Keep in mind that if you've added new layouts, they won't be assigned to a Record Type yet, so you'll have to do that manually. If you added fields, permissions won't be set up on that field as they would be if you were to manually create the field. The same goes for picklists options and their Record Type preferences.

Read More

EmailMessage Status Values

I was working with the EmailMessage Object last night and ran into a question I've spent time answering before. I was writing a SOQL query string to return EmailMessage records based on their Status.

This didn't work:

I knew the data type for the Status field was a picklist/string, so I looked at the results of this:

The results yielded numbers 0 through 4:

Here are the values and the Statuses they represent:
'0' = New
'1' = Read
'2' = Replied
'3' = Sent
'4' = Forwarded

The Spring '13 Release notes describe the ability to enable "Email Drafts and Approvals" (Page 63 of the PDF). Later, on page 189, there is a code sample that creates an outbound Draft; note the Status value of '5'.

Read More

Potential Org Issues With Future Releases

With Spring '13 currently deployed to Sandbox orgs and soon to Developer and Production orgs, there are tons of new features to utilize within your future projects.  I was recently excited about a new Cases Class called ".getCaseIdFromEmailThreadId."

Remember that "Ref" tag formula from this post?  You know, the unique ID that is attached to emails when using the Email-to-Case functionality?  I was working on another project in which an email web service was required and I needed to take the "ref" tag and identify the Case ID of the associated Case.

Then I read the Spring '13 Release Notes and was delighted to find the Cases.getCaseIdFromEmailThreadID method.  I wrote a quick code block to test it out:

Unfortunately, I received this dreaded message.
Error: Compile Error: Method does not exist or incorrect signature: Cases.getCaseIdFromEmailThreadId(String) 

I opened a Case with Salesforce in hopes of a quick remedy to save me from coding it myself.  The answer to my problem was that this org already had a custom "Cases" class.  This custom class was being referenced as opposed to the new Salesforce provided feature.

The moral of the story is, Salesforce introduced this new feature into my org in which their class conflicted with my class and I only discovered the issue when I tried using the new method.  Lots of developers may use a common class naming convention in which the class name mirrors the Objects in which they work with.  Lots of developers may try to use future new Salesforce Classes/Methods only to be stopped in their tracks while scratching their heads.  Hopefully, this possible conflict is the first thing they think of.

Read More