Handling ‘List has no rows for Assignment’ Error

How many times have we seen this error? What does it mean? Usually it means you’ve requested a record using SOQL from an Object which returns zero results.

The simplest way is always the best I always say. Consider the following:


Account a = [SELECT Id, Name FROM Account WHERE Id= :myId];

This should return an Account record. BUT what if myId contains a value that is no longer available (for whatever reason). Then you’ll get the “List has no rows for assignment” error. To solve this problem we have two solutions.

The first is an easy way which doesn’t require nesting try..catch for every SOQL query call. If you always return the Object query as a List<> you can check the .size() property to determine if the query found any results.


List aList = [SELECT Id, Name FROM Account WHERE Id= :myId]

If( aList.size() > 0 )
{
   a = aList[0];
}
else
{
   System.debug('Empty Results');
}

While this works great you do need to put an if() statement everywhere. Another way is to wrap the code in a try..catch…


try
{
   Account a = [SELECT Id, Name FROM Account WHERE id= :myId];
}
catch( DmlException de )
{
   // Handle the Exception
}

The advantage to using the second method is that now we can get creative and build a simple class to use this to test any Query so you don’t have to put the code in everywhere.


Public Class QueryChecker
{
    public Boolean QueryHasResults( String q )
   {
      List sobjList = Database.query( q );
      return( sobjList.size() > 0)
   }  
}

Now you can test the condition before you even execute the query.


Boolean isValidQuery = QueryChecker.QueryHasResults('SELECT Id, Name FROM Account WHERE Id = XXXXXX');

if(! isValidQuery)
{
   // code to do something
}

There you have it. A simple solution to handling an annoying error.

 

Advertisements

Text Replacements in Formula Fields

So I get asked this quite often.  How do I replace text in a formula field?  Normally as a programmer I’d lean toward using a regular expression (or regex).  Then I got to thinking… how would an administrator handle this? Because as a developer I have a whole set of tools at my disposal they don’t have..so I sat down and figured out HOW to handle it, and this is how I did it.

Consider the following problem : You have a text field (Company Name) that you need to use to create a unique file name.  So you of course can append date/time and other variables to the field value to produce an unique name…BUT what if that name contains a special character?  For Example : Wallace & Sons.  What do you do now?  The filename can’t contain the ampersand or “&” sign…

Enter the custom formula field.  Formula fields let us create a value based on a formula applied against any number of fields both on the target object and any related objects.  At first I decided to use the FIND() feature to determine if the text as present:


IF( FIND(Company_Name__c), "&")  > 0, SUBSTITUTE(Company_Name__c, "&", ""), "")

Which works well for locating a single special character… and yes you can string them together with IF() statements and nest them… BUT this leads to issues in that you must account for both finding and NOT finding the search text for every character.

Upon closer inspection though, I noticed that SUBSTITUTE while it only accepts 3 parameters it will ALWAYS return itself as unaltered text if the search string is not found.  So logically we can now nest the SUBSTITUTE function to find multiple characters.  The following locates the ampersand or “&” sign and the percent or “%” sign:


SUBSTITUTE(  SUBSTITUTE( Company_Name__c, "%", ""), "&", "" )

Therefore to create a more complex replacement is as simple as adding an outer SUBSTITUTE() function call.  The following function removes :
dashes (-),
parenthesis (“),
spaces(” “),
ampersand (&)
period (.)
percent (%)
pound (#)
forward slash (/)
semi-colon (;)
colon(:)


SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(Company__r.Name, "(", ""), ")", ""), " ", ""), "-", ""), "&", ""), ".", ""), "%", ""), "#", ""), "/", ""), ";", ""), ":", "")

And there you have it. The regular expression free, formula field way, to replace text in a field.

Using the Developer Console for Salesforce Administrators

Most Salesforce.com Administrators are often asked to perform a variety of tasks that are not easily available in the Salesforce.com User Interface or Reporting environment.  So to that end I’ll endeavor to teach you (the Salesforce.com Administrator) a few tricks of the trade, including how to use the Developer Console.

First log into Salesforce.  Then click your name and from the Dropdown select “Developer Console”

Menu

