I have searched this question but can’t seem to find a solution.

I have a field for a Social Security Number…I want to validate this to insure that a duplicate number is not entered.

I have tried every setting I can to get the Revert Record button to go away and just have the message pop up with the yes or no button.

Is it possible to remove the Revert Record button from the Validation / Unique Value settings?

Thanks,

James

... No

What you might try doing instead though is turning on the auto enter function with overwrite existing and format as red if there's a validation error i.e.

if( invalid ; TextColor( yourField ; RGB( 200 ; 0 ; 0 ) ) ; TextColorRemove( yourField ; RGB( 200 ; 0 ; 0 )) )

Thanks Genx,

As you well know from answering past questions Im still rather new at FM and this is my 2db attempt...so here is my next question.

Can I run your suggestion if I currently have the following in place?

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

Which as you can see is nothing more than adding "-" in the Social Number.

Thanks,

James

Sure, but i'd suggest something like this:

Let([ x = yourSocialFieldHere ; fx=Filter(x ; 0123456789 ) ];

if( Length( fx ) = 9; 

TextColorRemove( Left(fx ; 3 ) & "-" & Middle(fx ; 4 ; 2 ) & "-" & Right(fx ; 4 ) ; RGB( 200 ; 0 ; 0 ) ) ;

TextColor( x ; RGB ( 200 ; 0 ; 0 ) ) 

)//End If

)//End Let

Which basically says:

Strip all the non number characters (i.e. if the user entered: 123-12-1234, fx would change it to 123121234 so it could run the checks and formatting correctly).

If there are 9 numbers (and i'm assuming that social security number's consist only of numbers) format the numbers and remove the red formatting if it was previously applied.

Otherwise, put the field back as however the user entered it, but... make the text red.

Edited June 24, 200718 yr by Guest
Sorry... Length shoulldn't have an equal sign in it -- im doing a lot of php atm and it's making me screw filemaker functions up bad lol

There is no need to remove the color, Alex; not if you will be implementing a new Auto-Enter (Replace) calculation which addresses the default result. Only color it if an error. It might look like:

Let ( ssn = Filter ( OffenderSSN ; "0123456789" ) ;

Case (

Length ( ssn ) = 9 ;

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

TextColor( OffenderSSN ; RGB ( 200 ; 0 ; 0 ) )

)

)

Also, James, note that your OffenderSSN field won't change (apply this auto-enter calculation) until a change is made to the OffenderSSN number. And your existing auto-enter calculation will allow errors into the field and will enter double-dashes etc (if you only have 8 numbers in the field) so you should replace it with either of these calculations presented.

To quickly force all your records to update and correct themselves with the new calc, apply the following process (back up first):

1) Change your OffenderSSN field to type calculation and click CHANGE.

2) You will be warned; it's okay. Insert your new calc. Do NOT go into storage or make it unstored.

3) Before you leave that calc box, be SURE to change the calculation result to TEXT at the bottom.

4) Say OK and then OK to leave Field Definitions. This will force all records to change their values dependent upon your new calc.

5) Go back in and select your OfenderSSN field and change it back to Type Text. Click CHANGE.

6) Then select Options and check Calculated Value - your calc will still be there.

7) Back at Auto-Enter tab, be sure to UNCHECK 'Do Not Replace Existing Value for Field..."

If that OffendersSSN field is handy, you can also:

1) Select Options on your OffenderSSN and check Calculated Value.

2) Change to the new calculation.

3) Back at Auto-Enter tab, be sure to UNCHECK 'Do Not Replace Existing Value for Field..."

4) Go to layout with the field.

5) Show All Records.

6) Put your cursor in the OffendersSSN field.

7) Select Records > Replace Field Contents.

8) Replace by calculation and just select OffendingSSN field.

I prefer the first method because everything can happen in one place - no need to find the field on a layout. And it is just as fast.

LaRetta

There is no need to remove the color

...but then it stays red...

Whenever you change an Auto-Enter (Replace) calculation, you must change it through all the existing records for it to take effect. :smirk:

... Yes, but if you enter a SSN, and you enter it incorrectly it reformats it to red... which is fine, but when you change it to the correct format / correct length, it stays red - which to me would indicate there was still something wrong with the way it was entered. I'm not sure I'm following your line of thought?

Yep, you are right! Line of thought? None obviously today. :wink2:

LOL, don't worry, I'm having one of those days too, it took me 4 corrections to get my calc right in the first place hehe.

Ok, gang…sorry it took so long to say thanks for the help, I have been tied up with my sons appendectomy.

So I am confused…It appears from the conversation between Genx and Laretta that the final decision is that the final calc shown will not effectively show that there is a problem when someone has entered a duplicate SSN.

Is that correct? Or did I just not read it correctly.

Thanks

Either use the one I posted which removes the color or here is a slight modification of LaRetta's suggestion -- both are functionally equivilant:

Let ( ssn = Filter ( OffenderSSN ; "0123456789" ) ;

Case (

Length ( ssn ) = 9 ;

TextColorRemove( Left ( ssn ; 3 ) & "-" & Middle ( ssn ; 4 ; 2 ) & "-" & Right ( ssn ; 4 ) ; RGB( 200 ; 0 ; 0 ));

TextColor( OffenderSSN ; RGB ( 200 ; 0 ; 0 ) )

)

)