I have a checkbox field called Has_Key__c that checks whether an employee has a key to the building or not.

I want to make a sum of all checked Has_Key__c and compare it with a total number of available keys. There is a field that holds this total number, called Total_Keys__c

I want to output Total_Keys__c - Sum(Has_Key__c) somewhere, preferably in a field as well.

What's the easiest way to do so? Formula? Apex? Rollup Summary?

  • 1
    Is there any relationship (schema-wise) between Total_Keys__c and Has_Key__c? – Mike Chale Jul 25 '13 at 12:19
  • Martin, can you provide more of a description? Does the Object with the Has_Key__c field have a relationship to a Parent with Total_Keys__c? Is it a master detail relationship? If so, you could utilize a combination of rollup summary fields and formulas to get your desired output. Furthermore, is this something you want displayed on the detail page of the parent, or could you simply run a summarized report on the Total_Keys__c object? – James Loghry Jul 25 '13 at 13:39
  • 1
    Rollup summary would be ideal, but your data has to be configured correctly. I would envision that the design would be as follows: Building__c is a building, Key__c is a key, and Building_Key__c is a many-to-many object (master-detail to Building__c and Key__c, and a lookup to User, and Has_Key__c field). Standard Rollups would work with this model, and a formula to express the remaining keys. – sfdcfox Jul 25 '13 at 13:41
  • There are no relationships yet, but the feedback provides me with good information on how to implement that. – mcbetz Jul 26 '13 at 8:50
  • @sfdcfox If you could transform your comment to an answer, I would choose it as best answer. ;-) – mcbetz Jul 31 '13 at 7:49

You can do this with standard functionality using a roll up summary field and a formula field. This might be accomplished with three objects: Building__c, Key__c, and Building_Key__c. The Building__c would store data about each building, and Key__c would represent a key (e.g. serial number, etc). Building_Key__c would have two master-detail relationships (called a "join object"), one to Building__c and one to Key__c, with a lookup field to a User. From here, you can build a roll up summary that counts the number of keys that are assigned to each building, and, with a field called Total_Keys__c on Building__c, you can then calculate how many keys are left.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.