Adding columns to middle tables in many-to-many relationships

Collapse
X
 
  • Time
  • Show
Clear All
new posts

  • bandtank
    replied
    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
    Code:
    {
        "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/Contact.json
    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": "contacts",
                "columnAttributeMap": {
                  "label": "addressLabel"
                },
                "relationName": "cAddressContact"
            }
        }
    }​
    custom/Espo/Custom/Resources/metadata/entityDefs/CVendor.json
    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; 05-28-2026, 05:11 PM.

    Leave a comment:


  • bandtank
    replied
    Starting with the CAddress entity definition, I added six fields and three links:

    custom/Espo/Custom/Resources/metadata/entityDefs/CAddress.json

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

    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
    Code:
    {
        "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/Account.json
    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; 05-28-2026, 05:09 PM.

    Leave a comment:


  • bandtank
    replied
    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:

    Click image for larger version  Name:	CleanShot 2026-05-28 at 12.41.48 [email]2@2x.png[/email] Views:	0 Size:	83.5 KB ID:	126598

    In edit mode, here is how the relationship rows look (for the label field, 'Clients' uses enum and 'Contacts' uses varchar):

    Click image for larger version

Name:	CleanShot 2026-05-28 at 12.43.17@2x.png
Views:	64
Size:	41.1 KB
ID:	126599

    Here is how it looks from a client record in the relationship panel:
    Click image for larger version  Name:	CleanShot 2026-05-28 at 12.43.39 [email]2@2x.png[/email] Views:	0 Size:	24.7 KB ID:	126597
    Last edited by bandtank; 05-28-2026, 04:52 PM.

    Leave a comment:


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

    Leave a comment:


  • bandtank
    commented on 's reply
    I did not know that existed. To export the code, I built a new instance of the application using Docker, built the customizations, and then zipped the Custom folder through the command line. I am not sure if the export function would capture the changes for a middle table, but I'll give it a try.

  • bandtank
    commented on 's reply
    I will see if I can customize the relationship you discussed.

  • esforim
    commented on 's reply
    Wow! Did you use the "export" customization feature? I think I will be giving this a try now.

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

    Leave a comment:


  • bandtank
    replied
    SoBeGuy I had some time this morning, so I worked through steps 1 and 2 on a new instance of EspoCRM 8.2.4. Everything worked as expected even though some of the default names are slightly different.
    1. Start a new Espo instance.
    2. Delete custom/Espo/Custom.
    3. Unzip the attached zip file to custom/Espo, which will recreate custom/Espo/Custom
    4. Rebuild Espo
    5. Optionally, add the Vendor entity to the tab bar for easier access. Otherwise, you'll have to access the new entity (CVendor) by using the URL; e.g.: domain.com/#CVendor
    6. Create a new Vendor record
    7. Add or create a contact and assign a vendorRole

    New vendor:
    Click image for larger version  Name:	image.png Views:	13 Size:	35.9 KB ID:	106741

    After adding a contact:
    Click image for larger version  Name:	image.png Views:	13 Size:	44.1 KB ID:	106742

    After adding a value to vendorRole and saving the Vendor record:​
    ​Click image for larger version  Name:	image.png Views:	13 Size:	39.5 KB ID:	106743​​
    Attached Files
    Last edited by bandtank; 05-29-2024, 02:21 PM.

    Leave a comment:


  • bandtank
    replied
    SoBeGuy I will try the instructions again on a new instance to see if anything needs to be modified. Espo has changed since I wrote the tutorial, but I believe the instructions should still work. I'll follow up in a few days to give you an update.

    Leave a comment:


  • esforim
    commented on 's reply
    You are much further than me. Good luck, I can't help since it too much for me to delve into

  • SoBeGuy
    replied
    Originally posted by espcrm

    It is already possible in EspoCRM natively. See Account > Contact Role
    I know that Espo has contactRole in the middle table, but I want to add a contactDepartment field. I followed this tutorial and the field was added to the middle table, but I can't get it to show up in the GUI. For example, when I'm in the Account screen and I link a new contact to the account, it displays the role field, but it doesn't display the department field. I followed the instructions in Part 2 of the tutorial to make the department field visible, but it's not working.​

    Leave a comment:


  • bandtank
    commented on 's reply
    That extension adds similar functionality, but it's not quite the same as the current topic.

  • esforim
    replied
    Originally posted by SoBeGuy
    bandtank Thank you for posting this very comprehensive guide for adding additional relationship columns. Hopefully this functionality will be added to EspoCRM core, or as an extension. Adding additional relationship columns should be as simple as typing the column names into a list box in the Create/Edit Link screens and selecting the data type for each column. EspoCRM should handle the rest automatically.
    It is already possible in EspoCRM natively. See Account > Contact Role

    It just that if you want to do the same thing for your custom field, you need to follow this coding/tutorial guide.

    Looking at the Changelogs, I don't think it be anytime soon that we can easily add this using GUI.

    Leave a comment:


  • shalmaxb
    replied
    extension already exists: https://www.eblasoft.com.tr/espocrm-...le-inline-form

    Leave a comment:

Working...