- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 01, 2022 10:02 AM
Hi,
I have this formula:
Centre & "-" & {Musician Educators} & "-" & Time
Which is giving me Primary Record IDs like this:
Dublin-Johnny Teacher-12:00-12:30
which is great, but what I really want is:
Du-JT-12:00-12:30
So regex’ing the first two letters of the first field, and the first letters of the two names of the second (I can ensure all teachers are listed as simple first and last names.).
It would make my Primary Fields a lot shorter, clearer, and easier to find when entering in data.
Is that easily done?
Thanks!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 01, 2022 10:34 AM
Hm, if you’re alright with not using regex, you could do:
LEFT(
Centre,
2
) &
"-" &
LEFT(
{Musician Educators},
1
) &
LEFT(
SUBSTITUTE(
{Musician Educators},
LEFT(
{Musician Educators},
FIND(
" ",
{Musician Educators}
)
),
""
),
1
) &
"-" &
Time
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 01, 2022 12:04 PM
There are many possible formulas. Here is another one.
CONCATENATE(
LEFT({Centre}, 2),
"-",
REGEX_REPLACE(
{Musician Educators},
"(.)[^ ]* (.).*",
"$1$2"
),
"-",
{Time}
)
However, I think the most robust solution would be a different approach. In your [Centre] and [Musician Educators] tables, create a new {Short Code} text field that you manually enter. Then add lookup fields for these short codes and build your primary field using the lookups of the short codes.
This method will add a few more fields to your base but adds a lot of flexibility. For example, you can better address adding a new teacher with the same initials as an existing teacher, or handle locations that start with the same letters.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 01, 2022 03:53 PM
Could you explain the Regex @kuovonne ? Very interested in learning more! :pray:
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 01, 2022 04:02 PM
"(.)[^ ]* (.).*",
"$1$2"
I use regex grouping in the REGEX_REPLACE. The parenthesis in the first expression indicate the groups I want to use.
- The set of parenthesis indicates the first group
(.)
, which is a single character as indicated by a dot. - The next section indicates any characters that is not a space
[^ ]
repeated zero or more times*
, followed by a space: - The second set of parenthesis indicates the second group
(.)
, which is also a single character as indicated by a dot. - The rest of the expression
.*
is any additional characters that make up the text string.
The replacement expression says to replace the entire expression with the first capture group (as indicated by $1
) and the second capture group (as indicated by $2
).
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 02, 2022 04:22 AM
Many thanks @Adam_TheTimeSavingCo and @kuovonne.
I can see your point @kuovonne, I’ve already found teachers with the same initials! May I ask how I combine multiple lookups to create a primary field? Presumably this can’t be done with a customise field type but has to be done with a formula?
EDIT: Aah. I added the lookups to the table and then simply combined them. I thought it was more complicated than it was. Thanks!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 02, 2022 04:23 AM
Thanks @Adam_TheTimeSavingCo
Is that JS or a special Airtable code?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 02, 2022 05:21 AM
Just an Airtable formula, should work after you put it into a formula field
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 02, 2022 09:43 AM
Ah cheers. It worked a charm. Many thanks. I’ll have to go have a read about Airtable formulas some more. Thanks again!