Automatic star rating based on likes and dislikes

[Rene_Casana_Ama]

This trick is for the ones that want to make a rating mechanism that depends on the number of likes and dislikes of a given row.

Columns you’ll need:
[likes] - normal column - Decimal - hidden
[dislikes] - normal column - Decimal - hidden
[rating] - virtual column - Decimal - shown

Formula for the column [rating]: IF(AND( [likes]>0, [dislikes]>0), ([likes]/([likes]+[dislikes]))*100.00, 0)

So, based on that formula from above we’ll get a score between 0 and 100 wich is the percentage that the likes represent in the global sum of likes and dislikes (the 100% of ratings). If we divide 100 by 5 (the number of stars we want) we get 20 wich is the ratio of increase we set for each rule. So a score from 1 to 19 is true for 1star rule, 20 to 39 is true for 2stars rule and so on.

Format rules for [rating] column

• 0stars - Highlight color: gray - text color: white - Formula: [rate]=0.00
• 1 star - Highlight color: #FFD300 - Text color: white - Formula: [rate]>0.00
• 2stars - Highlight color: #FFD300 - Text color: white - Formula: [rate]>20.00
• 3stars - Highlight color: #FFD300 - Text color: white - Formula: [rate]>40.00
• 4stars - Highlight color: #FFD300 - Text color: white - Formula: [rate]>60.00
• 5stars - Highlight color: #FFD300 - Text color: white - Formula: [rate]>80.00

We use a custom color #FFD300 because the default yellow doesn’t look so good. And we use white for the text to hide the score. And You’ll get this in your detail view: or this if it has no stars

Actions:
like - Display inline into [rating] - Data: set the values from some columns - [likes]+1
dislike - Display inline into [rating] - Data: set the values from some columns -
[dislikes]+1

Formatting for like and dislike actions

• like action - icon: smile - Highlight color: green
• dislike action - icon: meh - Highlight color: red
  Yu’ll get this in your detail view:

Select “No headings” in your detail view. And customize as you wish. Depending on what you’re doing, this is the final result:

I hope it helps!

[retailpartnerco]

That’s cool!

[Francios]

Cool @Rene_Casana_Amaya. Sample app would be appreciated thanks.

[Suvrutt_Gurjar]

This is a nice implementation of ratings system @Rene_Casana_Amaya

Thank you

[Jerry_Lan]

Hi Rene,

Thanks! I understand that you use 5 formatting rules for 5 [rating] scenarios. But format rules, you can chose 1 star but not 5 consecutive stars… . See Picture

[retailpartnerco]

No, each formatting rule only shows up if the rating is above a certain number. Since 100 is above all
Of the set numbers for all 5 rules, 5 stars would show up, but if the value was only above 3 of the set values in the formulas, 3 stars show up.

[Jerry_Lan]

Yes! Got it! Just came back from a long travel. Need to warm up again to appsheet…Thanks!

[Rene_Casana_Ama]

Exactly, that’s why we make five formatting rules, because a single rule won’t give you all the stars you need, each star will show up depending on the score.

Let’s say our item to score has 343 likes and 299 dislikes, with the formula we get a score of 54. The first rule will trigger true and will show the first star because the score is major than 0, the second rule will trigger true too because the score is major than 20 and the third will trigger too because is major than 40, forth and fifth rule won’t trigger showing a star because the score is lower than the vale upon they trigger true wich is 60 and 80 respectively. And we get three stars.

Make sure that all five format rules are applied to the column [score].
I’d like to see the results.

[Francios]

Thanks @Rene_Casana_Amaya.

help.appsheet.com

Creating Public Samples in Your Portfolio

You can share the definition of an application with others by adding it to your Portfolio.

[Fabian_Weller]

@Rene_Casana_Amaya Thank you for sharing this Tipp.
You could add extra format rules to show also grey stars. So you will have also 5 stars in sum like this:

[Rene_Casana_Ama]

That was my first thought when I was making the formatting rules but unfortunately I think this is not possible because each formatting rule overrides the previous colors over the icons so when you apply the last rule all of the other icons adopt the same color, basically we end up with all gray stars.

