Skip to main content
Solved

Automate "5-stars" Rating Field

M
Forum|alt.badge.img+6
  • MCC
  • Known Participant
  • 15 replies

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! ๐Ÿ™‚

Best answer by Sho

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

 

View original
Did this topic help you find an answer to your question?

7 replies

S
Forum|alt.badge.img+19
  • Sho
  • Inspiring
  • 560 replies
  • September 20, 2023

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 "โญ"

ScottWorld
dilipborad
2 people like this
  • ScottWorld
    ScottWorld
  • dilipborad
    dilipborad
A
Forum|alt.badge.img+3
  • Airbid
  • Participating Frequently
  • 13 replies
  • September 20, 2023

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

M
Forum|alt.badge.img+6
  • MCC
  • Author
  • Known Participant
  • 15 replies
  • September 21, 2023
Sho wrote:

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 "โญ"


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.

S
Forum|alt.badge.img+19
  • Sho
  • Inspiring
  • 560 replies
  • Answer
  • September 21, 2023

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

 

M
1 person likes this
M
Forum|alt.badge.img+6
  • MCC
  • Author
  • Known Participant
  • 15 replies
  • September 22, 2023
Sho wrote:

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

 


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)? ๐Ÿ™‚

S
Forum|alt.badge.img+19
  • Sho
  • Inspiring
  • 560 replies
  • September 22, 2023

Yes, this formula ignores blank fields and averages them.

M
Forum|alt.badge.img+6
  • MCC
  • Author
  • Known Participant
  • 15 replies
  • September 22, 2023
MCC wrote:

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 sorry please ignore, it is working perfectly! thanks again! you are an absolute star! ๐Ÿ™‚

Reply


Most helpful members this week

Terms & Conditions