Update: Formula Improvements - Type understanding

Coda has a rich formula language composed of many different formulas, data types, and objects. A big part of making a good formula user experience is making sure that Coda can understand the data types of every part of your formula, so that we can provide relevant autocomplete, check for errors, and much more.

In some simple formulas the like Date() the typing is very specific. It takes a number for the year, month, and day and returns a date. In other formulas the typing is much more dynamic. Take Max() for example it takes a list of numeric values that could be formatted numbers, currency, percent, dates, or duration, and based on the input data type we determine what the output data type would be.

This gets a bit more complicated when you introduce more complicated data types like Rows, pack Objects, and Controls. As some of our users have noticed we would sometimes lose the full understanding of a data type when it passed through a formula. A common example of this was doing [Table].First().[Column]. Attempting to project the column would lead to a very confusing error about expecting a row, but the data type being a row, which we apologize for. The reason for this was the type system knew the data type was a row, but didn’t know which table it belonged to.

We recently did an overhaul of how we pass around type understanding in formula engine to address a whole category of these issues. As a result Coda formulas should no longer lose type information when passing through a formula. This made it possible to now do projections off of many common list functions like First, Last, Nth, List, ListCombine, and others.

As an added bonus we improved the type understanding on Controls, so that you should no longer need to use the Value accessor to project off of the references in the Control. The Value accessor will still continue to work, but hopefully in the future we can migrated everyone off it.

25 Likes

You folks are amazing. There are so many other products out there that would simply leave issues like this to fester because, unlike flashier ‘surface’ features, they don’t directly affect new user acquisitions. But it seems like the Coda team takes the view that what really matters to your success is what people are able to build with your product, so you’re always striving to give them the best tools possible. Kudos! :raised_hands::grin:

9 Likes

This is awesome. Thank you for continuing to improve the language and make it easier to grok and explain to newbies.

@Jason_Tamulonis What about making the formula editor window bigger? Right now it expands each line you add up to 7 lines then gets fixed. A bigger canvas allows for ‘cleaner’ and indented formulas which would, much like this improvement, make things easier to understand.

@Jason_Tamulonis It still doesn’t resolve CurrentValue in .FormulaMap(AddRow()) context properly.

E.g. here the suggestions should be from Source table, not from Table 2

Proof that CurrentValue in this context is actually a row from Source table is that this works:
image

2 Likes

Hi @Paul_Danyliuk,

Sorry about the delay. I didn’t want to respond until I had an answer I was happy with :slight_smile:

The problem that you called out is a pervasive issue we have had with how we handle context with loops, which is a slightly different beast that the original update. I was able to take some time over the holiday break and fix it though. If you reload your doc you should see this is working now.

I will point out (before anyone asks) this doesn’t add support nested loops, which we would like to do at some point. It should improve several scenarios similar to the one you listed above.

Once again thanks for the feedback!

5 Likes

@Jason_Tamulonis, Is it possible to query an object for its data type? For example:

[Table].[Column]._Type()
List(1, 2, 3)._Type()
7._Type()
1 Like

@Michael_Hewitt Good question. We currently don’t expose a way to directly query the type of an object in the formula language. It is something we could look at in the future.

1 Like

That would be nice. The first use case that jumps to mind is having a column that takes data from a form question like “age or date of birth” and then use a formula to (based on the data type) pull the information you need from that.

(Not sure that specific example would ever be that useful, but maybe use cases like that would pop up.)