Forum Discussion

JimHarrison's avatar
JimHarrison
Qrew Champion
2 years ago

Finding non- windows-1252 Characters in Text fields using a formula

I am looking for a way to expose non-windows-1252 characters when Users paste them into text fields in Quickbase.

I tried this but it doesn't work.

If(Contains([Scope change Description],"'", true,false)

If(Contains(URLEncode([Scope change Description]),"'", true,false)

If(Contains([Scope change Description],"URLEncode(')", true,false)

If(Contains(URLEncode([Scope change Description]),"URLEncode(')", true,false)

Thought I'd see if anyone has cracked this before I find the answer through random trying. I submitted a support case to Quickbase support as well. If I find anything I'll post it here.

------------------------------
Jim Harrison
transparency = knowledge + understanding : The Scrum Dudes
------------------------------

5 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew #1 Challenger
    2 years ago

    Just this week I built this lovely formula. It was a royal pain.  It might be your lucky day. 

    Website

    https://www.urlencoder.io/learn/

    The website says we only have to deal with the green ones 


    var text RawOne = URLEncode([My Pasted Field]);

    var text ReplaceLineFeed =    SearchAndReplace($RawOne, "%0A", "<br>");
    var text ReplaceCarriageReturn =    SearchAndReplace($ReplaceLineFeed, "%0D", "<br>");
    var text ReplaceSpace =       SearchAndReplace($ReplaceCarriageReturn, "%20", " ");
    var text ReplaceExclamation = SearchAndReplace($ReplaceSpace, "%21", "!");
    var text ReplaceQuote =       SearchAndReplace($ReplaceExclamation, "%22", "\"");
    var text ReplaceHash =        SearchAndReplace($ReplaceQuote, "%23", "#");
    var text ReplaceDollar =      SearchAndReplace($ReplaceHash, "%24", "$");
    var text ReplacePercent =     SearchAndReplace($ReplaceDollar,"%25", "%");
    var text ReplaceAmphersand =  SearchAndReplace($ReplacePercent,"%26","&");
    var text ReplaceSingleQuote = SearchAndReplace($ReplaceAmphersand,"%27", "'");
    var text ReplaceLeftRoundBracket = SearchAndReplace($ReplaceSingleQuote,"%28", "(");
    var text ReplaceRightRoundBracket = SearchAndReplace($ReplaceLeftRoundBracket,"%29", ")");
    var text ReplaceStar =       SearchAndReplace($ReplaceRightRoundBracket,"%2A","*");
    var text ReplacePlus =       SearchAndReplace($ReplaceStar,"%2B", "+");
    var text ReplaceComma =      SearchAndReplace($ReplacePlus, "%2C", ",");
    var text ReplaceSlash =      SearchAndReplace($ReplaceComma,"%2F", "/");
    var text ReplaceColon =      SearchAndReplace($ReplaceSlash,"%3A",":");
    var text ReplaceSemiColon =  SearchAndReplace($ReplaceColon,"%3B", ";");
    var text ReplaceLT =         SearchAndReplace($ReplaceSemiColon,"%3C","<");
    var text ReplaceEquals =     SearchAndReplace($ReplaceLT,"%3D", "=");
    var text ReplaceGT =         SearchAndReplace($ReplaceEquals,"%3E",">");
    var text ReplaceQuestion =   SearchAndReplace($ReplaceGT,"%3F","?");
    var text ReplaceAT =         SearchAndReplace($ReplaceQuestion,"%40","@");
    var text ReplaceLeftSquareBracket = SearchAndReplace($ReplaceAT,"%5B", "[");
    var text ReplaceBackSlash =  SearchAndReplace($ReplaceLeftSquareBracket,"%5C","\\");
    var text ReplaceRightSquareBracket = SearchAndReplace($ReplaceBackSlash,"%5D", "]");
    var text ReplaceCaret =     SearchAndReplace($ReplaceRightSquareBracket,"%5E", "^");
    var text ReplaceBackTick =  SearchAndReplace($ReplaceCaret,"%60", "`");


    $ReplaceBackTick

       



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • JimHarrison's avatar
      JimHarrison
      Qrew Champion
      2 years ago
      Nice! I have my ASCII windows-1252 reference (https://www.ascii-code.com/CP1251) on my desk, if I run across anything else I'll add it to the end of the list.

      ------------------------------
      Jim Harrison
      transparency = knowledge + understanding : The Scrum Dudes
      ------------------------------
      • JimHarrison's avatar
        JimHarrison
        Qrew Champion
        2 years ago

        Werps, I guess the button that says reply in the email doesn't update the post. 
        We needed to see the special characters in a field so we can diagnose what is causing the problem. So I took what Mark posted and modified it so if it finds a special character it gets added to the final text of the field. 

        UPDATED!

        var text RawOne= URLEncode( [Scope Change Description]);

        var text HorizontalTab= ToText(If(Contains($RawOne, "%09"),"htab." ,"."));
        var text LineFeed= ToText(If(Contains($RawOne, "%0A"),"lf." ,"."));
        var text CarriageReturn= ToText(If(Contains($RawOne, "%0D"),"cr." ,"."));
        var text Space= ToText(If(Contains($RawOne, "%20"),"sp." ,"."));
        var text Exclamation= ToText(If(Contains($RawOne, "%21"),"exclm." ,"."));
        var text Quote= ToText(If(Contains($RawOne, "%22"),"quote." ,"."));
        var text Hash= ToText(If(Contains($RawOne, "%23"),"hash." ,"."));
        var text Dollar= ToText(If(Contains($RawOne, "%24"),"dollr." ,"."));
        var text Percent= ToText(If(Contains($RawOne, "%25"),"pcent." ,"."));
        var text Amphersand= ToText(If(Contains($RawOne, "%26"),"amp." ,"."));
        var text SingleQuote= ToText(If(Contains($RawOne, "%27"),"snglqt." ,"."));
        var text LeftRoundBracket= ToText(If(Contains($RawOne, "%28"),"lbrkt." ,"."));
        var text RightRoundBracket= ToText(If(Contains($RawOne, "%29"),"rbrkt." ,"."));
        var text Star= ToText(If(Contains($RawOne, "%2A"),"star." ,"."));
        var text Plus= ToText(If(Contains($RawOne, "%2B"),"plus." ,"."));
        var text Comma= ToText(If(Contains($RawOne, "%2C"),"comma." ,"."));
        var text Slash= ToText(If(Contains($RawOne, "%2F"),"slash." ,"."));
        var text Colon= ToText(If(Contains($RawOne, "%3A"),"colon." ,"."));
        var text SemiColon= ToText(If(Contains($RawOne, "%3B"),"secolon." ,"."));
        var text LessThan= ToText(If(Contains($RawOne, "%3C"),"lessthan." ,"."));
        var text Equals= ToText(If(Contains($RawOne, "%3D"),"equals." ,"."));
        var text GreaterThan= ToText(If(Contains($RawOne, "%3E"),"grtrthan." ,"."));
        var text Question= ToText(If(Contains($RawOne, "%3F"),"questn." ,"."));
        var text At= ToText(If(Contains($RawOne, "%40"),"At." ,"."));
        var text LeftSquareBracket= ToText(If(Contains($RawOne, "%5B"),"lsqbrkt." ,"."));
        var text BackSlash= ToText(If(Contains($RawOne, "%5C"),"bkslsh." ,"."));
        var text RightSquareBracket= ToText(If(Contains($RawOne, "%5D"),"rsqbrkt." ,"."));
        var text Caret= ToText(If(Contains($RawOne, "%5E"),"caret." ,"."));
        var text BackTick= ToText(If(Contains($RawOne, "%60"),"bktic." ,"."));
        var text LSingleQuote= ToText(If(Contains($RawOne, "%91"),"lsnglQuote." ,"."));
        var text RSingleQuote= ToText(If(Contains($RawOne, "%92"),"rsnglQuote." ,"."));
        var text LQuote= ToText(If(Contains($RawOne, "%93"),"lQuote." ,"."));
        var text RQuote= ToText(If(Contains($RawOne, "%94"),"rQuote." ,"."));
        var text NDash= ToText(If(Contains($RawOne, "%96"),"ndash." ,"."));
        var text MDash= ToText(If(Contains($RawOne, "%97"),"mdash." ,"."));
        var text MiddleDot= ToText(If(Contains($RawOne, "%B7"),"dot." ,"."));
        var text FractionOnHalf= ToText(If(Contains($RawOne, "%BD"),"half." ,"."));

        $HorizontalTab & $LineFeed & $CarriageReturn & $Space & $Exclamation & $Quote & $Hash & $Dollar & $Percent & $Amphersand & $SingleQuote & $LeftRoundBracket & $RightRoundBracket & $Star & $Plus & $Comma & $Slash & $Colon & $SemiColon & $LessThan & $Equals & $GreaterThan & $Question & $At & $LeftSquareBracket & $BackSlash & $RightSquareBracket & $Caret & $BackTick & $LSingleQuote & $RSingleQuote & $LQuote & $RQuote & $NDash & $MDash & $MiddleDot & $FractionOnHalf



        ------------------------------
        Jim Harrison
        transparency = knowledge + understanding : The Scrum Dudes
        ------------------------------