Skip to main content

Can someone assist me with writing this formula? I'd like to basically have a column in which the staff or faculty nominee column is combined with the value. For example: Cameron, Ali - Innovative Spirit

I'm running into an issue were if the staff column is not populated the staff one is, and I need the names to pull from either or both- I tried using and "if" statement but am not having any success. Additionally, if there are multiple names linked, I'd like to separate them by a comma. Screenshot attached for reference of the table!

With reference to your screenshot, I assume you want it to display like the following, is that right?
 - Cameron, Ali - Innovative Spirit, Barnsby Alisha - Innovative Spirit

If so, I think this might be a lot more complicated than one would initially assume I'm afraid, and you'll need to create some new fields to handle it:


Note that for the rollup fields you'll need to specifically use "ARRAYJOIN()" too

Here's a link to the base so you can copy the set up

I've attached the formula I ended up coming up with below, but it feels...overly complicated to say the least.  Hopefully someone else has a more elegant solution!

SUBSTITUTE(
SUBSTITUTE(
IF(
{Staff Nominee},
SUBSTITUTE(
{Commas replaced (from Staff Nominee)} & "",
",",
" - " & Award & "$$"
),
SUBSTITUTE(
{Commas replaced (from Faculty Nominee)} & "",
",",
" - " & Award & "$$"
)
) &
" - " &
Award
, "|", ","
),
"$$",", "
)

It is great seeing different solutions to the same problem. 


I chose to tackle it slightly differently.  This ultimately depends on how you will use the data.


I took the liberty of assuming that you have a persons table that is has a type of staff or faculty.


 


Next is to have nominations linked to the People table.



 


Finally, I make the month a link to its own table. That is where I chose to combine your nominations using a rollup field.



This will scale for as many nominations as you need.  Requires no formulas.


 

Reply


Terms & Conditions