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?
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.
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.
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.
