The Formula Playground is open for play!

[Rose_K]

Hello Community!

This is quick announcement about a new base we built called the Formula Playground, created as a space to reference and test out formulas on the fly.

As the name of the base indicates—we created the Formula Playground base experience with “play” in mind for a more hands-on approach to learning and testing out formulas. You can create a personal copy of the Formula Playground here, or check it out at any time in the Formula field reference article.

As always we’d love your feedback on this base! You can add any comments or feedback here in this topic. 💫

[kuovonne]

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 CONCATENATE() versus ARRAYJOIN()
• using IF() versus SWITCH()
• using FIND versus SEARCH versus = 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.

[Databaser]

Rose_K:

This is quick announcement about a new base we built called the Formula Playground , created as a space to reference and test out formulas on the fly.

Thank you for this effort! Will surely help a lot of AT users.

kuovonne:

Now that I’ve typed all of this out, I realize that much of it is beyond the scope of this little playground base.

:astonished: Wow, great input!

[Rose_K]

@kuovonne
Thank you so much for the amazing feedback! We’ll definitely keep iterating on the Formula Playground, and the rest of our formula materials, for basically forever so this feedback is so helpful! :star_struck:

I’ll follow up separately to discuss more about the custom app you made and the bounty of ideas you’ve provided above.

[ericatihs]

The link create a personal copy of the Formula Playground here does not work?

[HatemG]

Hey guys, I am trying to access the formula playground but not able to. Is it still available? Thanks

[MaddieJ]

Hey @ericatihs and @HatemG, thanks for your patience here! I've updated the Formula Playground link above, you should be good to access it now. 

Thanks for flagging!