Discussions

Expand all | Collapse all

I need a formula to format a numeric field to show like this 051.801.264/0001-85

_anom Diebolt)02-03-2018 21:09

  • 1.  I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-01-2018 23:47
    I have a numeric field that is a tax id number field, and the format is all the same.
    051.801.264/0001-85
    It has dots and dashes and a slash. Some customers use only numbers and others use the dots and symbols when filling out.

    Is there a formula that can be helpful here in this case?

    xxx.xxx.xxx/xxxx-xx

    I have seen fields that it starts to add the dashes and dots automatically as you start typing.
    I appreciate all the help?


  • 2.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-02-2018 00:44
    So easy:

    Tax ID Auto Format ~ Add New Record
    https://haversineconsulting.quickbase.com/db/bnfj79i2f?a=nwr

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

    Notes:

    (1) I did this pretty fast and there are a few things to add. The script is responding to keyup events so if you were to paste a string into the field it will not apply the formatting.

    (2) You can enter a misplaced ".""-" or "/" character and it will not be disallowed

    (3) maybe some other weird edge cases but they are easy to fix.


  • 3.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-03-2018 17:13
    My friend, you are awesome!
    I have been following all your information, and I try to use most of your material, but I think I am not so smart when it comes to quickbase.

    I am creating a field url formula, I paste your code there, but it always shows me error messages. I think i need to learn still how to apply all your codes, which fields to use etc.

    Is there a video somewhere that i could learn how to use your codes in my applications? I have searched the whole internet and cant find one that shows me the step by step. Or a guide would work too.
    Thank you so much and enjoy your show!


  • 4.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-03-2018 17:36
    Many of my solutions involve using JavaScript which is not the same as QuickBase's formula language. The problem you are having is that you are attempting to paste my JavaScript code directly into the formula definition. 

    The way to use the script is to set up a special rich text field and place it on the form using a technique called the image onload technique (IOL technique) and described here:

    https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=294

    There are hundreds of similar scripts in the pastie database which use the IOL technique (and other scripting techniques). If you need help beyond the resources in the forum or the pastie database you can ask further questions here and I will answer as time permits or you can contact me directly using the info in my profile:

    https://getsatisfaction.com/people/dandiebolt">https://getsatisfaction.com/people/dandiebolt">https://getsatisfaction.com/people/dandiebolt


  • 5.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-03-2018 18:19
    Thank you so much. I left you a message on your phone number and also texted you.
    I guess I am stuck in the 1st step where i need to insert a "user defined variable". Can you give me a hint here? i feel after that i will be able to follow the other steps.
    Thank you!


  • 6.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-03-2018 18:34

    I am a consultant and while I often work on the weekend I am just following up on some email and leftover tasks and I probably won't be available till Monday.

    Can probably answer via the forum:

    To create the two user defined variables:

    Navigate: Dashboard | Settings | Variables | New Variable

    Should look like this when done:



    User defined variables are sort of like fields which once created are available in every table so they can be used in formulas. They are often used to provide a application wide "parameter" to a formula. In the case of the IOL technique the two variables [iol] and [/iol] are used to so you don't have to type in some very odd looking strings every time you want to use the IOL - in other words once you create  these two user defined variables they can be user repeatedly by just creating a new rich text formula like so:

    [iol] & "moduleTable.js" & [/iol]

    moduleTable.js is the name of the code page where the script goes.


  • 7.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-03-2018 18:51
    You are the best!
    I will probably talk to you next week to get a quote on some features i need to implement. I will send you an email ok? 
    Thank you so much!


  • 8.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-03-2018 18:57
    Email is always the best way to reach me: dandiebolt@gmail.com


  • 9.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-03-2018 19:50
    I just finished setting up.

    Now, I am trying to figure out how do I use your codes.


    IOL Field: [iol] & "module.js" & [/iol]

    1) I need to create a field text enrich with this name, or create a field name taxid and insert  this code in it?

    Content Type: IOL ModuleContent

    2) I understand here that i need to paste this into the formula field area somewhere, but not sure where?

    I think I am just missing the last steps to get this done.
    I am really sorry for the dummy questions.

    $("#_fid_6").on("keyup", function() {
      var taxid = $(this).val().replace(/[^0123456789./\-]/g, "");
      if (taxid.length == 3) {
        taxid += ".";
      } else if (taxid.length == 7) {
        taxid += ".";
      } else if (taxid.length == 10) {
        taxid += "/";
      } else if (taxid.length == 15) {
        taxid += "-";
      } else if (taxid.length > 18) {
        taxid = taxid.substr(0, 18);
      }
      $(this).val(taxid);
    });


  • 10.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-03-2018 20:04
    That code is applying the taxid formatting to the field with fid=6. To apply it to another field you have to substitute the appropriate field number:

    $("#_fid_7").on("keyup", function() {...}

    Also, I often times write code like this and manually apply it from the console. In such cases I often leave off the decoding logic that probably should be applied which restricts on which pages the code runs. The code will probably work as is but it should probably look like this instead:

    (function(){
      var querystring=document.location.search;
      if (/a=nwr/i.test(querystring)) {
        $("#_fid_6").on("keyup", function() {
          var taxid = $(this).val().replace(/[^0123456789./\-]/g, "");
          if (taxid.length == 3) {
            taxid += ".";
          } else if (taxid.length == 7) {
            taxid += ".";
          } else if (taxid.length == 10) {
            taxid += "/";
          } else if (taxid.length == 15) {
            taxid += "-";
          } else if (taxid.length > 18) {
            taxid = taxid.substr(0, 18);
          }
          $(this).val(taxid);
        });
      }
    })();


  • 11.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-05-2018 03:53
    Man, this is the happiest day of my life! I got it to work. Now I understand.
    So, everytime I need to use one of your codes, I need to create another page or I can just paste it under the other code in the same module.js page?

    I know it might be a stupid question, but I use the same page or I create other pages with watherver name I want and paste the code there?


  • 12.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-05-2018 03:53
    Man, this is the happiest day of my life! I got it to work. Now I understand.
    So, everytime I need to use one of your codes, I need to create another page or I can just paste it under the other code in the same module.js page?

    I know it might be a stupid question, but I use the same page or I create other pages with watherver name I want and paste the code there?


  • 13.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-05-2018 04:43
    >Man, this is the happiest day of my life! 

    Me too - I am gonna slack off and party the rest of the week based on your success!

    To memorialize your success I declare the First Monday in February IOL AWARENESS DAY.

    Once you set up the two user defined variables iol and /iol you can use the IOL technique any number of times in your application by just completing these two additional steps:

    (1) in the table where you will be using the new instance of the IOL create a rich text field with a formula similar to one of the following:

    [iol] & "module.js" & [/iol]
    [iol] & "moduleTable.js" & [/iol]
    [iol] & "moduleTaxIDFormatter.js" & [/iol]

    It does not really matter what you name the module file but it is a good idea to prefix it with the string "module" and end it with the string ".js". This naming convention will help you recognize it in the list of code pages.

    It does not really matter what you name the IOL field it but it is typically named "-" so it takes up as little space as possible on the form or report.

    (2) Create a code page using the name of the module you used above and paste the relevant code into it.


  • 14.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-05-2018 14:37
    Hi Dan, your code works fine. But it doesn't let me delete the values after I input.
    how do I overcome that?
    it stops on the dot or slash. 


  • 15.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-06-2018 13:27
    As I said in a prior answer "I did this pretty fast and there are a few things to add."

    The logic has to be expanded to cover backspacing. Send me an email if you want to pursue this:

    https://getsatisfaction.com/people/dandiebolt/">https://getsatisfaction.com/people/dandiebolt/">https://getsatisfaction.com/people/dandiebolt/


  • 16.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-02-2018 12:34
    FWIW, I wrote that script while binge watching old episodes of  CSI Miami (I'm a big fan of David Caruso) and if the script had my full attention there are a number of improvements I could easily have made to it.

    In fact, there are libraries such as Square's FieldKit that could be used which do all the hard work for you:

    Square's FieldKit Library
    https://github.com/square/field-kit/releases

    Check out the demo:

    FieldKit Demos:
    http://square.github.io/field-kit/

    But why stop with using a mere professionally developed library - how about building this feature into your version of QuickBase? After all, are we not Builders?

    Here is how you might approach the challenge. Just use a Service Worker to  splice a new section into the Field Properties page asking for the necessary properties to define a Validation Rule (shown here as a single conceptual property Mask):




    There are some additional details of where you would store the supplemental information in the Validation Section  (perhaps in a code page appropriately named and linked to the field). Or perhaps your might arrange to define and store the information in the form where the Validation Rule is used on a case by case basis.

    Turns out this is exactly how the WQuzat people of Flubus 5A evolved their QuickBase technology using a concept they called QuickBase Plugins!



  • 17.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-03-2018 20:12
    Sergio,
    Try pasting this into a simple formula text field. It converts the string to all numbers and then inserts the delimiters back into the string.

    Change [Raw input] to your field name.


    var text MyString = [Raw input];

    var text PartOne = Part($MyString,1, "./-");
    var text PartTwo = Part($MyString,2, "./-");
    var text PartThree = Part($MyString,3, "./-");
    var text PartFour = Part($MyString,4, "./-");
    var text PartFive = Part($MyString,5, "./-");

    var text AllNumbers = List("",$PartOne,$PartTwo,$PartThree,$PartFour,$PartFive);

    Left($AllNumbers,3)
    & "."
    & Mid($AllNumbers,4,3)
    & "."
    & Mid($AllNumbers,7,3)
    & "/"
    & Mid($AllNumbers,10,4)
    & "-"
    & Mid($AllNumbers,14,2)


  • 18.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-03-2018 20:17
    .. the age old tension between native and script, man and machine, love and heartbreak rears its head ...


  • 19.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-03-2018 20:23
    I�m at the airport now with time before my flight so I felt compelled to give Sergio an easier way as I can see he is trying so hard. Off now for a ski week.


  • 20.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-03-2018 20:47
    Enjoy the snow my friend! 
    I have been losing all my hairs trying to get this done. But I know once i learned the first one, I will be able to implement the other ones.

    I will use yours for now, but I am challenged to get this accomplished now.

    Lets see if i understood:

    1- I followed all the instructions and I created the user iol and /iol
    2- I created the page module.js and added the long script where shows what page I am working on.
    3- I created a normal text field named [taxid].
    4- I need to create a field name [iol] & "module.js" & [/iol] ? 
    5- Is number 4 correct? or i just need to paste that "[iol] & "module.js" & [/iol]" into a another field text formula type? 

    Anyways, I wont give up on this one yet, but for now I will use Marks proposal so I can at least have something working today. 
    Thanks for both of you!


  • 21.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-03-2018 21:09
    We got you covered.


  • 22.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-03-2018 22:30
    In case anybody is wondering, that is the infamous Dan Diebolt on the left and myself Mark Shnier a.k.a. Your Quick Base Coach on the right. This shot was taken at EMPOWER 2017.

    Come to EMPOWER 2018 in Austin in June. Maybe you will see us in person. Well, I know I will be there hopefully Dan will make it too.


  • 23.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-04-2018 00:01
    >hopefully Dan will make it too.

    I might have a conflict as I am speaking at the other QuickBase conference in Austin that same week.

    "Morphisms Gone Wild - Integrating ELM + Haskell  with QuickBase"






    http://elm-lang.org/


    https://www.haskell.org/


  • 24.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85



  • 25.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-04-2018 00:34
    ELM is a new framework for building web applications which happens to be written in Haskell (the Mac Daddy of Pure Functional Programming Languages) and transpiles to JavaScript. ELM beats the pants off of Angular and React - two frameworks your Earth based QuickBase developers are currently dabbling with.

    You may not realize it but QuickBase has deep roots with functional programming. In the vast majority of programming languages If and Case keywords are controls structures (not functions) that executes one of several different statements based on some critera. QuickBase's If and Case keywords are actually functions (not control structures) that return one of several different values. In fact, QuickBase's formula language is almost a pure functional programming language - the exceptions being the two functions Now() and Today() which have the unfortunate side effect of returning a different value every time you call them. This non-pure function oddity was corrected with the invention of the QuickBase Monad and the upgrade of the formula language to script but these innovations have not yet happen in your quadrant.


    I hope that this explains the tight connection between ELM, Haskell, Functional Programming and the QuickBase technology. Anyway come to my talk entitled "Morphisms Gone Wild - Integrating ELM + Haskell  with QuickBase" and I will explain it in simple terms a builder can understand.


  • 26.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-04-2018 01:56
    Hi Mark,
    This works fine! But different than Dan's code, it does not show it the actual field being filled out. I have to create a second field to display the content of the taxid field. 
    Am I doing something wrong?


  • 27.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-04-2018 09:05
    Right, my method does require two fields. If Dan�s method does it in one field, I guess that could be an advantage.


  • 28.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-04-2018 13:08
    Let's put it this way: Your business will be able to process twice the number of taxid records before you hit some type of table size limit if you use script rather than formula. This is because the script solution only uses one field whereas the formula solution uses two fields - (1) one to store the unprocessed raw taxid input and (2) another to stored the formatted version of the taxid..

    This superiority of script over formula solutions reappears often as formula solutions have to use extra fields to store calculated or processed versions of a field value or user input and script solutions don't have this unnecessary restriction.

    Likewise formula solutions often have to create faux relationships and their associated reference, proxy, summary and lookup fields when some type of aggregate calculation is required - scripts don't have this burden.

    So the bottom line is that script solutions are often more profitable than formula based solutions as you don't have to consume as many limited resources such as fields, relationships and form rules to produce the same result for the user.

    That last paragraph sounded so good I think I am on to something similar to the Ferengi Rules of Acquisition.

    Ferengi Rules of Acquisition
    http://memory-beta.wikia.com/wiki/Ferengi_Rules_of_Acquisition

    Let's call this our QuickBase's Rules of Building  :
    • there's more profit to be had in script solutions than formula solutions


  • 29.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-05-2018 03:23
    Mark and Dan,

    Really appreciate all the teaching here.
    I was able to get Mark's solution done. I guess to have Dan's it requires Knowledge of coding, which i dont have.
    Anyways, I will keep reading and learning how to get the technique working.
    At my situation, I dont mind creating two fields for one formula, but i will definitely want to learn how to get the scripts working.

      _


  • 30.  RE: I need a formula to format a numeric field to show like this 051.801.264/0001-85

    Posted 02-05-2018 03:23
    Mark and Dan,

    Really appreciate all the teaching here.
    I was able to get Mark's solution done. I guess to have Dan's it requires Knowledge of coding, which i dont have.
    Anyways, I will keep reading and learning how to get the technique working.
    At my situation, I dont mind creating two fields for one formula, but i will definitely want to learn how to get the scripts working.

      _