Help

Airtable Cobuilder is here! Learn more about our new no-code app creation feature, powered by AI on the Airtable Academy

Re: Automate "5-stars" Rating Field

Solved
Jump to Solution
2214 0
cancel
Showing results for 
Search instead for 
Did you mean: 
MCC
6 - Interface Innovator
6 - Interface Innovator

Hi,

I am trying to create an automation where:

If "Adviser 1 ranking" column has been updated as "Outstanding", then the "Adviser 1 rating" column will automatically have 5 stars appearing. Same goes to any update on "Adviser 2 Ranking" will update the "Adviser 2 rating", "Adviser 3 Ranking" with "Adviser 3 rating". 

In all 3 single select Adviser ranking fields, I have "Outstanding" (i.e. 5 stars), "Very good" (i.e., 4), "Good" (i.e.,3), "Poor" (i.e.,2), "Try Again" (i.e.,1).

MCC_0-1695171000852.png

Could anyone please help out?

Thanks! 🙂

1 Solution

Accepted Solutions
Sho
11 - Venus
11 - Venus

Both Stars in the formulas and Rating fields are the same in content with numbers.

For example, it can also be done this way without the Rating field.

IF(
  OR(
    {Adviser 1 Rank},
    {Adviser 2 Rank},
    {Adviser 3 Rank}
  ),
  REPT("☆", ROUND(AVERAGE(
    SWITCH({Adviser 1 Rank},
      "Outstanding",5,
      "Very good",4,
      "Good",3,
      "Poor",2,
      "Try Again",1
    ),
      SWITCH({Adviser 2 Rank},
      "Outstanding",5,
      "Very good",4,
      "Good",3,
      "Poor",2,
      "Try Again",1
    ),
      SWITCH({Adviser 3 Rank},
      "Outstanding",5,
      "Very good",4,
      "Good",3,
      "Poor",2,
      "Try Again",1
    )
  )))
)

 

See Solution in Thread

7 Replies 7
Sho
11 - Venus
11 - Venus

Hi @MCC,

Do you need to use a Rating field?

It is more eco to use in the formula field than to use automation.

 

SWITCH({Adviser Ranking Fields},
  "Outstanding",REPT("",5),
  "Very good",REPT("",4),
  "Good",REPT("",3),
  "Poor",REPT("",2),
  "Try Again",REPT("",1)
)

 

PS: "" in the code has lost the emoji Replace with ""

Airbid
6 - Interface Innovator
6 - Interface Innovator

You can add another field that compares the values in Sho’s field with your rating field, then use that to trigger an automation to copy the value over to the ratings field if you want to use the rating instead of the numerical values

MCC
6 - Interface Innovator
6 - Interface Innovator

Hi Sho, 

Thank you for the information. Yes, we would need the "Rating" field as we have another formula column that will auto average the three adviser's rating into one final rating. Or could you please kindly suggest any set up that will be more efficient? Happy to hear your thoughts. 

Basically our aim is to:

1. All 3 advisers will be ranking the same application - i.e., Adviser 1 rank application A "Good", but Adviser 2 may think it is "very good", and Adviser 3 may also rank it as just "Good"

2. There should be a column that takes all three different rankings and then average them out into a final "ranking".

Therefore, currently we only could think of automating the rating field where once adviser update their respective ranking field, the corresponding rating field will also be updated into say 3 or 4 stars. Then the formulated Final Ranking column will show how many stars as a final answer if that makes sense?

I trialled changing my "Rating" field to formula field and it does not auto update when I change the "Ranking" field though.

Sho
11 - Venus
11 - Venus

Both Stars in the formulas and Rating fields are the same in content with numbers.

For example, it can also be done this way without the Rating field.

IF(
  OR(
    {Adviser 1 Rank},
    {Adviser 2 Rank},
    {Adviser 3 Rank}
  ),
  REPT("☆", ROUND(AVERAGE(
    SWITCH({Adviser 1 Rank},
      "Outstanding",5,
      "Very good",4,
      "Good",3,
      "Poor",2,
      "Try Again",1
    ),
      SWITCH({Adviser 2 Rank},
      "Outstanding",5,
      "Very good",4,
      "Good",3,
      "Poor",2,
      "Try Again",1
    ),
      SWITCH({Adviser 3 Rank},
      "Outstanding",5,
      "Very good",4,
      "Good",3,
      "Poor",2,
      "Try Again",1
    )
  )))
)

 

MCC
6 - Interface Innovator
6 - Interface Innovator

Omg it worked! Thank you so much!

1 final question, if I were to allow the formula to be a bit more flexible (i.e., if only 2 advisers had time to rank it, could it take the average of 2 instead of 3)? 🙂

Sho
11 - Venus
11 - Venus

Yes, this formula ignores blank fields and averages them.

MCC
6 - Interface Innovator
6 - Interface Innovator

@Sho sorry please ignore, it is working perfectly! thanks again! you are an absolute star! 🙂