So for these examples I will use the User and Profile Objects. Here are some things you can keep and Modify for handling users.  The first is an SOQL Queries you can run that will give you a count of Users by Profile.  This is known as an “Aggregate Query”


SELECT ProfileId, Profile.Name, COUNT(Id) myCount FROM User WHERE IsActive = True
GROUP By ProfileId, Profile.Name

To execute this query you’ll select the “Query Editor” tab of the Developer Console and paste this query into the window:

Console

Next click the “Execute” button and view the Results

Console Query Results

Now if you’d like to see the Users in each profile you can use another SOQL Query to create a List by utilizing a Sub SOQL Query to filter for a particular profile like so:


SELECT Id, Name, UserType, LastLoginDate, Profile.Name FROM User
WHERE ProfileId IN (SELECT Id FROM Profile WHERE Name LIKE '%Partner%')
AND IsActive = True
ORDER BY Profile.Name, LastLoginDate

Several things to note here.  First the WHERE clause makes use of a Sub SELECT which will return the Id column from the Profiles Object for all profiles that contain the word “Partner”.  Secondly we are using the ORDER BY clause to group users for each Profile Name logically together, and then by Last Login Date.

Console Query Results - Sub Query

To reuse this query to find users in other Profiles you change %Partner% to %[name]% (where [name] represents the Profile Name) in order to get the users of a profile or group of Profiles.  For example if you wanted all users in an Administration Profile Group change it to %Admin%

Console Query Results - Sub Query - 2

So now you’ve seen how to use the SOQL Query Editor, let’s talk about something more useful…dumping the results to a text file. In order to do that you’ll need to Open the Execute Anonymous window.

Execute Anonymous Menu

Paste this code into that window



List listOfUsers = [SELECT Id, Name, LastLoginDate, Profile.Name 
                    FROM User
                    WHERE ProfileId
                    IN (SELECT Id FROM Profile WHERE Name LIKE '%Partner%')
                    AND IsActive = True
                    ORDER BY LastLoginDate, Profile.Name];

String csv = 'Id,Name,ProfileName,LastLoginDate\n';
for(User u : listOfUsers)
{
   csv += u.id + ',' + u.name.escapeCsv() + ',' u.Profile.Name + ','
        + u.LastLoginDate + '\n';
}

ContentVersion file = new ContentVersion(title = 'PartnerUsersActive.csv',
                                        versionData = Blob.valueOf( csv ),
                                        review_date__c = date.today(),
                                        pathOnClient = '/PartnerUsersActive.csv');

insert file;
System.debug('Content URL : /' + file.Id);

This will generate an Excel CSV file.  Check the Open Log option on the bottom right of the Execute windows (next to the Execute button) so when the code completes it will Open the Log file

Execute Anonymous Code

You’ll see the URL in the Log file

Debug Log

Double click on the line in the Log file and it will open up a new window with the details

URL To File

Copy the “/” and Id following it.

Then go back to the browser which will have the https://naXX.salesforce.com in the URL.  Duplicate the current tab of Salesforce.  Once duplicated Past the “/” + Id after it in the URL (example: https://na28.salesforce.com/0681A000002sAxaQAE)

This will jump you right to the content file.  Click the Download Link

Content Download

Then after you download it, delete the file from SFDC.

If you need to create a CSV file with the Aggregate results (from a SELECT Coun() query) use the following code in the Execute Anonymous window and follow the same steps as above to open the Content and download the CSV


AggregateResult [] arResults = [SELECT ProfileId, Profile.Name pName, 
                                COUNT(Id) myCount
                                FROM User
                                WHERE IsActive = True
                                 GROUP BY ProfileId, Profile.Name];

String csv = 'ProfileId, Count\n';
for(AggregateResult ar: arResults)
{
    Integer iCnt = (Integer)ar.get('myCount');
    
    String line = (String) ar.get('ProfileId') + ',';
    
    line += (String) ar.get('pName') + ',';
    line += iCnt.format() + '\n';
    csv += line;
}

ContentVersion file = new ContentVersion(title = 'ProfileUserCount.csv',
                                    versionData = Blob.valueOf( csv ),
                                    review_date__c = date.today(),
                                    pathOnClient = '/ProfileUserCount.csv');

insert file;
System.debug('Content URL /' + file.Id);

 

