Grouping Unique Values From Relation Function (Match Multiple)

[Claire_B]

Hi Community! Long time reader, first term poster. I’ve tried to look through the community and youtube for this answer but I’m getting stuck.

CONTEXT

I’m creating an app for a doctor friend that allows people to track health incidents over time such as having an extreme headache, bad bowel movement etc. The idea is that the user can go back over the last few months and see ‘oh wow, I had 11 headaches last month and now only 3 this month, the extra water intake must be working!’

The data I’m collecting from the user via a form is:

• Type of problem (which is linked to a seperate table I have of category types, like headache, bowel/bladder, period pain etc).
• Severity / 10
• Basic notes - where they were, how they felt etc.

Data I’m automatically recording is:

• Date added
• user email (making them the row owner of the data and linking to their profile via another relation column)

In the same table, I am also idenitfying data using calculations like:

• Unique month and year (so they can filter to all ‘June’ incidents)
• Entry name - so they can differentiate between the items on a detail screen.

It looks something like this in the data:

The data is already linked to a row owner, so I know the right rows are shown on my layout. What I am now trying to do, is within an incident detail screen (i.e. Headache Entry, 21 June 2024 at 12:06) is collate all the incidents they have had of in that category and show it on a chart, so they can see:

• Total number of items &
• Average intensity of issue for that month

An example of my screen for items categorised ‘Headaches’ (and the ideal items) looks like this:

THE ISSUE
Glide seems to want to add the intensity/10 numbers together so it shows 27/10, whereas I want it to just show me the average intensity/10 numbers for a single month, and the number of incidents.

I tried to create a helper table with all the months of the year there and then linked it with a relation but as you can see it collects all issues & users for that month. Is there a way to split out the data so I can calculate, for a specific user, their number of headaches, bladder issues per month and the average intensity?

I could have gone about this all wrong, I also wondered if there was a way to do it from the user table. Hopefully this makes sense and let me know if it doesn’t! Thanks in advance.

[ThinhDinh]

Is there a reason you store the intensity in a user-specific column? I know you are using row owners anyway, but would anyone other than the user is supposed to see their data?

For your other question, I would approach it like this:

• Create a query column that targets the Health Diary table, filter by type is this row > type, period is this row > period, and user email is this row > user email (just in case you need to remove the row owner at some point, this doesn’t hurt to add).

• Use a single value column to get the first rowID from the query above.

• Use a rollup column to get the average severity from the query.

• Use a rollup column to count the number of events from the query.

• In your chart, use your query as the source, with the values being the average severity and the count of events as calculated above. Filter the rows by rowID is single value column’s rowID. This is essentially a way to “unique” your records of a type - period - user into a single row, so charts can read the data correctly. Use the period as the X axis.

[Claire_B]

Hi ThinhDinh,

You are a JET!!! Thank you so much, that worked a treat. In answer to your original question, I was worried if two users were completing the form at once they might be able to see each other’s answers, and this was before I put in row owners. Should I change it back?

Your guide was GREAT, thank you so much. I’ll post screenshots below of how I followed your guide in case others need this help too. There was a tiny moment I got stuck but figured it out.

• Create a query column that targets the Health Diary table, filter by type is this row > type, period is this row > period, and user email is this row > user email (just in case you need to remove the row owner at some point, this doesn’t hurt to add).
  
  

Use a single value column to get the first rowID from the query above.

It then looked like this:

Use a rollup column to get the average severity from the query.

Use a rollup column to count the number of events from the query.

In your chart, use your query as the source, with the values being the average severity and the count of events as calculated above.

Filter the rows by rowID is single value column’s rowID. This is essentially a way to “unique” your records of a type - period - user into a single row, so charts can read the data correctly. Use the period as the X axis.

Just to add to this, I also filtered it by TYPE (i.e. Bladder / Bowel) and I assigned a number to each month (i.e. Jan = 1, Feb = 2) and sorted the graph in ascending order so people could read across their charts, no matter the year or spelling of individual months. Added a Year & ‘Period’ Filter too.

Thanks so much again ThinhDinh, I am so happy, you are amazing!

[ThinhDinh]

@Claire_B

You are a JET!!! Thank you so much, that worked a treat. In answer to your original question, I was worried if two users were completing the form at once they might be able to see each other’s answers, and this was before I put in row owners. Should I change it back?

I thought it was your responses table. Can you tell me are you building your form on top of that table?

[Claire_B]

Yes, I am building it through the Health Diary_User Responses Table

[ThinhDinh]

So it’s a different table compared to the one you show above? (Health Diary_User Responses)

[Claire_B]

No, same table - sorry I made a typo. I’ll update that post

[ThinhDinh]

Ok, I thought it was odd you configure your form on top of the responses table. Do I understand it correctly? Can you show me how you configure your form?

[Claire_B]

Sure! Here’s an example of the page below. It writes the information to the Health Diary_User Responses using user-specific columns and then the user email is taken from the user profile. The category of the event is sourced from another table via a drop-down and that response is written in as a user-specific text field (which then has a relation column next to this, but that connects once the user has hit submit)

[ThinhDinh]

If this is a form environment, why do you need user-specific columns?