Formula to find list of Account ID's and Link them to an entity.

[jay]

I have a notification entity, I need to write a formula that finds multiple Account ID's based on some conditions that match. then I need to link those account entities to the notification entity from one formula.

Any help or direction I will be very grateful for, it has been bugging me so much.

[lazovic]

Hi jay,

Please try to use these formulas:

https://docs.espocrm.com/administration/formula/#recordfindrelatedmany

https://docs.espocrm.com/administration/formula/#entityaddlinkmultipleid

[jay]

lazovic Thank you this is a great help and almost got it working however I cant seem to get record\findRelatedMany to work, does this only find records that are already related? Is there a way to get a list of Account IDs based on a filter that is not already related?

[lazovic]

jay,

To make a selection of records with certain criteria and make a list from this selection, which can then be used for any purpose, is possible only through functions such as Report and Report Filters. These features are included in the official extension Advanced Pack.
Find out more here please:
https://www.espocrm.com/extensions/advanced-pack/,
https://www.espocrm.com/features/reports/,
https://docs.espocrm.com/user-guide/reports/.

[jay]

Thank you lazovic, I have those but I can't find any example to convert a report result into an array of IDs so I can use them as intended, is that even possible?

[jay]

Ok, it was a little tricky but nothing too complicated with a little bit of coding. Created a custom function that queries the accounts table and returns the result, here is an example


/custom/Espo/Custom/Core/Formula/Functions/RecordGroup/FindManyType.php

<?php

namespace Espo\Custom\Core\Formula\Functions\RecordGroup;

use Espo\Core\Select\SelectBuilderFactory;
use Espo\Core\ORM\Entity as CoreEntity;
use Espo\ORM\Query\SelectBuilder;
use Espo\Core\ORM\EntityManager;

use Espo\Core\Formula\{
Functions\BaseFunction,
ArgumentList,
};

use Espo\Core\Di;

class FindManyType extends BaseFunction implements
Di\EntityManagerAware,
Di\SelectBuilderFactoryAware,
Di\MetadataAware
{

use Di\EntityManagerSetter;
use Di\SelectBuilderFactorySetter;
use Di\MetadataSetter;

public function process(ArgumentList $args)
{
$args = $this->evaluate($args);
$entityType = $args[0];
$value1 = $args[1];
$value2 = $args[2];
$value3 = $args[3];
$value4 = $args[4];
$value5 = $args[5];
$value6 = $args[6];
$value7 = $args[7];
$value8 = $args[8];
$value9 = $args[9];


$value1 = implode("|",$args[1]);
$value2 = implode("|",$args[2]);
$value3 = implode("|",$args[3]);
$value4 = implode("|",$args[4]);
$value5 = implode("|",$args[5]);
$value6 = implode("|",$args[6]);
$value7 = implode("|",$args[7]);
$value8 = implode("|",$args[8]);
$value9 = implode("|",$args[9]);


if($value1){
$sqlroad = '(column_name REGEXP "'.$value1.'" AND column_name REGEXP "'.$value4.'" AND column_name REGEXP "'.$value5.'" AND column_name REGEXP "'.$value6.'" AND column_name REGEXP "'.$value7.'")';
}else{
$sqlroad = $value1;
}

if($value2){
$sqlair = 'OR (column_name REGEXP "'.$value2.'" AND column_name REGEXP "'.$value8.'")';
}else{
$sqlair = $value2;
}

if($value3){
$sqlsea = 'OR (column_name REGEXP "'.$value3.'" AND column_name REGEXP "'.$value9.'")';
}else{
$sqlsea = $value3;
}


$sql = 'SELECT id FROM '.$entityType.' WHERE '.$sqlroad.' '.$sqlair.' '.$sqlsea.'';


$sth = $this->entityManager
->getSqlExecutor()
->execute($sql);
//->fetch();

$idList = [];

foreach ($sth as $e) {
$idList[] = $e[0];
}

return $idList;

}
}

Then you can do a formula like

$road = entity\attribute('byRoad');
$air = entity\attribute('byAir');
$sea = entity\attribute('bySea');

$ids = record\findMany('account', $road, $air, $sea, $from, $to, $roadservices, $roadtype);

entity\addLinkMultipleId('accounts', $ids);

[telecastg]

Thanks for posting the solution !

[rabii]

Thanks for sharing, here my implementation using Espo way, this even allow to pass in primary filters:

<?php

namespace Espo\Custom\Core\Formula\Functions\RecordGroup;

use Espo\Core\Formula\{
Functions\BaseFunction,
ArgumentList,
};

use Espo\Core\Di;

