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.

  • #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:

              PHP 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) :

              PHP Code:

              record\findMany
              (ENTITY_TYPELIMITORDER_BYORDERKEY1VALUE1KEY2VALUE2)

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

              $activeClientsIds record\findMany('Account'10'createdAt''desc''activeClients'); 
              Hope this help someone.

              Cheers
              Last edited by rabii; 05-23-2023, 11:28 AM.
              Rabii
              Web Dev

              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