Execute a query in a text field

[Darkcromb]

Hi,
I would like to set up a custom text field "street", which, when positioned in it, retrieves the value of another field of my entity (postal code) and according to it, executes a request to an API to retrieve street names (as seen here: https://jsfiddle.net/8apgu63n/embedded) and automatically fills the street "street".
I created my custom field based on a text field. But I can't seem to integrate the query script.
How could I do that ?

[Darkcromb]

I tried to get inspiration from the address-city client/src/views/fields/address.js field and autocompletion, but I can't get my query to run.
Do you think this is something easy to do? (and I'm missing something?) or has anyone done something similar?

[esforim]

Haven't seen anything that do retrievable yet. Actually as I type this I did remember one, it was an extension where it use to Track Shipping code, it was somewhere on the forum.

From my limit reading and knowledge this is possible through Webhook method. You can also look at the Google API, see how it was done to get an idea how to "send address" and get an response return (map photo/embed).

I look forward to your progress!

[Darkcromb]

Thank you for your answer. I'll give it a try and keep you posted.
I discovered EspoCRM in 2019 as part of my job at the time, then I went on another project and now I'm completely back on it.
So I'm on several things at once and moving at my own pace!
The software is really good and I thank the creators and all the community on this forum, which allow to progress without feeling alone!

[telecastg]

Hello Darkcromb,

I think that the autocomplete logic would have to be inserted in the actual field view class but since it would receive input from another field view class, it the record view will need to be modified as well.

If you are not familiar with Espo's view class structure, this posting might be useful. https://forum.espocrm.com/forum/deve...view#post67253

I don't think that what you are trying to accomplish would be easy but it is certainly very interesting

I am working on a similar feature for our system and will try to find some time to look into it and let you know of any advances.

[Darkcromb]

Thanks

[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

[telecastg]

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

[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]

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]

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]

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

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

[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

[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

[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.

[esforim]

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.