This will produce a CSV with the total number of users in each profile. For any queries that utilize Aggregate functions you’ll want to use this template.

Understanding CRUD and FLS

The Force.com Platform allows for multiple ways to access data and to control that access on different levels. The different ways you can access the Objects are:

1. Object Level
2. Record Level
3. Field Level

In this article I’m going to talk about Object and Field Level security and the different techniques you can use to work with them when creating custom code.

So what exactly is CRUD? CRUD stands for Create-Read-Update-Delete access. CRUD settings are applied at the profile level in Salesforce.com. To see an example, just go to Setup->Profiles and select a profile (in my case I used the default “Standard User” and cloned it, then named it “Sample CRUD”), and select the Object Settings. This brings up a List of all the Objects in your organization and the permissions for each one. If I select “Accounts” I will be present with the Object Permissions and the Field Permissions.

CRUD - Figure 1.PNG

I can edit or change the permissions for this user by clicking on the “Edit” button.

CRUD - Figure 2.PNG

For my Custom Sample CRUD profile I can change the Object access by turning on or off the Read, Create, Edit, and Delete. On the same screen you’ll see Field Level Security (FLS) for every field in the Object. I can turn the access off for each individual field as well.

CRUD is automatically supported when the developer references an object (like the Account) and the objects fields directly in a VisualForce page. For example, if a user without FLS visibility to the Phone field of the Accounts object was to view the below page, phone numbers would be automatically removed from the table.


<apex:page standardcontroller="Account" recordsetVar="Acct" sidebar="false">

<apex:form >
   <apex:sectionHeader title="CRUD Example One" />
   <apex:pageBlock title="Accounts">
      <apex:pageBlockTable value="{!Acct}" var="item">
          <apex:column value="{!item.Name}"/>
          <apex:column value="{!item.BillingState}"/>
          <apex:column value="{!item.Phone}"/>
          <apex:column value="{!item.WebSite}"/>
      </apex:pageBlockTable>
   </apex:pageBlock>
</apex:form>
</apex:page>

The same thing will happen in VisualForce if you are rendering an Edit page for an object. For example if we have a custom VisualForce edit page for Account and the user does not have access to the Phone Number it will not appear in the edit form. Along that same line, apex:inputField tags will be rendered as read-only elements for fields that are set to read-only through FLS.

However using other input tags such as apex:inputText or apex:inputTextArea with SObject fields indicate to VisualForce that the fields should not be treated as SObject fields and prevent the platform to automatically enforcing FLS.

There are often cases where developers use VisualForce pages to display data derived from an Object field in an indirect or processed form. For instance, a page controller may use internal logic to determine the appropriate value to display. A simple example of this would be a page that displays a random Contact Name from a list of Contacts:


<apex:page controller="RandomContactController">
   <apex:outputText value="{!getRandomName}" />
</apex:page>

public with sharing class RandomContactController 
{
    public String getGetRandomName() 
    {
        // Check if the user has read access on the Contact.Name field
        if (!Schema.sObjectType.Contact.fields.Name.isAccessible())
        {
          return '';
        }
         
        Contact [] myList = [SELECT Name FROM Contact LIMIT 1000];
        
        // Pick a list entry at random
        Integer index = Math.mod(Math.abs(Crypto.getRandomInteger()),myList.size());
        Contact selected = myList.get(index);
        return selected.Name;
    }
}


This example indirectly displays the Name field of the Contact object by using a custom get method that returns a string (the name) value. Because VisualForce only sees the return value as a string and not as an SObject field, CRUD and FLS is not automatically enforced and it is necessary to call the isAccessible() method on the appropriate Describe Field Result in order to manually check the user’s CRUD and FLS access. The isAccessible() method automatically checks that the user has the corresponding CRUD access to the object type.

By the same approach we now have a mechanism for checking all CRUD access on any Objects fields. First we need to find the SObject in question (say it’s the Contact) and then we need to check for priveledges. So for handling the Create option we can create a Utility Class (CRUD_Checker) which can check the status of any field on any Object.



