Valid if using wildcard

[ezmedicom1234]

Hi I made a app scanning barcode for tracking our delivery record.

Unfortunately, sometimes scanned barcodes didn't match with correct barcode.

For example, acutal barcode is this : I2301A123456

but scanned barcode value shown like this : IZ301AP12345

So to solve this problem I want to use valid if formula

So only code scanned like : IYYMMA123456 is valid.

Our barcode starts with "I" and followed by "YY(year)" and "MM(month)" and "A" and six digit number.

How can I make this kind of formula?

[Aurelien]

Hi @ezmedicom1234 

What about:

AND(
  FIND("I",[_THIS])=1,
  FIND("A",[_THIS])=6,
  ANY(EXTRACTNUMBERS(MID([_THIS],2,4)))=NUMBER(MID([_THIS],2,4)),
  NUMBER(MID([_THIS],2,2))>=23,
  NUMBER(MID([_THIS],4,2))<=12,
  ANY(EXTRACTNUMBERS(MID([_THIS],7,6)))=NUMBER(MID([_THIS],7,6))
)

Please note I didn't test this expression.

 For reference:

EXTRACTNUMBERS() - AppSheet Help

MID() - AppSheet Help

FIND() - AppSheet Help

ANY() - AppSheet Help

NUMBER() - AppSheet Help

[ezmedicom1234]

I2310A part worked as intended, but last six digit number thing does not work it seemed. I2310A123W23 accepted as a valid data. Thanks anyway!

* Did some more test, and this formula also perceived I231aA084742 as a valid value

[Aurelien]

This may be due to one condition missing. Can you try:

AND(
  FIND("I",[_THIS])=1,
  FIND("A",[_THIS])=6,
  ANY(EXTRACTNUMBERS(MID([_THIS],2,4)))=NUMBER(MID([_THIS],2,4)),
  NUMBER(MID([_THIS],2,2))>=23,
  NUMBER(MID([_THIS],4,2))<=12,
  ANY(EXTRACTNUMBERS(MID([_THIS],7,6)))=NUMBER(MID([_THIS],7,6)),
  LEN(ANY(EXTRACTNUMBERS(MID([_THIS],7,6))))=6
)

[ezmedicom1234]

now it says this

"Column Name 'Barcode' in Schema 'ScanBarcode_Schema' of Column Type 'Text' has an invalid data validation constraint '=AND( FIND("I",[_THIS])=1, FIND("A",[_THIS])=6, ANY(EXTRACTNUMBERS(MID([_THIS],2,4)))=NUMBER(MID([_THIS],2,4)), NUMBER(MID([_THIS],2,2))>=23, NUMBER(MID([_THIS],4,2))<=12, ANY(EXTRACTNUMBERS(MID([_THIS],7,6)))=NUMBER(MID([_THIS],7,6)), LEN(ANY(EXTRACTNUMBERS(MID([_THIS],7,6))))=6 )'. LEN function is used incorrectly"

[Aurelien]

OK. What about:

AND(
  FIND("I",[_THIS])=1,
  FIND("A",[_THIS])=6,
  ANY(EXTRACTNUMBERS(MID([_THIS],2,4)))=NUMBER(MID([_THIS],2,4)),
  NUMBER(MID([_THIS],2,2))>=23,
  NUMBER(MID([_THIS],4,2))<=12,
  ANY(EXTRACTNUMBERS(MID([_THIS],7,6)))=NUMBER(MID([_THIS],7,6)),
  LEN("" & ANY(EXTRACTNUMBERS(MID([_THIS],7,6))))=6
)

[ezmedicom1234]

The formula does perceive valid value as an invalid value

ex) I2312A084742

[Aurelien]

OK. For this specific case, I would recommend splitting my suggestion above and track which part goes wrong. Can you provide a feedback about it?

[ezmedicom1234]

Ok! Thank you for your answers! It'll take some time though before I can get some results.