# Using rollups and relationships to extract stats

**URL:** <https://community.glideapps.com/t/using-rollups-and-relationships-to-extract-stats/75577>\
**Category:** Ask for Help\
**Created:** [August 10, 2024, 1:00pm UTC](https://community.glideapps.com/t/using-rollups-and-relationships-to-extract-stats/75577 "2024-08-10T13:00:19Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![Mark\_Turrell](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mark_turrell/32/26605_2.png) [@Mark\_Turrell](https://community.glideapps.com/u/Mark_Turrell)\
**Post date:** [August 10, 2024, 1:00pm UTC](https://community.glideapps.com/t/using-rollups-and-relationships-to-extract-stats/75577/1 "2024-08-10T13:00:19Z")

</div>

Hi - I have built a rating system for an app that persuades users to provide a rating for AI responses. Their rating has an overall rating (Good, etc) and an explanation choice, plus a comment. There is some cleverness involved in data presentation and capture (I have a multilingual app, so I collect an index value not the text value, etc)… but these issues have all nicely been solved.

Where I am struggling is that I want to extract relevant statistics to present to managers.

Every line entry has a userID, then a ModuleID (the content), and then there are the ratings themselves.

What I would like to do it use relationships to connect things (all the user data… all the modules), then get a total (easy… roll-up count on one of the values)… then I want to sum up things like ‘how many good’, ‘how many poor’. And once I have this nailed, I can put it into various parts of the stats thing.

Any ideas or suggestions? Thanks!

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [August 10, 2024, 1:58pm UTC](https://community.glideapps.com/t/using-rollups-and-relationships-to-extract-stats/75577/2 "2024-08-10T13:58:27Z")

</div>

So are you storing your ratings row-by-row? If it’s the case, you should be able to query “good” rows, “poor” rows and use rollup columns to count them.

---

<div class="post-metadata">

**Author:** ![Mark\_Turrell](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mark_turrell/32/26605_2.png) [@Mark\_Turrell](https://community.glideapps.com/u/Mark_Turrell)\
**Post date:** [August 10, 2024, 6:03pm UTC](https://community.glideapps.com/t/using-rollups-and-relationships-to-extract-stats/75577/3 "2024-08-10T18:03:20Z")

</div>

I can extracted some numbers… but the underlying rating might have 2, 3… 5… options. And then I have up to 10 possible explanations too. I am thinking that I could roll up each row (as every user rating is it’s own row) and turn it into a json object. Then somehow roll those up… and give it to a ChatGPT created JS to process the data. Anyone have experience of doing this. And thanks for the Query tip, @ThinhDinh !

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [August 10, 2024, 11:57pm UTC](https://community.glideapps.com/t/using-rollups-and-relationships-to-extract-stats/75577/4 "2024-08-10T23:57:51Z")

</div>

Can you show me an example of how your ratings are structured? I’m not sure I get this part.

> [@Mark\_Turrell](#):
>
> but the underlying rating might have 2, 3… 5… options. And then I have up to 10 possible explanations too.

---

<div class="post-metadata">

**Author:** ![Mark\_Turrell](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mark_turrell/32/26605_2.png) [@Mark\_Turrell](https://community.glideapps.com/u/Mark_Turrell)\
**Post date:** [August 11, 2024, 8:34am UTC](https://community.glideapps.com/t/using-rollups-and-relationships-to-extract-stats/75577/5 "2024-08-11T08:34:06Z")

</div>

Here you go - one row of the key data in json:

```auto
{
    "user ID": "pUdAwTpFS1S6wwfVnBdVDQ",
    "user dept": "Strategy",
    "sv-overall type": "-",
    "sv-overall option": "Poor",
    "sv-0": "Properly addressed the topic and my input",
    "sv-1": "+!Provided me with useful insights",
    "sv-2": "+!Gives me ideas on how I could use a tool like this more",
    "module ID": "4ac1efd6-d248-48dc-9d1f-13d83ccc9bcc",
    "session ID": "7d9ecb44-cc59-41ac-8168-9ea3ecf4bc79",
    "campaign ID": "Fgzt35H4Tyyb8aiRP3cLWQ"
}

```

 ![Screenshot 2024-08-11 at 10.32.26](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/4/6/46f7ffe0d59826f70ede0a91d060818ad7d10d52.png)  
So, as a human reading it, Mark (user ID), rated this content (defined by the type and various module IDs) with an overall score (which has + or -, and the word like Great or Good Enough) and an explanation (from a mutlli-choice, and a comment).

Hopefully this helps 🙂

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [August 11, 2024, 1:24pm UTC](https://community.glideapps.com/t/using-rollups-and-relationships-to-extract-stats/75577/6 "2024-08-11T13:24:17Z")

</div>

So you want to somehow extract and roll up all sentiments/data from sv-overall option, sv-0, sv-1, sv-2 etc?

---

<div class="post-metadata">

**Author:** ![Mark\_Turrell](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mark_turrell/32/26605_2.png) [@Mark\_Turrell](https://community.glideapps.com/u/Mark_Turrell)\
**Post date:** [August 11, 2024, 4:32pm UTC](https://community.glideapps.com/t/using-rollups-and-relationships-to-extract-stats/75577/7 "2024-08-11T16:32:13Z")

</div>

I want to do the roll-ups through the module ID, Campaign ID, etc. And I would like to get the totals for each overall option, then explanations (the sv-x values). This is to allow me to say things like:

Module 1 has 75% + ratings, but Module 2 only 35% +ve – and the reasons for 2 are 50% ‘does not pick up my response’, 25% ‘bad formatting’, etc.

Then I can have some stats by user ID - so maybe user A is 90% negative… and then with other stats I might find out User A only puts in 1 word answers (so we can exclude their stats).

Does this help? Thanks!

---

<div class="post-metadata">

**Author:** ![gvalero](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/gvalero/32/1037_2.png) [@gvalero](https://community.glideapps.com/u/gvalero)\
**Post date:** [August 11, 2024, 5:56pm UTC](https://community.glideapps.com/t/using-rollups-and-relationships-to-extract-stats/75577/8 "2024-08-11T17:56:58Z")

</div>

Hola Mark!

Have you tried to use some JSONata commands in order to get what you want. JSONata has some very impressive query capabilities.

Try here: [https://try.jsonata.org/](https://try.jsonata.org/)

Saludos!

---

<div class="post-metadata">

**Author:** ![Mark\_Turrell](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mark_turrell/32/26605_2.png) [@Mark\_Turrell](https://community.glideapps.com/u/Mark_Turrell)\
**Post date:** [August 11, 2024, 9:13pm UTC](https://community.glideapps.com/t/using-rollups-and-relationships-to-extract-stats/75577/9 "2024-08-11T21:13:33Z")

</div>

Solution worked out!

1. json object to for every rating (row)
2. join list to connect all the ratings together
3. template to make a combined json with the context IDs, then the ratings)
4. javascript column to process the combined json (thanks to ChatGPT)
5. then split out the resulting json with a helper table

 ![Screenshot 2024-08-11 at 23.10.37](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/2/f/2fa3e309a241ea01f61fd1b329cc59298dfd8b77.png)

I will tweak it some more to extract all the things I need. A nice combination of techniques - and help! Thank you!

---

<div class="post-metadata">

**Author:** ![gvalero](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/gvalero/32/1037_2.png) [@gvalero](https://community.glideapps.com/u/gvalero)\
**Post date:** [August 12, 2024, 1:25am UTC](https://community.glideapps.com/t/using-rollups-and-relationships-to-extract-stats/75577/10 "2024-08-12T01:25:15Z")

</div>

Hola de nuevo Mark,

My suggestion might look like this:

 ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/4/b/4bf788bdb739191df75e1baae56a38af41b8085b.png)

This may be faster and easier to maintain due to your APP will load/create fewer columns.

Bye!

---

<div class="post-metadata">

**Author:** ![Mark\_Turrell](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mark_turrell/32/26605_2.png) [@Mark\_Turrell](https://community.glideapps.com/u/Mark_Turrell)\
**Post date:** [August 12, 2024, 8:40am UTC](https://community.glideapps.com/t/using-rollups-and-relationships-to-extract-stats/75577/11 "2024-08-12T08:40:40Z")

</div>

I actually get rid of the need for most columns in the rating table by using JS to process the data. I then have to unwrap the json using a jsonata and helper tables to display the results.

I have to show a lot of different views on the same data set, so the JS approach gives me tons of flexibility, thankfully - and I can make the code without having a clue what I am doing, thanks to some ChatGPT magic 😉

 ![Screenshot 2024-08-12 at 10.39.12](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/7/e/7eef2cb736bdf36517c62b3f3849bab3cb68ea22.jpeg)