AggregateResult in Salesforce?

AggregateResult in Salesforce is used to find Sum, Min, Max and Avg. It is similar to Aggregate function in SQL.

Using Aggregate Result, we cannot fetch data. It is mainly used to find Sum, Min, Max and Avg.

Sample Code:

Visualforce page:

<apex:page controller="Sample" sidebar="false" action="{!show}">
<apex:pagemessages />
<apex:form >
    <apex:pageBlock >
        <apex:pageblockTable value="{!SummaryList}" var="r">          
            <apex:column headerValue="Account Name" value="{!r.AcctName}"/>
            <apex:column headerValue="Number of Contacts" value="{!r.Total}"/>
        </apex:pageblockTable>
    </apex:pageBlock>
</apex:form>
</apex:page>

Apex Controller:

public with sharing class Sample {
    public List<AggregateResult> Result {get;set;}  
    public List<Summary> SummaryList {get;set;}
    public List<Account> AcctList;
    public Map<Id, Account> IdAccount;
    List<Id> Ids;
   
    public void show() {
        SummaryList = new List<Summary>();
        Result = new List<AggregateResult>();
        Ids = new List<Id>();
        AcctList = new List<Account>();
        IdAccount = new Map<Id, Account>();
       
        Result = [SELECT Count(Id) Total , AccountId FROM Contact GROUP BY AccountId];              
       
        for(AggregateResult a : Result) {  
            Ids.add((Id)a.get('AccountId'));
        }
       
        AcctList = [SELECT Name FROM Account WHERE Id IN : Ids];
       
        System.debug('Account List' + AcctList);
       
        for(Account a : AcctList) {
            IdAccount.put(a.Id, a);          
        }
       
        System.debug('Ids and Accounts are ' + IdAccount);
               
        for(AggregateResult a : Result) {
            Account TempAcct = new Account();          
            TempAcct = IdAccount.get((Id)(a.get('AccountId')));
            system.debug('Account Name is ' + TempAcct.Name);
            SummaryList.add(new Summary(a, TempAcct.Name));              
        }  
    }
   
    public class Summary {
        public Integer Total {get;set;}
        public String AcctName {get;set;}
       
        public Summary(AggregateResult a, String AccountName) {
            Total =  (Integer)a.get('Total');
            AcctName = AccountName;
        }
    }
}

 output:

Javascript to select all checkboxes in visualforce page?

I needed to develop a custom visualForce page which would hold a page block table which would contain a checkbox and details of a custom object based on which user can select and click on buttons to perform some operations.

I wrapped the custom object record and check box in a wrapper class. I needed a simple functionality when the header checkbox is selected or deselected accordingly all the checkbox will get selected or deselected accordingly. So i looked into community and first result community gave is this(controller way). It involved calls to back end and as a result functionality is little slow.

So I went for a Javascript solution which would provide a faster,elegant and much simpler way.Here is the VF code which was used in the page above.
<apex:pageBlockTable value="{!wrapreportsobj }" var="w" id="Selected_PBS">
<apex:column ><apex:facet name="header">
<apex:inputCheckbox onclick="checkAll(this,'checkedone')"/>
</apex:facet>
<apex:inputCheckbox value="{!w.selected}" id="checkedone"/></apex:column>
<apex:column ><apex:facet name="header">Id</apex:facet><apex:outputfield value="{!w.rep.id}"/></apex:column>
<apex:column ><apex:facet name="header">Name</apex:facet><apex:outputfield value="{!w.rep.name}"/></apex:column>
</apex:pageBlockTable>

Note that I have set the id of the checkbox as checkedone and while clicking on header checkbox it calls a javascript function  checkAll. Giving an id will segregate all the checkboxes in the table. Here is the javascript which would handle the selecting and deselecting all of the checkboxes which has id checkedone in it.

<script type="text/javascript">
    function checkAll(cb,cbid)
        {
            var inputElem = document.getElementsByTagName("input");                    
            for(var i=0; i<inputElem.length; i++)
            {            
                 if(inputElem[i].id.indexOf(cbid)!=-1){                                      
                    inputElem[i].checked = cb.checked;
                }
            }
        }
</script>

Loading products , pricebooks and pricebook entry?

There is always a need to load Products into salesforce. Its a 3 step process to load products and associate them with pricebook . There is a standard pricebook which salesforce provides by default which can be deactivated. There is also an option to create custom pricebook as many as you want in accordance with the business.

Fields in red are required and fields in blue are read only



