Remove disallowed characters from a text field

  • 1
  • 1
  • Question
  • Updated 2 years ago
  • Answered
Our app has a [Project Name] field in which our users can input whatever project name they like. We then have a [Project ID] field that uses 9 Part([Project Name],1," ") formulas to combine the first words of the project name without spaces:
Right(ToText(Year(ToDate([Date Created]))),2) & Right("0" & ToText(Month(ToDate([Date Created]))),2) & Right("0" & ToText(Day(ToDate([Date Created]))),2) & If(Length([Master Project Abbreviation]) > 0,"-" & [Master Project Abbreviation]) & "-" & If(Length([Department Abbreviation]) > 0,[Department Abbreviation],[Collaborator Department Abbreviation]) & "-" & Trim(
Part([Project Name],1," ")
&
Part([Project Name],2," ")
&
Part([Project Name],3," ")
&
Part([Project Name],4," ")
&
Part([Project Name],5," ")
&
Part([Project Name],6," ")
&
Part([Project Name],7," ")
&
Part([Project Name],8," ")
&
Part([Project Name],9," ")
)
This project ID also becomes a folder name in our file storage. However, our file storage can't handle characters like quotation marks, colons, semicolons, etc. Is there a way to strip disallowed characters when reading data from a text field?
Photo of Jason

Jason

  • 152 Points 100 badge 2x thumb

Posted 2 years ago

  • 1
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
The arguments for the Part function are all the delimiter characters you want to trap.  So you can do this



Part([Project Name],1," !@#$%^&*';:?/><")
But, I don't know how to trap a quote  ".  I will do a quick test now to see if a "" will do it.  
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
This seemed to work to trap a quote.  the backslash acts as as an escape character.  But next  you will be wanting to trap the backslash too!  I think that you use a double \ to do that.

Part([Part entry],1,"\";@#$\\/")
Photo of Jason

Jason

  • 152 Points 100 badge 2x thumb
This is perfect! Thank you so much!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
Does your dog do the QuickBase work and you take care of the dog or is it the other way around?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,884 Points 20k badge 2x thumb
Use IOL:

Remove Disallowed Characters
https://haversineconsulting.quickbase.com/db/bmj2gmzcs?a=nwr

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=556

It is best to prevent the disallowed characters from entering your application during keyboarding rather than after the fact using formulas.

If you or your dog need additional assistance implementing this solution feel free to contact me off-world using the information in my profile:

https://getsatisfaction.com/people/dandiebolt
(Edited)