How can I force my users to populate the phone number input to (###) ###-#### ?
I have a basic ‘Contacts’ table with Name, Street, City, State, Zip, Phone. On the gSheet side, I have the [Phone] formatted so that it displays as mentioned above. As my app stands now, the user can input any kind of phone number they want. I want to force them to input the numbers in the specified format. I’ve done some searching and couldn’t quite figure it out. Any and all help is greatly appreciated!
Thanks
From what I understand there is no firm masking in appsheet… Basic stuff like a valid on length, or requiring 2 parentheses… That stuff would be just some expression work in the valid if… But in my experience a non visible rule set really frustrates a user
@Grant_Stead Is there anyway to create an expression in the Valid_If and have a pop up warning show the correct input method?
@Brady_Lovell nope, the statement is a vanilla invalid box out that can’t be changed… You can always make an initial value to mimic what you want… but yeah the good options are pretty limited, I just don’t stress it… I only put valid requirements when the data really really needs it…
Thanks for the info @Grant_Stead! Guess I’ll just have to live with what I have available and hope AppSheet comes up with some sort of update to allow this option.
I don’t know if this feature is a “must” or not, but if your users needs to type the phone number like that, you can create an expression that could check everything from that entry. Not very simple formula, but doable. As Grant mentioned, it’s not a good idea if the user can’t understand the reason, but if you add a virtual show field with an explanation if the format is not correct, your user would understand it. Show the correct format with the description field and show the virtual show field only, if the validation is false.
Haha. Stumbled across this and I totally feel ya, @Brady_Lovell. It’s an old topic but figured I’d put my workaround in here in case anyone else wants to control phone number format.
I’ve got the [phone] column with data type Phone, which only shows in forms using the Show_if expression:
(CONTEXT(“ViewType”) = “form”)
Then, I’ve got a virtual column [_phoneFormatted] with data type Text, which only shows when NOT in forms:
(CONTEXT(“ViewType”) <> “form”)
The app formula for the virtual column formats the phone number by substituting all extra characters like (,-,), and spaces then creating the format I want.
Then, in Behavior>Actions, I change “Call Phone” appearance so it displays inline and attach it to the virtual column.
You may need to check the UX>View for your contact, “contact_Detail”, and change the column order so the formatted phone number and inline call button show up where you want instead of being weirdly tagged on at the bottom.
My app formula for the virtual column (you may need to change this up if you want to include country code and remove “+” from the original number):
“(”
&LEFT(Substitute(Substitute(Substitute(Substitute([employeePhone],“(”,“”),“)”,“”)," “,”“),”-“,”“),3)
&”) "
&MID(Substitute(Substitute(Substitute(Substitute([employeePhone],“(”,“”),“)”,“”)," “,”“),”-“,”“),4,3)
&”-"
&RIGHT(Substitute(Substitute(Substitute(Substitute([employeePhone],“(”,“”),“)”,“”)," “,”“),”-“,”"),4)
What about a Enum Virtual column based on the existing STATE adding their corresponding area code…
Also, you can do a web lookup to get the area the code using Excel Formulas (If that’s your data source)
Then you can concatenate with the phone to get what you look for…
Regards,
the way i worked out is
Valid If Expression:
AND(MID([Phone],4,1)="-",MID([Phone],8,1)="-",LEN([Phone])=12)
Invalid value error Expression:
IFS(
MID([Phone],4,1)<>"-","XXX-XXX-XXXX",
MID([Phone],8,1)<>"-","XXX-XXX-XXXX",
LEN([Phone])<12,"Number too short",
LEN([Phone])>12,"Number too long"
)
User end View:
