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?
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
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
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
)
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"
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
)
The formula does perceive valid value as an invalid value
ex) I2312A084742
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?
Ok! Thank you for your answers! It'll take some time though before I can get some results.
User | Count |
---|---|
43 | |
26 | |
23 | |
14 | |
12 |