Execute a query in a text field

[telecastg]

You're welcome

[rabii]

thanks for sharing

[telecastg]

Hello Darkcromb and item

I finally got to create a working example of implementing autocomplete using an outside database and having the ability to filter the query by the value of another field in the same entity.

As I mentioned I am working on similar functionality thus I decided to post it is as an article in the developers tutorial section to help others interested in this functionality.

https://forum.espocrm.com/forum/developer-help/developer-tutorials/89607-implement-field-autocomplete-from-remote-source-filtered-by-value-of-another-field#post89607

Best Regards

[espcrm]

Best of luck, I add it to the wiki and hopefully you manage to succeed. My country we need to ask for API Key so I have no idea what data we can get access to.

[Darkcromb]

Thanks to both of you. I haven't been able to move forward since my last post but I should be able to this weekend.

[telecastg]

Very good point item regarding jquery autocomplete not being allowed to access outside servers at least in Espo's implementation.

I am working on a solution and hope to be able to post something soon

[telecastg]

No problem Darkcromb happy to help, I can clearly see that you have put serious thoughts to solve this and are not just fishing for somebody to hand you a ready-made solution. I respect that

[item]

Hi,
i am not skill .. but i think you can't not directly call by ajax to external host.
you need to call a controller backend ..this call your api .. and return value to client.


sample call :

           Espo.Ajax
                .postRequest('GlobalController/action/ciscoExecute', {
                    user: this.getUser(),
                    extension: this.getUser().get('extension'),
                    phoneNumber: phoneNumber
                })
                .then(response => {
                    if (response['status'] == true)
                    {
                        Espo.Ui.notify(response['message'], 'success', 8000);
                    }else{
                         Espo.Ui.notify( response['message'] , 'error', 2000);
                    }
                });​ 

and in route.json

    {
        "route": "/GlobalController/action/ciscoExecute/:phoneNumber",
        "method": "post",
        "params": {
            "controller": "GlobalController",
            "action": "ciscoExecute",
            "phoneNumber": ":phoneNumber"
        }
    },​ 

and in controller something so :

    public function postActionCiscoExecute(Request $request, Response $response): array
    {
        $data = $request->getParsedBody();
        $phoneNumber = preg_replace('/\D/', '', $data->phoneNumber);
        $extension = preg_replace('/\D/', '', $data->extension);

        return Cisco::CiscoExecute($extension, $phoneNumber);
    }​ 

but i my case, i have lat/lng in entity where needed, so in before save, i make a api call in back-end and put lat/lng with result of call.


i have too post a "OpenStreetMap" extension in the past.. if someone adapt it to v7

auto-complete address field is not easy to use for user.. it's my experiment.

My post is just for information/idea/brainstorming

[Darkcromb]

if (this.mode === this.MODE_EDIT) {
this.$element.autocomplete({
source: function (request, response) {
$.ajax({
url: "https://api-adresse.data.gouv.fr/search/?postcode=01000",
data: { q: request.term },
dataType: "json",
success: function (data) {
response($.map(data.features, function (item) {
return { label: item.properties.name, value: item.properties.name};
}));
}
});
}
});
}​

But each time, it calls ".../api/v1/https://api-adresse.data.gouv.fr/search/?postcode=01000"

[Darkcromb]

So to summarize, my goal is, in the contact entity, when I choose a city, the autocomplete suggestions in the streets field, are filtered according to the city chosen (via a national french api, which is called BAN): https://adresse.data.gouv.fr/api-doc/adresse

https://jsfiddle.net/8apgu63n/embedded

- Of course, the city must be filled in first -
In my contact entity, I don't use the default address block.
Instead, I use for the city (label : Ville) a field linked to a custom entity called "Territoire".



"Territoire" includes a city field (name) and the associated postcode (codePostal).



