Linked Fields in n:n Relationships

[MicKress]

Hi, can anybody please help me with this problem. I created a new entity "jobs". When a contact takes over a job for our company like a freelancer it is here stored. The fields are "name" (the kind of the job like programming), jobStartDate (the Date someone begins to work on the job), jobEndDate (the Date the Job ends), jobRole (like consultant). The entitiy "jobs" is related with a n:n-relationship to contacts, multiple fields are activated. As far as I see, in the mySQL Database there is a table created contact_jobs with contact_id and jobs_id. So my question is, how can I add to this table contact_jobs the fields jobRole, jobStartDate and jobEndDate. You have similar in the relation accounts - contacts where contactRole and contactIsInactive als fields in the entity contacts,but stored in the table accounts-Contacts. Do I have to add the fields manually directly in the mySQL Database or is there a possibility to do this in EspoCRM? Regards Michael

[tanya]

Hi,
open application/Espo/Modules/Crm/Resources/metadata/entityDefs/Contacts.json and Opportunity.json (I think it's easier to understand)

You can create your relationship with EntityManager (what you did), after you can improve this relationship

Opportunity > links > contacts has "additionalColumns" option - here you need to define your additional fields for data base
in Contact fields defined opportunityRole field,
in Opportunity > contactRole and contacts with columns. Also you need to create a view for your linkMultiple field

[agri]

Hi Tanja,
this is an old post, I know. Still, I have the same task and try to understand the procedure. I get the part
"Opportunity > links > contacts has "additionalColumns" option - here you need to define your additional fields for data base",
but I dont understand what is meant by
"in Contact fields defined opportunityRole field,
in Opportunity > contactRole and contacts with columns."
Could you explain this again in a few words?
Thank you
Uli
p.s.: Does this apply to EspoCRM v. 8.x?

[MicKress]

Thank you, I'll try ...

[saled]

Hi,
How to create a view for the linkMultiple field ?

[tanya]

https://www.espocrm.com/documentation/development/custom-views/#user-content-field-views

Example:

application/Espo/Modules/Crm/Resources/metadata/entityDefs/Contact.json > fields > accounts > view
client/modules/crm/src/views/contact/fields/accounts.js

[saled]

I want to modify a n:n link between the entity Account a a new entity type "comptany". the company.json file to modify is in "custom/Espo/Custom/Resources/metadata/entityDefs/" and not in "application/Espo/Modules/Crm/Resources/metadata/entityDefs/", is it correct?

[tanya]

only in custom/Espo/Custom/Resources/metadata/entityDefs/
Account.json and Company.json (or other name)

In this case the modification is upgrade safe.

[saled]

Thanks!

[saled]

so...

for the view, the accounts.js file must be created in the client/custom/src/views/company/fields folder?

and defined in my Company.json file as bellow ?
"accounts": { "type": "hasMany", "relationName": "accountCompany", "foreign": "companies", "entity": "Account", "audited": true, "isCustom": true, "view": "custom:views/company/fields/accounts", "additionalColumns": { "shares": { "type": "int" }, "isInactive": { "type": "bool", "default": false } }, "layoutRelationshipsDisabled": true } ?

[tanya]

yeah, right (about the view name and the path)
but
as I see, you define the view in links accounts, but you need to do it in fields section
check the example again

https://github.com/espocrm/espocrm/blob/master/application/Espo/Modules/Crm/Resources/metadata/entityDefs/Contact.json#L116

https://github.com/espocrm/espocrm/blob/master/application/Espo/Modules/Crm/Resources/metadata/entityDefs/Contact.json#L317

[saled]

Thanks! You're used to read this code!!
I'm not...

[saled]

ok...

then I have this in my Company.json file :

        "accounts": {
            "type": "linkMultiple",
            "layoutDetailDisabled": false,
            "layoutListDisabled": true,
            "layoutMassUpdateDisabled": false,
            "importDisabled": false,
            "noLoad": false,
            "isCustom": true,
            "view": "custom:views/company/fields/accounts",
            "columns": {
                "shares": "companyShares",
                "isInactive": "CompanyLinkIsInactive"
            }
       }    
    },

and the file /client/custom/src/views/company/fields/accounts.js :

It's a copy of the client/modules/crm/src/views/contact/fields/accounts.js file, except the first line (Espo.define) and the replacement of "role" by "shares".

