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

Sentra_Ventures · 09-12-2020 03:23 PM

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.

Koichi_Tsuji

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.

Suvrutt_Gurjar

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?

Koichi_Tsuji

This needs to be text type.

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

Suvrutt_Gurjar

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.

Koichi_Tsuji

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

Suvrutt_Gurjar

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

Suvrutt_Gurjar

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…

Koichi_Tsuji

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

sukur_bejo

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?

OptimiX_XcrY

Is you column a text type ?

sukur_bejo

Yes it is sir.

Koichi_Tsuji

I placed a sample app for the community.

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

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.

sukur_bejo

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

Aleksi

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

Aleksi

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

Koichi_Tsuji

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

Suvrutt_Gurjar

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.

Koichi_Tsuji

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.

Suvrutt_Gurjar

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()

Koichi_Tsuji

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.

Sentra_Ventures

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.

Koichi_Tsuji

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

Aleksi

Just throwing ideas

Steve

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])

stilllifemuseum

Hola! Les comento mi experiencia con esta expresión y cómo resolví mi problema con ella.

Los números de teléfono en Argentina tienen diez dígitos si se tienen en cuenta el código de área (indicativo interurbano) y el número de abonado, pero sin tener en cuenta el prefijo telefónico internacional: los códigos de área pueden tener 2, 3 o 4 dígitos, siendo los 6, 7 u 8 dígitos restantes el número de teléfono local.

Siendo los valores código de área estáticos, pudiendo así colocarlos en una columna Enum (type Ref) y desplegándolos en un dropdown, tenía que colocar los siguientes números locales en otra columna cuyo Valid_if expresara que:
Si los valores del código de área cuentan 2 dígitos, los valores de número local serán de 8 dígitos,
Si los valores del código de área cuentan 3 dígitos, los valores de número local serán de 7 dígitos,
Si los valores del código de área cuentan 4 dígitos, los valores de número local serán de 6 dígitos.

El resultado fue una combinación entre la solución de @tsuji_koichi y @Steve, se las comparto:

IFS(
LEN([caracteristica]) = 2, 
AND(
	LEN([_THIS]) = 8,
  CONTAINS("0123456789", MID([_THIS], 1, 1)),
  CONTAINS("0123456789", MID([_THIS], 2, 1)),
  CONTAINS("0123456789", MID([_THIS], 3, 1)),
  CONTAINS("0123456789", MID([_THIS], 4, 1)),  
  CONTAINS("0123456789", MID([_THIS], 5, 1)),
  CONTAINS("0123456789", MID([_THIS], 6, 1)),
  CONTAINS("0123456789", MID([_THIS], 7, 1)),
  CONTAINS("0123456789", MID([_THIS], 8, 1))),
LEN([caracteristica]) = 3, 
AND(
	LEN([_THIS]) = 7,
  CONTAINS("0123456789", MID([_THIS], 1, 1)),
  CONTAINS("0123456789", MID([_THIS], 2, 1)),
  CONTAINS("0123456789", MID([_THIS], 3, 1)),
  CONTAINS("0123456789", MID([_THIS], 4, 1)),  
  CONTAINS("0123456789", MID([_THIS], 5, 1)),
  CONTAINS("0123456789", MID([_THIS], 6, 1)),
  CONTAINS("0123456789", MID([_THIS], 7, 1))),
LEN([caracteristica]) = 4, 
AND(
	LEN([_THIS]) = 6,
  CONTAINS("0123456789", MID([_THIS], 1, 1)),
  CONTAINS("0123456789", MID([_THIS], 2, 1)),
  CONTAINS("0123456789", MID([_THIS], 3, 1)),
  CONTAINS("0123456789", MID([_THIS], 4, 1)),  
  CONTAINS("0123456789", MID([_THIS], 5, 1)),
  CONTAINS("0123456789", MID([_THIS], 6, 1))),
)

Los códigos de área se pueden buscar en Números telefónicos en Argentina
Recuerden todas las columnas involucradas (la columna que tiene los valores numéricos en la tabla de códigos de área conectada vía Enum type REF, [caracteristica] en la fórmula, y la columna de números locales, [_THIS] en la fórmula) son tipo TEXT, para que la función LEN() pueda funcionar.

Saludos!

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