SOQL Group By – Grouping records in Salesforce.com

Posted by on February 26, 2010 · 9 Comments 

Overview

One of the most sought after features in SOQL (Salesforce.com Object Query Language) has been the inclusion of record grouping. While Aggregate functions such as Min, Max, Count, and Sum have been available, they have not been available to retrieve grouped Aggregate results. For example, if you wanted to retrieve the Count and Sum(Amount) of Opportunity Records grouped by an Account, you have to retrieve all of the Opportunities and create a loop to increment Counts and Sum based on the Account associated with the Opportunity. Ugly!
With the introduction of the Salesforce Platform Winter 2010 release and version 18 of the API, grouping and a few other functions are available. In this post I will concentrate on the “GROUP BY” clause. In future posts, I will examine other clauses such as:

  • GROUP BY ROLLUP ()
  • GROUPING ()
  • GROUP BY CUBE ()
  • HAVING ()

EXAMPLE

In this example, I want to showcase the different types of Grouping and Aggregate Functions that can be combined to come up with some interesting results. I want to create a Visual Force Page bound to a Custom Controller, where I can display the results of the SOQL statement.

First, let’s start with the Controller code. I wanted to bind the results of the query directly to a apex:dataTable, so I created an object to hold the Grouping values and Aggregated results. Below is the object:

public class GroupedQuery
{
    public string GroupBy1     {get;set;}
    public string GroupBy2     {get;set;}
    public string GroupBy3     {get;set;}
    public integer Count {get;set;}
    public Decimal Sum {get;set;}
    public Decimal Average {get;set;}
    public Decimal Min {get;set;}
    public Decimal Max {get;set;}
}

In addition, in order to bind an object to the dataTable within the Visual Force page, you need to expose a public member from your Controller code. For brevity sake, I just added a public List<> member like this:

public List<GroupedQuery> queryResults {get;set;}

Next in the Controller Constructor (keep in mind this is for demo purposes!) we use a SOQL query to retrieve a List of generic sObjects:

List<sObject> queries = [select Account.Name AccountName, Count(Name) CountResult
                                           from Opportunity group by Account.Name];

Once we have the Results we iterate through a loop to create our collection of GroupedQuery objects:

queryResults = new List<GroupedQuery>();
for (sObject query: queries)
{
    GroupedQuery myObject = new GroupedQuery();
    myObject.Count = Integer.valueOf(query.get('CountResult'));
    myObject.GroupBy1 = String.valueOf(query.get('AccountName'));
    queryResults.Add(myObject);
}

The variable queryResults is exposed as a public variable from the controller and thus we can tie this List directly to a dataTable and access the public properties of the GroupedQuery object contained in the List. Below is the Visual Force page code:

<apex:dataTable id="queryResults" value="{!queryResults}"
                var="queryResultsRow" border="1" width="100%">
    <apex:column >
        <apex:facet name="header">GroupBy1</apex:facet>
        {!queryResultsRow.GroupBy1}
    </apex:column>
    <apex:column >
        <apex:facet name="header">Count</apex:facet>
        {!queryResultsRow.Count}
    </apex:column>
</apex:dataTable>

The code above will output the following results (the Account data is data from the Developer Edition of Salesforce.com)

A more involved example is included below. This includes two levels of grouping, as well as Min, Max, Sum and Average functions:

Summary

As demonstrated, the new functionality released with the Salesforce.com API Version 18 with regards to Grouping is extremely powerful and addresses a pain point that many developers have had to overcome to achieve Rollup type queries. This information is contained within the What’s New notes for Salesforce.com Version 18. Keep in mind this is included with the Winter 2010 release of the Salesforce.com platform, so you will need to ensure that your organization has been upgraded in order to avail of this functionality.

Are you tired of working with consulting companies who give you what you ask for, but not what you need? If you want to partner with us to extend your IT capabilities and produce real results, contact us

public class GroupedQuery
{
public string GroupBy1     {get;set;}
public string GroupBy2     {get;set;}
public string GroupBy3     {get;set;}
public integer Count {get;set;}
public Decimal Sum {get;set;}
public Decimal Average {get;set;}
public Decimal Min {get;set;}
public Decimal Max {get;set;}
}

Filed under Andy's Anecdotes, ext.IT Blog, salesforce.com · Tagged with , , , , , ,

Matching Salesforce Data to Back Office Systems

Posted by on February 19, 2010 · 1 Comment 

Overview

Often when working with Salesforce Account and Contact records, you want to see if there’s a match with existing SQL Server data in back office systems. While there are several commercial solutions that perform data matching on the Force.com platform (just search the App Exchange for deduplication or lead management to see what’s out there), as well as SSIS fuzzy lookups on the Microsoft SQL Server platform, often you want a quick and free solution that compares data between Salesforce.com and SQL Server data.

To do this, you simply need to add a custom formula field in Salesforce.com and a similar query in your SQL Server data. You can tweak this formula to match different fields depending upon the demographic information contained in the back office system. I will demonstrate this with the Account object in Salesforce.com. In this example, I am assuming that the company name, street, city and postcode will be stored in the back office system.

Duplicate Check field

