Hi All - Hoping someone can help me figure out a formula to create the result outlined in the attached image. Involves stringing together text from fields, as well as replacing spaces with underscores, and shortening some Names to be Initials Only, etc.
If possible to create a Loom or Video explaining how to do this, I would be so grateful! I can’t seem to figure out how to properly put all the proper formulas together to make this happen but know it can be done. Thanks!
Hi @Databaser, I was able to figure it out, but I had to create a few separate formulas to do so. I couldn’t figure out how to put all these formulas into 1 field. Ideally that’s what I’d like to do. @Justin_Barrett , any help would be super appreciated!
While it’s possible to make a mega-formula that has everything (see below), the divide-and-conquer approach that you came up with has its benefits, @Tom_Blanchard . To take that a step further, consider adding specific formulas to the tables where the data resides. For example, add the formula that creates the photographer’s initials to the table containing the photographer records. Then you can look up the abbreviation and use that in the assembly formula.
As for the formulas themselves, you came up with some good options. Here are variations to consider for some of them.
For the photographer’s initials (slightly simpler REGEX, plus it will account for hyphenated names; e.g. James Allen-Smith would become JAS):
As a side note, the only part that really needs the space-to-underscore replacement is the project name. While there’s no harm wrapping the REGEX_REPLACE() function around the whole shebang, I prefer to focus function use where it’s actually needed.
If you want to have an all-in-one formula, here you go:
If you follow my tip above and move some formulas into their relevant tables and use lookup fields to pull the result into your main table, this could be simplified further (assuming that all of the “Formatted” fields below are lookup fields for the appropriate linked records):