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

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
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?
Photo of Sergio

Sergio

  • 492 Points 250 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,894 Points 20k badge 2x thumb
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.
(Edited)
Photo of Sergio

Sergio

  • 492 Points 250 badge 2x thumb
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?
Photo of Sergio

Sergio

  • 492 Points 250 badge 2x thumb
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?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,894 Points 20k badge 2x thumb
>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.
(Edited)
Photo of Sergio

Sergio

  • 492 Points 250 badge 2x thumb
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. 
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,894 Points 20k badge 2x thumb
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/
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,894 Points 20k badge 2x thumb
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!

(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,316 Points 50k badge 2x thumb
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)
Photo of Sergio

Sergio

  • 492 Points 250 badge 2x thumb
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?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,316 Points 50k badge 2x thumb
Right, my method does require two fields. If Dan’s method does it in one field, I guess that could be an advantage.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,894 Points 20k badge 2x thumb
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
(Edited)
Photo of Sergio

Sergio

  • 492 Points 250 badge 2x thumb
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.

  
Photo of Sergio

Sergio

  • 492 Points 250 badge 2x thumb
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.