Auto format for Social Security Number ?

[BMS3122]

Been trying to format SS#s so user entry of 123456789 becomes 123-45-6789. I've successfully auto formatted telephone numbers with area codes using a calculation code so I know the basics. But, the SS# has me stumped. P L E A S E... someone help !!!

Edited April 15, 2009 by Guest

[Lee Smith]

Take a look at this sample file by John Mark Osborne Link

[RalphL]

Also Look at the custom function at http://www.briandunning.com/cf/41

[BMS3122]

The Osborne Link was NG.

The code I've created thus far is: If(Length(SSNumber) = 9; Left(SSNumber;3) & " - " & Right

(SSNumber;4)) and this results in "123-6789" I need to get those two middle digits in there, i.e., "45".

What syntax & operators do I need for "Middle" :(

[Lee Smith]

Sorry, the file I pointed to requires the Advance Edition of FileMaker, and you have the standard edition. The CF that Ralph pointed to would also require the Advance Edition.

There are other sample files at his site, just do a search for Social Security. BTW, most Custom Functions can be converted to a normal calculation field..

Lee

[LaRetta]

Case ( Length ( Filter ( ssNumber ; "0123456789" ) ) = 9 ;

Left ( ssNumber ; 3 ) & "-" & Middle ( ssNumber ; 4 ; 2 ) & "-" & Right ( ssNumber ; 4 ) ;

"ERROR" )

[LaRetta]

Actually, in case a User attempts to be clever and add the dash (or any other garbage), I'd remove it before the calculation evaluates, such as:

Let ( ss = Filter ( ssNumber ; "0123456789" ) ;

Case ( Length ( ss ) = 9 ;

Left ( ss ; 3 ) & "-" & Middle ( ss ; 4 ; 2 ) & "-" & Right ( ss ; 4 ) ;

"ERROR" )

)

Edited April 15, 2009 by Guest

[RalphL]

Note that the custom function I suggested is very similar to the solution suggested by LaRetta. Just because it is a custom function doesn't mean that it can't be used as a calculation in FMP.

// remove any non-number

Let([

NumOnly= Filter(number; "0123456789");

SSNumb = Left(NumOnly;3) &"-"

& Middle(NumOnly;4;2) &"-"

& Middle(NumOnly;6;99)

];

//Set clolor to red if invalid number of digits.

If(Length ( NumOnly ) ≠ 9 ;

TextColor ( SSNumb ; RGB ( 255 ; 0 ; 0 ) );

TextColor ( SSNumb ; RGB (0 ; 0 ; 0 ))

))

[LaRetta]

Ralph, I typed a calculation which answered the question in 15 seconds. You both provided links to custom functions (which I never even looked at and didn't care to because BMS3122 wasn't using Advanced). It would have been quicker if someone typed a simple calculation for them!

My second calculation took a total of another 5 seconds to modify to offer protections in the calculation. Since the CF you referenced didn't even need to be a custom function, why didn't you simply paste or just type the calc here?

I did nothing wrong in responding to BMS3122; it seemed much simpler and quicker than providing multiple links.

Middle(NumOnly;6;99)

BTW, using 99 here is sloppy. :(

Edited April 16, 2009 by Guest
Modified sentence

[RalphL]

BTW, using 99 here is sloppy.

Agreed, My point was that just because it was a cf didn't mean that it couldn't be adapted for this.

[LaRetta]

BMS3122 rates themselves as a beginner. Might they not know that most custom functions can be used within standard calculations? Might they not even know how to convert the syntax of a custom function to a calculation? Why should they have to go off searching for the calculation when a solution can be clearly provided (based upon their self-rating, the FM version and their OS)?

I cannot believe that you provided that link any more quickly than I typed the calculation. And I can truly believe that, by providing links (particularly to custom functions), wasted BMS3122's time ... not to mention to many more people who find this thread looking for the same solution only to be sent off on similar goose-chases.

And Lee clarified that many custom functions can be used as regular calculations so I was surprised that you responded telling us the same thing again but regardless, a beginner probably doesn't even understand the differences. The calculation was simple, for goodness sakes, Ralph! You could have typed it as quickly as I did and you would have even provided a better calculation than that one; just as I did! :smile2:

[Richard W Graham IV]

On April 15, 2009 at 0:26 PM, Lee Smith said:

Take a look at this sample file by John Mark Osborne Link

I have advanced version and I already setup masking for phone numbers which works perfectly so I do have some idea how to do this, but when I use this calc I just get 8 numbers as output no dashes at all. Something is not right with this one I'm pretty sure. When I put numbers in it gives result in masking field like it should but no dashes at all.

On April 16, 2009 at 11:55 AM, RalphL said:

Note that the custom function I suggested is very similar to the solution suggested by LaRetta. Just because it is a custom function doesn't mean that it can't be used as a calculation in FMP.

this one gives me same result no dashes... can someone please post a working ss example?

// remove any non-number

 

Let([

NumOnly= Filter(number; "0123456789");

 

SSNumb = Left(NumOnly;3) &"-"

& Middle(NumOnly;4;2) &"-"

& Middle(NumOnly;6;99)

];

 

//Set clolor to red if invalid number of digits.

 

If(Length ( NumOnly ) ≠ 9 ;

TextColor ( SSNumb ; RGB ( 255 ; 0 ; 0 ) );

TextColor ( SSNumb ; RGB (0 ; 0 ; 0 ))

))

 

[webko]

You need to set the result of the Calculation to be Text rather than a Number

[Richard W Graham IV]

I did make field text not number, but I still get only numbers output, dashes are not being inserted. I have advanced version. I already have working phone number masks which i got somewhere so i know how it should be done, I just don't know enough to change phone formula to ss one.

 

here is working calc for Cell PhoneNumber field:

 

Choose(Length(Cell PhoneNumber);
"(###) ###-####";
"("&Cell PhoneNumber&"##) ###-####";
"("&Cell PhoneNumber&"#) ###-####";
"("&Cell PhoneNumber&") ###-####";
"("&Left(Cell PhoneNumber;3)&") "&Middle(Cell PhoneNumber;4;1)&"##-####";
"("&Left(Cell PhoneNumber;3)&") "&Middle(Cell PhoneNumber;4;2)&"#-####";
"("&Left(Cell PhoneNumber;3)&") "&Middle(Cell PhoneNumber;4;3)&"-####";
"("&Left(Cell PhoneNumber;3)&") "&Middle(Cell PhoneNumber;4;3)&"-"&Middle(Cell PhoneNumber;7;1)&"###";
"("&Left(Cell PhoneNumber;3)&") "&Middle(Cell PhoneNumber;4;3)&"-"&Middle(Cell PhoneNumber;7;2)&"##";
"("&Left(Cell PhoneNumber;3)&") "&Middle(Cell PhoneNumber;4;3)&"-"&Middle(Cell PhoneNumber;7;3)&"#";
"("&Left(Cell PhoneNumber;3)&") "&Middle(Cell PhoneNumber;4;3)&"-"&Middle(Cell PhoneNumber;7;4))

[Richard W Graham IV]

On April 15, 2009 at 1:12 PM, LaRetta said:

 

1 hour ago, Richard W Graham IV said:

I have advanced version and I already setup masking for phone numbers which works perfectly so I do have some idea how to do this, but when I use this calc I just get 8 numbers as output no dashes at all. Something is not right with this one I'm pretty sure. When I put numbers in it gives result in masking field like it should but no dashes at all.

 

ok this one works! thank you!

// remove any non-number

 

Let([

NumOnly= Filter(number; "0123456789");

 

SSNumb = Left(NumOnly;3) &"-"

& Middle(NumOnly;4;2) &"-"

& Middle(NumOnly;6;99)

];

 

//Set clolor to red if invalid number of digits.

 

If(Length ( NumOnly ) ≠ 9 ;

TextColor ( SSNumb ; RGB ( 255 ; 0 ; 0 ) );

TextColor ( SSNumb ; RGB (0 ; 0 ; 0 ))

))

oops i spoke too soon. I see it work i.e. it puts in dashes and then disappears! if i put in numbers into ss field and then tab out only numbers appear in masking field. (i put both fields separate so i can see and click masking field) if i click masking field the dashes appear but as soon as I tab out or leave field the dashes disappear and I'm left with numbers again. something very small needs tweaking. can someone please help as I'm so close here. I'd love to add the red text if not enough numbers too but I'm happy with just the working mask if someone can please point out what's wrong.

 

Edited April 8, 2016 by Richard W Graham IV
mistake, couldn't find cancel anywhere

[webko]

Like I said - the result must be of type Text - see where it says number in the dropdown, change that to Text and this works

[Richard W Graham IV]

I'm sorry I didn't realize there was another one in the calculation itself (number option) Thank you so much! yeah now I remember having the same problem with the phone calc... ha ha!

 

Thank you once again and I'm humbled by my own stupidity!

[Lee Smith]

Hey Richard, welcome the the FM Forums,

Please Finish your profile so we know what version of FileMaker you are using, OS and Platform. It is also helpful to know what your skill level is so we know how much detail we might have to go into. 

You can update your profile by clicking on the dropdown arrow next to your picture above.

Lee