I need to update a custom field called Contact Attempts on the Contact sobject with a numerical value equal to the number of all Tasks associated with the Contact. Any time a Task is added or deleted, this value should update accordingly.
I'm attempting to use a SOQL query in the Advanced Formula builder for Contact Attempts field. The query works without a problem in Execute Anonymous if provided a Contact Id. And even though there are no syntax errors, the field does not calculate the number of Tasks. On the Contact record, the Contact Attempts field is always blank!
If I attempt to "Insert Field", I am unable to find any field or object that is related to the Contact's Tasks. Tasks are a separate sobject that are related to the Contact with the "WhoId" lookup field.
Is it possible to use a SOQL query in the formula builder?
VALUE("SELECT COUNT() FROM Task WHERE WhoId = Id")
If not, how else could I capture this data using the formula builder?
(I imagine I could solve this same problem in Apex, but I prefer to handle the logic here)
See the picture below:
EDIT:
Here's the Apex code that makes it work in a Task trigger:
Boolean afterInsert = Trigger.IsAfter && Trigger.IsInsert;
Boolean afterDelete = Trigger.IsAfter && Trigger.IsDelete;
Map<Id, Task> tasks = new Map<Id, Task>();
Map<Id, Contact> contacts = new Map<Id, Contact>();
List<Contact> contactsToUpdate = new List<Contact>();
// Grab all new tasks
if (afterInsert) {
for (Task t : Trigger.new) {
tasks.put(t.WhoId, t);
}
}
// Grab all deleted tasks
if (afterDelete) {
for (Task t : Trigger.old) {
tasks.put(t.WhoId, t);
}
}
// Grab all contacts that need the Contact Attempt Counter field updated
contacts = new Map<Id, Contact>([
SELECT Id, Contact_Attempt_Counter__c
FROM Contact
WHERE Id IN :tasks.keySet()
]);
// Update Contact Attempt Counter by querying the total number of Tasks on each Contact record
for (Contact c : [SELECT Id, Contact_Attempt_Counter__c, (SELECT Id FROM Tasks) FROM Contact WHERE Id IN :contacts.keySet()]) {
c.Contact_Attempt_Counter__c = (Integer)c.Tasks.size();
contactsToUpdate.add(c);
}
update contactsToUpdate;