0

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:

SOQL in formula builder


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;

1 Answer 1

0

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).

2
  • Thanks for the quick reply @Derek! I'm learning SF and Apex, so I'll definitely look into creating a trigger to handle this updating field.
    – Ian G
    May 13, 2022 at 15:15
  • 1
    @IanG If you're new to Apex, then it's recommended you install and use the DLRS tool. There are several pitfalls that inexperienced people run into when they try to "roll their own" rollup trigger, and it's best to have a decent grasp of Apex and Triggers before you try to tackle this yourself (and have a reason why the DLRS tool isn't the right fit for your task/org).
    – Derek F
    May 13, 2022 at 15:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .