Contacts filter based on number of linked opportunities

[boneater]

Hi, I would like to add filter option to the list of Contacts based on which I could filter only contacts with specific number of linked opportunities.

I tried to re-use solution for "hasPortalUser" filter option so I created "hasOpportunity" boolean field in Contact entity and added following piece of code into /metadata/entityDefs/Contact.json. But this would work only if there was 1:1 relation. How should I update the code to make it work with collection? Is there any documentation I could browse to check all options I could use?

"hasOpportunity": {
"type": "bool",
"notStorable": true,
"readOnly": true,
"mergeDisabled": true,
"customizationDefaultDisabled": true,
"customizationReadOnlyDisabled": true,
"foreignAccessDisabled": true,
"select": {
"select": "IS_NOT_NULLopportunity.id)",
"leftJoins": [["opportunity", "opportunity"]]
},
"where": {
"= TRUE": {
"whereClause": {
"portalUser.id!=": null
},
"leftJoins": [["opportunity", "opportunity"]]
},
"= FALSE": {
"whereClause": {
"portalUser.id=": null
},
"leftJoins": [["opportunity", "opportunity"]]
}
},
"order": {
"order": [
["portalUser.id", "{direction}"]
],
"leftJoins": [["opportunity", "opportunity"]],
"additionalSelect": ["opportunity.id"]
}
},

Many thanks

[rabii]

i don't think this will work. one wat is to created an int field on contact which counts how many opportunities a contact has (hence contacts and opportunities has a Many-To-Many relationship). then from the contact list you can filter by the new field. this will be much easier with few formula function you can achieve this easily.

[boneater]

Thank you rabii, I actually tried this approach but formula functions are triggered only on record edit. In this case Case record is not edited so the count field would not be updated. Is that right?

[Kharg]

Hi,

Wouldn't a filter work for this?

https://docs.espocrm.com/development/metadata/select-defs/

https://www.youtube.com/watch?v=BYllS-6_xdE

[boneater]

Thank you Kharg, this solution worked for me :-)

[Kharg]

Glad it worked

[boneater]

Hi, one more question regarding ORM related to this topic. I created custom filter as written above but now I need list of contacts that have no assigned opportunity. This is the original piece of code I want to update:

<?php

namespace Espo\Custom\Classes\Select\ContactObj\PrimaryFilte rs;

use Espo\Core\Select\Primary\Filter;
use Espo\ORM\Query\SelectBuilder;

class HasOpportunity implements Filter
{
public function apply(SelectBuilder $queryBuilder): void
{

$queryBuilder
->join(
'Opportunity',
'opportunity',
[
'opportunity.contactId:' => 'id'
],
)
->where(['opportunity.contactId:' => 'contact.id']);

}
}


Could you please advise me how to do it since "!=" operator didn't work for me.

[Kharg]

did you try

->where([ 'opportunity.contactId' => null ]); 

?

[boneater]

Hi Kharg, many thanks, it worked! :-) Beside the change you proposed I also had to use leftJoin() instead of join(). Final code that worked for me looks like this:

<?php

namespace Espo\Custom\Classes\Select\ContactObj\PrimaryFilte rs;

use Espo\Core\Select\Primary\Filter;
use Espo\ORM\Query\SelectBuilder;

class HasNoOpportunity implements Filter
{
public function apply(SelectBuilder $queryBuilder): void
{

$queryBuilder
->leftJoin(
'Opportunity',
'opportunity',
[
'opportunity.contactId:' => 'id'
],
)
->where(['opportunity.contactId' => null]);

}
}

BTW: How do you embed text in code style as you did in your previous post? I wasn't able to achieve this with the built in wysiwyg :-)

[Kharg]

Perfect!

You have to select advanced Editor and select PHP CODE.

[Donish]

HI Guys, sorry I asked more than one time an update.... for the opportunity status and color. I mean I would like to see and move also the opportunity to Close Lost and see here with color red, but I can see only in the other window in list mode. Is it possible to receive this update ? do you know something about this ...???? thanks in advance ! opportunity #opportunitycolor color + #oportunity in kanban mode

[lazovic]

Hi Donish,

Go to Administration > Entity Manager > Opportunity, press Edit button and remove Closed Lost value from Ignored groups in Kanban view field.

[Donish]

Thanks ! I try, if you will not hear nothing from me, it means that it's ok !