Only allow certain format in text box in appsheet form

[dilawar72]

I have a box in a form that needs the user to enter a project number. The project number format is ##-###. Is there anyway to make sure the entry matches this format? I don't want users to put in project numbers that are not the right format.

[WillowMobileSys]

There is not a way to force the format as the value is being entered.  However, AppSheet provides the ability to validate the entered value.  You will need to insert into the "Valid_If" property an expression that check the value for all of format criteria required.  You can also create custom validation messages to tell the user what exactly is wrong.  See image below and refer to this Check Form Input Validity article for more details details.

[dilawar72]

What formula would I put in the valid if box to only allow this format ##-### and give me an error message if its not in this format?

[WillowMobileSys]

First, so that users don't have to guess what the format should look like, you want to include a format example in the field label:

Field Name (e.g. 12-345)

The expression in Valid_If would need to be something like:

AND( 
    COUNT(SPLIT([_THIS], "-")) = 2,

    LEN(INDEX(SPLIT([_THIS], "-"), 1)) = 2,
    OR(INDEX(SPLIT([_THIS], "-"), 1) = "00",
       NUMBER(INDEX(SPLIT([_THIS], "-"), 1) > 0),

    LEN(INDEX(SPLIT([_THIS], "-"), 2)) = 3,
    OR(INDEX(SPLIT([_THIS], "-"), 2) = "000",
       NUMBER(INDEX(SPLIT([_THIS], "-"), 2) > 0)
) 

 This expression checks these things:

1. There is one and only one hyphen.
2. The length of characters before the hyphen is 2
3. The value of the characters before the hyphen is either "00" or is a number
4. The length of characters after the hyphen is 3
5. The value of the characters after the hyphen is either "000" or is a number

Error messages

For an error message, I think in this case I would just describe the format.

"The entered value must follow the format of a 2 digit number followed
by a hyphen followed by a 3 digit number. Please correct entry."

[dilawar72]

Thank you for this. When I copy and pasted the formula in I get an error message saying that the number of open and closed parentheses don't match. 

[Lynn]

Did you fix it and test it again?

[dilawar72]

No I couldn’t figure out where the missing parentheses were.

[WillowMobileSys · Accepted solution]

Try this:

AND( 
    COUNT(SPLIT([_THIS], "-")) = 2,

    LEN(INDEX(SPLIT([_THIS], "-"), 1)) = 2,
    OR(INDEX(SPLIT([_THIS], "-"), 1) = "00",
       NUMBER(INDEX(SPLIT([_THIS], "-"), 1)) > 0),

    LEN(INDEX(SPLIT([_THIS], "-"), 2)) = 3,
    OR(INDEX(SPLIT([_THIS], "-"), 2) = "000",
       NUMBER(INDEX(SPLIT([_THIS], "-"), 2)) > 0)
) 

One easy way to figure out missing parenthesis is to place the matching closing parenthesis under the function it belongs to.  something like this:

AND( 
    COUNT(
          SPLIT([_THIS], "-")
    ) = 2,

    LEN(
        INDEX(
              SPLIT([_THIS], "-"), 
              1
        )
    ) = 2,
    OR(
       INDEX(
             SPLIT([_THIS], "-"), 
             1
       ) = "00",
       NUMBER(
              INDEX(
                    SPLIT([_THIS], "-"), 
                    1
              ) 
       ?? > 0
    ),

    LEN(
    ... 
    )
) 

[Uk]

First, you have to reference it to another table contains the project number

example:

TableA as Form

TableB as a reference table contains the project numbers

You can use Ref, or, valid_if, or this formula

AUTOFILL DATA

any(select(tableb[columnprojectnumber],[_thisrow].[Idcolumntbalea]=[Idcolumnprojectnumber]))