SWITCH

AppSheet AppSheet Q&A
1

Hello,

Have a column which digit must bey 5 characters. Users are adding the data as 123.

How possible to switch the data as 00123 even user add it as 123?

Thanks in advance…

2

I think that’s what the Numeric digits column property governs. Try entering 5 for this property.

dbaum_0-1663555525607.png
dbaum_0-1663555525607.png767×197 9.14 KB

Otherwise, use the column’s Valid if property to define what values can be entered.

4 Likes
3

Did not know this… Thanks,

Also may want to uncheck the thousand separator..

1 Like
4

Dear friend,

Think that Valid if gonna work. Still i do not have much experience for such formulas.

The app should change the data user added as from 123 to 00123. So, what kind formula i need to use?

Thanks.

5

What is wrong with the solution proposed by @dbaum ?

Do you want a text column rather than a number column?

2 Likes
6

Dear TeeSee1,

It is not an issue with format. It is about digits of the records users are adding.

They are adding 1 instead of 00001 for example. It is difficult to change their habit.

That is why i am trying to change date they are adding.

Example;

from 1 to 00001

from 11 to 00011

from 111 to 00111

from 1111 to 01111

Thanks.

7

As you noted, the Numeric digits column property governs only the display format of the number within the app–not the value stored in the data source, which is still a number value, which therefore doesn’t include leading zeros.

You either need to use one of the following approaches.

Change the existing column’s data type from Number to Text and use a Valid if expression to require exactly 5 numeric characters.

AND(
LEN([_THIS]) = 5, 
NUMBER([_THIS]) > 0
)

Create an additional column that uses an App formula expression to add any necessary leading zeros to the value from your existing column and use that new column wherever necessary in your app.

RIGHT("00000" & TEXT([Number Column]), 5)
1 Like
8

Dear dbaum,

Thanks a lot for your help on that issue.

Do not want to change to text cause; users are putting numbers all the time and the type is “phone” actually. It is much more easy to add numbers using the number keyboard. No option as numeric keyboard if the format is “number” or “text”.

9

Dear friend,

Is that formula gonna work even type is phone?

SWITCH(
LEN(CONCATENATE([ULD NR])),
5, CONCATENATE([ULD NR]),
4, CONCATENATE(“0”,[ULD NR]),
3, CONCATENATE(“00”,[ULD NR]),
2, CONCATENATE(“000”,[ULD NR]),
CONCATENATE(“0000”,[ULD NR]),
)

10

I don’t understand. In case it’s helpful: You can configure the AppSheet column to be a Text type and still only only numeric digits to be entered in the column.

1 Like
11

I believe so. I think that AppSheet generally treats Phone values as Text values anyway. Regardless, try it and see for yourself!

1 Like