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

[item]

Nice …

you have not forget a entry in route.json files ?
And not library to include ? This use existing library in espocrm ?

[telecastg]

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:


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

{
    "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

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

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

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($curl, CURLOPT_POST, 1);
                if ($data)
                    curl_setopt($curl, CURLOPT_POSTFIELDS, $data);
                break;
            case "PUT":
                curl_setopt($curl, CURLOPT_CUSTOMREQUEST, "PUT");
                if ($data)
                    curl_setopt($curl, CURLOPT_POSTFIELDS, $data);                                
                break;
            default:
                if ($data)
                    $url = sprintf("%s?%s", $url, http_build_query($data));
        }
        // set curl options:
        curl_setopt($curl, CURLOPT_URL, $url);
        curl_setopt($curl, CURLOPT_HTTPHEADER, array(
            'APIKEY: 111111111111111111111',
            'Content-Type: application/json',
        ));
        curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt($curl, CURLOPT_HTTPAUTH, CURLAUTH_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;        
    }  

}
​