Help with report on Tasks and Accounts

[MatLudlam]

Hi, in my general usage of the system I have tasks attached to Opportunities and Opportunities attached to Accounts. I believe this is the standard configuration.

When I look at Accounts, by default the system shows me all of the tasks that are hung off its Opportunities.

I now want to write a report that lists all Accounts that meet the following criteria:

• All accounts of type "Customer"
• Have no Tasks that have a status of "Completed"

If there is a single complete task then nothing should be shown.

I appreciate that the Tasks' parent is an Opportunity, but the database has a separate link on the Task to the Account so it does know.


Separately I see when messing around with NOT IN that there is a field on the Account called "Tasks.status" (which makes sense), plus also "Tasks (expanded).status". Can you let me know what the difference is?

Thanks.

[Maximus]

Hi there,
You need to create this report from the Task entity. Take a look at the first screenshot where is shown the simple configs to fetch all uncompleted Tasks with related Accounts.
On the second screenshots, I've added a new filter to show only Accounts with the uncompleted Task that has relation to Opportunity.

[MatLudlam]

Hmm, I am trying to get a list of Accounts that don't have tasks of a certain profile. This is basically an error report, all Accounts should have Tasks of a certain profile. If an Account doesn't have Tasks of that profile, then it should appear on the error report.

There may not be any Tasks on an Account so I don't see how this would work because they should be on that report.

Have I got this wrong?



PS - any idea on the 2nd point the "Tasks (expanded).status" thing? not related but trying to work things out.

[MatLudlam]

Hi Maximus any further thoughts?

[Maximus]

Hi, I tested a workaround. But it won't help. The idea was to use the countRelated formula to count a related case to Account and if this value equals to 0 then push this Account to a report. However, this task is more complicated because the countRelated formula counts only related to a particular Account tasks, that means it doesn't count tasks that are related to the Account's related entities like Lead, Contact, Opportunity, etc. that can be shown in the Task side panel.
Not sure if that possible to achieve without coding.

[MatLudlam]

Hi Maximus, thanks for the help. Let me think if there is another way.

When the task is created on the opportunity of the correct profile, I could create another on the Account automatically and use this for the report

I could also have another workflow that completes the other Task when the current Task is completed.

Ugly, but will probably work.

Thanks

[Maximus]

Hi,
I was able to create a hook for your purpose.
1. You need to create a new Integer type field for Account (in my example it is 'taskCounter');
2. Create a hook that will be triggered upon the task creation or update action -> then count all related tasks of the related account -> store the number of the tasks into the taskCounter filed.
Create the file /custom/Espo/Custom/Hooks/Task/TaskCounter.php with the code below:

<?php
namespace Espo\Custom\Hooks\Task;

use Espo\ORM\Entity;

class TaskCounter extends \Espo\Core\Hooks\Base
{
    public function afterSave(Entity $entity, array $options = [])
    {

        $accountId = $entity->get('accountId');

        $status = $entity->get('status');

        if($accountId)
        {
            $counter = 0;

            $collection = $this->getEntityManager()
            ->getRepository('Task')
            ->where([
                'accountId' => $accountId,
                'status!=' => ['Completed', 'Canceled', 'Deferred']
            ])->find();

            $counter = count($collection);

            $account = $this->getEntityManager()->getEntity('Account', $accountId);

            $account->set('taskCounter', $counter);

            $this->getEntityManager()->saveEntity($account);

        }
    }
}

3. Create a report that will show you all accounts that has type = Customer and has taskCounter != 0.

Note, this hook will work for all future changes. In order to trigger it and run the counting procedure for the existed Tasks you can utilize mass update feature.

Also in my example, I set the filter to prevent counting Task with the 'Completed', 'Canceled', 'Deferred' statuses. If you just need to omit the 'Completed' status you can remove the rest both from the list.

[MatLudlam]

Hi Maximus, thanks for the help. Unfortunately I am using the SaaS version of ESPO so don't have access to create PHP files.