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

**bandtank** · 10-14-2023, 07:18 PM

Part 3: Change the role field to be an enum instead of a varchar to give the user a list of options

Changing role from varchar to enum allows you to present the user with a list of options. You can accomplish this in the following ways:

Store a list of generic options that every Contact to Vendor relationship will have to use for the value of role
- The list of options could be Pizza, Cheese, and Paper.
- Every Contact that relates to a Vendor will have to choose between Pizza, Cheese, and Paper as the value of role.
Store a list of custom options in each Vendor record, which allows the user to set a value for role that is stored specifically in the related Vendor record.
- The list of options is stored in the Vendor record. Vendor A could store Desk, Chair, and Table as the options while Vendor B could store Grass, Tree, and Flower as the options.
- Contacts related to Vendor A would be able to choose from Desk, Chair, and Table as the value of role. Contacts related to Vendor B would be able to choose from Grass, Tree, and Flower as the value of role.
- This method is what you see when you relate a User to a Team.

I will update this tutorial in the future to explain the steps involved with each process. The newer view called link-multiple-with-columns implements enums in a different way than the older view called link-multiple-with-role. I do not yet know how to use the newer view to accomplish both methods. For now, you can see an example of each method by searching for the aforementioned views in client/src/.

**bandtank** · 10-14-2023, 07:44 PM

To update the value of role using the ORM, there are multiple methods.

Method 1 - relate()

PHP Code:

$vendor = $this->em->getRDBRepository(Vendor::ENTITY_TYPE)->where(...)->findOne();
$contact = $this->em->getRDBRepository(Contact::ENTITY_TYPE)->where(...)->findOne();

$this->em->getRDBRepository(Vendor::ENTITY_TYPE)->getRelation($vendor, 'contacts')->relate($contact, array('role' => "New Role Here"));

Method 2 - queryBuilder()

PHP Code:

$vendor = $this->em->getRDBRepository(Vendor::ENTITY_TYPE)->where(...)->findOne();
$contact = $this->em->getRDBRepository(Contact::ENTITY_TYPE)->where(...)->findOne();

$updateQuery = $this->em->getQueryBuilder()->update()->in('ContactVendor')
->set(array('role' => "New Role Here"))
->where(array(
  'contactId' => $contact->getId(),
  'vendorId' => $vendor->getId(),
))
->build();

$this->em->getQueryExecutor()->execute($updateQuery);

To find records based on the value of role using the ORM, there are multiple methods.

For example, this is a filter in custom/Espo/Modules/MyModule/Classes/Select/Vendor/BoolFilters/OnlyOwners.php that finds Vendors with Contacts who have a role of Owner.

Code:

<?php

namespace Espo\Modules\MyModule\Classes\Select\Vendor\BoolFilters;

use Espo\Core\Select\Bool\Filter;

use Espo\ORM\Query\{
  SelectBuilder,
  Part\Where\OrGroupBuilder,
  Part\WhereClause,
};

class OnlyOwners implements Filter
{
  public function __construct() {
  }

  public function apply(SelectBuilder $queryBuilder, OrGroupBuilder $orGroupBuilder): void
  {
    $queryBuilder
      ->leftJoin('contactVendor', 'cv', ['cv.vendorId:' => 'vendor.id'])
      ->leftJoin('contact', 'c', ['cv.contactId:' => 'c.id']);

    $orGroupBuilder->add(
      WhereClause::fromRaw(array(
        'cv.role' => "Owner",
      ))
    );
  }
}

Here is a query builder statement on its own:

Code:

$selectQuery = $this->em->getQueryBuilder()
  ->select(['id'])
  ->from(Vendor::ENTITY_TYPE)
  ->leftJoin('contactVendor', 'cv', ['cv.vendorId:' => 'vendor.id', 'cv.deleted' => false, ])
  ->leftJoin('contact', 'c', ['cv.contactId:' => 'c.id', 'c.deleted' => false, ])
  ->where(['cv.role' => "Owner"])
  ->build();

