My submission for the scripting challenge is a custom validation import process. If I use the input.fileAsync method to import a file, Airtable gives me some standard validations out of the box. For example, you can’t import a string into a number field. My script takes this further by allowing you to configure some custom validations for fields within the import file - have a look at the video below to see it in action:
The script also takes rejected records (either custom validations or Airtable validations) and puts them into a rejects table so that these can be viewed later without the need to sort through the original file to find out what was accepted and what was rejected.
The script can be found here:
Your base needs to be set up in a specific way to get it to work:
-
Your “Import” table needs to have fields that match your file, e.g. same field header names and the field types you expect to receive from the file.
-
An “Import Rejects” table. This has 2 single line text fields - “Record” and “Reason”
- Finally, a “Conditions” table with a single field “Condition”. This has 3 rows:
Is not empty
Must be an email
Must contain
The Script
The script has a few comments which explain how it works, but to add to this, the logic behind each of the conditions is coded into the script. So…you can’t just add a new condition without the code to match :slightly_smiling_face: . In that sense, the script is very much a work in progress - I am sure there are other validation conditions that could be added, which would add to the usefulness.
The 10 second version is that the script asks the user to define validation conditions and these are “built up” as a set of validations prior to the import. As the file is imported, each record is validated against the set conditions (and also, by default, against Airtable standard validations).
Side point: I used records in the Conditions table for the user to select from. I started off setting these conditions as fields rather than records as, arguably, the user experience of picking a condition was better if you are choosing a field, rather than a record (a select dropdown, as opposed to a record pop-up), but this didn’t feel right (as in a bad use of Airtable fields), so I switched to records. Possibly one for the roadmap is a way to define a dropdown with some arbitrary selections.
If you’ve got any thoughts on how this could be improved, please let me know. One thing I thought about was putting the conditions logic in the “Conditions” table, but left this out for now (no idea if this is possible, but would be great if you could).
Hope you like it, feedback appreciated!
JB
