I recently came across an issue where I needed to create a view with a grouping based on a related table. It required a comparison of the current table and a related table field, however I was unable to achieve this in a group.

To solve this, I created a new calculated field which involves both tables and used it in the filter which resolved the issue.

In detail, I had a requirement to group several filters in a view using the current table and a related table field. The requirement is that the related table field is greater than current table field or that the related table field does not contain data. Both fields are date only fields, hence I created a new calculated field with difference in days logic. Most of you might already know how to use a related table field in a calculated field, however, let me explain it for those who don’t know how to achieve this.

In calculated fields, we can get the fields of the current entity just by typing the schema name of the field. To get the field from a related table we must enter the schema name of the lookup field to the related table then enter a ‘.’ and the schema name of the field from that table and select from the options. For example, if the related table schema name is crm_quote and field is crm_datecompleted then the logic will be as follows crm_quoteid.crm_datecompleted.

Below is the filter I created. The first condition is to check that the calculated field is greater than or equal to 1 and it resolved the first filter condition. Then for the second condition I used the calculated field does not contain data because if any of the fields we are using for the calculation does not contain data then the calculated field value will be empty.

Hope this helps!