$pdoStatement = $this->em->getQueryExecutor()->execute($selectQuery);
$rows = $pdoStatement->fetchAll(\PDO::FETCH_ASSOC);

**bandtank** · 10-15-2023, 03:25 PM

Part 4: Add custom list views in related entities that show the role

To create a custom list layout for an entity in another entity, you must first create the new layout. In this example, I will create a new list layout for the Account entity, which will be viewed in the Contact entity. I'm using Account and Contact because they have a more difficult relationship to manage than any of the other entities.

Goal: Display the list of Accounts related to a contact in the relationship section of a Contact record with the associated title/role.

Step 1 - Create the new layout
Create the following file: custom/Espo/Custom/Resources/layouts/Account/listForContact.json. The file defines a layout that will be used to display Accounts in the relationship panel of a Contact record. Add this code to the file:

Code:

[
    {
        "name": "name",
        "link": "true"
    },
    {
        "name": "contactRole"
    },
    {
        "name":"emailAddress"
    },
    {
        "name":"phoneNumber"
    }
]

Note the name of the second field. contactRole is defined in application/Espo/Modules/Crm/Resources/metadata/entityDefs/Account.json. It is populated by the string from the middle table column called role in the accountContact table, which is the same concept as the role field we defined earlier for the Vendor to Contact relationship in the vendorContact table.

Step 2 - Define the layout
Now that the layout file has been created, it must be made available as one of the layouts for the Account entity. Create or edit the following file: custom/Espo/Custom/Resources/metadata/clientDefs/Account.json. Add the following code:

Code:

{
    ...

    "additionalLayouts": {
        ...
        "listForContact": {
            "type": "listSmall"
        }
    }
}

You should now see the layout in the UI:

Because the contactRole (shown above as Title) field only makes sense in the context of a contact, we must limit the availability of the field to only be shown in the list layout called listForContact. The field is defined by default here: application/Espo/Modules/Crm/Resources/metadata/entityDefs/Account.json. Create or edit a custom entity definition file here: custom/Espo/Custom/Resources/metadata/entityDefs/Account.json. Add the following code:

Code:

{
    ...
    "fields": {
        ...
        "contactRole": {
            "layoutAvailabilityList": ["listForContact"]
        }
    },
    ...
}

Step 3 - Use the layout in the relationship panel for Contacts
Create or edit the following file: custom/Espo/Custom/Resources/metadata/clientDefs/Contact.json. Add the following code:

Code:

    "relationshipPanels": {
        "contacts": {
            "layout": "listForContact",
            "orderBy": "name"
        },
        ...
    },

There is one more critical step before the list will work correctly. The accounts field in the Contact entity is not allowed to be added to a relationship panel due to the default entity definition of the Contact entity. You must reverse that setting by creating or editing the following file: custom/Espo/Custom/Resources/metadata/entityDefs/Contact.json. Add the following code:

Code:

