Help

Combining fields in table into formula

Topic Labels: Base design Formulas Views
561 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Faculty_Schedul
6 - Interface Innovator
6 - Interface Innovator

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!

2 Replies 2

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:

Screenshot 2024-01-31 at 9.20.55 PM.png
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
    , "|", ","
  ),
  "$$",", "
)
Dan_Montoya
Community Manager
Community Manager

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.Screenshot 2024-02-01 at 6.39.10 PM.png

 

Next is to have nominations linked to the People table.

Screenshot 2024-02-01 at 6.40.25 PM.png

 

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

Screenshot 2024-02-01 at 6.41.32 PM.png

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