The field simply concatenates parts of the customer’s name and address together, converts it to upper case, and pads any empty spaces with a character not likely to appear in the name or address fields. I chose the asterisk “*” for this example.

Name
 Street City Post Code Duplicate Check Code
***************************
FELLS WARGO HOME EQUITY FELLS W********************
CATAMARAN EXPRESS COMPANY SAN BERNADINO 91911 CATAMA E**********SAN B91911
GUMFORT CARE DENTURE CENTER 900 HOCKSVILLE RD MASSAPEQUA 91758 GUMFOR C900 HOCKSVMASSA91758
ORLANDO/GREEN COUNTY CAB 1234 LAKE ROAD ORLANDO 12319-5273 ORLAND C1234 LAKE ORLAN12319

You’ll notice that this works best when the fields you choose to populate the check code are required, or at least generally populated in both systems. If you choose fields that are often left blank, you can turn up more false positives for matches, which defeats the purpose of the duplicate check.

Salesforce.com Custom Formula Field

To implement this in Salesforce, you need to add a custom formula field to the Account object in Salesforce.com:

  1. Login to Salesforce.com as an account with administrator privileges.
  2. Click the ‘Setup’ link at the top of the screen:
  3. On the left hand App Setup menu, click Customize, Accounts, Fields:
    Customize Accounts
  4. In the Account Custom Fields and Relationships section, click New.
  5. Choose Formula as the field type:
    Formula Field
  6. Enter a label and a name for the field. Choose Text as the output type of the formula field:
    Output Type
  7. Enter the matchcode formula into the Advanced Formula text area:
    Matchcode Formula
  8. Setup the field level security and add the field to the appropriate page layouts, and Save the field.
  9. That’s it! The field will now be available via the API, Apex code and can be exposed through data exports with the Apex Data Loader.

I’ve reproduced the code from the screenshot in a copyable format below to save you from RSI

UPPER (
              MID(TRIM( NULLVALUE(Name, '') )& '******' , 1, 6)
            & MID(TRIM(NULLVALUE(Name, '')) & '*' , FIND(' ', TRIM(NULLVALUE(Name, ''))
                         & '*'),1)
            & MID(TRIM(NULLVALUE(Name, '')) & '***' , FIND(' ', MID(TRIM(NULLVALUE(Name,
                         '')) & ' ',
                   FIND(' ',TRIM(NULLVALUE(Name,'')) & ' ') + 1,59)) +
                   FIND(' ', TRIM(NULLVALUE(Name,'')) & ' ') + 1,1)
            & MID(TRIM( NULLVALUE(BillingStreet, '')) &'**********' , 1, 10)
            & MID(TRIM( NULLVALUE(BillingCity, '')) & '*****' , 1, 5)
            & MID(TRIM( NULLVALUE(BillingPostalCode, '')) & '*****' , 1, 5)
        )

SQL Custom Query

Although adding the custom field to Salesforce alone has some value (for example, you could use it in custom code to detect duplicates), the real value is when you can match up the values to your back office system. I’m assuming your back office server is running SQL Server 2005 or higher for this T-SQL example, but most forms of SQL have equivalent functions that can manipulate strings, so check your documentation if you are using a different flavour of SQL, and please post your custom formula in the comments section to help out others.

This query could be added to a view, stored procedure or custom field in SQL.

For this example I’m assuming that the fields are called ‘Name’, ‘StreetName’, ‘City’, and ‘ZipCode’, and are stored in a table called CompanyInfo.

SELECT UPPER (
        SUBSTRING(LTRIM(RTRIM(ISNULL(Name, '')))+ '******' , 1, 6) +
        SUBSTRING(LTRIM(RTRIM(ISNULL(Name, '')))+ '*' , CHARINDEX(' ', LTRIM(RTRIM(ISNULL
                         (Name, ''))) + '*'),1)
      + SUBSTRING(LTRIM(RTRIM(ISNULL(Name, ''))) + '***' , CHARINDEX(' ', CHARINDEX(LTRIM
                         (RTRIM(ISNULL(Name,''))) + ' ',
             CHARINDEX(' ', LTRIM(RTRIM(ISNULL(Name,''))) + ' ') + 1,59)) +
             CHARINDEX(' ', LTRIM(RTRIM(ISNULL(Name,''))) + ' ') + 1,1)
      + SUBSTRING(LTRIM(RTRIM(ISNULL(StreetName, ''))) + '**********' , 1, 10)
      + SUBSTRING(LTRIM(RTRIM(ISNULL(City, ''))) + '*****' , 1, 5)
      + SUBSTRING(LTRIM(RTRIM(ISNULL(ZipCode, ''))) + '*****' , 1, 5)
     ) AS 'DuplicateCheck'
FROM CompanyInfo

Using your duplicate check code

Once the check is in place in both systems, you can use it in several ways. You can match the fields directly, you could create a matching “confidence” score by using character by character comparisons or you could use more complex fuzzy lookup functions to further match the data.

Summary

Now that you have a single field to compare Salesforce and SQL data, you can tweak the values to find better matching algorithms, and use these fields as part of any duplicate checking process for data integration.

Filed under ext.IT Blog, Paddy's Prose, salesforce.com · Tagged with , , , , ,