In my contact entity, I use until now the adressStreet field (Label : Rue) for the street, but to try to integrate the API, I created a new field (rueBAN Label : Rue_BAN) based on a new type voieban ((the goal being to do it in the original street field)).



I attach to this post the ModBAN extension I created, creating the custom type voieban, by completing the voieban.js with the API request in the afterRender, in the condition (this.MODE_EDIT).

Thanks again for your help.
My goal in posting here was not to have a ready-made work, but to exchange, to have advices but if you are on the same problems, i'd be happy to receive your help.

[telecastg]

Thanks, can you also post all the templates codes, and if you are referring to any elements in the record detail view, then post the code for the record detail view and its templates as well.

One thing that I have always found tricky when integrating jquery instructions with espo views is that due to javascript asynchronous nature, many times the elements simply do not exist yet when the jquery call is made, but I can't tell with your script at first glance, so I would like to test them.

Normally I am opposed to doing code debugging for someone else, but as I mentioned, I am working on a project that involves invoking autocomplete from outside sources as well, so I won't mind spending time testing your software as part of my research

[Darkcromb]

define('custom:views/contact/fields/voieban', ['views/fields/base'], function (Dep) {
    /**
     * Field api BAN
     *
     */

    return Dep.extend({
        type: 'voieban',
        listTemplate: 'custom:contact/fields/voieban/list',
        detailTemplate: 'custom:contact/fields/voieban/detail',
        editTemplate: 'custom:contact/fields/voieban/edit',
        searchTemplate: 'custom:contact/fields/voieban/search',
        seeMoreText: false,
        rowsDefault: 10,
        rowsMin: 2,
        seeMoreDisabled: false,
        cutHeight: 200,
        searchTypeList: [
            'contains',
            'startsWith',
            'equals',
            'endsWith',
            'like',
            'notContains',
            'notLike',
            'isEmpty',
            'isNotEmpty',
        ],
        noResize: false,
        changeInterval: 5,
        events: {
            'click a[data-action="seeMoreText"]': function (e) {
                this.seeMoreText = true;
                this.reRender();
            },
            'click [data-action="mailTo"]': function (e) {
                this.mailTo($(e.currentTarget).data('email-address'));
            },
        },
        setup: function () {
            Dep.prototype.setup.call(this);
            this.params.rows = this.params.rows || this.rowsDefault;
            this.noResize = this.options.noResize || this.params.noResize || this.noResize;
            this.seeMoreDisabled = this.seeMoreDisabled || this.params.seeMoreDisabled;
            this.autoHeightDisabled = this.options.autoHeightDisabled || this.params.autoHeightDisabled ||
                this.autoHeightDisabled;
            if (this.params.cutHeight) {
                this.cutHeight = this.params.cutHeight;
            }
            this.rowsMin = this.options.rowsMin || this.params.rowsMin || this.rowsMin;
            if (this.params.rows < this.rowsMin) {
                this.rowsMin = this.params.rows;
            }
            this.on('remove', () => {
                $(window).off('resize.see-more-' + this.cid);
            });
        },
        setupSearch: function () {
            this.events = _.extend({
                'change select.search-type': function (e) {
                    var type = $(e.currentTarget).val();
                    this.handleSearchType(type);
                },
            }, this.events || {});
        },
        data: function () {
            var data = Dep.prototype.data.call(this);
            if (
                this.model.get(this.name) !== null &&
                this.model.get(this.name) !== '' &&
                this.model.has(this.name)
            ) {
                data.isNotEmpty = true;
            }
            if (this.mode === this.MODE_SEARCH) {
                if (typeof this.searchParams.value === 'string') {
                    this.searchData.value = this.searchParams.value;
                }
            }
            if (this.mode === this.MODE_EDIT) {
                if (this.autoHeightDisabled) {
                    data.rows = this.params.rows;
                } else {
                    data.rows = this.rowsMin;
                }
            }
            data.valueIsSet = this.model.has(this.name);
            if (this.isReadMode()) {
                data.isCut = this.isCut();
                if (data.isCut) {
                    data.cutHeight = this.cutHeight;
                }
                data.displayRawText = this.params.displayRawText;
            }
            data.noResize = this.noResize;
            return data;
        },
        handleSearchType: function (type) {
            if (~['isEmpty', 'isNotEmpty'].indexOf(type)) {
                this.$el.find('input.main-element').addClass('hidden');
            } else {
                this.$el.find('input.main-element').removeClass('hidden');
            }
        },
        getValueForDisplay: function () {
            var text = this.model.get(this.name);
            return text || '';
        },
        controlTextareaHeight: function (lastHeight) {
            var scrollHeight = this.$element.prop('scrollHeight');
            var clientHeight = this.$element.prop('clientHeight');
            if (typeof lastHeight === 'undefined' && clientHeight === 0) {
                setTimeout(this.controlTextareaHeight.bind(this), 10);
                return;
            }
            if (clientHeight === lastHeight) {
                return;
            }
            if (scrollHeight > clientHeight + 1) {
                var rows = this.$element.prop('rows');
                if (this.params.rows && rows >= this.params.rows) {
                    return;
                }
                this.$element.attr('rows', rows + 1);
                this.controlTextareaHeight(clientHeight);
            }
            if (this.$element.val().length === 0) {
                this.$element.attr('rows', this.rowsMin);
            }
        },
        isCut: function () {
            return !this.seeMoreText && !this.seeMoreDisabled;
        },
        controlSeeMore: function () {
            if (!this.isCut()) {
                return;
            }
            if (this.$text.height() > this.cutHeight) {
                this.$seeMoreContainer.removeClass('hidden');
                this.$textContainer.addClass('cut');
            } else {
                this.$seeMoreContainer.addClass('hidden');
                this.$textContainer.removeClass('cut');
            }
        },
        afterRender: function () {
            Dep.prototype.afterRender.call(this);
            if (this.isReadMode()) {
                $(window).off('resize.see-more-' + this.cid);
                this.$textContainer = this.$el.find('> .complex-text-container');
                this.$text = this.$textContainer.find('> .complex-text');
                this.$seeMoreContainer = this.$el.find('> .see-more-container');
                this.$rueBAN = this.$el.find('[data-name="' + this.rueBANField + '"]');
                if (this.isCut()) {
                    this.controlSeeMore();
                    if (this.model.get(this.name) && this.$text.height() === 0) {
                        this.$textContainer.addClass('cut');
                        setTimeout(this.controlSeeMore.bind(this), 50);
                    }
                    this.listenTo(this.recordHelper, 'panel-show', () => this.controlSeeMore());
                    this.on('panel-show-propagated', () => this.controlSeeMore());
                    $(window).on('resize.see-more-' + this.cid, () => {
                        this.controlSeeMore();
                    });
                }
            }
            if (this.mode === this.MODE_EDIT) {
                this.$rueBAN.on('change', () => {
                    this.trigger('change');
                });
                this.$rueBAN.autocomplete({
                    source: function (request, response) {
                        $.ajax({
                            url: "https://api-adresse.data.gouv.fr/search/?postcode=01000",
                            data: { q: request.term },
                            dataType: "json",
                            success: function (data) {
                                response($.map(data.features, function (item) {
                                    return { label: item.properties.name, value: item.properties.name};
                                }));
                            }
                        });
                    }
                });
                this.$rueBAN.on('focus', function () {
                    if (this.$rueBAN.val()) return;
                    this.$rueBAN.autocomplete('onValueChange');
                }.bind(this));
                this.once('render', function () {
                    this.$rueBAN.autocomplete('dispose');
                }, this);
                this.once('remove', function () {
                    this.$rueBAN.autocomplete('dispose');
                }, this);
            }
            if (this.mode === this.MODE_SEARCH) {
                var type = this.$el.find('select.search-type').val();
                this.handleSearchType(type);
                this.$el.find('select.search-type').on('change', () => {
                    this.trigger('change');
                });
                this.$element.on('input', () => {
                    this.trigger('change');
                });
            }
            if (this.mode === this.MODE_EDIT && !this.autoHeightDisabled) {
                this.controlTextareaHeight();
                this.$element.on('input', () => {
                    this.controlTextareaHeight();
                });
                let lastChangeKeydown = new Date();
                const changeKeydownInterval = this.changeInterval * 1000;
                this.$element.on('keydown', () => {
                    if (Date.now() - lastChangeKeydown > changeKeydownInterval) {
                        this.trigger('change');
                        lastChangeKeydown = Date.now();
                    }
                });
            }
        },
        fetch: function () {
            let data = {};
            let value = this.$element.val() || null;
            if (value && value.trim() === '') {
                value = '';
            }
            data[this.name] = value
            return data;
        },
        fetchSearch: function () {
            var type = this.fetchSearchType() || 'startsWith';
            var data;
            if (~['isEmpty', 'isNotEmpty'].indexOf(type)) {
                if (type === 'isEmpty') {
                    data = {
                        type: 'or',
                        value: [
                            {
                                type: 'isNull',
                                field: this.name,
                            },
                            {
                                type: 'equals',
                                field: this.name,
                                value: ''
                            }
                        ],
                        data: {
                            type: type
                        }
                    };
                } else {
                    data = {
                        type: 'and',
                        value: [
                            {
                                type: 'notEquals',
                                field: this.name,
                                value: ''
                            },
                            {
                                type: 'isNotNull',
                                field: this.name,
                                value: null
                            }
                        ],
                        data: {
                            type: type
                        }
                    };
                }
                return data;
            }
            else {
                var value = this.$element.val().toString().trim();
                value = value.trim();
                if (value) {
                    data = {
                        value: value,
                        type: type
                    };
                    return data;
                }
            }
            return false;
        },
        getSearchType: function () {
            return this.getSearchParamsData().type || this.searchParams.typeFront ||
                this.searchParams.type;
        },
        mailTo: function (emailAddress) {
            var attributes = {
                status: 'Draft',
                to: emailAddress
            };
            if (
                this.getConfig().get('emailForceUseExternalClient') ||
                this.getPreferences().get('emailUseExternalClient') ||
                !this.getAcl().checkScope('Email', 'create')
            ) {
                require('email-helper', (EmailHelper) => {
                    var emailHelper = new EmailHelper();
                    var link = emailHelper
                        .composeMailToLink(attributes, this.getConfig().get('outboundEmailBccAddress'));
                    document.location.href = link;
                });
                return;
            }
            var viewName = this.getMetadata().get('clientDefs.' + this.scope + '.modalViews.compose') ||
                'views/modals/compose-email';
            Espo.Ui.notify(' ... ');
            this.createView('quickCreate', viewName, {
                attributes: attributes,
            }, (view) => {
                view.render();
                view.notify(false);
            });
        },
    });
});
​

[telecastg]

Can you post the complete code for .../fields/voieban.js please ?

[Darkcromb]

I just created a field type "voieban", copied on the text type, created a field "rueBAN" of type "voieban".
In the .../fields/voieban.js, I put
if (this.mode === this.MODE_EDIT) {

this.$rueBAN.on('change', () => {
this.trigger('change');
});

this.$rueBAN.autocomplete({
source: function (request, response) {
$.ajax({
url: "https://api-adresse.data.gouv.fr/search/?postcode=59000",
data: { q: request.term },
dataType: "json",
success: function (data) {
response($.map(data.features, function (item) {
return { label: item.properties.name, value: item.properties.name};
}));
}
});
}
});
this.$rueBAN.on('focus', function () {
if (this.$rueBAN.val()) return;


But this does not produce anything

[Darkcromb]

Thanks