Espo.define('custom:views/company/fields/accounts', 'views/fields/link-multiple-with-columns', function (Dep) {

    return Dep.extend({
 
        roleType: 'varchar',

        events: {
            'click [data-action="switchPrimary"]': function (e) {
                $target = $(e.currentTarget);
                var id = $target.data('id');

                if (!$target.hasClass('active')) {
                    this.$el.find('button[data-action="switchPrimary"]').removeClass('active').children().addClass('text-muted');
                    $target.addClass('active').children().removeClass('text-muted');
                    this.setPrimaryId(id);
                }
            }
        },

        getAttributeList: function () {
            var list = Dep.prototype.getAttributeList.call(this);
            list.push('accountId');
            list.push('accountName');
            list.push('shares');
            return list;
        },

        setup: function () {
            Dep.prototype.setup.call(this);

            this.primaryIdFieldName = 'accountId';
            this.primaryNameFieldName = 'accountName';
            this.primarySharesFieldName = 'shares';

            this.primaryId = this.model.get(this.primaryIdFieldName);
            this.primaryName = this.model.get(this.primaryNameFieldName);

            this.listenTo(this.model, 'change:' + this.primaryIdFieldName, function () {
                this.primaryId = this.model.get(this.primaryIdFieldName);
                this.primaryName = this.model.get(this.primaryNameFieldName);
            }, this);


            if (this.mode === 'edit' || this.mode === 'detail') {
                this.events['click a[data-action="setPrimary"]'] = function (e) {
                    var id = $(e.currentTarget).data('id');
                    this.setPrimaryId(id);
                    this.reRender();
                }
            }
        },

        setPrimaryId: function (id) {
            this.primaryId = id;
            if (id) {
                this.primaryName = this.nameHash[id];
            } else {
                this.primaryName = null;
            }

            this.trigger('change');
        },

        renderLinks: function () {
            if (this.primaryId) {
                this.addLinkHtml(this.primaryId, this.primaryName);
            }
            this.ids.forEach(function (id) {
                if (id != this.primaryId) {
                    this.addLinkHtml(id, this.nameHash[id]);
                }
            }, this);
        },

        getValueForDisplay: function () {
            if (this.mode == 'detail' || this.mode == 'list') {
                var names = [];
                if (this.primaryId) {
                    names.push(this.getDetailLinkHtml(this.primaryId, this.primaryName));
                }
                this.ids.forEach(function (id) {
                    if (id != this.primaryId) {
                        names.push(this.getDetailLinkHtml(id));
                    }
                }, this);
                return names.join('');
            }
        },

        getDetailLinkHtml: function (id, name) {
            var html = Dep.prototype.getDetailLinkHtml.call(this, id, name);
            if (this.getColumnValue(id, 'isInactive')) {
                var $el = $(html);
                $el.find('a').css('text-decoration', 'line-through');
                return $el.prop('outerHTML');
            }
            return html;
        },

        afterAddLink: function (id) {
            if (this.ids.length === 1) {
                this.primaryId = id;
                this.primaryName = this.nameHash[id];
            }
            this.controlPrimaryAppearance();
        },

        afterDeleteLink: function (id) {
            if (this.ids.length === 0) {
                this.primaryId = null;
                this.primaryName = null;
                return;
            }
            if (id === this.primaryId) {
                this.primaryId = this.ids[0];
                this.primaryName = this.nameHash[this.primaryId];
            }
            this.controlPrimaryAppearance();
        },

        controlPrimaryAppearance: function () {
            this.$el.find('li.set-primary-list-item').removeClass('hidden');
            if (this.primaryId) {
                this.$el.find('li.set-primary-list-item[data-id="'+this.primaryId+'"]').addClass('hidden');
            }
        },

        addLinkHtml: function (id, name) {
            if (this.mode == 'search') {
                return Dep.prototype.addLinkHtml.call(this, id, name);
            }

            var $el = Dep.prototype.addLinkHtml.call(this, id, name);

            var isPrimary = (id == this.primaryId);

            var $a = $(
                '<a href="javascript:" data-action="setPrimary" data-id="' + id+ '"</a>' +
                this.translate('Set Primary', 'labels', 'Account') +
                '</a>'
            );

            var $li = $('<li class="set-primary-list-item" data-id="'+id+'">').append($a);

            if (isPrimary || this.ids.length === 1) {
                $li.addClass('hidden');
            }

            $el.find('ul.dropdown-menu').append($li);


            if (this.getColumnValue(id, 'isInactive')) {
                $el.find('div.link-item-name').css('text-decoration', 'line-through');
            }
        },

        afterRender: function () {
            Dep.prototype.afterRender.call(this);
        },

        fetch: function () {
            var data = Dep.prototype.fetch.call(this);

            data[this.primaryIdFieldName] = this.primaryId;
            data[this.primaryNameFieldName] = this.primaryName;
            data[this.primarySharesFieldName] = (this.columns[this.primaryId] || {}).shares || null;

            data.accountIsInactive = (this.columns[this.primaryId] || {}).isInactive || false;

            return data;
        }

    });

});

... But nothing on the interface. the field "shares" and companyLinkIsactive appears nowhere...

[pathfinder]

Hi, is there any way of linking fields in different entities? Non coding way. I'm not a programmer. Don't know how to access the code.
Thanks!

[rabii]

what do you mean by linking fields in different entities ? do you linking two different entities ?

[pathfinder]

Hi, for example:

I've created several opportunities, so I would like to summarize the total value of all the opportunities for a specific month. I created a separate entity "monthly opportunities", so would like to link the opportunities (that I choose) to this entity, to create a record.

I have NO programming skills. So I cant do it in code.

[rabii]

I am not sure why would you need to do that because you can create a report that does this for you instead of creating new entities. However if this what you need then, follow these steps: (all of this can be done using Entity Manager through UI and using formula).

1 - You create a relationship between "monthly opportunities" and "opportunity" entities (one-to-many) meaning one (monthly opportunity) has many (opportunities).
2 - Add a currency field call it (totalValue) on the monthly opportunities entity
3 - Add this formula to your monthly opportunities entity to calculate the totals of related opportunities (amount)

totalValue = entity\sumRelated('opportunities', 'amountConverted');

Don't forget to place the fields/list on the UI once you created the relationships.

Hope this helps

[pathfinder]

Thanks rabii, i will try this!