Automate "5-stars" Rating Field

[MCC]

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).

Could anyone please help out?

Thanks! 🙂

[Sho]

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]

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]

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 · Accepted solution]

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]

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]

Yes, this formula ignores blank fields and averages them.

[MCC]

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