{
    ...
    "links": {
        ...
        "accounts": {
            "layoutRelationshipsDisabled": false
        },
        ...
}

Changing the setting for layoutRelationshipDisabled to false is a step you do not need to take for the Vendor to Contact relationship. At this point, you should see the roles in the relationship panel of the Contact entity for the related Accounts. Notice the roles are the same as the roles for the contacts in the Accounts field of the Overview panel:

Step 4 - Change the label
Create or edit the file here: src/files/custom/Espo/Custom/Resources/i18n/en_US/Account.json. Add the following code:

Code:

{
    "fields": {
        ...
        "contactRole": "Role"
    },
    ...
}

Set the value for contactRole. You should now see the new label in the UI:

I would suggest removing the Accounts field from the Overview panel to prevent the same information from being displayed twice.

**manhtq** · 11-02-2023, 09:45 AM

this one is helpful. thank you so much

**Letsgetlost** · 11-28-2023, 01:01 PM

First of all, thank very much bandtank. Nonetheless, after having completed Part. 2, I still don't have any value in the new column "role" in the middle table. I am using EspoCRM version 8.0.5. While EspoCRM is a fantastic tool, still there are parts where one does things without having a clear knowledge of what he is doing. I also read the EspoCRM documentation regarding entityDefs but still it is difficult to understand how to use a feature like AdditionalColumns without a complete example. I just know that to configure the custom entity you can add something like:

Code:

"additionalColumns": {
    "role": {
        "type": "varchar",
        "maxLength": 64 
    }
}

The only difference with the bandtank guide is that at Part. 2 I don't have a "contacts" object in the "links" part of Contact.json configuration file

Code:

"contacts": {
...
    "columnAttributeMap": {
        "role": "vendorRole"
    }
}

Does anyone have any suggestion to have values in the column "role" of the vendor_contact table whenever I add a new vendor?

**bandtank** · 11-28-2023, 02:47 PM

This is a complete example. The relationship in the links area is required for the additional column to populate. You can add the relationship in the Entity Manager through the GUI if you are not familiar with the structure of the json file(s).

Regarding your second question, default values can be populated by adding a default to the additionalColumns.role field. You can also use javascript. It depends how you want to solve the problem. More context and code would be required for a more complete answer to be provided.

When posting on the forum, it is helpful if you use CODE tags to make the code easier to read.

**Letsgetlost** · 11-28-2023, 04:16 PM

When I wrote about complete example I was speaking in general regarding the official documentation (you also noticed that there for several properties it is not clear what they do) and I appreciate very much your guides because they are an important reference when one dare to customize EspoCRM.

My main problem is that I don't know why I don' have values in the "role" column of the middle table when I add a new vendor record.
What I have understood (please correct me if I am wrong) so far is:

At the end of part.1 a new column (named role) has been added to the middle table. I can confirm that I have the new column.
At the end of part. 2 every time a new vendor record is added, the middle table is also updated with a new record which contains a value for all the columns. In my case the "role" column has always a null value

The only difference from my setup and the instruction in part. 2 of your guide is on the "Contact.json" file because I don't understand why, in the code you provided on the guide, in the "links" part of the Contact entity there is a reference to "contacts":

Code:

"contacts": {
...
    "columnAttributeMap": {
        "role": "vendorRole"
    }
}

In my Contact.json file there is only a field of type "linkMultiple" i.e. "vendors" and consequently there is only the object "vendors" in the "links" section.

Can you confirm that I didn't miss any important configuration step and that at the end of Part. 2 the "role" column should have values?

Thank you very much.

Originally posted by bandtank

This is a complete example. The relationship in the links area is required for the additional column to populate. You can add the relationship in the Entity Manager through the GUI if you are not familiar with the structure of the json file(s).

Regarding your second question, default values can be populated by adding a default to the additionalColumns.role field. You can also use javascript. It depends how you want to solve the problem. More context and code would be required for a more complete answer to be provided.

When posting on the forum, it is helpful if you use CODE tags to make the code easier to read.

**bandtank** · 11-28-2023, 10:00 PM

The link to vendorRole is required because that is how the system knows which field to use when storing the value in the middle table. Earlier in contact.json, you had to define a field called vendorRole as a varchar. WIthout the last section, the value you type in the GUI to the vendorRole field will not be stored in the database.

**SoBeGuy** · 05-23-2024, 07:45 PM

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.

**shalmaxb** · 05-23-2024, 08:40 PM

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

**esforim** · 05-24-2024, 02:40 AM

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.

**SoBeGuy** · 05-26-2024, 05:43 PM

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.

**bandtank** · 05-28-2024, 02:27 PM

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.

**bandtank** · 05-28-2024, 05:10 PM

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.

Start a new Espo instance.
Delete custom/Espo/Custom.
Unzip the attached zip file to custom/Espo, which will recreate custom/Espo/Custom
Rebuild Espo
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
Create a new Vendor record
Add or create a contact and assign a vendorRole