Formula Checking: Don't Catch Errors that Cannot Happen

Suggestion Box
1

When checking whether a formula has errors, only throw Unexpected dot operator error if that line can be logically reached.

Right now, when you write the formula:

If(
  variable.IsBlank(),
  false,
  variable.[Column]
)

If variable is blank it displays this error:
image

I want it to return false.

You can play with it here: Error Handling

This matters because there are scenarios where you have a variable that is normally not blank and is used in a subsequent calculation. If it is ever left blank and a formula tries to access its attribute you will end up with an error that bricks the rest of the formulas that rely on this variable.

The price to pay will be that formulas written inside If() and Switch() statements won’t be able to tell you ahead of time whether they reference a non-existent attribute. However, formula chips should help you to get around this shortcoming.

Originally, I explored this in a very misguided suggestion to add a third argument to IfBlank. Thanks to @Ryan_Martens2 for pointing out that I was wrong and for pointing to this as the better solution.

3 Likes
2

Any codan on this? Seems almost a bug and in our case makes several correctly working formulas return errors instead of messages to users.

3

@Connor_McCormick et al

one of the best practices that I advocate for is to avoid using named canvas formulas as intermediary variables and use a single-row table for these instead.

Reasons:

  • Canvas formulas don’t have type affinity, and table columns do. The issue in this thread is exactly this: variable.[Column] is invalid because Coda cannot infer that variable is a row/list-of-rows from a the table when it’s given a Blank value. There’s nothing to take that info from. Meanwhile in a table, the fact that the column has type affinity of “Lookup from table X” actually lets Coda assume that in case of no value, it should still treat that cell as of type “row/list-of-rows from table X” and not report an error on attempted dereference.

  • Besides, Coda actually creates a single-cell table behind each formula anyway.

  • Also you’re saving on objects count if you’re on free plan. But most important, of course, is bullet #1

image

P.S. It’s not the “error that cannot happen”; Coda does not (and should not IMO) analyze that deep into the dependency tree. I’d argue that it should work with variable.[Column] at all given that there’s no information about variable's type. But I guess they were trying to make it work “in the most commonly expected way” for regular users, so it attempts to infer the type when the value is actually something.

4 Likes
4

Ah, very interesting.

It’s surprising to me that a single-row table is lower object count than a selector.

Should the title of this post be changed to: “Don’t infer the variable type if the variable is blank”?

5

@Paul_Danyliuk, following up on this

6

@Coda Is there any effort from Coda to fix this? It is still an issue.

It seems like obvious and expected behavior that error correction should short-circuit on “if” branches.

if(variable.IsBlank(), "", variable.[Column]) should return "" rather than an error.

1 Like
7

I’d love to know if anyone from Coda or the community has a way around this?

To add an example: I have two tables DB Projects which stores projects and DB Updates which stores text updates related to projects. I want to display a preview of the updates in a column on DB Projects so I add a column with the following formula:

If(thisRow.[_Previous Updates].Count() < 1, "",

  List(
  
    Concatenate(
      thisRow.[_Previous Updates].First().[Display value].ToText(), ": ", 
      thisRow.[_Previous Updates].First().Update
    ),
    If(thisRow.[_Previous Updates].Count() > 1,
      "and " + ToText(thisRow.[_Previous Updates].Count() - 1) + " more..",
      ""
    )
  
  )

)

Here’s what happens with the above:

  1. If none of the rows in DB Projects have related records in [_Previous Updates] then the column displays an error in the formula for all rows.
  2. If one or more of the rows in DB Projects do have related records in [_Previous Updates] then the column does not display any errors on any rows, meaning that even rows without any related records do not display a formula error.

Observed behavior for # 1 above:

Unexpected dot operator

thisRow.[_Previous Updates].First() is an unknown value, which can not be chained with a dot

Expected / desired behavior:

  1. A way to avoid the error if possible
  2. A way to optionally suppress the error from being displayed to normal doc users.
8

Hello @Ville ,

I can’t reproduce the error’s you encounter, can you share a demo doc so we can see what is going on?

Greetings, Joost