fixing ssn

[swf]

I am importing ssn from an outside database. They left the ssn as a number not text and therefore 0xxxxxxxx becomes xxxxxxxx (1 number short)(there are no dashes in the data output). When I import this I want to add a zero if the length is <9.

If (Length(ssn)<9;ssn="0"& ssn)

Do I place this under validation or autoenter.

I also need help with my syntax. thanks

[LaRetta]

Hi swf,

ssn should be text with: Right("0" & ssn; 1)

You may nor may not want to apply this during import using Apply Auto-Enter. Because Auto-Enter is an all or nothing thing with imports, ie, it will apply to all fields which have an Auto-Enter option. : It might be safer to import, then either Replace Contents (using via calculation) or loop - or even change all data in one whack using Field Definitions only. Choice is yours.

LaRetta

[Lee Smith]

Can't the 0 be on either end?

Wouldn't it make more sense to change the Export file to Text, and then import it?

If you can't do that, I would create a field to flag for those records that do not have the proper number of characters, via a calculation or script, and then review the flag records manually for correction.

Lee

[BrentHedden]

I'm with LaRetta's 2nd choice - import then look for and correct mistakes. It seems to be the safer choice.

On a simular topic - I've sometimes seen Zip Codes as number fields. Causes the same problems, especially for East Coast USA areas.

[swf]

I can't change the output data as it comes from someone elses database. I solved the problem by making a new calculated field

calcssn=If (Length(PATIENT ID )≠ 9;Left("0"&PATIENT ID ; 9 );PATIENT ID)

This takes the number adds a zero and uses the leftmost 9 digits. It also truncates any data beyond 9 digits. Sometimes there is an A after the nine digits. Then I create the relationship between calcssn and and patients:ssn.