Announcement

Collapse
No announcement yet.

Implement field autocomplete from remote source, filtered by value of another field

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

  • Implement field autocomplete from remote source, filtered by value of another field

    This tutorial describes how to implement autocomplete for a given entity field (input field), filtered by the value of another field in the same entity (filter field) using a remote data source.

    For this example, we will be making an API call to a French service that lists address information. https://adresse.data.gouv.fr/api-doc/adresse

    Our test entity has two fields: autocompleteInput where the user will enter characters to look for in the service database and autocompleteFilter wich contains a zipcode that will be used to filter the search query submitted to the service API.

    Since generally Ajax calls can not be made to outside servers (they throw a 403 error) we will create a "gateway" controller and service to send a curl request to the service.

    The diagram below illustrates the example dataflow:

    Click image for larger version  Name:	Autocomplete Diagram.png Views:	27 Size:	21.9 KB ID:	89608
    Please note that this example assumes that the reader is familiar with javascript and php coding, and it covers a specific situation to illustrate what can be done with the autocomplete plug in, but is is not intended to be a cut and copy recipe that can easily be used without further coding.

    For in depth information on the autocomplete plug in, used in Espo please go to this webpage: https://github.com/devbridge/jQuery-Autocomplete

    Step 1: Specify the autocomplete related fields in the test entity entityDefs metadata file.
    custom\Espo\Custom\Resources\metadata\entityDefs\A utocompleteTest.json
    Code:
    {
        "fields": {
            "autocompleteInput": {
                "type": "varchar",
                "maxLength": 150,
                "options": [],
                "isCustom": true,
                "view": "custom:views/fields/varchar-autocomplete-from-api",
                "autocompleteMinChars": "1",
                "autocompleteSourceUrl": "http://api-adresse.data.gouv.fr/search/",
                "autocompleteQmodifier": "+bd+du+port",
                "autocompleteQueryFilters": [
                    {"limit":"20"},
                    {"postcode": "autocompleteFilter.val()"}
                ]
            },
            "autocompleteFilter": {
                "type": "varchar",
                "maxLength": 150,
                "options": [],
                "isCustom": true
            }
        }
    }​
    Please note in the code above, that the options "autocompleteQmodifier" and "autocompleteQueryFilters" are used to satisfy the requirements for the source server API. In your application, you will need to find out if there are specific requirements for the API call and if it is better to store them in metadata or hard code them in the service class.

    Step 2: Create the custom field view class for the field that holds the autocomplete query input data.
    client\custom\src\views\fields\varchar-autocomplete-from-api.js
    Code:
    define('custom:views/fields/varchar-autocomplete-from-api', 'views/fields/varchar', function (Dep) {
    
        return Dep.extend({
    
            autocompleteUrl: null,
            autocompleteGatewayUrl: "AutocompleteFromApi",
            autocompleteOptions: {},
    
            setup: function () {
                Dep.prototype.setup.call(this);    
                // direct the autocomplete Ajax call to the gateway url
                this.autocompleteUrl = this.autocompleteGatewayUrl+'/action/fetchJson?s='+this.entityType+'&id='+this.model.id+'&a='+this.name;
            },
    
            afterRender: function () {
                Dep.prototype.afterRender.call(this);
                // define autocomplete options for details on the autocomplete plug in options and methods visit: https://github.com/devbridge/jQuery-Autocomplete
                this.autocompleteOptions = {
                    noCache: true,
                    serviceUrl: this.autocompleteUrl,
                    paramName: 'q',                    
                    minChars: this.getMetadata().get(['entityDefs',this.entityType,'fields',this.name,'autocompleteMinChars']),                  
                    beforeRender: ($c) => {
                        if (this.$element.hasClass('input-sm')) {
                            $c.addClass('small');
                        }
                    },
                    formatResult: (suggestion) => {
                        return this.getHelper().escapeString(suggestion.value);
                    },        
                    maxHeight: 200,                    
                    onSearchStart: (q) => {
                            // called before the Ajax request is made - could be used to pre-filter the search string
                    }                    
                };
                this.autocompleteOptions.transformResult = response => this.transformAutocompleteResult(response);
    
                if(this.isEditMode()) {
                    this.$element.on('focus', () => {                            
                        // trigger autocomplete
                        this.$element.autocomplete(this.autocompleteOptions);
                        this.$element.attr('autocomplete', 'espo-' + this.name);
    
                    });  
                    // define actions to take, if any, when the field value is being updated
                    this.$element.on("input", () => {
                        // define actions here
                    });
                    // remove the autocomplete functionality after a field is saved and rendered again in detail mode
                    this.once('render', () => {
                        this.$element.autocomplete('dispose');
                    });
                    // remove the autocomplete functoinality if a field element is removed
                    this.once('remove', () => {
                        this.$element.autocomplete('dispose');
                    });
    
                }
            },
    
            // This function will need to be customized according to the structure of the JSON response from the external server
            transformAutocompleteResult: function (response) {
                let parsedResponse = JSON.parse(response);
                let list = [];
    
                // API RESPONSE SPECIFIC VALUES
                const jsonResponseDataContainer = 'features';
                const jsonResponseItemAttribute = 'properties';  
    
                parsedResponse[jsonResponseDataContainer].forEach(item => {
                    list.push({
                        id: item[jsonResponseItemAttribute].id,
                        name: item[jsonResponseItemAttribute].name || item[jsonResponseItemAttribute].id,
                        data: item[jsonResponseItemAttribute].id,
                        value: item[jsonResponseItemAttribute].name || item[jsonResponseItemAttribute].id,
                        attributes: item[jsonResponseItemAttribute]
                    });
                });
    
                return {
                    suggestions: list
                };
            }
       });
    
    });
    ​
    Step 3: Create the gateway Controller class that will receive the autocomplete Ajax call
    custom\Espo\Custom\Controllers\AutocompleteFromApi .php
    PHP Code:
    <?php

    namespace Espo\Custom\Controllers;

    use 
    Espo\Core\Exceptions\Error;
    use 
    Espo\Core\Exceptions\Forbidden;
    use 
    Espo\Core\Exceptions\BadRequest;

    use 
    Espo\Core\Api\Request;

    use 
    Espo\Core\Exceptions\NotFound;
    use 
    Espo\Custom\Services\AutocompleteFromApi as Service;

    class 
    AutocompleteFromApi
    {
        private 
    Service $service;

        public function 
    __construct(Service $service)
        {
            
    $this->service $service;

        }

        public function 
    actionFetchJson(Request $request): string
        
    {
            
    $q $request->getQueryParam('q') ?? null;
            
    $scope $request->getQueryParam('s') ?? null;
            
    $recordId $request->getQueryParam('id') ?? null;
            
    $attribute $request->getQueryParam('a') ?? null;
            
    // verify input completeness
            
    if (!$q) {
                throw new 
    BadRequest("No 'q' parameter.");
            }
            if (!
    $scope) {
                throw new 
    BadRequest("No 's' parameter.");
            }
            if (!
    $recordId) {
                throw new 
    BadRequest("No 'id' parameter.");
            }
            if (!
    $attribute) {
                throw new 
    BadRequest("No 'a' parameter.");
            }
            
    // remove all non-alphanumeric characters, allowing spaces to remain, and make the input string url safe
            
    $cleanQ urlencode(preg_replace("/[^A-Za-z0-9 ]/"""$q));        
            
    $payload = [];
            
    $payload['q'] = $cleanQ;
            
    $payload['scope'] = $scope;
            
    $payload['recordId'] = $recordId;
            
    $payload['attribute'] = $attribute;
            
    $payload['scope'] = $scope;
            
    // invoke the method fetchJsonFromApi at AutocompleteFromApi service class
            
    $autocompleteResponse $this->service->fetchJsonFromApi($payload);
            return 
    $autocompleteResponse;
        }    
    }
    Step 4: Create the gateway Service class that will be invoked by the Controller and will make the curl call to the external server
    custom\Espo\Custom\Services\AutocompleteFromApi.ph p
    PHP Code:
    <?php

    namespace Espo\Custom\Services;

    use 
    Espo\Core\Exceptions\Error;

    class 
    AutocompleteFromApi extends \Espo\Core\Templates\Services\Base
    {
        public function 
    fetchJsonFromApi ($payload) {
            
    // get the exernal api url from the scope's entityDefs
            
    $autocompleteUrl $this->metadata->get(['entityDefs'$payload['scope'], 'fields'$payload['attribute'], 'autocompleteSourceUrl']).'?q='.$payload['q'];
            
    // get the API query paramteres from metadata
            
    $qUrlModifier $this->metadata->get(['entityDefs'$payload['scope'], 'fields'$payload['attribute'], 'autocompleteQmodifier']);
            if(empty(
    $qUrlModifier)) {
                
    $qUrlModifier '';
            }
            
    $urlFilters $this->metadata->get(['entityDefs'$payload['scope'], 'fields'$payload['attribute'], 'autocompleteQueryFilters']);
            
    $urlFilterModifier '';
            if(!empty(
    $urlFilters)) {
                
    $entity $this->entityManager->getEntityById($payload['scope'], $payload['recordId']);
                foreach(
    $urlFilters as $filter) {
                    foreach(
    $filter as $key => $val) {
                        if(
    str_contains($val'.val()')) {
                            
    $filterParts explode('.',$val);      
                            
    $value $entity->get($filterParts[0]);
                            if(!empty(
    $value)) {
                                
    $urlFilterModifier.='&'.$key.'='.$value;
                            }
                        } else {
                            
    $urlFilterModifier.='&'.$key.'='.$val;
                        }                    
                    }
                }

            }
            
    $url $autocompleteUrl.$qUrlModifier.$urlFilterModifier;
            
    $json $this->callAPI('GET',$url);
            return 
    $json;
        }

        private function 
    callAPI($method$url$data=false){
            
    $curl curl_init();
            switch (
    $method){
                case 
    "POST":
                    
    curl_setopt($curlCURLOPT_POST1);
                    if (
    $data)
                        
    curl_setopt($curlCURLOPT_POSTFIELDS$data);
                    break;
                case 
    "PUT":
                    
    curl_setopt($curlCURLOPT_CUSTOMREQUEST"PUT");
                    if (
    $data)
                        
    curl_setopt($curlCURLOPT_POSTFIELDS$data);                                
                    break;
                default:
                    if (
    $data)
                        
    $url sprintf("%s?%s"$urlhttp_build_query($data));
            }
            
    // set curl options:
            
    curl_setopt($curlCURLOPT_URL$url);
            
    curl_setopt($curlCURLOPT_HTTPHEADER, array(
                
    'APIKEY: 111111111111111111111',
                
    'Content-Type: application/json',
            ));
            
    curl_setopt($curlCURLOPT_RETURNTRANSFER1);
            
    curl_setopt($curlCURLOPT_HTTPAUTHCURLAUTH_BASIC);
            
    // execute curl:
            
    $result curl_exec($curl);
            if(!
    $result){die("Connection Failure");}
            
    curl_close($curl);
            return 
    $result;
        }    

        private function 
    buildUrl($action$params = [])
        {
            
    $params['private_token'] = self::PRIVATE_TOKEN;

            
    $url self::API_URL $action '?';

            
    $count count($params);
            
    $i 0;

            foreach (
    $params as $key => $value) {
                if (!empty(
    $value)) {
                    
    $url .= $key '=' $value;            
                }
                
    $i++;
                if (
    $i < ($count)) {
                    
    $url .= '&';                
                }
            }

            return 
    $url;        
        }  

    }
    Last edited by telecastg; 03-18-2023, 07:19 PM.

  • #2
    Nice …

    you have not forget a entry in route.json files ?
    And not library to include ? This use existing library in espocrm ?
    Last edited by item; 03-18-2023, 09:06 AM.

    Comment


    • telecastg
      telecastg commented
      Editing a comment
      Thanks

      It is not necessary to add a route entry as the Ajax call goes to a standard controller.

      This project uses the existing autoselect library in Espo. The clarification was to warn users not to try to modify relying on the standard jquery extension which is also not used in Espo.

  • #3
    wow, what a job!
    Thank you so much for sharing your work.
    For now, to test, I have integrated your fields directly into the contact entity.
    I did it as an extension.
    But as soon as I integrate the extension, I get a 500 error.​

    The logs are :
    ERROR: Slim Application Error Type: Error Code: 0 Message: Call to a member function get() on null File: C:\xampp\htdocs\CRMCD01\custom\Espo\Modules\modban \Services\AutocompleteFromApi.php Line: 25 Trace: #0 C:\xampp\htdocs\CRMCD01\custom\Espo\Modules\modban \Controllers\AutocompleteFromApi.php(52): Espo\Modules\modban\Services\AutocompleteFromApi->fetchJsonFromApi(Array) EspoCRM Forum C:\xampp\htdocs\CRMCD01\application\Espo\Core\Api\ ActionProcessor.php(88): Espo\Modules\modban\Controllers\AutocompleteFromAp i->actionFetchJson(Object(Espo\Core\Api\RequestWrapp er), Object(Espo\Core\Api\ResponseWrapper)) Forum C:\xampp\htdocs\CRMCD01\application\Espo\Core\Api\ RequestProcessor.php(124): Espo\Core\Api\ActionProcessor->process('AutocompleteFro...', 'fetchJson', Object(Espo\Core\Api\RequestWrapper), Object(Espo\Core\Api\ResponseWrapper)) #3 C:\xampp\htdocs\CRMCD01\application\Espo\Core\Api\ RequestProcessor.php(95): Espo\Core\Api\RequestProcessor->proceed(Object(Espo\Core\Api\RequestWrapper), Object(Espo\Core\Api\ResponseWrapper)) #4 C:\xampp\htdocs\CRMCD01\application\Espo\Core\Api\ RequestProcessor.php(62): Espo\Core\Api\RequestProcessor->processInternal(Object(Espo\Core\Api\Route), Object(Espo\Core\Api\RequestWrapper), Object(Espo\Core\Api\ResponseWrapper)) Groups C:\xampp\htdocs\CRMCD01\application\Espo\Core\Api\ Starter.php(86): Espo\Core\Api\RequestProcessor->process(Object(Espo\Core\Api\Route), Object(Espo\Core\Api\RequestWrapper), Object(Espo\Core\Api\ResponseWrapper)) Uncategorized Groups C:\xampp\htdocs\CRMCD01\vendor\slim\slim\Slim\Hand lers\Strategies\RequestResponse.php(43): Espo\Core\Api\Starter->Espo\Core\Api\{closure}(Object(Slim\Psr7\Request) , Object(Slim\Psr7\Response), Array) Special C:\xampp\htdocs\CRMCD01\vendor\slim\slim\Slim\Rout ing\Route.php(384): Slim\Handlers\Strategies\RequestResponse->__invoke(Object(Closure), Object(Slim\Psr7\Request), Object(Slim\Psr7\Response), Array) Visitor Messages C:\xampp\htdocs\CRMCD01\vendor\slim\slim\Slim\Midd lewareDispatcher.php(81): Slim\Routing\Route->handle(Object(Slim\Psr7\Request)) Private Messages C:\xampp\htdocs\CRMCD01\vendor\slim\slim\Slim\Midd lewareDispatcher.php(81): Slim\MiddlewareDispatcher->handle(Object(Slim\Psr7\Request)) Albums C:\xampp\htdocs\CRMCD01\vendor\slim\slim\Slim\Rout ing\Route.php(341): Slim\MiddlewareDispatcher->handle(Object(Slim\Psr7\Request)) #11 C:\xampp\htdocs\CRMCD01\vendor\slim\slim\Slim\Rout ing\RouteRunner.php(84): Slim\Routing\Route->run(Object(Slim\Psr7\Request)) #12 C:\xampp\htdocs\CRMCD01\vendor\slim\slim\Slim\Midd leware\RoutingMiddleware.php(59): Slim\Routing\RouteRunner->handle(Object(Slim\Psr7\Request)) CSS Examples C:\xampp\htdocs\CRMCD01\vendor\slim\slim\Slim\Midd lewareDispatcher.php(147): Slim\Middleware\RoutingMiddleware->process(Object(Slim\Psr7\Request), Object(Slim\Routing\RouteRunner)) #14 C:\xampp\htdocs\CRMCD01\vendor\slim\slim\Slim\Midd leware\ErrorMiddleware.php(107): Psr\Http\Server\RequestHandlerInterface@anonymous->handle(Object(Slim\Psr7\Request)) General Discussion C:\xampp\htdocs\CRMCD01\vendor\slim\slim\Slim\Midd lewareDispatcher.php(147): Slim\Middleware\ErrorMiddleware->process(Object(Slim\Psr7\Request), Object(Psr\Http\Server\RequestHandlerInterface@ano nymous)) Announcements C:\xampp\htdocs\CRMCD01\vendor\slim\slim\Slim\Midd lewareDispatcher.php(81): Psr\Http\Server\RequestHandlerInterface@anonymous->handle(Object(Slim\Psr7\Request)) Feature Requests C:\xampp\htdocs\CRMCD01\vendor\slim\slim\Slim\App. php(215): Slim\MiddlewareDispatcher->handle(Object(Slim\Psr7\Request)) Installation and Upgrade Help C:\xampp\htdocs\CRMCD01\vendor\slim\slim\Slim\App. php(199): Slim\App->handle(Object(Slim\Psr7\Request)) Extensions C:\xampp\htdocs\CRMCD01\application\Espo\Core\Api\ Starter.php(62): Slim\App->run() Developer Help C:\xampp\htdocs\CRMCD01\application\Espo\Core\Appl icationRunners\Api.php(45): Espo\Core\Api\Starter->start() Bug Reports C:\xampp\htdocs\CRMCD01\application\Espo\Core\Appl ication\RunnerRunner.php(87): Espo\Core\ApplicationRunners\Api->run() Unminify assets or how to recreate t...o.min.js file? C:\xampp\htdocs\CRMCD01\application\Espo\Core\Appl ication.php(78): Espo\Core\Application\RunnerRunner->run('Espo\\Core\\Appli...', NULL) C:\xampp\htdocs\CRMCD01\public\api\v1\index.php(37 ): Espo\Core\Application->run('Espo\\Core\\Appli...') #24 {main} Tips: To display error details in HTTP response set "displayErrorDetails" to true in the ErrorHandler constructor. [] []
    Attached Files

    Comment


    • telecastg
      telecastg commented
      Editing a comment
      I did not test it as an extension, I simply created the scripts in the Custom namespace and everything worked fine. I would suggest that you try testing like that first.

    • item
      item commented
      Editing a comment
      constructor
      Last edited by item; 03-19-2023, 09:55 AM.

  • #4
    Even without extension, in Custom, I have the same error.

    Comment


    • telecastg
      telecastg commented
      Editing a comment
      The error is not related to the code, the "offending" line where the error is thrown (line 25 of custom\Espo\Custom\Services\AutocompleteFromApi.ph p) is where the code is making a reference to the entity that contains the autocomplete fields and the error is triggered because the system could not find such entity record.

      I suggest that you create a test entity, copy everything just like it is posted and run some tests to make sure you have the functionality working, and then try to adapt to your own system. I used the scripts as shown and they worked fine.

      One thing that will help is if you inject $GLOBALS['log'] statements in your php classes to be able to track if the inputs are being transferred correctly from one script to another. Not sure if you are familiar with this technique, but if not this posting has some information about it:

      For those that may not be aware, the following is really useful when debugging PHP code: $GLOBALS['log']-&gt;debug('Here is my variable:', [$variable]); You just need to ensure that in your `data\config.php`, the relevant logging `level` is enabled for your output: 'logger' =&gt; [ 'path' =&gt;

  • #5
    Thank you for your advice!
    In fact, it's the record id that was not sent in the request, simply because I was in "create" mode and not "edit", so the id doesn't exists
    So, when I edit a record, it works, I can confirm that!​

    Comment


    • telecastg
      telecastg commented
      Editing a comment
      That makes sense !. I only tested the code in edit mode.

  • #6
    I modified the fetchJsonFromApi function to not fetch the recordId. So the URL is valid on an existing record (edit) or a new record (create).
    I also modified the API specific q argument (+bd+du+port returns only all "port boulevard") by replacing it with "***".
    The only thing that doesn't work is retrieving the "autocompleteFilter" value.
    autocompleteFilter.val() does not seem to work.​​

    Comment


    • telecastg
      telecastg commented
      Editing a comment
      That is because the record does not exist yet, so the "autocompleteFilter" value is undefined.

      Trying to use autocomplete when you are creating an entity, using the value of another field in the same entity as filter is a little tricky, because in order for the query to work you need to know the value of a filter which doesn't exist yet.

      One option would be to modify the field view class client\custom\src\views\fields\varchar-autocomplete-from-api.js to wait until the value of the filter field exists, include that value in the payload for the Ajax call and then trigger autocomplete.

      In other words, fetch the value of the filter in the field view class and pass it along, instead of waiting for the service class to fetch the record and grab the filter value to prepare the query for the API call.
      Last edited by telecastg; 03-20-2023, 05:12 PM.

  • #7
    Thank you for all your help telecastg item .
    For the moment, it has not been successful. Especially since I am trying to retrieve the zip code value from another related entity.
    This is getting difficult for me, but I'm not going to give up! In any case, thanks for all your advice.​

    Comment


    • item
      item commented
      Editing a comment
      Hi Darkcromb, Telecastg,


      just idea : why don't use something so for retreive from related entity the zip code. ? maybe something on "load event (if exist)"
      can work only for many2one relation. like meeting->parentType

      this.ajaxGetRequest('Patient/' + this.model.get('parentId')).then(function (patient) {
      let postalCode = patient['addressPostalCode'];
      this.model.set('postalCode', postalCode )

      so after your autocomplete can work ?

    • espcrm
      espcrm commented
      Editing a comment
      Are you by any chance trying to retrieve it from "Real Estate Property" entity/extension?! If so good luck! I be very interested in your success.

    • espcrm
      espcrm commented
      Editing a comment
      By the way, I end doing this Postcode using formula. It not live data though, all it does is search through my records in another entity (RealEstate), match the City name and copy the postcode over.
Working...
X