[romanus]

Thank you @Rene_Casana_Amaya… I have tried and it works… How to limit one user one response (like or dislike)?

[Rene_Casana_Ama]

To do that you’ll need that the people sign in and it has a cost of $5 per user.

If you can’t pay for each user, then what you can do is use a date field that disables the like action after is triggered and enables it again after 1 day passed. This will ensure that the user can give likes only once a day, is not a good solution but is enough to prevent users from overliking an item.

[Grant_Stead]

You could leverage the Device UUID is via CONTEXT(“Device”)

[romanus]

I use one column [Likes] show as Action button, and have another VC showing [Likes] as total of the Likes. What expression to achieve limit by Device UUID using CONTEXT(“Device”), should I add a column of UUID and using SELECT and COUNT?

[Grant_Stead]

I would create a child table of likes…
Everytime they like, or unlike the “parent” item, it fires an action to add/delete a record with the Device UUID and Parent ID datetime, etc… then a VC on the parent item that looks for that… to know if it is liked or not…

You may also want to build an “entry form” in which they can add their name or other info… One way push, security filter set to FALSE… This will allow you collect the device IDs so that you correlate likes with other things down the road, or even perform technical support backend in which you merge multiple devices, etc…

This also creates a referenced situation that you could show that device their “liked” content…

[romanus]

Thank you @Grant_Stead … I will try to do your suggestion…

[Suvrutt_Gurjar]

Some variations/ expansion based on excellent concept shared by @Rene_Casana_Amaya to show half stars and empty stars in addition to full stars

1. The example below shows half star rating such as (0.5. 2.5 etc. on a scale of 5) and also to include the empty stars to always show 5 stars.
   Examples Below:
   

This was done in response to the following question thread.

How to display the star rating from google sheet? Questions

Another option is based on the tip and trick mentioned below and given by @Rene_Casana_Amaya The AppSheet icon set has half star icons. So that may be used. However to show combination of empty, half and full stars, the workaround does require around 13 format rules. So this option will also take a bit longer to set up The test app’s screen with with those many format rules shown below. [Stars]

1. The column in this example where the user enters the numerical rating is called [NumberPart]

Column Type- Decimal, Increase /decrease steps of 0.5, decimal digits of 1 , minimum value of 0 and max of 5

2. The format rules for empty stars are. as below There are 5 such format rules each with one empty star icon each.

   A) [NumberPart]< 4.5
   B) [NumberPart]< 3.5
   C) [NumberPart]< 2.5
   D) [NumberPart]< 1.5
   E) [NumberPart]< 0.5
   Example Below:
   

   

3. The format rule for half star rating is as below. Only one rule for half Icon

OR([NumberPart]=0.5 , [NumberPart]=1.5,[NumberPart]=2.5, [NumberPart]=3.5,[NumberPart]=4.5)

Example Below:

4. The format rules for full stars are as follows. here are 5 such format rules each with one * full star icon** each.

   A) [NumberPart]> 4.5
   B) [NumberPart]> 3.5
   C) [NumberPart]> 2.5
   D) [NumberPart]> 1.5
   E) [NumberPart]>0.5

Example Below:

Miscellaneous:
6) Format Rules- Highlight color #FFD300 , Text Color #FFFFFF

7. Attach all the format rules to a text column with app formula “.” one may show this column only in detail and table views. This is done so that the column text does not occupy large space with the icons

8. Some reordering of format rules order may be required so that half star icons appear after full star icons. Initially they appeared in the beginning.

Hi @Kirk_Masden: As you had requested, I have tried to document. Hope it helps.

[Kirk_Masden]

Now I finally understand. Thanks!

[Kirk_Masden]

Thanks to @Suvrutt_Gurjar for this ingenious solution.

I think it’s the best that can be done under the current circumstances but I’m looking forward to the day when a completely text-based solution will be possible.

First, for those of you who may not have see it, I should point out that @Steve has provided us with a simple way of converting numbers into text stars, as long as we don’t need the half stars:

