Count / Aggregate Many-To-Many Relationships

[TutorEmpire]

Hi there!

I’m trying to compute statistics and reports based on a many-to-many relationship, and I’m having a hard time getting this data into reports or columns.

I’m building a CRM that has Contacts. Each Contact has a ‘Status’ and links to one or more ServiceType(s) via a multi-field.

I add several service types to each contact, but is it possible to see how many Contacts there are for each service type?

I can ALMOST get this data using a JOIN in Data sources creator in the Reporter:

However, what I would need to do is to do an “Aggregate” on the JOIN data source, but that doesn’t seem to be an option:

Ideally, I’d love to be able to compute statistics such as "What percent of each service type has a specific Status (i.e. how many leads were closed by type). As far as I can tell, if the above problem can be solved, the rest is easy.

Thanks so much for making this great software - please let me know if I can be of any help in solving this.

[TutorEmpire]

After playing around with Corteza a bit more, it seems like what I’m looking for can be done with a workflow or automation populating some fields in my database based on computations.

This doesn’t seem like the most elegant solution since it’s replicating db query functionality (joins/etc), but I’m curious to hear how other people handle these kinds of situations / what the ‘best practices’ are.