Pricebookentry is the junction object between product2 and pricebook2 object( For strange reasons they have suffix 2 for standard object) . It has lookup for product2 and pricebook2 field through product2id and pricebook2id field. Name and productcode is copied over from product2 based on product2id field.

Essentially for loading Products you need to load product2 table first. Create a CSV file. Name is the only required field. Optionally have columns for other fields indicated in the pic.set Isactive to true. use dataloader to insert data. Get the id of the products from success file which got created.

Pricebook2 will also have only Name as required field. Isstandard indicates whether its a standard or custom and its readonly. Also Isactive should be set to true if you are planning to use it. Get the Pricebook2id from success file. Alternatively export pricebook2 table to get id of pricebook already created.

Final step is to create pricebookentry. Pricebook entry needs to be created for each Pricebook.
populate ids of product2 in product2id column and pricebook2 in pricebook2id column. Unitprice is a required field where the price for this pricebook is set. Pricebook entry for a custom pricebook cannot be made if that product is not there in the standard pricebook.

Unitprice can be made read only if usestandardprice field is set to true. This can be made only for custom pricebook and when the product is added to standard pricebook. So the unitprice set in standard pricebook will be carried over to the custom pricebook. Make Isactive true whenever u are planning to use it. Use dataloader to insert the pricebookentry. you have to repeat the above 2 steps for each custom pricebook.If you have multiple currencies enabled there should be as many pricebook entry records for a single product in a pricebook for each currency.

Salesforce.com - Reference to tools and resources - All in one place

Everytime i need to install a administrative tool or if i need tutorials or references, i found that i spend a considerable amount of time in locating them... As a result, this article came to my mind..

Here, you will find links to some of the administrative tools in Salesforce.com as well as less-known good resources..

This list would be updated as and when i find new resources.. So, make sure you bookmark this page :-)

Salesforce objects, fields and their usage - Complete description

This is the official WebServices Developer's guide but has enough information that you would want to know about Salesforce objects, how to use them etc through the API. Click here

Excel Connector for Salesforce.com

Sforce Connector - Click here

Office toolkit 3.0 - Click here

Please note that Office toolkit 4.0 is installed automatically when you install the Outlook connector for Salesforce....

Apex Explorer

Installation guide and notes - Click here

Installation file - Click here to download

Apex Data Loader

Installation guide and notes - Click here

Running the Apex Data Loader from the command prompt: Click here
Running the Apex Data Loader from the command prompt (Simplified) : Click here

SFDC Web to anything -

You might have heard about web to Case, web to anything lets you to create records in any object through a web form..

You can find the code, screenshots and description here - Click here

iGoogle like Salesforce Home Page (iSalesforce)

The project source and a demonstration are hosted here. Click here

Tutorials and guides -

The official salesforce.com guide's and tutorial's page - Click here

Discussion forums -

The official salesforce.com community forums - Click here

insufficient_access_on_cross_reference_entity APEX / Salesforce

Even though i understood what this means and have dealt with it earlier, this time this error message sucked my brain for one week. Finally, i got it sorted out and i hope this post helps someone understand and solve as well.

When does this error happen?
This error normally happens when a DML operation is performed. Meaning that, whenever an Insert or Update is performed. You won't see this error when you save your APEX class or TRIGGER, because this error happens at RUNTIME.

What does this error mean?
This error means that you are trying to do an operation which is not supposed to be done, or the operation you are about to perform is not permitted according to the Sharing and Security settings defined. This error message does NOT always mean that you lack access to do the operation, even though it might be the case sometimes. So, even if you are an ADMINISTRATOR you may get this message.

Possible Causes:
Let's take some scenario's and analyze.

 Scenario 1:  Creating a new record (Account/Contact/...) and setting the Owner. Applies to updating records as well.

So, in your code you create some records. By Default the creator of the record is the Owner. You want to change this and you modify the OwnerId column of the newly created records. You make "User X" as the Owner. Now when you run the code, you get the Error below:
System.DmlException: Insert failed. First exception on row 2; first error: INSUFFICIENT_ACCESS_ON_CROSS_REFERENCE_ENTITY, insufficient access rights on cross-reference id: []

Things to check:
  *Check that the running user (in this case you) has access to the object being operated. Check that he has CREATE privileges on the object. This is optional, but is always better to start from here.
  *Check that the Owner ie User X has CREATE permission on the object. Check that his profile has the CREATE permission on the particular object. He might not be having it, grant him permission and the issue is resolved.

 Scenario 2:
Creating or Updating an Opportunity (just for an example,  might be any object). Setting the related Account of the Opportunity.

