Error 500 When Filtering on Many-to-Many Relationship Field in EspoCRM Reports

[mfliorko]

Disclaimer: I used ChatGPT while investigating the issue. Eventually, it prepared the summary I'm publishing below

I’m trying to create a report in EspoCRM (using the Advanced Pack) that lists Accounts with at least one active Contact. In this context, "active" refers to a Contact with the relationship field isInactive set to false. This isInactive field is stored in the intermediary table account_contact, which holds additional attributes for the many-to-many relationship between Account and Contact (specifically, role and isInactive).

Report Configuration:

• Entity Type: Account
• Filter: Contacts.Account Inactive == No (selected directly from the filter list in the report configuration)

Observed Issue:
When I save or run the report, I get an Error 500 with the message Invalid column name. The EspoCRM system log provides further details, showing:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'contacts.account_is_inactive' in 'where clause'

Troubleshooting Steps:

1. Database Check: The account_contact table indeed has role and is_inactive columns, with is_inactive being set to 0 or 1 for active or inactive contacts, respectively.
2. Custom Configuration: The Contact.json metadata in the core application defines the accounts field with "columns": { "role": "contactRole", "isInactive": "contactIsInactive" }, making these fields available in the relationship context.
3. Testing Without Filter: The report works fine without the Contacts.Account Inactive filter, suggesting the issue specifically arises from attempting to filter on this relationship field.

Assumption: It appears that EspoCRM’s reporting system may not natively support filtering on fields stored in many-to-many intermediary tables, such as account_contact. While I can display and manage isInactive through the standard UI (e.g., in detail views of Account or Contact), attempting to filter by it in reports causes an SQL error.

Workaround Attempted: I created a calculated boolean field (hasActiveContacts) in the Account entity using a formula to check for any related Contact with isInactive=false. This approach provides an indirect way to filter accounts based on active contacts, which I can then use in the report filter. While this works, it feels like a workaround, as it requires creating additional fields and recalculations.

Question: Is there a way to directly filter on many-to-many relationship fields (like isInactive in account_contact) in EspoCRM reports? If not, are there any recommended approaches or planned updates to support this kind of reporting natively?

Additional Notes: I want to avoid modifying core files or custom-coding extensively to maintain update compatibility.

[mfliorko]

I also tried to add a custom field "activeContactCount" to Account entity and calculate it with Workflow functionality from the Advanced Pack. TL;DR: it doesn't help.
Below you will find more details formulated with ChatGPT.

Attempted Workflow Approach for Updating activeContactCount on Account Based on Contact Relationships


Goal: Automatically update an activeContactCount field on the Account entity whenever there are changes in the isInactive field within the intermediary table (account_contact), or when a contact is added or removed from an account.

Steps Taken:

1. Workflow Setup:
   • We created a workflow targeting the Contact entity, configured to trigger After Record Saved (created or updated).
   • Conditions were set to detect changes in either the accounts field or the isInactive attribute to capture any modifications in the account-contact relationships.
   • An Update Related Records action was used to iterate over the related accounts and recalculate activeContactCount using the formula:javascript
     

     activeContactCount = countRelated('contacts', 'isInactive=false');

2. Test Scenarios and Observations:
   • Scenario 1: We modified the isInactive attribute in a contact’s relationship with an account. The workflow did not trigger, and activeContactCount remained unchanged.
   • Scenario 2: We removed an account from the contact's associated accounts, saved it, then re-added it. The activeContactCount field on the account still did not update.

Conclusion: The workflow approach does not work in this context because changes in the intermediary table (account_contact) do not trigger workflows on the Contact entity. EspoCRM’s workflow engine currently only recognizes direct changes in the primary fields of the target entity (in this case, Contact) and does not detect modifications in many-to-many relationship tables. As a result, it does not trigger updates when only the relationship attributes (like isInactive) are modified.

[yuri]

Hi,

I believe we should have made "Contacts . Account Inactive" not available in Reports. It can't be accessed this way.

[mfliorko]

Hi,

I believe we should have made "Contacts . Account Inactive" not available in Reports. It can't be accessed this way.

Agree. It's better to hide non-working features rather than mislead users.

The described scenario (get a list of accounts with active contacts) is quite popilar. And the report feature will be appreciated.