2 Replies Latest reply on Jun 17, 2011 3:06 PM by mk4482

    Formatting a field

    mk4482

      Title

      Formatting a field

      Post

      I am using FMP 11 Advanced.  I have a text field that contains letters and numbers, but the data is not entered consistently.  Sometimes there are spaces between a few characters, sometimes there are dashes, sometimes there are colons.  I would like to remove those types of characters so the field just has the letters and numbers in it (it is a computer's Mac Address field).  In the future I will restrict the field to 12 characters, but would like to fix the current entries before I do that.  How can I change this globally so I don't have to change each entry manually that is incorrect?

      Thanks!

        • 1. Re: Formatting a field
          philmodjunk

          Replace Field Contents is a tool you can use to repair bad data in multiple records all in one Batch operation.

          Here's an example, but you may need to modify the calculation I'm using if more characters are permitted than Uppercase letters and the digits 0...9.

          1. Make a back up copy of your file.
          2. Go to a layout that displays the text field
          3. Select Show All records
          4. click or tab into your text field
          5. Select Replace field contents and select the calculation option
          6. Enter a calculation similar to this:
            Filter ( Upper ( Yourtable::YourTextField ) ; "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ" )
          7. That calculation will map all lower case letters to upper case and then filter out every character except those listed in quotes.
          • 2. Re: Formatting a field
            mk4482

            Perfect!  Thank you.