+ Start a Discussion
Mark MazzitelloMark Mazzitello 

Custom Field formula based on either a SQL query or a 1:many relationship

This is way too complex and convoluted to explain all the details, but what my users are asking for is an Account custom field that will display a property of an account that can only be determined by examining a 1:many relationship with another object and those other objects' properties, or by a SQL query.

Here's a simplified version: Say an Account has a 1 to zero-many relationship with a custom object called Muppet__c which has a Name attribute.  
My users want a calculated/read-only Account field that is populated by:
 - if the Account is associated with exactly one Muppet__c where the Name = "Kermit" then the custom field value will be "Frog only"
 - if the Account is associated with multiple Muppet__c and one of them has Name = "Kermit" then the custom field will be "Frog plus others"
 - if the Account is associated with one or more Muppet__c and none of them have Name = "Kermit" then the custom field value will be "others only"
 - if the Account is not associated with any Muppet__c then the custom field value will be "none"
And the custom field must update automatically to reflect changes made in the Account relationship(s) with the Muppet__c object.  

So - here are my questions on the best way to do this:
 - Is it possible to have a custom field (either a pick list or just text) that is read-only in the UI and is populated and updated by a database query?
 - Do I need to create Triggers on every possible method there could be for changing Account-Muppet__c associations to update this custom field?
 - Is there some better way to accomplish this?  

Any advice or recomendations, examples, nudges in the right direction, etc. are greatly appreciated by this under pressure Salesforce newbie.
Mark MazzitelloMark Mazzitello
I have a feeling I can do this using a "cross-object formula" but just not sure of the syntax of the formula.
You can't do this with formula fields, as they can't span multiple related records.  You could do this with a combination of a master detail relationship to the child records and a couple of roll up summary fields - one to count the total number of 'muppet' child records, and the other to count the total number of 'muppet' child records named 'kermit'.  Then you cna create formula fields that refer to the roll up summaries to generate the appropriate values.