Forcing text format OR importing huge list for multiple select/linked table

[Natalia_Mushegi]

Hi there! I’d appreciate any ideas about how to handle this data entry need:

I have a form (external users) where one of the fields needs to collect assessor’s parcel numbers. Users tend to input this in very inconsistent formats, so I wanted to make this a multi-select or use a linked record from another table to force consistent formatting. However, there are about 29k potential parcel numbers to select from, and Airtable is not handling a CSV upload of these numbers into a new table (it crashes out even though the CSV is <2MB).

I’d take all kinds of solutions, including:

• A way to add 29k options to a multi-select
• A better way to upload a single-column, 29k row table to use for linked records
• A way to force a 000-000-000 format for a text entry field (however, I’d need a way to have multiple parcel numbers entered with this format in this field, as each record could have multiple parcels listed)

Or any other clever ideas about how to handle this to cut down on post-entry data cleaning as much as possible!

Thank you!

[ScottWorld · Accepted solution]

The CSV import in Airtable has a maximum limit of 25,000 rows, so you would need to break up your CSV into multiple CSV files to create all of those records in a table. Then, you will be able to create the “linked records” functionality that you were talking about.

I’m not sure what the limit is for a multi-select field, but after you create all of those records, you could convert that field into a multi-select field, and Airtable will convert all of your options into your multi-select options.

Another option would be to turn to an advanced form tool like JotForm, which supports forcing formats like 000-000-0000, and can also create a multi-select option list based on importing a CSV file:

Jotform

How to Use the Spreadsheet to Form Widget

Are you hoping to populate your form fields from an external Excel Sheet? Perhaps you wanted to pull existing records and display them on your...

[Natalia_Mushegi]

Thanks! I actually already tried splitting the CSV into 10,000-row sections to upload, but still no dice. Any other tips I might try?

[ScottWorld]

Very odd! Maybe try 5,000, which was the old limit.

[Natalia_Mushegi]

5000 worked! Thanks for the tip.

[ScottWorld]

That’s great! Question: were you using Airtable’s CSV Import App? Their documentation says 25,000, so I should let them know if that is wrong.

[Natalia_Mushegi]

I was using the Quick Import From - CSV file option.

At 10,000 rows my browser tab just kept hanging/dying after I selected the file and once I could see the data, but before I actually clicked the final Import button.

[Justin_Barrett]

While I’m glad that you got the import situation mostly worked out, I’m not sure that using a table with publicly-visible parcel numbers would be a good idea. I recommend parsing/validating the numbers after submission using a script that runs via an automation when the form is submitted. If you’re not familiar with scripting and would like help with such a tool, message me and we’ll set up a meeting to discuss the options.

[Moe]

Another option is to use miniExtensions Form. It supports field validation for inputs using Regex. Which allows you to enforce any formatting rules you like. This is an example of only accepting 9 digits exactly.