Valid_If Expression not working as expected

[Lindsey_Lindow]

In a vehicle repair app, users must scan VIN barcodes when adding vehicles. Most of the time, the barcode scan returns just the VIN, but for some vehicle makes, the barcode returns the vehicle's VIN, plus a lot of additional information and special characters that are not needed. The desired behavior is that when a barcode scan returns more than just the vehicle's VIN, the app should recognize it as invalid and the user will then manually input the last 8 digits of the VIN and capture an image of the vehicle's VIN plate in another column. I currently have the following Valid_If expression for the VIN barcode scan column:

OR(
  LEN([VIN SCAN])=8,
  LEN([VIN SCAN])>=17,
    AND(
     NOT(CONTAINS([VIN SCAN], ",")),
     NOT(CONTAINS([VIN SCAN], " ")),
     NOT(CONTAINS([VIN SCAN], ".")),
     NOT(CONTAINS([VIN SCAN], "-")),
     NOT(CONTAINS([VIN SCAN], "/")),
     NOT(CONTAINS([VIN SCAN], "_")),
     NOT(CONTAINS([VIN SCAN], "#")),
     NOT(CONTAINS([VIN SCAN], "*")),
     NOT(CONTAINS([VIN SCAN], "-")),
     NOT(CONTAINS([VIN SCAN], "O"))
))

Today, a user scanned a VIN and the app accepted the barcode output which should have been invalid because it contains commas and spaces. The barcode in question returned the following when scanned:

1G6KB5RS2JU153626,2018,6KH69,07,18,K ,WDVJ2N, AR7 AXJ AYX CJ2 C3U DD8 DEH DNP EF7 FHO F46 GMU HJT IO6 I18 J56 LGX MAH M5N NE1 NP5 NUB PYZ RJZ RT9 UHS UQA U2M V8D WMJ ZCD 1SB 4CG 6HB 7HB 8HB 9HB,441B,,,,

Since my Valid_If expression specifies that the value cannot contain commas and spaces, I am unsure as to why the above entry was considered valid. Can someone please let me now what may be wrong with my expression?

[Rafael_ANEIC-PY]

Hello there @Lindsey_Lindow , while you explicitly forbid the presence of those special characters inside your AND() expresion, said expression lies within an OR() expression that allows anything as long as it has 17 or more characters.

Remember that an OR() statement only requires one of the expressions to return TRUE for it to return TRUE as a whole.

[Lindsey_Lindow]

Thank you for reading and replying to my post. Can you provide any guidance on how to re-write the expression to fit my needs? I've tried putting AND() before OR() and couldn't get it to work that way previously.

[Rafael_ANEIC-PY · Accepted solution]

If you're trying to limit your barcode entry to 8 to 17 VIN characters with no symbols allowed I'd use this expression:

AND(
     LEN([VIN SCAN])>=8,
     LEN([VIN SCAN])<=17
     NOT(CONTAINS([VIN SCAN], ",")),
     NOT(CONTAINS([VIN SCAN], " ")),
     NOT(CONTAINS([VIN SCAN], ".")),
     NOT(CONTAINS([VIN SCAN], "-")),
     NOT(CONTAINS([VIN SCAN], "/")),
     NOT(CONTAINS([VIN SCAN], "_")),
     NOT(CONTAINS([VIN SCAN], "#")),
     NOT(CONTAINS([VIN SCAN], "*")),
     NOT(CONTAINS([VIN SCAN], "-")),
     NOT(CONTAINS([VIN SCAN], "O"))
)

Or this one, if you only want 8 OR 17 characters allowed, with no symbols:

AND(
     OR(
      LEN([VIN SCAN])=8,
      LEN([VIN SCAN])=17
     )
     NOT(CONTAINS([VIN SCAN], ",")),
     NOT(CONTAINS([VIN SCAN], " ")),
     NOT(CONTAINS([VIN SCAN], ".")),
     NOT(CONTAINS([VIN SCAN], "-")),
     NOT(CONTAINS([VIN SCAN], "/")),
     NOT(CONTAINS([VIN SCAN], "_")),
     NOT(CONTAINS([VIN SCAN], "#")),
     NOT(CONTAINS([VIN SCAN], "*")),
     NOT(CONTAINS([VIN SCAN], "-")),
     NOT(CONTAINS([VIN SCAN], "O"))
)

[Lindsey_Lindow]

Thank you so much for your help! Your second suggestion is exactly what I need 🙂