Announcement

Collapse
No announcement yet.

Execute a query in a text field

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

  • telecastg
    commented on 's reply
    You're welcome

  • rabii
    commented on 's reply
    thanks for sharing

  • telecastg
    replied
    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.

    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


    Best Regards
    Last edited by telecastg; 03-18-2023, 12:46 AM.

    Leave a comment:


  • espcrm
    replied
    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.
    Last edited by espcrm; 03-20-2023, 07:20 AM.

    Leave a comment:


  • Darkcromb
    replied
    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.

    Leave a comment:


  • telecastg
    commented on 's reply
    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
    commented on 's reply
    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
    replied
    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 :

    PHP Code:
               Espo.Ajax
                    
    .postRequest('GlobalController/action/ciscoExecute', {
                        
    userthis.getUser(),
                        
    extensionthis.getUser().get('extension'),
                        
    phoneNumberphoneNumber
                    
    })
                    .
    then(response => {
                        if (
    response['status'] == true)
                        {
                            
    Espo.Ui.notify(response['message'], 'success'8000);
                        }else{
                             
    Espo.Ui.notifyresponse['message'] , 'error'2000);
                        }
                    });
    ​ 
    and in route.json

    PHP Code:
        {
            
    "route""/GlobalController/action/ciscoExecute/:phoneNumber",
            
    "method""post",
            
    "params": {
                
    "controller""GlobalController",
                
    "action""ciscoExecute",
                
    "phoneNumber"":phoneNumber"
            
    }
        },
    ​ 
    and in controller something so :

    PHP Code:
        public function postActionCiscoExecute(Request $requestResponse $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
    Last edited by item; 03-06-2023, 09:56 PM.

    Leave a comment:


  • Darkcromb
    replied
    Code:
    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"

    Leave a comment:


  • Darkcromb
    replied
    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

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

    Click image for larger version

Name:	image.png
Views:	498
Size:	38.0 KB
ID:	88717

    "Territoire" ​​​​includes a city field (name) and the associated postcode (codePostal).
    Click image for larger version

Name:	image.png
Views:	427
Size:	38.5 KB
ID:	88719


    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)).
    ​​
    Click image for larger version

Name:	image.png
Views:	410
Size:	8.1 KB
ID:	88718

    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.
    ​​
    ​​​
    Attached Files

    Leave a comment:


  • telecastg
    replied
    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
    Last edited by telecastg; 03-04-2023, 08:22 AM.

    Leave a comment:


  • Darkcromb
    replied
    Code:
    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);
                });
            },
        });
    });
    ​

    Leave a comment:


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

    Leave a comment:


  • Darkcromb
    replied
    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​​

    Leave a comment:


  • Darkcromb
    replied
    Thanks

    Leave a comment:

Working...
X