Forum Discussion

MeredithMoore5's avatar
MeredithMoore5
Qrew Assistant Captain
6 years ago

Encoding combined text fields to Numeric Sequences

I need all of the smartest QB cats on the block for this one. I have been banging my head against the limits of my Java Script and have come up with nothing that works.

Here is what I need to accomplish:

I need to take 3 text fields ([text1]&t[text2]&[text3]) and turn them into a string of numbers based on their alpha make up. Example: [a cat]&[the dog]&[a rat] = 10312222850417118122 (space can be 0)

Does anyone have a working formula for this?

7 Replies

  • There are several problems with your proposed encoding:

    (1) what happens to upper case letters or characters outside the range a-z?

    (2) your encoding "10312222850417118122" for "a catthe doga rat" is wrong as "t" is the 20'th letter of the alphabet not the 22'nd. There are more errors later in the string.

    (3) if you intend to decode your string at some point it isn't clear how say the sub-string "22" would be decoded as it could be "bb" or "v".

    In any event this fragment of code patches some of these issues and deals with issue (3) by inserting a space delimiter between the encodings for individual characters:
    var string = "a catthe doga rat".toLowerCase();
    string =  string.replace(/./g, function(c) {
      var charCode = c.charCodeAt(0);
      if (charCode == 32) {
        return 0 + " ";
      } else {
        return c.charCodeAt(0) - 96 + " ";
      }
    });
    console.log(string);
    //output:
    //1 0 3 1 20 20 8 5 0 4 15 7 1 0 18 1 20
  • MeredithMoore5's avatar
    MeredithMoore5
    Qrew Assistant Captain
    The set given was an example. 22 was a miscount on my tired and worn out part. I will not be decoding it. It is for the numerical need only. 
  • MeredithMoore5's avatar
    MeredithMoore5
    Qrew Assistant Captain
    Looking more to this: ([ToText] is already all lowercase, no space, concatenated text string from 3 different fields)

    var text String = [ToText];

    I need it to be numbers with no spaces between (so we can nix the +" "'s)

    For some reason, it keeps throwing errors.  not liking any of the .'s
    • MeredithMoore5's avatar
      MeredithMoore5
      Qrew Assistant Captain
      These are examples of the Text strings I am working with:

      accountsreceivablespecialistoperationsfrontlineservdel
      corporateservicesanalystoperationsprofessional
      m2mxcoordinatoroperationsadministrative
  • MeredithMoore5's avatar
    MeredithMoore5
    Qrew Assistant Captain
    Any links to any help articles or formulas would also be helpful. I have found lots of articles on different solutions, and none seem to work. :(
    • _anomDiebolt_'s avatar
      _anomDiebolt_
      Qrew Elite
      This can't be done with a conventional formula. You have to use JavaScript. However, there are minor details to resolve as to what that JavaScript should be because it isn't clear what the script should do in all cases. Your third example introduces the possibility that numbers may be included in the source string in which case there will be a "-" character in the output string:
      var string = "m2mxcoordinatoroperationsadministrative".toLowerCase();
      string =  string.replace(/./g, function(c) {
        var charCode = c.charCodeAt(0);
        if (charCode == 32) {
          return 0;
        } else {
          return c.charCodeAt(0) - 96;
        }
      });
      //"13-46132431515184914120151815165181209151419141391491920181209225"
      This script removes the spaces between characters (although they are very useful during debugging). You still have to accommodate all possible character values that could possibly be inputs with additional logic. 

      Once you get the script working from the console you can implement it with (1) the IOL technique or (2) the OEH (On Error HTML) technique using the OEH Machine which automatically creates the formula (ie no code page):

  • Would this work for you? It combines the three fields, replaces space with 0 (though I note that this may be redundant now) and lowercase letters with a numerical equivalent. Note it will leave all other characters (such as numerics, uppercase, symbols, letters with accents, etc.) unchanged.

    var text combined = [text1] & [text2] & [text3];
    var text space = SearchAndReplace($combined, " ", "0");
    var text a = SearchAndReplace($space, "a", "1");
    var text b = SearchAndReplace($a, "b", "2");
    var text c = SearchAndReplace($b, "c", "3");
    var text d = SearchAndReplace($c, "d", "4");
    var text e = SearchAndReplace($d, "e", "5");
    var text f = SearchAndReplace($e, "f", "6");
    var text g = SearchAndReplace($f, "g", "7");
    var text h = SearchAndReplace($g, "h", "8");
    var text i = SearchAndReplace($h, "i", "9");
    var text j = SearchAndReplace($i, "j", "10");
    var text k = SearchAndReplace($j, "k", "11");
    var text l = SearchAndReplace($k, "l", "12");
    var text m = SearchAndReplace($l, "m", "13");
    var text n = SearchAndReplace($m, "n", "14");
    var text o = SearchAndReplace($n, "o", "15");
    var text p = SearchAndReplace($o, "p", "16");
    var text q = SearchAndReplace($p, "q", "17");
    var text r = SearchAndReplace($q, "r", "18");
    var text s = SearchAndReplace($r, "s", "19");
    var text t = SearchAndReplace($s, "t", "20");
    var text u = SearchAndReplace($t, "u", "21");
    var text v = SearchAndReplace($u, "v", "22");
    var text w = SearchAndReplace($v, "w", "23");
    var text x = SearchAndReplace($w, "x", "24");
    var text y = SearchAndReplace($x, "y", "25");
    var text z = SearchAndReplace($y, "z", "26");
    $z