6 Replies Latest reply on Jan 21, 2014 7:08 PM by rbogdanoff

    Trimming white space

    rbogdanoff

      Two questions: How do I trim all leading and trailing spaces in an existing text field that contains lots of data? How do I set up a text field to automatically trim white space when text is entered? Thanks for the help.

        • 1. Re: Trimming white space

          Hi,

           

          Using the Trim () function will delete leading and trailing spaces. Using it as part of an AutoEnter Calc will do that for you during data entry.

           

          To get rid of extra spaces within a text block, use the Substitute () function.

           

          I HTH,

           

          John

          • 2. Re: Trimming white space
            rbogdanoff

            Ok, I set AutoEnter calculation to Trim ( Self ) which solves the problem of keeping new data entry clean.

             

            How would I go about cleaning up existing records where some fields have trailing spaces?

            • 3. Re: Trimming white space
              robwoof

              If you have the Auto-Enter with Replacing set up, you can just go to each field and do a Records->Replace Field Contents…

               

              Use the "Specify Calculation…" option, and enter the field in question as the calculation. The idea is to "replace the current value back into the field". This will trigger the Auto-Enter replacement, and you're done.

               

              HTH

              Rob

              • 4. Re: Trimming white space
                Stephen Huston

                Caveat: this will also update any modification tracking fields in the table where the trimmed field is.

                 

                If the developer has tracking of modification dates/accoutns/etc., those fields' setting need to be turned off before this is run, and then reset to track them after the replace function is completed.

                 

                I always run a manual backup before tackling any replace function during data cleanup, just in case.... There is no undo for the Replace field contents process.

                • 5. Re: Trimming white space
                  Malcolm

                  Good advice, it's no fun explaining to a client that the mod dates are meaningless because you've messed them all up.

                   

                  I don't like changing the field definition. Bulk data transformation is safely done on a copy and it doesn't take much extra time. *

                   

                  • Locate the record set that needs to be modified.
                  • Export these as a FileMaker file.
                  • Make changes as required.
                  • Push the data back into the database, including the original modification date.

                   

                  Sometimes you want modifies/updates to occur. In that case do one import with modifies/updates. Then do a second one without it, just putting the original modification dates back into place.

                   

                  * Unless the record set is large. When the record set is large, having a backup and working on a copy are even more important!

                  • 6. Re: Trimming white space
                    rbogdanoff

                    Really excellent info, I never thought how that kind of data cleanup would affect the modified date which we use extensively. Thanks for that.

                     

                    Sent from my iPad