public Class CRUD_Checker
{
    public Boolean IsUpdateAllowed(String obj, String fld)
    {
	Boolean bResult = true;

	// First get the Object		
	SObjectType objType = Schema.getGlobalDescribe().get(obj);
	Map m = objType.getDescribe().fields.getMap();

        // Check if the user has create access on the each field
        if (!m.get(fld.toLowerCase()).getDescribe().isUpdateable()) 
        {
   	    system.debug('Insufficient access for field : ' + fld + ' in ' + obj);  
   	    bResult = false; 			
        }
		
	return(bResult);
    }

    public Boolean IsCreateAllowed(String obj, String fld)
    {
	Boolean bResult = true;

	// First get the Object		
	SObjectType objType = Schema.getGlobalDescribe().get(obj);
	Map m = objType.getDescribe().fields.getMap();

        // Check if the user has create access on the each field
        if (!m.get(fld.toLowerCase()).getDescribe().isCreateable()) 
        {
   	    system.debug('Insufficient access for field : ' + fld + ' in ' + obj);  
   	    bResult = false; 			
        }
		
	return(bResult);
    }
}


Finally we look at the case of Delete. You do not have applications that delete fields (generally speaking). That’s a method applied to the Object record. Which means the CRUD access is at the Object (Table) level as opposed to the field level. So for handling our Delete we check against the CRUD utility for delete permissions.


public Boolean IsDeleteAllowed(String obj)
{
    Boolean bResult = true;
    
    // First get the Object     
    SObjectType objType = Schema.getGlobalDescribe().get(obj);
    if(! objType.getDescribe().isDeletable())
    	bResult = false;
        
    return(bResult);	
}

NOTE : If you are using controller extensions and intend to delete the active record, another option is to call the standard controller’s delete() function instead of deleting the object within the controller extension. The standard controller will automatically check CRUD access before performing the operation.

So there you have it, the basics of handling CRUD in your Apex and Visual Force classes.

Turn Triggers On and Off with Custom Settings.

Recently I was doing some data manipulation work for a customer when I ran into some issues with Triggers firing when the data was being updated.  However, in production environments disabling triggers can be tricky. So what I settled on was using a Custom Setting.

Custom_settings

Custom Settings are a way for Salesforce.com admins and developers to create configurations that can be applied to various elements of the environment organization wide on a profile or user basis. Pretty cool and useful.

I’ve been using a variation of this for years with Custom Objects. Custom Settings however, are much quicker because they are stored in the Application Cache and they don’t count against SOQL query limits when you retrieve them. They can be used in not just APEX code but also formula fields, validation rules and of course within a Web Service.

There are two types of Custom Settings:
– List
– Hierarchy

Of the two the List custom settings are the easiest. For example, in the past before the State listbox type if you needed to maintain a list of States you used a custom object. Which in turn required a SOQL query to retrieve it. Now you can use the List type to store these types of lists and retrieve them pretty much anywhere you might need them.

Basically setting up the Custom Setting is the same as creating a Custom Object. One big difference is that you will see a button for “Manage” which will allow you to enter the data in a Standard page layout. Once the settings are entered you can query the Custom Setting like a standard Custom Object:

Custom Settings have instance methods you can invoke for easy access. If you wanted a single value (like a State name):

State__c state = State__c.getInstance('Texas');

if you want the list of all values back into a map:

Map mapOfStates = States__c.getAll();

To get the values back as a List :

List listOfStates = States__c.getAll().values();

Hierarchy settings work differently than lists, and for the sake of this article I’d say that I generally find them most useful for settings I want to apply to a profile or user. The interface for Hierarchy settings already includes the navigation that allows it to drill down and return the lowest value in the Hierarchy (based on the current system user).

So back to the trigger setting. Using the List Custom Setting we create one named “TriggerSupport”. With fields for the Name of the Custom Object (text) and TriggerOff (checkbx).

So let’s say we want to turn off a trigger on the Lead object. Go to the Manage Custom Settings and create an entry for Lead and set the Trigger Off setting to True.

Then in your trigger code:

TriggerSupport__c support = TriggerSupport__c.getAll();
Boolean isOff = support.get('Lead').TriggerOff__c;

if(! isOff)
{
... trigger code ...
}

As you study Custom Settings I’m sure you will find many more uses for it within your organization.

Large Data Sets and Aggregate Functions

