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
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
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!
Thatโs cool!
Cool @Rene_Casana_Amaya. Sample app would be appreciated thanks.
This is a nice implementation of ratings system @Rene_Casana_Amaya
Thank you
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
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.
Yes! Got it! Just came back from a long travel. Need to warm up again to appsheetโฆThanks!
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.
Thanks @Rene_Casana_Amaya.
@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:
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.
Thank you @Rene_Casana_Amayaโฆ I have tried and it worksโฆ How to limit one user one response (like or dislike)?
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.
You could leverage the Device UUID is via CONTEXT(โDeviceโ)
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?
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โฆ
Thank you @Grant_Stead โฆ I will try to do your suggestionโฆ
Some variations/ expansion based on excellent concept shared by @Rene_Casana_Amaya to show half stars and empty stars in addition to full stars
This was done in response to the following question thread.
Column Type- Decimal, Increase /decrease steps of 0.5, decimal digits of 1 , minimum value of 0 and max of 5
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:
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:
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
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
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.
Now I finally understand. Thanks!
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:
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:
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.
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
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.
We have lesser or no control to match their color formatting to match say app theme colors.
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.
Great stuff! Thanks for sharing!
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.
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.
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:
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.
Hi!
Thank you very much for this, I'll make a sample app. I hope is not too late to do it.
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
Thanks for sharing everyone. This is exactly what I was searching for.
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.