So, let's say that you create 5 Opportunities and you set the Owner to "User X". You set the Account to "Account X". When your code tries to insert these 5 opportunities, it fails and you get the same error message.
System.DmlException: Insert failed. First exception on row 2; first error: INSUFFICIENT_ACCESS_ON_CROSS_REFERENCE_ENTITY, insufficient access rights on cross-reference id: []
Reason:
This is because "User X" does not have access to "Account X". When you try to create an Opportunity for "Account X" that he does not have access to the code fails. Either grant access to "User X" for "Account X" manually or through code and then do the Insert.

Scenario 3:
The Sharing Object.

This is a bit complex to get at. atleast for me. You might be aware that every object has its own Share object. So, Account has AccountShare and Customobj__c has Customobj__Share
When you insert or update records in this object you might receive the same error message. There are a number of reasons for this to happen.

    *If you are trying to share "Record X" with "User Y" and you yourself do not have access to "Record x", this error happens.
    *If you are trying to share "Record X" with "User Y" and "User Y" does not have access to the object (the profile level permission, create read edit delete), this error happens.
    *If you are trying to share "Record X" with "User Y" and "User Y" already has access to "Record X" this error happens.

Read and Insert records from a CSV file - Using Visualforce?

The Apex Data Loader is always there when you want to insert records into Salesforce from a CSV file. But, just in case if you don't want your users to install the Apex Data Loader and learn how to use it, then here is a simple example which tells you how to do the same using Visualforce.

Click here to view the demo.


Step 1:

Download the template from the DEMO URL above. Save the file in your desktop. Upload the file into Static Resources with the name "AccountUploadTemplate".

Step 2:

Create an Apex Class named "FileUploader". Paste the code below and save it.



public class FileUploader
{
    public string nameFile{get;set;}
    public Blob contentFile{get;set;}
    String[] filelines = new String[]{};
    List<Account> accstoupload;
 
    public Pagereference ReadFile()
    {
        nameFile=contentFile.toString();
        filelines = nameFile.split('\n');
        accstoupload = new List<Account>();
        for (Integer i=1;i<filelines.size();i++)
        {
            String[] inputvalues = new String[]{};
            inputvalues = filelines[i].split(',');
         
            Account a = new Account();
            a.Name = inputvalues[0];
            a.ShippingStreet = inputvalues[1];    
            a.ShippingCity = inputvalues[2];
            a.ShippingState = inputvalues[3];
            a.ShippingPostalCode = inputvalues[4];
            a.ShippingCountry = inputvalues[5];

            accstoupload.add(a);
        }
        try{
        insert accstoupload;
        }
        catch (Exception e)
        {
            ApexPages.Message errormsg = new ApexPages.Message(ApexPages.severity.ERROR,'An error has occured. Please check the template or try again later');
            ApexPages.addMessage(errormsg);
        }  
        return null;
    }
 
    public List<Account> getuploadedAccounts()
    {
        if (accstoupload!= NULL)
            if (accstoupload.size() > 0)
                return accstoupload;
            else
                return null;                  
        else
            return null;
    }          
}

Step 3:

Create a Visualforce Page named "UploadAccounts". Paste the code below and save it.



<apex:page sidebar="false" controller="FileUploader">
   <apex:form >
      <apex:sectionHeader title="Upload data from CSV file"/>
      <apex:pagemessages />
      <apex:pageBlock >
             <center>
              <apex:inputFile value="{!contentFile}" filename="{!nameFile}" /> <apex:commandButton action="{!ReadFile}" value="Upload File" id="theButton" style="width:70px;"/>
              <br/> <br/> <font color="red"> <b>Note: Please use the standard template to upload Accounts. <a href="{!URLFOR($Resource.AccountUploadTemplate)}" target="_blank"> Click here </a> to download the template. </b> </font>
             </center>
   
   
      <apex:pageblocktable value="{!uploadedAccounts}" var="acc" rendered="{!NOT(ISNULL(uploadedAccounts))}">
          <apex:column headerValue="Account Name">
              <apex:outputField value="{!acc.Name}"/>
          </apex:column>
          <apex:column headerValue="Shipping Street">
              <apex:outputField value="{!acc.ShippingStreet}"/>
          </apex:column>
          <apex:column headerValue="Shipping City">
              <apex:outputField value="{!acc.ShippingCity}"/>
          </apex:column>
          <apex:column headerValue="Shipping State">
              <apex:outputField value="{!acc.ShippingState}"/>
          </apex:column>
          <apex:column headerValue="Shipping Postal Code">
              <apex:outputField value="{!acc.ShippingPostalCode}"/>
          </apex:column>
          <apex:column headerValue="Shipping Country">
              <apex:outputField value="{!acc.ShippingCountry}"/>
          </apex:column>
      </apex:pageblocktable>
   
      </apex:pageBlock>    
   </apex:form>