class FindManyType extends BaseFunction implements
Di\EntityManagerAware,
Di\SelectBuilderFactoryAware,
Di\MetadataAware
{
use Di\EntityManagerSetter;
use Di\SelectBuilderFactorySetter;
use Di\MetadataSetter;
  public function process(ArgumentList $args)
{  $args = $this->evaluate($args);
      if (count($args) < 4) {  $this->throwTooFewArguments(4);  }     
$entityManager = $this->entityManager;

$entityType = $args[0];
$limit = $args[1];

$orderBy = null;
$order = null;

if (count($args) > 2) {  $orderBy = $args[2];  }

if (count($args) > 3) {  $order = $args[3];  }

if (!$entityType || !is_string($entityType)) {  $this->throwBadArgumentType(1, 'string');  }

if (!is_int($limit)) {  $this->throwBadArgumentType(2, 'string');  }

$metadata = $this->metadata;

if (!$orderBy) {  $orderBy = $metadata->get(['entityDefs', $entityType, 'collection', 'orderBy']);

if (is_null($order)) {  $order = $metadata->get(['entityDefs', $entityType, 'collection', 'order']) ?? 'asc';  }  }
else {  $order = $order ?? 'asc';  }

$builder = $this->selectBuilderFactory  ->create()  ->from($entityType);
   $whereClause = [];     
if (count($args) <= 5) {  $filter = null;
   if (count($args) == 5) {  $filter = $args[4];  }
      if ($filter && !is_string($filter)) {  $this->throwError("Bad filter.");  }     
if ($filter) {  $builder->withPrimaryFilter($filter);  }  }
else {  $i = 4;
      while ($i < count($args) - 1) {  $key = $args[$i];
$value = $args[$i + 1];

$whereClause[] = [$key => $value];

$i = $i + 2;  }  }  
$queryBuilder = $builder->buildQueryBuilder();

if (!empty($whereClause)) {  $queryBuilder->where($whereClause);  }

$queryBuilder->limit(0, $limit);

if ($orderBy) {  $queryBuilder->order($orderBy, $order);  }

$collection = $entityManager  ->getRDBRepository($entityType)
->clone($queryBuilder->build())
->select(['id'])
->find();  
$idList = [];

foreach ($collection as $e) {  $idList[] = $e->getId();  }

return $idList;  } }

This is how it should be used (function blue print) :

record\findMany(ENTITY_TYPE, LIMIT, ORDER_BY, ORDER, KEY1, VALUE1, KEY2, VALUE2)

Example return all ids of accounts applying existing primary filter activeClients, you can also provide Limit of ids to return:

$activeClientsIds = record\findMany('Account', 10, 'createdAt', 'desc', 'activeClients'); 

Hope this help someone.

Cheers

[jay]

Thank you for sharing rabii, this is an important function and I hope it becomes part of the core system.

Thanks for sharing, here my implementation using Espo way, this even allow to pass in primary filters:

<?php

namespace Espo\Custom\Core\Formula\Functions\RecordGroup;

use Espo\Core\Formula\{
Functions\BaseFunction,
ArgumentList,
};

use Espo\Core\Di;

class FindManyType extends BaseFunction implements
Di\EntityManagerAware,
Di\SelectBuilderFactoryAware,
Di\MetadataAware
{
use Di\EntityManagerSetter;
use Di\SelectBuilderFactorySetter;
use Di\MetadataSetter;

public function process(ArgumentList $args)
{
$args = $this->evaluate($args);

if (count($args) < 4) {
$this->throwTooFewArguments(4);
}

$entityManager = $this->entityManager;

$entityType = $args[0];
$limit = $args[1];

$orderBy = null;
$order = null;

if (count($args) > 2) {
$orderBy = $args[2];
}

if (count($args) > 3) {
$order = $args[3];
}

if (!$entityType || !is_string($entityType)) {
$this->throwBadArgumentType(1, 'string');
}

if (!is_int($limit)) {
$this->throwBadArgumentType(2, 'string');
}

$metadata = $this->metadata;

if (!$orderBy) {
$orderBy = $metadata->get(['entityDefs', $entityType, 'collection', 'orderBy']);

if (is_null($order)) {
$order = $metadata->get(['entityDefs', $entityType, 'collection', 'order']) ?? 'asc';
}
}
else {
$order = $order ?? 'asc';
}

$builder = $this->selectBuilderFactory
->create()
->from($entityType);

$whereClause = [];

if (count($args) <= 5) {
$filter = null;
if (count($args) == 5) {
$filter = $args[4];
}

if ($filter && !is_string($filter)) {
$this->throwError("Bad filter.");
}

if ($filter) {
$builder->withPrimaryFilter($filter);
}
}
else {
$i = 4;

while ($i < count($args) - 1) {
$key = $args[$i];
$value = $args[$i + 1];

$whereClause[] = [$key => $value];

$i = $i + 2;
}
}

$queryBuilder = $builder->buildQueryBuilder();

if (!empty($whereClause)) {
$queryBuilder->where($whereClause);
}

$queryBuilder->limit(0, $limit);

if ($orderBy) {
$queryBuilder->order($orderBy, $order);
}

$collection = $entityManager
->getRDBRepository($entityType)
->clone($queryBuilder->build())
->select(['id'])
->find();

$idList = [];

foreach ($collection as $e) {
$idList[] = $e->getId();
}

return $idList;
}
}

This is how it should be used (function blue print) :

record\findMany(ENTITY_TYPE, LIMIT, ORDER_BY, ORDER, KEY1, VALUE1, KEY2, VALUE2)

Example return all ids of accounts applying existing primary filter activeClients, you can also provide Limit of ids to return:

$activeClientsIds = record\findMany('Account', 10, 'createdAt', 'desc', 'activeClients');

Hope this help someone.

Cheers