Hi Folks, I have a scannable column which i...

[Freemup_Technol]

Hi Folks,

I have a scannable column which is valid if and only if the first 6 letters of the value falls in a column given in another sheet.

I’ve put a valid_if constraint in AppSheet which looks like

=IN(LEFT([_THIS], 6),ProductMaster[StockCodePrefix])

Now my intent is quite clear. Allow any value inside this column as long as the first 6 letters are matching. However the constraint is being evaluated by appsheet such that any value other then the values in ProductMaster[StockCodePrefix] are being considered invalid.

Any help here ? The intent is to allow the barcodes which start with a set of prefixes rather then any and all barcodes.

[Aleksi_Alkio]

Try to use AND(TRUE,IN(…)).

[Steven_Coile]

You’re getting bit by some AppSheet magic.

If Valid_If consists of a column reference (e.g., ProductMaster[StockCodePrefix]) or entirely of an IN() expression (as you’re using), AppSheet makes the assumption that the list (the column reference or the list in the IN() expression) contains the entirety of the allowed values and presents the user with a dropdown menu containing only those values.

In your case, you don’t want the magic and the auto-generated dropdown menu. The workaround, as @Aleksi_Alkio suggested, is to “hide” the IN() expression so it isn’t the entirety of the Valid_If formula. The common method of hiding it is to wrap it in a benign AND() expression:

AND(IN(LEFT([_THIS], 6),ProductMaster[StockCodePrefix]), TRUE)

You could also instead use OR(…, FALSE) or NOT(NOT(…)).

[RezaRaoofi]

I understand the logic that Steve explained above could be confusing, particularly when something like [_THIS] is involved in the IN(…) expression, because user has not even had a chance to enter any value in [_THIS], and boom, a list of valid suggestions show up!

But if you had [AnotherColumn] in the IN(…) expression; in that case the default behavior would be selecting from a list of valid options which would make more sense.