How can I determine the no. of digits in an input column

Hi all,
I’d like to set up a PIN column where users must enter a 4 digit code of their choice. How can I determine the no. of digits in the column.
Thanks a lot.

This could be one of the workaround while other community member should have better idea and expression.

Put this expression into valid if constrain.

and(
len([_this])=4,
in(number(left([_this],1)),{0,1,2,3,4,5,6,7,8,9}),
in(number(right(left([_this],2),1)),{0,1,2,3,4,5,6,7,8,9}),
in(number(right(left([_this],3),1)),{0,1,2,3,4,5,6,7,8,9}),
in(number(right([_this],1)),{0,1,2,3,4,5,6,7,8,9})
)

Make sure to set the data type to Text instead of number to run.

This expression will verify if the number (actually it is text though) is always in between 0000 and 9999
In case the length is shorter than 4 or any characters other than 1,2,3,4,5,6,7,8,9, such as a, b, c etc, is entered, then it will reject the entery.

2 Likes

Hi @tsuji_koichi,

May be I am missing some important point. Can this be achieved by assigning the column as number type and following settings?

1 Like

This needs to be text type.

If it turns to be number type , it won’t work.

Thank you @tsuji_koichi. What I mean is if the column type itself is number type with the suggested settings above?

Edit: : If the column is number type with suggested column settings in the picture above, then I believe we may not need valid_if expression as in text column to check the entry is from 0000 to 9999.

1 Like

The problem with number type is it is going to return 111 if PIN is actually 0111 for instance z

1 Like

Hi @tsuji_koichi,

Thanks. If the numeric digits length is kept at 4 in the number column settings, 1 will save as 0001 and 11 will save as 0011 or 111 will save as 0111

1 Like

But I get your point that the text column will force the user himself to enter all 4 digits. So even though the results with the number column with suggested settings and text column with valid_if expression will be identical, from the user experience point of view, probably it is more suitable to make the user to punch in all four digits, thereby giving the user a feel of 4 digit pin. So text column is better option here than number column…

PIN is basically requiring 4 digit number. meaning user need to enter all 4 numbers to verify…

1 Like

How about…
AND(
LEN([Column])=4,
NUMBER([Column])>0
)

2 Likes

If “0000” is also needed, you need to add that exception as well.

My expression is taking care of 0000, so don t need to alter the expression.

1 Like

Just throwing ideas :slight_smile:

1 Like

Hi @tsuji_koichi and @Aleksi,

This was useful learning for me in the discussion.

So far, I believe, @tsuji_koichi’s approach looks most fitting one from entering 4 digit PIN point of view. It is interesting that even though the requirement is to enter a number, the use case makes the text type column more appropriate to meet the user experience.

Problem with number type is that we are not able to use LEN() expression to valid the length of data user enter. However, even setting it up as text type, with my constrain, user is only able to number, i.e. 0 to 9, in that sense, it looks number type, but 0001 0002 is actually text.

1 Like

Yes, you are right. I realized that limitation while testing that we cannot use LEN() expression with numbers , even after converting number to text with TEXT()

1 Like

Yes, that s the essense of this trick. User len() with text type and then convert each different positional text to number to do another validtation test if they are IN of 0 to 9. Thats expression is saying.

1 Like

Thanks guys for the response.
I also thought the no of digits setting would do but it is more like a max limit.
Tsuji is also correct that numerical PINs should include leading zeros which can only be preserved if used as text. Alexis also looks very promising and I will be trying it as well. Maybe ASheet could add a function to deal with this…
Cheers and thanks again.

Appsheet is already capable of what you want, so no need any new feature. :grinning:

1 Like

image

AND(
  CONTAINS("0123456789", MID([_THIS], 1, 1)),
  CONTAINS("0123456789", MID([_THIS], 2, 1)),
  CONTAINS("0123456789", MID([_THIS], 3, 1)),
  CONTAINS("0123456789", MID([_THIS], 4, 1))
)
(RIGHT(("0000" & NUMBER([_THIS])), 4) = [_THIS])
2 Likes