- Create table.
- Fill cells in first column: “ID-1.01-01” and “ID-1.01a-01”.
- Add formula to second column: “thisRow.[Column 1]=ToText(thisRow.[Column 1])”.
- After step 3 table first cell in second column contains “false”:
But why?! It should contains “true”, like in second cell, is’t it?
Dear @Aleksey, great having you in the community
thisRow.[Column 1]=ToText(thisRow.[Column 1]) is an not existing syntax that results in the error.
When you do just:
Because you compare always Column 1 against Column 1 at each row
You are wrong. There is no error in my formula.
Type (format) of first column is “Text”.
ToText([Column 1]) function should return value of cell in “Column 1” converted to “Text” type. But it already has type “Text”. So, result of ToText([Column 1]) must be equal to value of cell in “Column 1”.
But when value of cell is “ID-1.01-01” something goes wrong and it does’t equal.
I think I got your point and there is actually a weird behaviour while equalling values that are somehow internally treated “differently”.
As in your example, if you mix numbers and letters the comparison does work.
I tried some workarounds and so far the only one working is
RegexMatch(thisRow.[Column 1],thisRow.[Column 1] ).
It is still not clear to me the intent of this test, but I’m pretty sure you made it up just to highlight this unexpected behaviour.
Please, let me know if there is a a wider need that can be addressed in a different way.
Btw, in this case it works even with
RegexMatch(thisRow.[Column 1].ToText(),thisRow.[Column 1] )
It’s not about an error, but a syntax that Coda doesn’t treat as you should expect, and as I showed there is a solution to get it right.
Of course if you could elaborate on your use case, it would be interesting to see the response of the engineers at Coda.
Hi @Jean_Pierre_Traets, because you are usually very wise and careful before posting, I have the feeling I’m getting this wrong.
What do you mean with your statement?
thisRow.[Column 1]=ToText(thisRow.[Column 1]) - as far as not very useful per se - is syntactically correct.
I think this is the reason why @Aleksey is puzzled: not because of the formula, but because of its outcome.
Let me know if I’m dragging away from your thought.
I believe the boolean test is checking is (dateformat) == (stringformat) which returns false.
In which case I believe @Aleksey is correct, this is odd behaviour as the “ID-” should force the cell to consider itself a string format.
Interesting little test I did with some different boolean checks.
I think you are all right.
If everything were set deliberately and stuck to that, this would work, but Coda does some work in the background to make a best guess as to what the column type is. For the first value in your example, Coda guesses that to be a date and therefore is comparing a date value with a text value in your example.
So what’s there and what’s happening in the background aren’t 100% lined up. But that said, if you’re comparing a column value, there should be no need to add
ToText() and if you’re comparing text values, it’s fine to deliberate and add
ToText() to both.
It’s interesting to see the example though.
I don’t have a background in programming at all.
For me it felt wrong to convert an existing text field again to a text field and lately our dear @Paul_Danyliuk stated in one of his posts to avoid using “to text” where possible.
Thanks a lot @Aleksey opening up this subject, because it’s very valuable not to take everything for granted
I personally still remain curious if you made the formula on purpose to highlight all what we discuss or…?
It does seem an odd formula to use, as there already is a
isText() formula that performs the check.
Though it still returns false in this case.
Thanks @BenLee for clarifying some of the logic behind the scene. I will be careful for sure when determining product ID’s to not clash with this.
I’ve been summoned from my slumber and this caught my attention.
To me this is pretty much a bug and a critical one. The column type is set to be Text. There is zero reason for Coda to interpret a particular cell as a date internally. Think of the potential issues: e.g. imagine trying to implement a filter on a table. Would be confusing why it returns nothing, wouldn’t it?
I would personally have never guessed that
ID-1.01-01 would autodetect to a date. And I don’t imagine the scenario in which a Coda user would legitimately want that with a value like this. The existence of such unpredictable behavior undermines trust in Coda’s calculation engine and therefore must be fixed. @BenLee
Additionally I noticed that values like
"01" (explicitly wrapped in quotes) too get converted into numbers. I think this is a bug as well; if I explicitly enclose a numeric string in quotes I want it to behave like a string, not like a number.
Also @Jean_Pierre_Traets just to say, the use of the formula was a legitimate one: to display the issue. The faulty
Value comparison makes it perfectly clear that something’s wrong with data types.
This is added to our tracker now!