Announcement

Collapse
No announcement yet.

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

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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.

  • #2
    Hi jay,

    Please try to use these formulas:

    https://docs.espocrm.com/administrat...indrelatedmany
    https://docs.espocrm.com/administrat...linkmultipleid

    Comment


    • #3
      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?

      Comment


      • #4
        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/.

        Comment


        • #5
          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?

          Comment


          • #6
            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 Code:
            <?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);

            Comment


            • #7
              Thanks for posting the solution !

              Comment


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

                Code:
                <?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) :

                Code:
                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

                Comment


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

                  Originally posted by rabii View Post
                  Thanks for sharing, here my implementation using Espo way, this even allow to pass in primary filters:

                  Code:
                  <?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) :

                  Code:
                  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

                  Comment

                  Working...
                  X