4 Replies Latest reply on Jun 6, 2016 3:40 PM by BruceRobertson

    Various cleanup and prevention techniques for carriage returns?

    JamesGrubic

      Hi, we've got a rather large inventory database (30,000+ records) and we're having quite the problem with carriage returns in one of the fields.

       

      I've been googling this quite a bit trying to come up with how best to do the following different tasks:

       

      1. Quickly do a search in Find mode in a limited set to find records in a certain field that currently have a carriage return. I currently want to do this just to see how many records I am dealing with that have this problem. I have tried Pilcrow character, chr(13), slash r, etc, and I don't seem to be able to find the wording or text needed to find returns in a field. Everything I have tried is no results.

       

      2. Be able to quickly zap records that have the CR. So far I am reading that an auto-enter with Substitute ( YourEmailField ; "¶" ; "" ) would work well. I currently have some text formatting on this field so I have to be careful.

       

      TextStyleRemove (TextColor ( TextSize ( TextFont ( SerialNumber; "Courier New" ); "12"); RGB ( 0;0;51) ); Bold)

       

      This is to present serial numbers in a consistent way instead of the typical garbage that gets pasted in.

       

      3. Prevent carriage returns from being entered in the field ever again. One trick I found was to add Return as a Go to next object, but we do searching on this field heavily so I need the carriage return to work in Find mode.

       

      Thanks for any advice!

       

      James

        • 1. Re: Various cleanup and prevention techniques for carriage returns?
          erolst

          JamesGrubic wrote:

          I have tried Pilcrow character, chr(13), slash r, etc, and I don't seem to be able to find the wording or text needed to find returns in a field. Everything I have tried is no results.

           

          Use a script like:

           

          Enter Find Mode

          Set Field [ YourTable::yourField ; "\"¶\"" ]

          Perform Find

           

          You could also manually copy a return character from a field, then search for it by putting it into quotes (which is exactly what the script does).

           

          JamesGrubic wrote:

          2. Be able to quickly zap records that have the CR. So far I am reading that an auto-enter with Substitute ( YourEmailField ; "¶" ; "" ) would work well. I currently have some text formatting on this field so I have to be careful.

           

          TextStyleRemove (TextColor ( TextSize ( TextFont ( SerialNumber; "Courier New" ); "12"); RGB ( 0;0;51) ); Bold)

           

          Let() to the rescue:

           

          Let (

            cleaned = TextStyleRemove ( TextColor ( TextSize ( TextFont ( SerialNumber; "Courier New" ) ; "12" ) ; RGB ( 0 ; 0 ; 51 ) ) ; Bold ) ; // your nested monstrosity ;-)

          Substitute ( cleaned ; ¶ ; "" )
          )

           

          JamesGrubic wrote:

          3. Prevent carriage returns from being entered in the field ever again. One trick I found was to add Return as a Go to next object, but we do searching on this field heavily so I need the carriage return to work in Find mode.

          Same idea, now as an auto-enter calculation (with replace option checked):

           

          Substitute ( self ; ¶ ; "" )

          1 of 1 people found this helpful
          • 2. Re: Various cleanup and prevention techniques for carriage returns?
            siplus

            TextFormatRemove ( text ) will fix things faster and better, IMHO.

             

            For a fast, one-time job cleaning of the current situation use Replace field contents, calc result, Subsitute(yourfield; ¶; "")

             

            For avoiding CR's in your field, create a script:

             

            If [ Code(Get(TriggerKeystroke)) = 13 ]

              Exit Script [ Text Result: False ]

            Else

              Exit Script [ Text Result: True ]

            End If

             

             

            and attach it to your field(s) that don't like returns in their content, via OnObjectKeystroke trigger.

            1 of 1 people found this helpful
            • 3. Re: Various cleanup and prevention techniques for carriage returns?
              JamesGrubic

              Hi, thank you both for the replies.

               

              What I did was a combination of sorts of your suggestions.

               

              First was to isolate the found set of these records (found out the hard way that I shouldn't run the cleanup Replace on all records, but fortunately I was working with a copy of the database!), which was 2000+.

               

              Entering Find mode, I search for:

               

              "

              "

               

              Then I was able to do a Replace as suggested with

               

              Substitute(SerialNumber; ¶; "")

               

              But I realized I didn't want to change the modification date on EVERY record in the db, so I made sure I was working with the found set first.

               

              Then I changed my calculation field to the monstrous:

               

              Let (

                cleaned = TextStyleRemove ( TextColor ( TextSize ( TextFont ( SerialNumber; "Courier New" ) ; "12" ) ; RGB ( 0 ; 0 ; 51 ) ) ; Bold ) ; // your nested monstrosity ;-)

              Substitute ( cleaned ; ¶ ; "" )

              )

               

              to prevent any further incarnations of that pesky return character from getting into this field.

               

              All appears to be working. I am grateful for the advice!

               

              James

              • 4. Re: Various cleanup and prevention techniques for carriage returns?
                BruceRobertson

                Do you really want to format the content of the field? Seems very very doubtful practice.

                Here's another possible auto enter calc:

                trim( textformatRemove( getValue( self;1)))

                 

                Usually it it is the display style of the field that you want to control; not the content.