Re: Split single record into multiple records in the same cell
Hey @federica!
So, I've gone ahead and created a small demo to provide a side-by-side workflow.
For the sake of an example, let's say that I have previously created a table called Classes with a single-line text field called Class Languages.
After some time, I realize that these languages should be their own records that then relate to relevant classes.
To fix my schema, I'll first create a new table called Languages. Each record here will be a unique language.
I'll then create a linked field between these two tables.
Now I need to transfer the stored data in the Class Languages (single-line text) field to the Languages table.
I'm going to assume that your Class Languages field contains values that universally fit the following format:
languageOne & languageTwo
If this is the formatting we can expect, then we can simply create a temporary formula field with the following formula:
IF(
{Class Languages},
REGEX_EXTRACT(
TRIM({Class Languages}),
"^\\w+\\b"
)
& "," &
REGEX_EXTRACT(
TRIM({Class Languages}),
"\\b\\w+$"
)
)This will produce the following behavior:
From here, you can simply copy and paste the extract into the Languages linked record field.
Once we've completed the data transfer, we can delete both the formula field and the old single-line text field.
This entire process assumes that your data is formatted in the same way. If your data takes on different formats, then we'll have to adjust to the other formats that might be present in your database.
If you have other data formats that need to be accounted for, let me know and I'll write a revision to the extract formula.
Hi Ben, Thank you! This is really helpful.
The dateset looks like this
The language section includes both blanks, single entry as well as a wide range of possible language combinations.
Also, since the dataset comprises more than 3,000 rows, is it possible to apply the extraction formula to the first row (or first 10 rows) and apply that to the whole dataset without copying that? I am a beginner on Airtable so thanks for your help!!!
Federica
To deal with the possibility of a blank field, a field with a single value, and a value of two languages separated by an ampersand, you can use the following formula:
IF(
{Language(s)},
IF(
REGEX_MATCH(
TRIM("" & {Language(s)}),
"&"
),
REGEX_EXTRACT(
TRIM("" & {Language(s)}),
"^\\w+\\b"
)
& "," &
REGEX_EXTRACT(
TRIM("" & {Language(s)}),
"\\b\\w+$"
),
TRIM("" & {Language(s)})
)
)Also, since the dataset comprises more than 3,000 rows, is it possible to apply the extraction formula to the first row (or first 10 rows) and apply that to the whole dataset without copying that?
Speaking plainly, this is spreadsheet thinking!
No, you cannot selectively apply a formula to a single record. The field lives on a table and runs the calculation on every record stored in the table.
That being said, don't be afraid of the number of records you're dealing with.
This is what makes databases preferable over spreadsheets. They're built to handle immense amounts of data and relationships. They are incredibly robust when executed properly.
To highlight this, I created 3,000 records with randomized language values in a multi-select field similar to what you shared in your screenshot.
Here's the Languages table before I transfer the data. Currently, we only have our four languages from our first test.
Now that we're set, we can just copy the entire table field value and paste it directly into the linked record field.
All the new records will be automatically created in the Languages table.
Then, same as before, we can just delete our language field and the formula field since we don't need them anymore.
If anything happens that you don't like, you can simply hit undo (CTRL + Z) or do it manually in the base using the Base history feature.
Thank you! In case I get to this (language 1, language 2, language 3)
Is there a way to merge these entries in such a way that they are still separate and colourful ones but within one single column? Thanks a lot for your help