</apex:page>



Screenshot:


Some pointers:
• You can use only the standard template. Because, that's how we have done the mapping to the columns in excel and the fields in Salesforce. You can modify the mapping and use your own template.
• Allowing the user to choose his own mapping is possible i believe, but may be a bit complex.
• Also, we use a CSV file. So, you may have to use additional criteria if your data values itself have a comma in them (For ex: Billing Street = 'Mumbai, India ') . This would cause problems because Mumbai and India would be considered as seperate values because of the comma in between them.

Getter and setter methods - What are they?

Well, once you start using a controller or an extension you will get used to these words...

So, it is good that we understand what these methods really do..

Getter and setter methods are used to pass data from your visualforce page to your controller and vice versa..

Let's take a very simple scenario... Let's assume you want to display a textbox in your visualforce page.. When the user enters some value in this textbox and clicks on a button you want the value entered by the user in your Apex class (ie. basically your controller or extension)

So go ahead and create a simple visualforce page.. the code for this would be
<apex:page controller="simplegetset">
  <apex:form>
    <apex:outputlabel value="Enter your name here"/>
       <apex:inputtext value="{!userinput}"/>        
  </apex:form>  
</apex:page>


The Apex code for this page would be...

public class simplegetset
{
    public String userinput{get; set;}
}


Now, the variable "userinput" in your Apex class will store the value entered by the user....

Let's analyze the methods now...

Get

The "get" method is used to pass data from your Apex code to your Visualforce page.. In our example we are not passing any value.. hence, when your page loads initially the textbox will have a empty value...

So, lets modify our code and pass a default value to the textbox.. Change the Apex code as follows..

public class simplegetset
{
    public String userinput;
    public String getuserinput(){return 'John';}
 
    public void setuserinput(String userinput)
    {
        this.userinput = userinput;
    }  
}


You can now see that your page loads with a value 'John'...

Set

The "set" method is used to pass values from your visualforce page to the controller... In our example the variable "userinput" will be storing the value entered in the textbox..

Now modify your VF page as below..

<apex:page controller="simplegetset">
  <apex:form>
    <apex:outputlabel value="Enter your name here"/>
       <apex:inputtext value="{!userinput}">
           <apex:actionsupport event="onclick" rerender="display" />
       </apex:inputtext>                  
    <apex:outputpanel id="display">
        <apex:outputtext value="The name entered is {!userinput}"/>
    </apex:outputpanel>                  
  </apex:form>  
</apex:page>


The Apex code would be...

public class simplegetset
{
    public String userinput{get; set;}
}


In this example what happens is.. the variable "userinput" stores the value entered in visualforce page and passes it to the Apex code.. hence you are able to see the entered value in the visualforce page..

I guess you might understand what i am saying.. to make things simple now use the same visualforce page.. but modify the Apex code as below..

public class simplegetset
{
    public String userinput;
    public String getuserinput(){return 'John';}
 
    public void setuserinput(String userinput)
    {
        this.userinput = userinput;
    }  
}


Now, whatever value you enter the page always displays "The name entered is John".... This is because your get method always returns 'John'... but still your set method will store the value you entered in the variable "userinput"....

Salesforce delete record: 'Insufficient privileges' error?

In some organisation, user allow to delete records, but sometimes user get error message 'Insufficient privileges' although Delete button exist. What's happening? Let us analyze:

1. User able to access the record
There are few scenario:

• Organization-Wide Defaults sharing setting on that object is Public Read Only or Public Read/Write; or
• Sharing rule added based on record owner or criteria for Public Groups or Roles; or
• User is the owner of the records; or
• User in higher role hierarchy of record owner; or
• User profile have "View all Data" or "Modify all Data" permission

2. User see Delete button
This is happened because:

• Delete button is added in record page layout; and
• User profile have permission to delete that object

3. Error 'Insufficient privileges'
The ability to delete records in Salesforce is controlled by the role hierarchy. Setting a sharing model to "Public Read/Write" alone does not give users the right to delete others records. There are 2 scenarios in which a user can delete a record:

• The user attempting to delete the record is a System Administrator.
• The user attempting to delete the record is the owner, or higher on the role hierarchy than the record owner.
Any other user that attempts to delete a record will receive an "Insufficient Privilege" error message.

