5 Replies Latest reply on Jan 27, 2017 3:59 PM by lindseym

    Calculated Field Produces extra space in result

    lindseym

      To star, I found this post but it did not lead me to a solution that was noticeable to me

       

      Nagging Problem in Calculated Field Produces extra space in result

       

       

      I'm running FMP 14.0.6.

       

      What I am getting as a result is:

       

      101 F

       

       

      What I expect:

       

      101F

       

       

       

      There are some results that have the letter in front of the numbers and those results are as expected.

       

      Here is my calculation:

       

      Let ( [

        t = LeftWords ( Location Calculation ; 1 ) ;

        x = Filter ( LeftWords ( t ; 1 ) ; "0123456789" )  = LeftWords ( t ; 1 ) ;

        l = Length ( LeftWords ( t ; 1 ) ) ;

        n = GetAsNumber ( LeftWords ( t ; 1 ) ) ;

       

        locationCalculationWithoutLeadingZeroes =

          Case (

            x ;

            n & Right ( t ; Length ( t ) - l ) ;

            t

           )

        ] ;

       

        // And the second: (substitutes)

       

        Substitute (

          ${Copy Side A} ;

            [ "XLEVEL#" ; "*" & Level from Space ] ;

            [ "LEVEL#" ; Level from Space ] ;

            [ "STAIR#" ; Stair from Space ] ;

            [ "BLDG#" ; If ( PatternCount ( GetAsText ( Building from Space) ; "RL") > 0 ; "L" ; Building from Space )] ;

            [ "LOC#" ; locationCalculationWithoutLeadingZeroes ] ;

            // This is the substitution that would need the leading zeros removed

            [ ";" ; "¶" ] ;

            [ "TBD" ; TextStyleAdd ( "TBD" ; HighlightYellow ) ]

        )

       

      )

       

       

       

       

      ---------------

       

      And my incoming data is "BLDG#LOC#" So I know there is no space between the two. It doesn't seem to be a space associated before the letter or after the number as I align the fields flush right and left in layout and there doesn't seem to be hidden characters (I don't know how to "show hidden characters" like you would in Adobe Illustrator.

       

      Does anyone see what I am missing?

       

      Thank you Kindly,

       

      -L

       

      I've also added some photos for support.

       

      Screen Shot 2017-01-27 at 8.55.13 AM.png

       

      Screen Shot 2017-01-27 at 9.23.06 AM.png

        • 1. Re: Calculated Field Produces extra space in result
          alquimby

          In your calculation, try

           

          Trim (whatever field produced 101)

          Trim (whatever field produced F)

           

          I'll bet someone has typed some extra spaces in one or both of those fields.

          1 of 1 people found this helpful
          • 2. Re: Calculated Field Produces extra space in result
            lindseym

            That someone would be me...

             

            I checked the data and it doesn't appear I did any extra characters... so I am not sure how to prevent it in the future, but your fix worked.. so I am happy

             

            Thank you!

             

            -L

             

            Here is my new calculation:

            Let ( [

              t = LeftWords ( Location Calculation ; 1 ) ;

              x = Filter ( LeftWords ( t ; 1 ) ; "0123456789" )  = LeftWords ( t ; 1 ) ;

              l = Length ( LeftWords ( t ; 1 ) ) ;

              n = GetAsNumber ( LeftWords ( t ; 1 ) ) ;

             

              locationCalculationWithoutLeadingZeroes =

                Case (

                  x ;

                  n & Right ( t ; Length ( t ) - l ) ;

                  t

                 )

              ] ;

             

              // And the second: (substitutes)

             

              Substitute (

                ${Copy Side A} ;

                  [ "XLEVEL#" ; "*" & Level from Space ] ;

                  [ "LEVEL#" ; Level from Space ] ;

                  [ "STAIR#" ; Stair from Space ] ;

                  [ "BLDG#" ; If ( PatternCount ( GetAsText ( Trim(Building from Space)) ; "RL") > 0 ; "L" ; Trim(Building from Space ))] ;

                  [ "LOC#" ; Trim(locationCalculationWithoutLeadingZeroes) ] ;

                  // This is the substitution that would need the leading zeros removed

                  [ ";" ; "¶" ] ;

                  [ "TBD" ; TextStyleAdd ( "TBD" ; HighlightYellow ) ]

              )

             

            )

            • 3. Re: Calculated Field Produces extra space in result
              alquimby

              Here's something I use as an auto enter calc (uncheck "Do Not Replace Existing Value") for those fields like names, phone numbers, cities, etc.:

               

              Trim ( Substitute ( Self ; "¶" ; "" ) )

               

              It removes leading and trailing spaces, carriage returns, etc., that somehow get inadvertently entered.

              1 of 1 people found this helpful
              • 4. Re: Calculated Field Produces extra space in result
                philmodjunk

                or even:

                 

                TextformatRemove ( Trim ( Substitute ( Self ; "¶" ; "" ) ) )

                 

                to eliminate any formatting that might get pasted into the field from the clipboard.

                 

                And there are custom functions that use filter or substitute to strip out a lot of other non visible characters that can screw up your data if pasted into the field.

                1 of 1 people found this helpful
                • 5. Re: Calculated Field Produces extra space in result
                  lindseym

                  Thanks

                   

                  I've a growing list of things to look into. It seems like every time I solve one problem -- I get something else coming up!

                   

                  Good thing is I am able to fix more and more by myself so learning must be working!