Airtable Times out when Trying to Convert a large field to Single Select

[Julie_Blackburn]

I have a larger base (for Airtable), about 30,000 rows and want to change one field with about 60 options to single select, to help prevent human error in the future when adding new items to this base. When I attempt to change this field from short line text to single select, it times out and asks me to refresh the page, and the change is not saved after several attempts. I’ve tried to turn off the “color” feature to make it easier to process, and still no luck. Is there a limit to this feature? What am I missing?

[Bill_French · Accepted solution]

Julie_Blackburn:

and the change is not saved after several attempts

Yep - this is a challenge because you’re asking Airtable to perform an in-place transformation across 30,000+ items.

In cases like this, you have to rope the cow, flip it on its back, and tie its legs before injecting the medicine. I have no idea why I used this metaphor. I’ve never [ever] roped a cow.

Onward…

Setting aside my roping skills, I have had to do this on a 50,000 record set and it required a script process that used a new field [single-select] that was populated with the values from the original field. Once the transformation process was complete, I renamed the original field (the client wanted to keep that data) and then renamed the new field to take its place.

[ScottWorld]

@Julie_Blackburn Yep, I’ve discovered that Airtable can’t natively handle much of ANYTHING that is in “batches” greater than 2,000 to 5,000 records at a time (depending on what you’re trying to do). Unfortunately, Airtable is a very weak tool for attempting to do anything powerful like this.

@Bill.French‘s idea of scripting this with JavaScript sounds like a great idea.

I actually solved this IDENTICAL problem last month without using JavaScript. The way that I solved this is that I exported all of the records out of Airtable as a CSV file, being sure to include a unique identifier for each record (such as the Record_ID).

Then, I opened the CSV file in FileMaker and divided up the records into batches of 2,000 records. Then, I manually exported the records as individual CSV files that each had 2,000 records in them. (I tried doing this with more than 2,000 records, but it didn’t work because Airtable timed out every time.)

Note that when I created the CSV files, I only exported the unique identifier for each record, along with the field that needed to become the single-select field.

Then, I manually imported each one of the CSV files back into Airtable using Airtable’s CSV Import block, but I turned on the “MERGE” option to merge records using the unique identifier (instead of creating new records).

I also turned on the option to automatically add new single-select options into the other field, which I started off as a brand new field in Airtable. I also made sure that the option to “Create missing select options” was turned on.

(I am both a FileMaker consultant and an Airtable consultant, so using the tools that I am intimately familiar with made this process easier for me.)

[Bill_French]

ScottWorld:

exported all of the records out of Airtable as a CSV file

Yep - there are many ways to overcome this, but the instant it involves dumping the data to CSV and re-importing, all sorts of risks begin to emerge. The goal is simple - change the data type of field(x) and apply any related logical assertions.

A javascript approach (hired or otherwise) is <= 0.5% risk level and completed in the time it takes to have lunch (about an hour) as opposed to >= 40.0% risk level and many hours of effort. I’d go with door #1 every time even if you have to hire it out.

[Julie_Blackburn]

Excellent Solutions Everyone! I was able to solve the issue after eliminating some blank fields in the columns I was trying to transform into a single select.