Tracking number of contact attempts based on Tasks under Contact

Question

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;

Answer 1

No, it is not possible to have a formula field execute a query.
Further, what you're trying to do (count the number of records related to a parent) is also not possible with a formula field alone.

The reason you're not getting a syntax error in your formula is because the query is being treated as a string (I mean, you are enclosing it in quotes, making it a string literal). VALUE() turns a string of a number into an actual number, so I guess that a string with no numbers (or something that's not a integer or a decimal) just returns a blank value.

To do this, you'll need a trigger (or use the Declarative Rollup Summary Tool to build the trigger and the test for you).