HOW DO I FORMAT A FIELD ON A FORM FOR A SOCIAL SECURITY FIELD TO: ###-##-####

  • 0
  • 2
  • Question
  • Updated 2 months ago
  • Answered
Photo of Becky Moore

Becky Moore

  • 110 Points 100 badge 2x thumb

Posted 6 months ago

  • 0
  • 2
Use a formula-text field "##-##-####" and show that field on the form instead and make sure it shows only in view mode. For add/edit mode, you can use the real field.
Photo of Becky Moore

Becky Moore

  • 110 Points 100 badge 2x thumb
I am new to this.  Can you walk me through it please?
Folks,

Can someone create a screencast for Becky? I don't have access to it from work.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,782 Points 50k badge 2x thumb
Becky. Is your SSN a text field now or numeric? If you answer that then I will help you with the code to show a formatted formula text field.
Photo of Becky Moore

Becky Moore

  • 110 Points 100 badge 2x thumb
It was a text field but I changed it to a formula text field but that did not work either. 
Photo of Slider

Slider

  • 1,386 Points 1k badge 2x thumb
You should have 2 fields:
1 for data entry - on form for add and edit.
2 for showing Xs - on form for view.
I assume it is not to match number for digit, so I would simply create a text formula, if(length([ssn])>0,"XXX-XX-XXXX",""). 

If you do want to match digit for digit you need a different formula, I would use this...
case(length([ssn]),
9,"XXX-XX-XXXX",
8,"XXX-XX-XXX",
7,"XXX-XX-XX",
6,"XXX-XX-X",
5,"XXX-XX",
4,"XXX-X",
3,"XXX",
2,"XX",
1,"X","")




Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,782 Points 50k badge 2x thumb
@slider. Becky wants to show the SSN digits but in a formatted manner.


But I will wait and see if Becky responds to my question before I put the effort into a reply.
(Edited)
Photo of Slider

Slider

  • 1,386 Points 1k badge 2x thumb
my mistake. so many ways to take this request.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,782 Points 50k badge 2x thumb
Becky,
Try this

// desired format is ###-##-####

var text PartOne = Left([SSN],3);
var text PartTwo = Mid([SSN,4,2);
var text PartThree = Right([SSN],4);

IF(Length([SSN]=9,
List("-", $PartOne, $PartTwo, $PartThree))
Photo of Robbin Marshall

Robbin Marshall

  • 100 Points 100 badge 2x thumb
I tried this and received an error on the "If(Length" line. The error states "The operator '=' can't be applied on types text, number"

My SSN field was a numeric field, but I had to change it to text to fix the errors in the first 3 lines that begin with "var"
Photo of Robbin Marshall

Robbin Marshall

  • 100 Points 100 badge 2x thumb
I found out the issue. I was missing the ")" behind [SSN].
Photo of John Rogers

John Rogers

  • 420 Points 250 badge 2x thumb
I did it like this in my APP

//This pulls the data from the SSN field and masks it. 

var text SSN = ToText([field which contains SSN]);
var text filler = "###";
var text fillerr = "##";

List ("-", $filler, $fillerr, Right($SSN,4))

Hope this helps.
Photo of Becky Moore

Becky Moore

  • 110 Points 100 badge 2x thumb
Could someone please screenshot the resolution; like I said I am new to this.

Photo of Robbin Marshall

Robbin Marshall

  • 100 Points 100 badge 2x thumb
Hey Becky,

Your solution may be a little different than mine, but I ended up using the formula below. I will be instructing the users of my app to exclude the hyphens from the social security numbers.

If(Length([SS#]) = 9,

var text SSN = ToText([SS#]);
var text filler = "###-##-#";

List ("", $filler, Right($SSN,3)),

"Not Formatted")
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,782 Points 50k badge 2x thumb
I'm not sure what your question is.

If you are trying to take a text field and  format it with hyphens then formula text field would be

var text PartOne = Left([SSN],3);
var text PartTwo = Mid([SSN,4,2);
var text PartThree = Right([SSN],4);

IF(Length([SSN]=9,
List("-", $PartOne, $PartTwo, $PartThree))


If he field was numeric then let me know and it would be slightly different formula
Still missing the close paren behind Length, Mark

var text PartOne = Left([SSN],3);
var text PartTwo = Mid([SSN,4,2);
var text PartThree = Right([SSN],4);

IF(Length([SSN])=9,
List("-", $PartOne, $PartTwo, $PartThree))
(Edited)