Imports can't trip script triggers so you'll have to use other methods to clean up your imported data.
If you can enable auto-enter options during import, you can import the phone number into a global field and use an auto-enter calculation to reformat and enter the data from the global field into the Filemaker phone number field. This isn't always possible if there are other fields with auto-enter options that you don't want enabled during the import.
Immediately after the import, the imported records will form the current found set. Use Replace Field contents with the calcualtion option to reformat your phone number fields in the imported records.
ok but is there simple calculation i can use to change the format from (xxx) xxx-xxxx to just xxxxxxxxxx?
also when i try the replace field content in the menu option its grey'd out and wont let me do it
Filter ( PhoneText ; 9876543210 )
The replace option will be greyed out until you put the cursor in your field.
One last question. I have a field that imports appointment dates that our sales team have went out on. The problem is when it imports to the field it looks like this:
Jun/15/2011 at 11:00:00
It imports the date and time, and when i try to search just the date nothing will come up. is there a calculation that can filter out the "at 11:00:00" and leave just the date: "Jun/15/2011" maybe like delete 12 charcaters from the right hand side or something?
The better solution would be to correct the source from which this data is imporated so that the date is importd as a date into one field and the time is imported as time into another. Or is properly formatted so that it can be imported into a timestampe field. Such a change may not be possible of course, but do it that way if you can.
I'd add a pair of fields that use calculations--either fields of type calculation or date and time fields with auto-enter calculations. Oh yes, and a singel timestamp calculation that includes both date and time is possible too.
GetasTime will extract the time from this text field.
The month name will complicate extracting the date, but it can be done:
Let ( [ Dtext = LeftWords(YourTextfield ; 1) ;
Month = Ceiling ( Position ( "JanFebMarAprMayJunJulAugSepOctNovDec" ; Left ( Dtext ; 3 ) ; 1 ;1 ) / 3 )
GetAsDate ( Month & Right ( Dtext ; Length ( Dtext ) - 3 ) )
This calculation ( result date ) could return a real date from your appointment date text field:
t = appointmentDate ;
m = Ceiling ( Position ( "JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC" ; Upper ( LeftWords ( t ; 1 ) ) ; 1 ; 1 ) / 3 ) ;
d = Substitute ( LeftWords ( t ; 2 ) ; LeftWords ( t ; 1 ) ; m )
GetAsDate ( d )
Thank You thank you!!!!!!!
Hmmm, thought leftwords ( "Jun/15/2011" ; 1 ) would return the entire string, but see that it actually returns "Jun" after testing. Use Raybaudi's solution here in place of mine.