Validation for No Special Characters in a Phone Number Text Input

[ElizabethK]

Hello,

I have a PowerApp connected to a SharePoint list with a number input box. This textbox is intended for a phone number, but we do not want any special characters. I made the range for the numbers in the SharePoint List 0-9. I tried using the validation:

IsMatch(DataCardValue5.Text,".*[\\\"&Char(34)&"/<>!@#$%^&*()].*") 

But it still is allowing me to type a period. Is there any way to eliminate the period? 

A gif below shows me still able to type the period, but not able to type the - or \ 

Your help is appreciated! Thank you. 

 

[ElizabethK]

@RandyHayes yes, all of that logic checks out. 

If the user says yes to phone change but types nothing…that sucks but technically no harm. 

If the user types 1 number, we would like them to type the full number so yes. We need 0-9 for 10 digits (formatted or not) 

if it matters, right now the share point column itself is set as a number column but 0 minimum and 9 maximum and no separating commas. I was unsure if single line text input would be better or if it matters. 

Thank you so much, Randy! 

[RandyHayes]

@ElizabethK 

When you say that it is set for 0 as min and 9 as max, then you'll not be able to get what you want as it will only allow a single number between 0 and 9.

Your phone numbers are going to be numeric and much larger than 9!

I would remove the min and max on the column.

As a future-thinking concept to keep in mind - phone numbers can get tricky.  In some cases you might need country codes.  In some cases you might need extensions.  Etc...

If you restrict your column to be numeric, then you will need additional columns for the other aspects of the number.  For simplicity, having the phone number as a text column instead, allows you to provide other number details.  Example (123) 456-7890 x123 can be stored as:  1234567890x123 or, depending on the application, 1234567890123 and then 10 digit number assumed and all remaining are extension.  Ex: 12345678904321 can be formatted as (123) 456-7890 x4321

Just mentioning as an FYI so that you don't restrict yourself by the data type.

Now, back to the story...

When it comes to providing a warning label to someone in a form, I usually utilize a "one shot" process.  You have one shot to get it right and then I will show you errors.

So, how to do that is, in the action that performs the OnSubmit for the form, the formula would be similar to the following:

UpdateContext({lclOneShot: formName.Valid});
If(formName.Valid, SubmitForm(formName))

Then, in the Visible of the warning label (for strictly entry of 10 digits only):

lclOneShot &&
!IsBlank(DataCardValue5.Text) &&
DataCardValue5.Visible &&
!IsMatch(DataCardValue5.Text,"^\d{10}$") 

And for formatting allowed entry (I will be using the formatting allowed option from here on in this reponse):

lclOneShot &&
!IsBlank(DataCardValue5.Text) &&
DataCardValue5.Visible &&
!IsMatch(DataCardValue5.Text, "^\(*\d{3}\)*-* *\d{3}-*\d{4}$")

That will govern the visibility of the warning label, which should just notify that the phone number is not valid.

What that will do is, if the user has tried to submit the form once and it is not valid (more on that shortly) AND the text entry is not blank (meaning that they started typing something) AND the text entry is Visible (assuming you have that governed by the dropdown to select changing the number) AND the data entered into the textinput does not match the pattern, then the warning label will be visible.

Now...the form have a really nice property on it called Valid.  Valid is true if all of the required fields have data entered into them.  That is really all it validates.  To "custom" validate, you need to do a little more on your own.

So...first we need to set the Required property of the datacard for that phone number field.  To do that, the formula on the Required property would be:

!IsBlank(DataCardValue5.Text) &&
DataCardValue5.Visible

This will make the field "required" (and thus included in the .Valid property of the form) only if the text input control is visible (again, assumed governed by your dropdown) AND there is something typed into that control.  If it is visible and there is nothing typed into the text input (remember, you said this is valid...they can say they want to change, but then not enter anything) then the field is not considered required.  But, if they do type something in, then it now needs to be correct.

The next thing we need to do it to establish the Update property of the data card.  The update property will not only determine what is written to the list when submitted, but also will participate in the .Valid property evaluation of the form.

So, the Update property would be:

If(DataCardValue5.Visible &&
   IsMatch(DataCardValue5.Text, "^\(*\d{3}\)*-* *\d{3}-*\d{4}$"),
   With({_l:Concat(Split(DataCardValue5.Text, ""), If(IsNumeric(Result), Result, ""))}, If(Len(_l)=10, _l, Blank())
   ),
   Self.Default
)

This should give all the formulas needed to have the .Valid work properly and also to abide by the rules of the change.  Also will provide a one shot opportunity for the user to enter a number without getting a warning.

There is some question in my mind on the Update formula only because we have not discussed some other "what if's" - like, what if the record already has a number and they choose to change and then enter nothing.  And a couple other What If's like that.  But, this should get you heading in the right direction.

[ElizabethK]

Hi @RandyHayes 

Thank you yet again for getting back to me and for spending so much time on this just to help me.  I changed the column type to single line text.

Small aside - I also appreciate your input about the context of phone numbers. We will only require 10 digit numbers for this particular form because that number will always be a mobile number in the US (they will be receiving a text with a link to the number they provide ultimately).

So back to the main event, your instructions are beautiful, it is possible I just botched them up in execution. I will try to show my work to the teacher... 

On Submit for the form:

Warning Label:

Here is where I got confused and had to use google (shame). I didn't know what you meant by "formatting allowed entry" so what I found with google on where to put the format formula your provided was the "On Change" for DataCardValue5 - I could be wrong.

Formatting Allowed Entry (OnChange?)

Okay so then I went to the dropdown question, the one that guides the visibility of the new phone number datacardvalue5, I changed that required formula

Required

Then I did the update property of the New Phone card

Update

So now if I put in a number that is NOT 10 digits...it gives me a warning but I still can submit it. - for example 2222. If I put one in that is in the XXX-XXX-XXXX format, it appears in the SharePoint data as 10 digits without dashes (perfect). If for some reason I make a wild entry and I type in 5$%#12 or something - it gives me the warning, but I can still submit the form and then nothing shows up in the SharePoint at all.

My guidance to you may have impacted this and after executing the formulas, it is possible I have changed my mind. Is there a way to make it required for them that if they are going to say yes there is a new number, they need to at least write out a 10 digit number? If not or if the effort of explaining how to accomplish that is too much, please let me know because I have taken a lot of your time. 

Otherwise it works very well and I am so thankful for your help. ❤️

[RandyHayes · Accepted solution]

@ElizabethK 

Ah my student 😊 where you went off the rail was in the third step - those formulas I provided were for the Visible property of your label.

In your second screen shot - that formula is correct if you are only going to allow users to enter 10 digits with no formatting in it.

The second formula (the one mentioned in my response as And for formatting allowed entry (I will be using the formatting allowed option from here on in this response):

That also was for the Visible property, but that formula would allow users to not only just enter 10 digits, but also, if they wanted to, to enter formatting characters.

You definitely do NOT want that on an OnChange action.

Let's fix that first and the look at the other scenario.  If they enter something that is not valid, then it should not let them submit.  So your wild entry should not have submitted.  And, it kind of does not submit that.

The way the formula was set up, if the entry was not valid, then it would have the existing phone number submitted.  This is why I mentioned the "There is some question in my mind on the Update formula only because we have not discussed some other "what if's"..."

Because, if a record has a phone number and a person chooses to enter a new one, but then does not add a valid number, it is really submitting the previous valid number and not the entered invalid number.

That can use some refinement I am sure, but since the "what if's" were a little murky in my mind for what you wanted, I took one route.  We can clean that up.  But for now, let's get the other issue fixed and see where things are.

Always happy to help 😁

[ElizabethK]

Hi friend/teacher/great one,

Working later tonight - did not have time to solve this during the day but I so appreciate your response. So, the app is working lovely, and works well enough for them to do UAT this weekend. The only thing is it isn't behaving consistently. So when I first go to the app and fill out the information - if I use a special character, it won't submit, but it also won't tell me with the warning label or with the error message that I have formatted for the form for OnFailure. After I submit a version that the form "likes" it will accept the phone number and put it in the sharepoint without dashes. Then on the reset version of the form (after I have submitted a form and get a new, reset screen), if I type in wild symbols, I get the warning label. I am not really sure where I went wrong at this point. I appreciate your help and I sent coffee yesterday. Have a wonderful weekend.

[Uthhra]

Hi,

In my case I have a number field which allows + and - symbol to enter in the field and gives a error message "The value - cannot be converted to a number". For other special
characters it's not allowed to enter.
Any help would be greatly appreciated.