Salesforce.com has a limit of 50,000 records which can be queried at one time.  Anytime you have queries with aggregate functions which need to access more than 50,000 records you’ll get the following error:

“System.LimitException: Too many query rows: 50001″

For example let’s say you want to count the number of contacts in your organization:

System.debug('total: ' + [SELECT count() FROM Contact]);

We are just trying to count the number of records, and in a traditional SQL engine such a call would not need to transverse every record in the Contacts table to obtain this count. However, in Salesforce it does…why? Well as one example something like checking sharing settings on records so that different profile users get different number of records.

There are several techniques you can use to get around this problem. My choice however, is to use the REST API. There are other solutions available including using the ReadOnly attribute on a Visual Force page or Creating a Batchable Apex Class, but I find that the REST API solution is the easiest and offers the most flexibility.

The REST API allows for the execution of a query by setting the query string parameter “q” equal to the requested query. Note that you must first MIME encode your SOQL query. The simple example below:


HttpRequest req = new HttpRequest();

req.setEndpoint('https://'+URL.getSalesforceBaseUrl().getHost()+'/services/data/v30.0/query/?q=SELECT+Id+from+Contact');

req.setMethod('GET');

string auth = 'Bearer '+ userInfo.getsessionId();
req.setHeader('Authorization', auth);

Http http = new Http();

try
{
   HttpResponse res = http.send(req);

   string response = res.getBody();
   string total = response.substring(response.indexOf('totalSize":') + 11, response.indexOf(','));
   system.debug('Total: '+ total);
}
catch( Exception e )
{
   system.debug('Error ' + e.getMessage());
}

You can get more elaborate and create a Class that contains methods for each aggregate function, however, the above code will get your around the 50,000 record limit.

Lastly once you have the code written you’ll need to add an entry to your Organizations remote sites. For example say your production org is on na2 you’ll need to add na2.salesforce.com as a remote site.

Using Dynamic SOQL

We’ve all had situations where you needed to dynamically build a query.  How do we do this in Apex? Queries can be constructed in several ways, but for this article we’ll focus just on the method of building a string and executing it with the Database engine.

A common method of executing a query:

// Retrieve a list of Contacts
List listOfContacts = [SELECT Id, Name FROM Contacts LIMIT 100];

OR you can use the Database Engine:

// Retrieve a list of Contacts
List listOfContacts = Database.Query('SELECT Id, Name FROM Contacts LIMIT 100');

So how do we create a query that always returns fields we need even when they are added later? Apex can access the Schema object to get the list of fields in any Object. To retrieve the fields in the Contact Object for example:

// Get a map of all the fields
Map<String, Schema.SObjectField> fieldList = Schema.getGlobalDescribe().get(objectName).getDescribe().fields.getMap(); 

So now we have the list of fields. All we need to do is construct a Query for the fields in any Object (similar to the SELECT * method of SQL).

String query = 'SELECT ';

Map<String, Schema.SObjectField> fieldList = Schema.getGlobalDescribe().get(objectName).getDescribe().fields.getMap(); 

for(String fld : fieldList.keySet())
{
    query += fld + ',';
}

// Strip the ending comma
query = query.subString(0, query.Length()-1);

// Add the FROM portion
query += ' FROM Contact LIMIT 100';

So if we construct a QueryBuilder class we can make any Object, Limit or even a WHERE filter parameters or settings within the class.

public class QueryBuilder
{
   public String ObjectName {set;get;}
   public Integer LimitSize {set;get;}
   
   public String BuildQuery()
   {
       String query = 'SELECT ';

       Map<String, Schema.SObjectField> fieldList =    Schema.getGlobalDescribe().get(objectName).getDescribe().fields.getMap(); 

       for(String fld : fieldList.keySet())
       {
          query += fld + ',';
       }

       // Strip the ending comma
       query = query.subString(0, query.Length()-1);

       // Append Object
       query += ' FROM ' + ObjectName;
       query += ' LIMIT ' + String.valueOf(LimitSize);

       return(query);
   }
}

Just like that you have a query engine that will always return the full field list of any object you request. You can also modify the QueryBuilder to accept lists of fields, filters such as “WHERE” and you can even create a method using queryMore to return blocks of records beyond the 1000 row limit.