Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 04, 2021 07:48 AM
I am not sure if this is even possible -
I have a table that has 3 name fields - {Name 1}, {Name 2} and {Name 3}. A formula field {All Names} combines the content of all 3 name fields separated by commas.
I am using this formula:
IF({Name 3}, ({Name 1} & ', ’ & {Name 2} & ', ’ & {Name 3}),
IF({Name 2}, ({Name 1} & ', ’ & {Name 2}),
IF({Name 1},{Name 1}
)))
Is there a way to have the name “ENDING” always be at the end of the string, regardless of the name field it was entered in?
So in my example, the second row would read: “Michael, ENDING”.
If this can’t be done in one formula, maybe a second formula field can re-arrange the order. The name “ENDING” will be consistent across the table.
I can of course make sure that “ENDING” is always the last name to appear in any of the 3 name fields. What I’d like to do is make sure that it is indeed always the final entry in the combined string, regardless of what users enter where.
Thank you so much for pointing me in the right direction.
Solved! Go to Solution.
Mar 07, 2021 02:58 PM
It is possible with a formula field. The difficulty is not in the formula itself. The difficulty is in seeing the patterns to come up with the formula.
Note that I am assuming that “ENDING” will appear at most one time in the list.
IF(
FIND("ENDING, ", {All Names}),
SUBSTITUTE({All Names}, "ENDING, ", "") & ", ENDING",
{All Names}
)
Notice that if the word “ENDING” is the last word in the list, it will never be followed by a comma. However, if it is not the last word in the list, it will always be followed by a comma and a space. So the algorithm becomes this:
"ENDING, "
with the comma and the space."ENDING, "
, remove it including the comma and space, and tack ", ENDING"
on to the end of the result."ENDING, "
, simply use the original string (which will either have ENDING
at the end or not have ENDING
at all.By the way, your screen shot looks like you are used to working with Spreadsheets. Because Airtable is a database, not a spreadsheet, I recommend considering a two table system, one for groups, and another table for people, with linked records between the two to show who is in which group.
Of course, you may have other reasons for your base design, such as a need to be able to manually copy/paste from a spreadsheet.
Mar 05, 2021 04:16 AM
If you could maybe tell the community what are you trying to achieve by putting the ENDING string at the end somebody could help you.
Mar 07, 2021 09:09 AM
I am realizing now, this is too complicated. Better make sure everybody enters the information that should appear last in the correct field…
But thanks for responding, @Raminder_Singh
Mar 07, 2021 02:58 PM
It is possible with a formula field. The difficulty is not in the formula itself. The difficulty is in seeing the patterns to come up with the formula.
Note that I am assuming that “ENDING” will appear at most one time in the list.
IF(
FIND("ENDING, ", {All Names}),
SUBSTITUTE({All Names}, "ENDING, ", "") & ", ENDING",
{All Names}
)
Notice that if the word “ENDING” is the last word in the list, it will never be followed by a comma. However, if it is not the last word in the list, it will always be followed by a comma and a space. So the algorithm becomes this:
"ENDING, "
with the comma and the space."ENDING, "
, remove it including the comma and space, and tack ", ENDING"
on to the end of the result."ENDING, "
, simply use the original string (which will either have ENDING
at the end or not have ENDING
at all.By the way, your screen shot looks like you are used to working with Spreadsheets. Because Airtable is a database, not a spreadsheet, I recommend considering a two table system, one for groups, and another table for people, with linked records between the two to show who is in which group.
Of course, you may have other reasons for your base design, such as a need to be able to manually copy/paste from a spreadsheet.
Mar 08, 2021 12:32 AM
Brilliant! And not too difficult. Thank you so much, @kuovonne! And thank you for not only sending me a solution but also explaining the individual steps to me. This is incredibly helpful to people who don’t work with formulas every day.
My example is not reflective of the actual design of my base. I just set it up as a simplified version to find a solution to my problem. The original base is a true database that keeps track of large amounts of audio metadata for thousands of music tracks. We do take advantage of linked tables and many other advantages a database brings to the table.
The reason for my formula need came up because sometimes we have to arrange and display certain information in a very specific way depending on who it will be sent to eventually.
But you’re right, spreadsheets are part of our workflow. We ingest data that way (thank you Airtable for the CSV Import App!!!) and we have to export CSV files to send data to our clients and to embed it into our audio files.
Honestly, I don’t know what I’d do without Airtable (and without helpful people like yourself).
So, thank you again!