Tweet
bandtank I'm sure you're right that it works with the custom Vendor table from your example. My difficulty is that I'm trying to make it work with the existing Account table, adding a department field to the account_contact table. I believe Espo has hard-coded some special functionality into the relationship between Contact and Account, which makes it difficult to customize. As I mentioned, I'm able to get the department field to show up in the middle table, but I'm not able to view that field in the UI. I was hoping for it show up next to the existing Title field.
-
-
The information in the first few comments of this post is no longer applicable. I will add instructions for Espo 9.3+ below this comment.Comment
-
In Espo 9.3, I created an entity called CAddress to hold custom address records. The new entity comprises an address field and two decimal fields for latitude and longitude. Because it is possible in my system for clients (accounts), contacts, and vendors (another custom entity) to share addresses, I also need middle table columns to describe the relationships.
In this tutorial, I show how to use a custom middle table field called 'label'. For clients (accounts), the label is an enum. For contacts and vendors, the label is a varchar. From an address record, here is the end result in detail mode:
In edit mode, here is how the relationship rows look (for the label field, 'Clients' uses enum and 'Contacts' uses varchar):
Here is how it looks from a client record in the relationship panel:
Last edited by bandtank; Yesterday, 04:52 PM.Comment
-
Starting with the CAddress entity definition, I added six fields and three links:
custom/Espo/Custom/Resources/metadata/entityDefs/CAddress.json
The linkMultiple fields are used in the address detail view to show the related records. The label fields are used to display the middle table value. The links define half of the relationship between the address entity and the other entities. The links also define the middle table fields.Code:{ "fields": { ... "clients": { "type": "linkMultiple", "view": "views/fields/link-multiple-with-columns", "columns": { "label": "addressLabel" }, "additionalAttributeList": ["columns"], "orderBy": "name" }, "contacts": { "type": "linkMultiple", "view": "views/fields/link-multiple-with-columns", "columns": { "label": "addressLabel" }, "additionalAttributeList": ["columns"], "orderBy": "name" }, "vendors": { "type": "linkMultiple", "view": "views/fields/link-multiple-with-columns", "columns": { "label": "addressLabel" }, "additionalAttributeList": ["columns"], "orderBy": "name" }, "clientLabel": { "type": "varchar", "notStorable": true, "utility": true }, "contactLabel": { "type": "varchar", "notStorable": true, "utility": true }, "vendorLabel": { "type": "varchar", "notStorable": true, "utility": true } }, "links": { ... "clients": { "type": "hasMany", "entity": "Account", "foreign": "cAddresses", "additionalColumns": { "label": { "type": "varchar", "len": 20 } }, "additionalAttributeList": [ "columns" ], "layoutRelationshipsDisabled": true, "columnAttributeMap": { "label": "clientLabel" }, "relationName": "cAddressClient", "audited": false }, "contacts": { "type": "hasMany", "entity": "Contact", "foreign": "cAddresses", "additionalColumns": { "label": { "type": "varchar", "len": 20 } }, "additionalAttributeList": [ "columns" ], "layoutRelationshipsDisabled": true, "columnAttributeMap": { "label": "contactLabel" }, "relationName": "cAddressContact" }, "vendors": { "type": "hasMany", "entity": "CVendor", "foreign": "cAddresses", "additionalColumns": { "label": { "type": "varchar", "len": 20 } }, "additionalAttributeList": [ "columns" ], "layoutRelationshipsDisabled": true, "columnAttributeMap": { "label": "vendorLabel" }, "relationName": "cAddressVendor" } }, ... }
Create custom layouts for each entity:
custom/Espo/Custom/Resources/layouts/CAddress/listForAccount.json
Code:[ { "name": "address", "link": true, "align": "left" }, { "name": "clientLabel", "width": 28, "notSortable": true }, { "name": "type", "width": 20 } ]
custom/Espo/Custom/Resources/layouts/CAddress/listForContact.json
Code:[ { "name": "address", "link": true, "align": "left" }, { "name": "contactLabel", "width": 28, "notSortable": true }, { "name": "type", "width": 20 } ]
custom/Espo/Custom/Resources/layouts/CAddress/listForVendor.json
Code:[ { "name": "address", "link": true, "align": "left" }, { "name": "vendorLabel", "width": 28, "notSortable": true }, { "name": "type", "width": 20 } ]
Add definitions for localization:
custom/Espo/Custom/Resources/i18n/en_US/CAddress.json
custom/Espo/Custom/Resources/i18n/en_US/Account.jsonCode:{ "fields": { ... "clientLabel": "Label", "contactLabel": "Label", "vendorLabel": "Label", "clients": "Clients", "contacts": "Contacts", "vendors": "Vendors" }, "links": { "clients": "Clients", "contacts": "Contacts", "vendors": "Vendors" }, "layouts": { "listForContact": "List (for Contact)", "listForAccount": "List (for Account)", "listForVendor": "List (for Vendor)" } ... }
custom/Espo/Custom/Resources/i18n/en_US/Contact.json
custom/Espo/Custom/Resources/i18n/en_US/CVendor.json
Code:{ "fields": { ... "cAddresses": "Addresses", "addressLabel": "Label" }, "links": { ... "cAddresses": "Addresses" } }Last edited by bandtank; Yesterday, 05:09 PM.Comment
-
Add the relationship definitions to the CAddress client definition:
custom/Espo/Custom/Resources/metadata/clientDefs/CAddress.json
Code:{ ... "relationshipPanels": { "clients": { "layout": null, "selectPrimaryFilterName": null }, "contacts": { "layout": null, "selectPrimaryFilterName": null }, "vendors": { "layout": null, "selectPrimaryFilterName": null } }, "additionalLayouts": { "listForAccount": { "type": "list", "isCustom": true }, "listForContact": { "type": "list", "isCustom": true }, "listForVendor": { "type": "list", "isCustom": true } } }
Add the fields and links to the other entities:
custom/Espo/Custom/Resources/metadata/entityDefs/Account.json
custom/Espo/Custom/Resources/metadata/entityDefs/Contact.jsonCode:{ "fields": { "addressLabel": { "type": "enum", "options": ["Billing", "Clinic", "Daycare", "Home", "Home2", "School", "Other"], "notStorable": true, "utility": true, "orderDisabled": true, "maxLength": 20, "pattern": "$noBadCharacters", "layoutMassUpdateDisabled": true, "layoutListDisabled": true, "layoutDetailDisabled": true, "layoutSearchDisabled": true, "fieldManagerParamList": [ "pattern" ] } }, "links": { "cAddresses": { "type": "hasMany", "entity": "CAddress", "foreign": "clients", "columnAttributeMap": { "label": "addressLabel" }, "relationName": "cAddressClient" } } }
custom/Espo/Custom/Resources/metadata/entityDefs/CVendor.jsonCode:{ "fields": { "addressLabel": { "type": "varchar", "notStorable": true, "utility": true, "orderDisabled": true, "maxLength": 20, "pattern": "$noBadCharacters", "layoutMassUpdateDisabled": true, "layoutListDisabled": true, "layoutDetailDisabled": true, "layoutSearchDisabled": true, "fieldManagerParamList": [ "pattern" ] } }, "links": { "cAddresses": { "type": "hasMany", "entity": "CAddress", "foreign": "contacts", "columnAttributeMap": { "label": "addressLabel" }, "relationName": "cAddressContact" } } }
Code:{ "fields": { "addressLabel": { "type": "varchar", "notStorable": true, "utility": true, "orderDisabled": true, "maxLength": 20, "pattern": "$noBadCharacters", "layoutMassUpdateDisabled": true, "layoutListDisabled": true, "layoutDetailDisabled": true, "layoutSearchDisabled": true, "fieldManagerParamList": [ "pattern" ] } }, "links": { "cAddresses": { "type": "hasMany", "entity": "CAddress", "foreign": "vendors", "columnAttributeMap": { "label": "addressLabel" }, "relationName": "cAddressVendor" } } }Last edited by bandtank; Yesterday, 05:11 PM.Comment
Comment