Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 11, 2020 08:48 AM
Hi!
Can someone tell me why my formula is not working?
IF({Rating}=1,
” :star: ”,
IF({Rating}=2,
” :star: :star: ”,
IF({Rating}=3,
” :star: :star: :star: ”,
IF({Rating}=4,
” :star: :star: :star: :star: ”,
IF({Rating}=5,
” :star: :star: :star: :star: :star: ”,
)))))
Basically, I want to convert a lookup value of integers into an emoji.
Many thanks.
Solved! Go to Solution.
Jun 14, 2020 12:33 PM
Jun 11, 2020 09:00 AM
What field type is your {Rating}
field? Is it a number that you’ve manually entered? Is it a lookup/rollup from another table? Is it Airtable’s own rating field?
Type aside (for now), you could simplify this by using the REPT()
function, which repeats a given item a specified number of times.
REPT("⭐️", Rating)
If {Rating}
is 5, you’ll get five stars. If it’s 50, you’ll get 50 stars. The number directly drives the output.
Jun 11, 2020 10:06 AM
Your formula has an extra comma at the end. That is why it isn’t working. You can remove the last comma before the closing parenthesis. (This is a very common error.)
IF({Rating}=5,
"⭐️⭐️⭐️⭐️⭐️", <=== here is the extra comma
)))))
Or you can use @Justin_Barrett’s formula, which is a bit more flexible.
Jun 12, 2020 12:29 AM
You are right - it is a lookup. I have used your suggestion and it works. I didn’t know we didn’t even need {braces}!
Jun 12, 2020 12:30 AM
I tried it without the comma, it still didn’t work. Strange…
Jun 12, 2020 12:56 AM
It didn’t work because the field was a lookup field. Sometimes there are multiple issues in a formula. I’m glad you got it sorted out.
Jun 12, 2020 01:14 AM
Since I have you two experts, can I ask if there is a sleeker way to write this formula?
{K1 age} & IF(AND({K1 age}, OR({K2 age}, {K3 age}, {K4 age}, {K5 age})), ", ")
& {K2 age} & IF(AND({K2 age}, OR({K3 age}, {K4 age}, {K5 age})), ", ")
& {K3 age} & IF(AND({K3 age}, OR({K4 age}, {K5 age})), ", ")
& {K4 age} & IF(AND({K4 age}, {K5 age}), ", ")
& {K5 age}
Basically I have 5 fields of kids ages (K1 to K5), but not everyone has 5 kids. I want to put all the ages together e.g. 10, 5, 2 and not have a result like 10, 5, 2, , , . So that was the formula I used - it works but I thought there might be a better way.
Thanks so much for your help so far!!
Jun 12, 2020 08:24 AM
Braces are only required if the field name contains spaces and/or certain special characters. For example, if you use an emoji as a field name (which is valid), it will require braces.
Yes, this can be simplified. All you need to do with each field after {K1 age}
is look to see if its value exists. If it does, add the comma and the field value.
{K1 age} & IF({K2 age}, ", " & {K2 age})
& IF({K3 age}, ", " & {K3 age})
& IF({K4 age}, ", " & {K4 age})
& IF({K5 age}, ", " & {K5 age})
Jun 14, 2020 01:46 AM
Thanks Justin. It works! But what if the first value is a null but the second is not a null? how can i tweak your formula to still get a 5, 2 and not a , 5, 2, ?
Jun 14, 2020 12:33 PM