Discussions

Expand all | Collapse all

Remove disallowed characters from a text field

  • 1.  Remove disallowed characters from a text field

    Posted 02-24-2017 13:19
    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?


  • 2.  RE: Remove disallowed characters from a text field

    Posted 02-24-2017 13:24
    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.  


  • 3.  RE: Remove disallowed characters from a text field

    Posted 02-24-2017 13:31
    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,"\";@#$\\/")


  • 4.  RE: Remove disallowed characters from a text field

    Posted 02-24-2017 13:35
    This is perfect! Thank you so much!


  • 5.  RE: Remove disallowed characters from a text field

    Posted 02-24-2017 13:37
    Does your dog do the QuickBase work and you take care of the dog or is it the other way around?


  • 6.  RE: Remove disallowed characters from a text field

    Top
    Contributor
    Posted 02-24-2017 14:05
    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


  • 7.  RE: Remove disallowed characters from a text field

    Bronze
    Contributor
    Posted 01-04-2019 19:57
    In QuickBaseCoach's answer... does that one line capture all of them? or in this case, do you have to still run through the Part 9 times?


  • 8.  RE: Remove disallowed characters from a text field

    Posted 01-04-2019 20:05
    Yes, you need to have that long Part formula for as many "parts" that there could be, where a part is delimited by any of those trap characters.






  • 9.  RE: Remove disallowed characters from a text field

    Bronze
    Contributor
    Posted 01-04-2019 20:29
    It might help to understand what the part function does. It takes an input and divides it into parts, and the 'fenceposts' it uses to divide, is what you define as the 'delimiter'.

    so

    Part([Project Name],1," ") &  Part([Project Name],2," ")

    says:
    'divide the field into parts defined by spaces. get the first part.'
    'and then append to that'
    'divide the field into parts defined by spaces. get the second part.'

    in this context, that's simply performing the handy function of taking out all the characters you define as delimiters in your part function, and thus serving as a replace/remove kind of function. So you could make it say

    Part([Project Name],1," -") &   Part([Project Name],2," -")

    which does the same thing except it uses - as well as [space] to part out your input.

    etc.

    So the reason the part function is run through 9 times is because doing it once doesn't REMOVE or REPLACE the offending characters, it just stops when it gets to one. So in Mark/Coach's answer to the previous poster, if you only use the one line he posted, you'll just get whatever is in your field until it finds the first character you don't like. You have to repeat the part function a number of times to get the 'replace' functionality you're actually looking for.

    Does that help?


    I guess somehow I never realized until today that QB doesn't have an equivalent to what's in Excel called SUBSTITUTE, or, apparently, any kind of string transformation function. Using Part() this way is a pretty cool workaround.

    Also, I expect your question was answered more succinctly while I was typing all this out. I'm posting it anyway.