Does a scanned field override the field's validation?

[Daisy_Ramirez]

We have the following validation in place on a scannable field. The user scanned a barcode into the field and it looks like a period was generated but the field allowed it to be saved.

Does a scan override the field’s validation or is there a validation expression problem?

[Koichi_Tsuji]

Im not following your point here.

Scan is scan, and validation is validation.

Get the value through scanning is same thing that that use manually type the text into the fields.

Once the text and strings are captured to the column, if they are savable, then there would be a problem in ValidIf expression and validtion for sure.

[Daisy_Ramirez]

Thanks Tsuji, based on your notes (scanned number = entered number) I’m wondering how this BIN Barcode was saved in the database when it doesn’t meet the valdif requirements.

[Koichi_Tsuji]

Hi @Daisy_Ramirez

Hope you are well.

I just thought your validation is against NUMBER , which is naturally no decimals, but your actual data is not a number, but decimal, how do you clarify this point ?

Are they simply text ?

[Daisy_Ramirez]

Thank you Tsuji - doing well Currently I have it set to TEXT but used the NUMBER() to indicate only a numerical value is allowed - characters are not allowed in the field. Its a barcode being scanne.

Hope you’re all staying well

[Koichi_Tsuji]

Thanks Daisy, I m super well, luckily.

Im not sure how your 3rd validation expression of NOT(IN([Bin…) would work for your presented case, but 1st of 2 expression len() and number is meeting your result of
11.0000199

11.0000199 — len is 10 (they are text, so inc. period)

Removing decimal part, is 11. Yes more than 0.

AppSheet - san is working based on your expression rightly.

[Daisy_Ramirez]

I would agree if the “.” is considered valid for NUMBER(). Is this the case? If yes, which validation expression should I use to ensure “.” is not allowed.

[Koichi_Tsuji]

Before you pass the string (texts) for validation through your expression, you need to remove .
using substitute expression.
This is what I thought now quickly, while i m enjoying my beer!!!

[Koichi_Tsuji]

When you selet text, all the character is just one string inside your whole string.

[Daisy_Ramirez]

Thanks Tsuji! I’ll try the substitute. Enjoy your beer - and have another for me!

Have a great weekend

[Koichi_Tsuji]

Your source and original field and column for valiation is text type, which gives a trickiness.

Before you pass the value of tihs fields for your own validation, why do you the validate by getting though such a validation?

Count(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
[Text],“1”,""),
“2”,""),
“3”,""),
“4”,""),
“5”,""),
“6”,""),
“7”,""),
“8”,""),
“9”,""),
“0”,""),
“.”,"")
)=0

This should make sure the data being scanned by your users are always number (incl period) , otherwise can not pass to your next validation.
When text is included, then it fails this validation.
Just have a think.

[Daisy_Ramirez]

Thanks Tsuji!

[Daisy_Ramirez · Accepted solution]

Just wanted to update the post with the implemented solution: I added the following to the validation expression:

NOT(CONTAINS([_THIS],".")),

AND(
LEN([_THIS])=10,
NUMBER([_THIS])>0,
NOT(CONTAINS([_THIS],".")),
NOT(IN([BIN Barcode],SELECT(Collection[BIN Barcode],
AND(NOT([collection_id]=[_THISROW].[collection_id]),
[route_id]=[_THISROW].[route_id]))))
)