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

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

Hi sir, your expression seems to be the solution of the problem i’ve been facing this couple days.
I tried this but the expression assistant says LEN function is used incorrectly.

Any help on why is this happening sir?

Is you column a text type ?

Yes it is sir.

I placed a sample app for the community.

https://www.appsheet.com/samples/Public-Sample-App?appGuidString=4d7a0299-9a31-4cd6-9a1b-976f23666e10

You have a look inside and compare with your own settings.

This sample is designed for PIN validation to access to the app.

The user register their own unique credential, email + PIN.
Then go to app , enter PIN which must be 4 digit number, as well as matching with the registered pin in order to access to the app.

Kinda of additional security system to secure the app.

3 Likes

Thanks for the amazing app sir.
Your expression in PIN column helps me solve the problem.

I ended up writing a very long expression but good enough for me as long as it works.
Personally, unlike its tagline “no coding experience needed”, i found Appsheet really hard to use since it doesn’t have (or clear enough for me) explanation on how to use expressions or at least where to learn about it.

I have one last question sir, how to set the expression for a column to act based on the value of the other column?

Say i have 2 columns, Bank and AccountNo
I want the AccountNo input is limited to 10 digits if the value in BANK column is A, and 11 digits if the BANK value is B.

Thank you so much for replying sir :slight_smile: