[SalesForce] Creating a custom field from a SOQL query

We have a custom Object and our Opportunity objects have a Lookup field to the Object. However, when we go to add a Roll-up field to Object that shows the total Opportunities attached to the Object, Opportunity is not an option to use for a Roll-up field.

I am thinking that I will have to write a custom Apex class that gets the information from a Soql query. Is it possible to create a custom field that is based on the Apex class to display the output of a query like:

SELECT COUNT(Id), Object_Name__c
FROM Opportunity
WHERE Object_Name__c = ''

Or what would be the proper way to get this to show up as a field in our custom Object?

Best Answer

Rollup's are possible only in a master detail scenario, in your case you say the custom object is a lookup from Oppty.Oppty can never be the child

http://www.salesforce.com/us/developer/docs/api/Content/relationships_among_objects.htm

You can define master-detail relationships between custom objects or between a custom object and a standard object. However, the standard object cannot be on the detail side of a relationship with a custom object. In addition, you cannot create a master-detail relationship in which the User or Lead objects are the master.

check this on how to make rollup'sish feature possible with some APEX code:

http://blog.jeffdouglas.com/2009/07/30/roll-up-summary-fields-with-lookup-relationships-part-1/

(OR)

Create a button on Oppty and use ajax toolkit : 

{!REQUIRESCRIPT("/soap/ajax/22.0/connection.js")}
var cust_related_to_oppty = sforce.connection.query("Select id,Number_field__c from custom_object__c WHERE Opportunity__c = '{!Opportunity.Id}'");
var sum_of_numbers_in_custom = 0;
var total_custom_objects = 0;
var records = cust_related_to_oppty.getArray("records");
 for (var i=0; i< records.length; i++) {
    var record = records[i];
//assume you want to calculate sum of a number field in custom object,gives you sum.
    sum_of_numbers_in_custom = +sum_of_numbers_in_custom + +record.Number_field__c;
//gives you count of childern  
 total_custom_objects = records.length;
}
alert(sum_of_numbers_in_custom);
alert(total_custom_objects);

Go here and build your sforce.connection.update method that will update the oppty once you create a field that will be a holder for the sum of / count of alert that you have on the screen now.

http://www.salesforce.com/us/developer/docs/ajax/Content/sforce_api_ajax_more_samples.htm

enter image description here

Related Topic