Setting an exact value for number

AppSheet AppSheet Q&A
,
1

Hi there, I am trying to set an exact number of digits on a staff ID column. Supposedly, when user enters their Staff ID under [Staff ID] column, it should only contain 6 digits. Any deviation from this will result in invalid.

Example:

Staff ID: 12345 (Invalid)

Staff ID: 1234567 (Invalid)

Staff ID: 123456 (valid)

Greatly appreciate if someone could spare the time to provide a suggestion to this..

2

Hi @Rexatron

Check this post. You could use 100,000 and 999,999

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Data-validation-number/m-p/291418

2 Likes
3

Use this as the Valid_IF formula - LEN([Staff ID])=6

https://support.google.com/appsheet/answer/10107966?hl=en

Simon@1minManager.com

5 Likes
4

Thank you, it works. I had to set the type to “Text” instead of “Number”, then only LEN() works.

1 Like
5

It all boils down to your need.

If you need a number, you set minimum and maximum.
If it’s text, LEN() works.

Now, don’t tell anybody but TEXT() can return your number to text adding a thousand separator, so I guess LEN(TEXT([_THIS]))=7 on your number column would work

1 Like
6

I supose another question would be how you want to handle “1”. Either 000001 or 1 is required to be valid?

With hindsight, the issue with LEN([Staff ID])=6 is that abcdef is also valid :thinking:

2 Likes
7

Yep, I think @Rexatron 's need was not clear enough

2 Likes