9 Replies Latest reply on Feb 23, 2012 3:00 PM by philmodjunk

    Phone Number formating issue

    AaronRoss

      Title

      Phone Number formating issue

      Post

       hello, i have a table in my database that imports phone numbers from a seperate database. The problem is the phone numbers imported are not in the format matching the rest of my database so i cant set up automatic search scripts. i need a script tigger to de-format the imported phone number. for ex: the imported phone numbers look like this (xxx) xxx-xxxx when i need it to look like this xxxxxxxxxx. can anyone help me?

        • 1. Re: Phone Number formating issue
          philmodjunk

          Imports can't trip script triggers so you'll have to use other methods to clean up your imported data.

          Option 1:

          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.

          Option 2:

          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.

          • 2. Re: Phone Number formating issue
            AaronRoss

             ok but is there simple calculation i can use to change the format from (xxx) xxx-xxxx to just xxxxxxxxxx?

            • 3. Re: Phone Number formating issue
              AaronRoss

              also when i try the replace field content in the menu option its grey'd out and wont let me do it 

              • 4. Re: Phone Number formating issue
                philmodjunk

                Filter ( PhoneText ; 9876543210 )

                The replace option will be greyed out until you put the cursor in your field.

                • 5. Re: Phone Number formating issue
                  AaronRoss

                  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?

                  • 6. Re: Phone Number formating issue
                    philmodjunk

                    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 ) )
                           )

                    • 7. Re: Phone Number formating issue
                      raybaudi

                      This calculation ( result date ) could return a real date from your appointment date text field:

                      Let([
                      t = appointmentDate ;
                      m = Ceiling ( Position ( "JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC" ; Upper ( LeftWords ( t ; 1 ) ) ; 1 ; 1 ) / 3 ) ;
                      d = Substitute ( LeftWords ( t ; 2 ) ; LeftWords ( t ; 1 ) ; m )
                      ];
                      GetAsDate ( d )
                      )

                      • 8. Re: Phone Number formating issue
                        AaronRoss

                         Thank You thank you!!!!!!!

                        • 9. Re: Phone Number formating issue
                          philmodjunk

                          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.