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