Problems with implicit column type auto-detection

Suggestion Box Bugs
1

Hello,

There are some problems with number-like or formula-like values when a text column contains them:

  • Column type is not detected corrected in formula editor
  • Some values break formula calculations

Please see the published doc for a test case. You need the “Failed Test 1” page. Here’s the link: https://coda.io/@rcx/failed-tests

Thanks.

2

Dear @Dmitri_Silaev,

In this case you are right on the fact that the behavior is not consistent as the column type setting is not followed in the formula.

Use the " totext()" enforces at least to get the intended outcome :thinking:

Concatenate(thisRow.[Quote for], ": ", Join(" ", thisRow.Price, If(IsNotBlank(totext(thisRow.[Lead time])), "in " + totext(thisRow.[Lead time]) + " days", "")))

Dear @BenLee, it would be great if this can be reviewed by the engineers :building_construction:

3

Hello @Jean_Pierre_Traets,

True, I played with the formula itself, of course, and was able to get the result. But what’s especially concerning is the fact that formula calculations turn out to be brittle per se. This time it could be this, next time it could be something else. Therefore you need to remain alert all the time to spot potential problems with one of the much used functional areas.

Another concern, which is in fact quite big, is the unstable effect with values “2-3”, “7-12” and “40-45”. For the former 2 cases it is working one way, for the latter one another way. I tested this and and I know the effect, but is there anything else is that I don’t know? Occasional operator characters, numbers, their combinations? Leading, trailing, in the middle?

4

Hi @Dmitri_Silaev,

I checked out your doc and there is a part of that formula that is written in a way for things to be considered a formula instead of text. This part of the from that column is what I’m referring to…

"in " + thisRow.[Lead time] + " days"

If you use Concatenate() here instead, that will treat the values as text, but using a + is stating more literally that you want to add values, not combine text. I think what you’re looking for here can be done completely with a Concatenate() formula.

1 Like
5

Hey @BenLee,

Thanks, it worked.

But still a few points:

  • Lead time shows as a “123” type, not “T”.
  • I’ve made up another test - see “Test 3” in the same doc. Apparently, there’s a 50% threshold for column type auto-detection.
  • Strings like “40” are treated like numbers, strings like “3-20” are treated like dates. Much implicit type casting is happening behind the scenes. Any Coda docs on that?
  • Another way to handle that would be to error out but it keeps silent.
6

You’re right, we do work behind the scenes to make some best guesses. There will inevitably be some errors when doing this and we’ll continue to work on refining it.

If your numbers are meant to designate “3 days to 20 days” then using “3 to 20” would be read in as a text string every time. I know this might not be the solution you’re looking for though.

7

Thanks @BenLee! Definitely , I can use some workarounds.

The tool in general is awesome. I was thinking about using something like this for years and only now decided to take a plunge. Allowed me to set up a small SRM system in one day! That’s really great.

I don’t know if I still need to keep posting quirks, bugs and test cases. I already found a couple more. But most won’t be encountered by general public. Plus, I’m going to run out of my free quota too soon :slight_smile:

8

We’re always open to hearing feedback. We can’t dedicated resources to every bug and have to triage them based on the impact we feel they have, but we definitely want to log them. Posting here in the “Bugs” category is great.