Thank you for putting together this base.
I am a huge fan of Airtable formulas, and I applaud any resource that helps others in learning how to craft formulas. I also heartily recommend having a formula playground when learning or working with formulas. Although I have a base dedicated to playing around with features, I also tend to play around with formulas in whatever base I am in. If a formula doesn’t do quite what I want, I fiddle with it until it does.
I hope that the base will continue to grow to include additional functions as they are released.
This base (or a version of it) could be a hug asset on the formula field reference. As a reference it could use much more information, such as the number of input parameters, the input types of each parameter, and the output type of each function.
I also recommend a “best practices” table that lists recommendations, such as
- use of white space and formatting (especially multi-line formulas)
- when to use
= versus regex functions
- how to document formulas
- converting field values into text strings
- understanding how a value is formatted versus how it is stored (especially with calculated numbers and dates)
- understanding the basic value types (text, number, date, boolean, array) and how they relate to field types
- converting one value type to another (especially converting to boolean, converting arrays to strings, converting strings to dates)
- using formula fields versus rollup fields
- limitations of formulas (especially referencing other rows)
- using a plain text editor or a code editor to write the formula
- what to include when posting a question about a formula on these forums and how to format formulas on these formulas
Your list of common errors only seems to cover errors produced by valid formulas. A far bigger concern is dealing with syntax errors that prevent a formula from being saved to begin with.
- curly quotes versus straight quotes
- extra or missing commas
- extra or missing parenthesis
- extra or missing curly brackets around field names
- typos in field names
- input types not matching the required input type of the function
Also consider adding a section for common unexpected results. These are situations where the formula runs without error, but does not give the result expected.
- Timezones throwing off the result of date functions
- NOW() and TODAY() update schedules
- negative values with date/time arithmetic
- Formula result type (text/number/date) different from expected
- empty values in rollup arrays
- difficulties when using lookup fields in formulas
- durations being stored as seconds
- percents being stored as decimals
One of the biggest learning curves with crafting formulas is correctly nesting functions. One of the best ways to teach this is through a series of “sentence combining” exercises. I would love to be able to talk to Airtable in a live meeting to discuss how these exercises work.
I also recommend encouraging formula writers to test formulas with a variety of inputs: typical inputs, missing inputs, and edge cases. I often see formulas that work for typical inputs, but have not been thoroughly tested for missing inputs or edge cases. When those edge cases crop up later, the formulas can produce incorrect results that can be hard to detect.
Finally, I have submitted a custom app to the Airtable marketplace that generates several common “ready-made formulas” for the fields in a user’s base. In addition to copy/pasting the formulas into formula fields, users can examine the formulas to see how they work. Almost all of these formulas involve combining multiple functions together.
Now that I’ve typed all of this out, I realize that much of it is beyond the scope of this little playground base. However, as it contains useful information, I am going to leave it.