User specific column for 'rating out of 10'

I have a list of items. The app allows users to rate the items by inputting a user specific score (1 - 10). The values are stored in a data column that is specified to be a number, it is restricted to values 1 - 10 and is user specific. So far so good. For example, an item is an apple and receives 3 user ratings : 8,9 & 10.

How can I have add a new data column that calculates the average rating for the apple, e.g. 9 out of 10? Will it work if I simply add a maths column and use Average() ? Will it see the rating entry has many user values or will it only âsee the single value enteredâ by the current user?!

Thanks for any pointers.

1 Like

First up, if you want an average, you must catch those ratings in separate rows, in another sheet.

Then you can make a relation that match the item name in the current sheet with the item name in the newly created sheet, set to match multiple, then rollup the average of that relationâs ratings.

We donât have rollup over user-specific columns now.

2 Likes

Thank you.

This is a bit beyond meâŚ do you have an example of the first part âyou must catch those ratings in separate rows, in another sheetâ?

Where do those ratings go? In my Users sheet?

No, you should create a new sheet, letâs call it âRating submissionsâ.

The structure should be: Email | Item ID/name | Rating (number)

1 Like

I have another idea that wouldnât require additional rows in another sheet, but first I have a question. Once a rating is submitted, can the user ever change it again?

To answer your last question, no you canât use a math column to average a user specific column value from all users. The calculation happens on the userâs device and the only value on the device is the value thatâs specific to the user. There is no access to the user specific value of other users.

1 Like

Increment on 2 columns then a math to divide I guess?

1 Like

Hi Jeff. - thanks for responding.

I would be fine if users were only able to submit a rating once. Does that help?

2 Likes

Sorry for the delay.

If a user wonât be allowed to change the rating, then what Iâm thinking is that you can use something like a choice component so a user can choose a rating and it will be saved to the user specific column you already created. Next you would create two non-user specific columns. One to hold a âtotal ratingâ amount, and one to hold a âtotal countâ. Next youâll probably need another user specific numeric column to hold the âsaved ratingâ value to indicate if a user âratedâ the item or not. Now you can create a button or some other component that will allow you to create a custom action. In the custom action, you can use the increment action to increment the âtotal ratingâ column by amount the user chose, create another increment action to increment the âtotal countâ by 1, and finally, the set column action to save the rating value to the âsaved ratingâ, so we know if they have already rated the item or not.

Now that we have the columns filled, you can create a math column to calculate the average from the âtotal ratingâ and âtotal countâ columns and display the average in the item details. Also, we can set visibility to hide the choice component and button once the âsaved ratingâ column is greater than 0 so the user cannot update the rating again.

If you ever wanted to allow the user to change their rating, then there are a few extra steps. First we will need a math column that takes the âsaved ratingâ value and multiplies by -1. Instead of hiding the choice and button components, they would always be visible. What we then have to do is modify the custom action to first perform an IF to check it the âsaved ratingâ is 0. If it is then it can perform the rest of the actions that we set before. Else, we will need to first increment the âtotal ratingâ, by the negative amount calculated in the math column. Next we will need to increment the âtotal countâ by -1. Finally, now that we have reversed the previous rating, we will need to set up the rest of the actions like we did before to add the new rating through increments and set set columns.

Hope that helps.

5 Likes

Great !!

I achieved something similar here. It uses compound actions. I will create a tutorial soon.

4 Likes

@Manan_Mehta that would be helpful, thanks

1 Like

So, something similar to this, I have custom ratings for my app (user-specific) and want to build a pseudo recommendation system or at least show users similar elements that other rated similarly. Thoughts?

So letâs say you rated item A 4 stars, you want to show all items that belong to the same category as item A, and also rated 4 stars or above?

Kind of, I guess Iâm not asking the question good. I started a new topic with a better question here: Recommendation System - Like Netflix