Skip to main content

Combining fields in table into formula

  • January 30, 2024
  • 2 replies
  • 25 views
F
Forum|alt.badge.img+9

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!

1 Attachments

2 replies

TheTimeSavingCo
Forum|alt.badge.img+31

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 , "|", "," ), "$$",", " )
Adam, Airtable Consultant - thetimesaving.company
Dan_Montoya
Forum|alt.badge.img+17
  • Dan_MontoyaEmployee
  • Employee
  • February 2, 2024

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.

 

Terms & ConditionsAccessibility statement