Those below you on the role hierarchy may have read/write privileges according to the sharing model rules, however, they may not delete information from those individuals above them in that hierarchy.

How to undelete records Using Apex?

Salesforce keep record you delete to Recycle Bin for 15 days with maximum record of 25 times the Megabytes (MBs) in your storage. For example, if your organization has 1 GB of storage then your limit is 25 times 1000 MB or 25,000 records. If your organization reaches its Recycle Bin limit, Salesforce automatically removes the oldest records if they have been in the Recycle Bin for at least two hours.

You can manually undelete records by click Recycle Bin icon in bottom left menu. Undelete account will include contact, case, opportunity tagged to that Account. But, if you have many records or you have specific record Id or you want to undelete records with some conditions, manually delete is not an option, use Apex code for it.

You not really need to be good developer to utilize Apex code. Here we go:

1. Open Developer Console
From your name in top right, look for Developer Console

2.From Developer Console
Go to Debug menu and select Open Execute Anonymous Window

3. Enter SOQL 
Example:
Account[] a = [SELECT Id FROM Account WHERE name = 'singtel' ALL ROWS];
undelete a;
Important to add ALL ROWS, otherwise deleted records will be not return on query.

4. Monitor Apex Debug Log
Go to Monitor - Logs - Debug Logs (in Salesforce menu, not Developer console)
Add user to monitor, default it will capture 20 log request for the users

5. Execute 
Click Execute button in Enter Apex Code window

6. Back to Debug Log
Select latest debug log and click View to monitor

How to fetch Contact details from Opportunity using SOQL in Salesforce?

Query:

SELECT Id, Opportunity.Name, Opportunity.OrderNumber__c, Opportunity.Approval_Status__c, Contact.Email, Contact.Name FROM OpportunityContactRole

Output:

REQUIRED CHECKBOX VALIDATION?

So we now how to enable a required field in a record, but how about making a requried checkbox in your Salesforce form? well it's simple and here is how to do it:

Field 1: Lookup field called : "Account" Field 2: Date field called "Date" Field 3: Date field called "Expected Completion Date" Field4: Checkbox called "Review"" AND(Review__c = FALSE, OR( NOT(ISBLANK(Account__c)), NOT(ISBLANK(Date__c)), NOT(ISBLANK(Expected_Completion__Date__c))))

and now, If the user enters data in to any one, any two, or all three fields, but does NOT check the box, an error will be thrown.

"SYSTEM.QUERYEXCEPTION: LIST HAS NO ROWS FOR ASSIGNMENT TO SOBJECT"

So I was quering Account a = [Select name from account where id=: strld];

and an exception was thrown: System.QueryExceptoin: List has no rows for assignment to SObject.

Here is the mistake I made and how to fix it:

List<Account>lst = [Select name from account where id=: strld];

WORKBENCH FOR SALESFORCE ?

What is WorkBench ?

Here is the another interesting GUI based tool for Salesforce though announced in Dreamforce before,  this is a free tool from Salesforce.com and not just limited for developers. Other than developer related offering, this tool leverage bunch of different features that you can access through web-based UI to work with Salesforce

Workbench pleasant features 

• Query the results, download bulk XML and .csv files 
• Pretty structured information is provided alike Data Loader
• Offer more data load options than data loader
• Offer tons of options for developers
• Handy and web based tool, with easy access to both Production and Sandbox 
• Provided with Chrome extension and Firefox Add-on to ease the life 

Some Limitations

• Can't save the data load mapping, which is turn-off
• Can't schedule, no scheduled options

Workbench is insanely powerful tool for admins and developer to interact with Salesforce.com. This purely web-based tool that uses oauth to connect your salesforce org. The WorkBench use force.com API and supports bulk api calls, rest call, streaming data and surprisingly these all do not end here. The best feature I like about this application is SOQL query calls from to Salesforce cloud right away with pretty interactive interface

You can query, manipulate data and the metadata in Salesforce org, this also leverage advance features like debug logs and backward compatibility testing and single sign-on integration

Query Results and Pagination 

Query results are shown there itself while in the old versions, the users were required to jump from page to page to retrieve records,  but now with pagination support, the row number on SOQL query results continue from page to page and make data view pretty easy

Browser Support 

Workbench comes with chrome extension that ease the access, and in no time work bench is ready to use and can be pulled up right inside your favorite chrome browser

Download the chrome extension of workbench from 

here 

How to get started with Workbench ?

Open workbench here  https://workbench.developerforce.com/login.php

Login with your org credentials and select objects

Pull up a data using Query