Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Sep 19, 2023 05:52 PM
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! 🙂
Solved! Go to Solution.
Sep 21, 2023 12:54 AM
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
)
)))
)
Sep 19, 2023 06:39 PM - edited Sep 19, 2023 08:38 PM
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 "⭐"
Sep 20, 2023 09:45 AM
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
Sep 20, 2023 09:12 PM
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.
Sep 21, 2023 12:54 AM
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
)
)))
)
Sep 22, 2023 12:41 AM
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)? 🙂
Sep 22, 2023 12:45 AM
Yes, this formula ignores blank fields and averages them.
Sep 22, 2023 12:46 AM
@Sho sorry please ignore, it is working perfectly! thanks again! you are an absolute star! 🙂