Re: TEXT("") does not work like it used to!

PocketSurvey · 12-20-2021 05:33 AM

TEXT("") does not work like it used to!

I previously used TEXT("") to blank out a numeric field and it worked perfectly

Now the same function puts in a 0

Why and when did this behavior change?

WillowMobileSys

Try using “” instead of TEXT("").

There was a recent change in an attempt to fix the TEXT() function returning blank instead of ZERO. Looks like the opposite was introduced as a bug. It seems they are trying to a default value based on the context of the function usage - which I don’t think I agree with since the initial value of a numeric field can be blank.

TEXT("") should simply return “” - not zero.

Please post your discovery in the Release Notes article above and open a support ticket:

Steve

Use NUMBER("") or DECIMAL("") instead.

WillowMobileSys

Correct! Using just “” doesn’t work - the default of zero is applied. I just tried it. I thought it did work at one time but maybe I just don’t remember correctly.

Using NUMBER("") assigned a blank value as expected.

I don’t agree with the defaulting behavior where assigning “” is switched to zero instead. Since “” IS a valid value then it should simply be used as assigned - no wrapping function required. If I want zero as my default, then I just assign zero instead!

PocketSurvey

@Steve

Thanks. Those are better options.

I temporarily used TRIM("") to reinstate the correct behavior’s of inserting a blank (because a NUMBER Field can be blank.

Shahaf

Hi @WillowMobileSystems and @PocketSurvey ,

Thanks for bringing these things up. Would you be able to tell me what steps you’re taking to see this (or share an app that shows this)? As I was trying this out, I wasn’t getting the same results as you so I think there’s a step or scenario I don’t know about

Also, is this what you would expect?

For reference, this is the original bug we were looking for TEXT(): New Bug Encountered: When value is 0, TEXT() function returns empty string - #7 by Shahaf

Best,
Shahaf

PocketSurvey

Simply, TEXT("") returned a blank a few weeks ago, not it returns 0 so the developers have messed around with its behavior.

This will break many apps, but nobody will notice it unless they do rigorous testing like I did,

I used TRIM("") to reinstate the correct behavior’s of inserting a blank (because a NUMBER Field can be blank.

Others have suggest during NUMBER("") or DECMAL("") which also works.

WillowMobileSys

Below is an example view where I have 4 columns for each of the Types - Number, Decimal, Text and Price. I am using the App Formula to ASSIGN different variations of blank and empty string, clicking Save after each App Formula update to refresh the row. See below images of the results.

Based on my tests below, the results are not good. Is it possible the updates haven’t been rolled out to everyone or that somehow I haven’t received the update?

Assigning TEXT("") results in Zeros in the numeric columns

Assigning “” result in Zeros in the numeric columns

Assigning NUMBER("") results in “empty string” in ALL columns

Assigning " " (blank) results in Zeros in ALL columns

Shahaf

Hi @WillowMobileSystems –

Thank you for these screenshots. They’re extremely helpful! I think I see what you’re talking about now and am working on making it better so a value of 0 returns "0" from TEXT and a blank value (including "") returns "". I’ll look into undoing these changes for the moment as well.

Will keep updating.
Best,
Shahaf

PocketSurvey

TEXT(" ") i.e with a space should return exactly that, a space.

Not blank. If you want blank you can either use trim or just put in " with no space between.

All these hidden overrides cause massive problems for programmers

WillowMobileSys

This is one of those terminology preferences. I think of “blank” and “space” meaning the same thing - a visible character of “white space”.

The case of “” - no space between - is an “empty string” or “NULL” and is NOT visible.

Totally agree when the values being overridden are valid values.

Note: There are some systems where numeric columns DO NOT allow nulls or spaces, They MUST always contain a numeric value and zero is used as a default. In these systems, it is acceptable that if a blank or space is assigned it is overwritten to a zero - the default value. Yes, this causes issues with knowing if zero is a legit value or if the column is just not assigned. There are design methods to deal with that. AppSheet is obviously not one of these systems.

Marc_Dillon

Using any TEXT() expression in a Number or Decimal column should result in a type mismatch error, if you ask me.

WillowMobileSys

I agree, provided that we can assign the “empty string” in the form “”.