How to display the star rating from google sheet? Questions

Try: left( concatenate( left( "★★★★★", floor([yournumber]) ), "☆☆☆☆☆" ), 5 )

It should also be possible to do the same thing with half stars but, alas, most systems don’t support that yet. Here’s some information about the half star, which has been included in UNICODE:

fileformat.info

STAR WITH LEFT HALF BLACK (U+2BEA)

Get the complete details on Unicode character U+2BEA on FileFormat.Info

This page has a link to a browser test page and that page, in turn, provides a list of fonts that support the half star. I downloaded the two truetype fonts (the third is bitmap and, hence, not really usable) and was pleased with the results:

I think that both are attractive as Japanese fonts and so, as someone who works with that language, I’m happy to add them to my arsenal. However, as far as I can tell, these fonts can’t be added to smartphones. (I may be wrong about this but I couldn’t figure out how to add them to phones.)

This little exercise has taught me that UNICODE is expanding nicely but that we are held back by lack of font support. I hope the day will come when all phones (and, of course, AppSheet fonts) will be able to display anything for which UNICODE has been established but, unfortunately, we don’t seem to be there yet.

[Suvrutt_Gurjar]

Hi @Kirk_Masden,

Thank you.

You have nicely summarized the options and status. Yes, you are right. Unicode based stars or any other emojis will be easier to implement in expressions.

I too explored the Unicode based half star and I also could not insert it in the text expression.

Based on my understanding of unicode characters / emojis, I believe they have following limitations for our extensive use in business apps

1. Most of the emoji set appears to be lacking in emojis suited for business apps. They do have fruits, vehicle types, food and drink etc. but overall for business / industrial app use they have limited options.

2. We have lesser or no control to match their color formatting to match say app theme colors.

3. Most importantly, they are not available uniformly on all OS and devices.

Of course, we can continue to make limited use of unicode / emojis wherever feasible in apps.

[Daisy_Ramirez]

Great stuff! Thanks for sharing!

[jcsme2008]

PLEASE HELP: I used the above and received this error “Arithmetic expression ‘(“0 stars”-“Highlight color: gray”)’ has inputs of an invalid type ‘Unknown’”

I am new to APP Sheet, but working hours to gain experience.

[Kirk_Masden]

Hi @jcsme2008! Sorry that I just noticed your post and that no one else responded. I’m not sure if there’s a sample app that shows how to do this but I’m hoping one will become available. I’ve found sample apps to be a very valuable learning tool.

[Kirk_Masden]

Rene_Casana_Amaya:

Hi @Francios, here’s the app but I don’t know how to make it a sample app.

Hi @Rene_Casana_Amaya! I’ve returned to this helpful tip of yours because I just saw an inquiry about incorporating ratings into AppSheet I’d I thought I’d share your tip. In reviewing your post, I noticed that you had written that you weren’t sure how to make your app a “sample.” I too have found the process of making sample apps (particularly samples that you prefer not to keep on your portfolio page) a bit confusing so I made the following tip to try to layout the process in detail:

How to share your app as a template / sample app via your portfolio and/or a URL Tips & Tricks 💡

In this post, I’m revisiting topics that have been covered several times in “Tips” and other posts in this community. None of the information in this tip is new but I thought I’d glean the best information from the various posts that have been made so far and put it in one “tip” for easy reference. I’ve appended a set of links to previous posts that I have referred to in putting this together for those who might be interested. There are two ways in which one can share a sample app (now referr…

If you still have the app you made and would be willing to make it into a sample that others can refer to, please take a look at my tip.

[Rene_Casana_Ama]

Hi! 

Thank you very much for this, I'll make a sample app. I hope is not too late to do it.

[nathaniel_fishe]

Instead of making the text white you can type the word transparent which means when grouping data it wont show white text on grey background

[dunn]

Thanks for sharing everyone. This is exactly what I was searching for.

[Rene_Casana_Ama]

Great!, check out the comment from Suvrutt_Gurjar It's a better way to do it and It's more detailed for you to understand.