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.

[peter]

Hi @TutorEmpire ,

we are using custom views for cases like this.

In a nutshell, you can create a view which fetches data from compose_record for this specific module, do any joins or aggregates you want.

Then create a new module and set the data source as the view you defined. The fields you create can be either columns or again a json blob, but it’s easier to handle columns imo.

So a Foo module would in the easiest example have a bar string field.
create or replace view foo_view as select 1 as bar;

Then you would define the bar field as an alias in the Data store tab.

Should you use a json structure for fields (so group them and have them in one field):
create or replace view foo_view as select '{"bar":["1"]}'::json as values; and define bar field as a JSON.

Don’t hold me up for syntax, it’s too early

Documentation is here: Customizing Record Storage :: Corteza Docs

Hope that helps.

[TutorEmpire]

Interesting workaround - my mind is blown! This is super clever - I’ll play around with it if working within the system directly gets too annoying.

Thanks!

[walychado]

Hi, I’m stucked as @TutorEmpire with the same scenario. I would like to implement what @peter mentioned, but it would be great to have some more details on how this is achieved. Can anyone give an concrete example of this?

[jfortun]

Which part requires more explanation?