Creating a field with values selectable from a db query

[vampirefrog]

Hello. Long time user of EspoCRM, new to the forum.

I am trying to create a field that allows you to select values from a list (a <select> field), but those values come from a DB query. The DB query is not that important, it just takes as a parameter the value of one of the current entity's fields (which is a foreign key).

How would I go about creating this? I suspect I need to use ajax to load the possible values when the entity is being edited.

Thanks!

[tanya]

Hello
You can try to use Formula https://github.com/espocrm/documenta...ion/formula.md
or open application/Espo/Modules/Crm/Resources/metadata/entityDefs/Opportunity.json to see an example how to define non-storable calculated field (amountWeightedConverted)

[vampirefrog]

Thank you, tanya. The field itself is not calculated, but I am allowing the user to select values for the field from a list that comes from a DB query. I think it is different from a calculated field, as it is not read only, and it is storable. I just want a <select> dropdown with my own dynamic <option>s in it (loaded from ajax).

[tanya]

You need to set own view of your field. This view has to extend from views/fields/enum and define in method setupOptions your ajax.
A little bit looks a like includingActionList field in TargetList
(application/Espo/Modules/Crm/Resources/metadata/entityDefs/TargetList.json)
(client/modules/crm/src/views/target-list/fields/including-action-list.js)

[vampirefrog]

Thanks, tanya, that helped. I was able to populate a <select> with data loaded from ajax. However, my last problem is the fact that, while the select shows up correctly when I hit 'Edit', the field itself does not show up correctly immediately after it loads. What I see, instead is the ID. I think it has to do with the fact that the data is loaded from ajax, and is not immediately available. The field itself is a foreign key, so it should display the name of the foreign entity, but it displays its ID instead. I am not sure where to look next. Do I need to use a separate view? I am uncertain what to do. Thanks.

[tanya]

I did something looks like you want.
How to set the view of the field, see examples from my previous comment.

Espo.define('Mine:Views.Fields.MyField', 'Views.Fields.Enum', function (Dep) {

    return Dep.extend({

        translatedOptions: null,
        foreignScope: null,
        nameName: null,
        idName: null,

        searchTemplate: 'fields.enum.edit',

        setup: function () {
            this.foreignScope = this.options.foreignScope || this.foreignScope;
            this.foreignScope = this.foreignScope || this.model.getFieldParam(this.name, 'entity') || this.model.defs.links[this.name].entity;

            this.nameName = this.name + 'Name';
            this.idName = this.name + 'Id';

            if (this.mode != 'list') {
                var url = this.foreignScope + '?sortBy=name&maxCount=50';
                this.translatedOptions = null;
                var list = [];
                this.wait(true);
                $.ajax({
                    url: url,
                    dataType: 'json',
                    async: false,
                }).done(function (respose) {
                    if (respose.total > 0) {
                        var translatedOptions = {};
                        translatedOptions[''] = '';
                        list.push('');
                        respose.list.forEach(function (item) {
                            translatedOptions[item.id] = item.name;
                            list.push(item.id);
                        }, this);
                        this.translatedOptions = translatedOptions;
                    }
                    this.wait(false);
                }.bind(this));

                this.params.options = list;
            }
        },

        setupSearch: function () {
            this.searchParams.typeOptions = ['is', 'isEmpty', 'isNotEmpty'];
            this.events = _.extend({
                'change select.search-type': function (e) {
                    var type = $(e.currentTarget).val();
                    this.handleSearchType(type);
                },
            }, this.events || {});
        },

        getValueForDisplay: function () {
           return (this.mode != 'list') ? this.model.get(this.idName) : this.model.get(this.nameName);
        },

        validateRequired: function () {
            if (this.params.required || this.model.isRequired(this.name)) {
                if (this.model.get(this.idName) == null) {
                    var msg = this.translate('fieldIsRequired', 'messages').replace('{field}', this.translate(this.name, 'fields', this.model.name));
                    this.showValidationMessage(msg);
                    return true;
                }
            }
        },

        fetch: function () {
            var data = {};
            data[this.nameName] = this.$el.find('[name="'+this.name+'"] option:selected').text() || null;
            data[this.idName] = this.$el.find('[name="'+this.name+'"]').val() || null;
            return data;
        },

        fetchSearch: function () {
            var type = this.$el.find('select.search-type').val();
            var value = this.$el.find('[name="' + this.name + '"]').val();

            var data = {
                type: 'equals',
                typeFront: type,
                field: this.idName,
                value: value,
                valueName: this.getValueForDisplay(),
            };
            return data;
        },

        afterRender: function () {
            if (this.mode == 'edit' || this.mode == 'search') {
                this.initElement();
            }

            if (this.mode == 'search') {
                this.$elementName = this.$el.find('[name="' + this.name + '"]');
                var searchField = this.searchParams.field || '';

                if (searchField == this.idName) {
                    this.$elementName.val(this.searchParams.value);
                }
            }
        },

    });

});

[hers]

Tanya hello! Can y make video tutor. on your code?)

[vampirefrog]

Thank you, tanya. I was able to take your code and fiddle with it until it worked, and it seems to work perfectly. There was one more problem though, the names of the foreign entities were not links, so I fixed that by copying the list and detail templates from the link field type, along with the data() method, and now it works perfectly!

[vampirefrog]

Hello, sorry to dig up an old thread. The code works in full view, and I am using this same code in the detail small view, and when the popup first appears, the select is populated almost correctly (only values, not translated values appear), but the main problem is that if I open the same edit popup, the select is empty (in fact, it is populated with the initial options declared in entitiDefs json. I have no idea what to do, I've tried everything obvious, and am at a loss. The code I've used is the same. Here is my entityDef:

        "foo": {
            "type": "link",
            "required": true,
            "view": "custom:views\/entity-type\/fields\/foo",
            "foreignScope": "Bar",
            "options": [ "test" ],
            "audited": false
        }

Thanks for any ideas that you may have!

[vampirefrog]

Turns out I had to call this.reRender() in the ajax callback!