1 2 Previous Next 17 Replies Latest reply on Dec 31, 2016 1:43 PM by keywords

    How to remove hidden spaces or characters

    fmckinnon

      Hey Gang,

      Happy New Year . I've searched the forums here and found some potential solutions back in 2011, but hoping there is an easier way by now.  We have an ongoing issue where data we are uploading into FM is creating new records, instead of matching against existing records, because of hidden characters, like spaces at the end of our field.

       

      The easy solution:  when our staff enters a PO# in the PO field, they shouldn't paste in an extra space.   Yeah, well, they are entering hundreds of orders, copying and pasting out of our ticket system .. and it just happens. 

       

      Is there some way to validate a field using data validation that strips any extra space, or even validates it and warns if it exists?

       

      Example:

      One of our staff enters a record for a new sales order with PO# 345678 .   (note the space at the end, added in FM, but not intended to be there).  It's hard to notice - very hard, if you put your mouse in the cursor, you can see it, but easy to overlook.

       

      Then, a week later, we upload our UPS or FedEx bill with the setting to match PO# and insert the shipping charge into the shipping field.   Instead of PO# 345678 seeing the existing record and simply adding the shipping charge, it creates a new record since the space prevented it from being recognized.

       

      I'm on FMPro 11 but would upgrade immediately if there is some type of validation in future versions that would resolve this.

       

      Thanks a ton!
      Fred

        • 1. Re: How to remove hidden spaces or characters
          CarlSchwarz

          You want to use the Trim() function and the Substitute() function.  In the substitute function replace carriage returns with nothing.

           

          Here are three options

          - Use a script trigger "On Save" to run the calculation on the field

          - Go to your database manager and in the "auto enter" section turn on calculation and make the field replace itself with the result of the calculation

          - Use a script to replace the field contents with the calculation, perhaps before doing an import (this is the least efficient way).

           

          The calculation would look like

          Trim( Substitute ( self; "¶"; "") )

          If self doesn't work (depending on context) then just replace that with the PO field.

          • 2. Re: How to remove hidden spaces or characters
            davidhead

            I agree with Carl on his options.

            Personally I would tend towards an auto-enter calculation (Carl's second option).

             

            Depending on what characters are valid in a PO#, you could use a Filter function to get rid of anything that is not valid. For example, if PO#s only contained digits as in your example, you could use an auto-entered calculation:

             

                      Filter ( Self ; "0123456789" )

            • 3. Re: How to remove hidden spaces or characters
              fmckinnon

              Awesome gang - can you walk me through exactly where I'd go to set this up?  I use it every day but not super saavy on the programming part.   The only validatino we have on that field now is that it has to be unique (can't have duplicates).   Our valid POs will include numbers, letters, and hypens.  (not always all of them).

              ie:

              1233456 is valid

              123456R is valid

              123-1234567-1234567 is valid

              Basically numbers, letters, and hyphens.  That's it.

              • 4. Re: How to remove hidden spaces or characters
                keywords

                If you are rolling David's suggested approach—and I certainly would if I were you—you would place the Filter () calc as an auto-enter in the field itself. The calc David gave, Filter ( Self ; "0123456789" ), will instantly replace whatever the user enters with everything but the Filter characters removed, provided you leave unchecked the "Do not replace existing value of field" option.

                 

                As you wish to allow letters and hyphens as well you need to include these in the Filter list—Filter ( Self ; "0123456789abc…etcABC…etc-" )

                 

                Note that if both upper and lower cad letters are acceptable you need to include both in your Filter list. You might also need to consider whether you want to standardise in other ways (e.g. convert all letters to Upper case, convert dashes to hyphens, etc)

                • 5. Re: How to remove hidden spaces or characters
                  fmckinnon

                  Hey Gang,

                  CarlSchwarz

                  keywords

                  I'm revisiting this thread, and hope that someone who originally responded will help.

                  I asked for this help at the beginning of the year, and here I am on the last day of 2016 - I never employed this, and now I'm sitting here fixing 3-4 hours worth of bad entries from my staff who can't seem to NOT paste a leading space in our Order # (PO) field.   As a result, when uploading vendor invoice data into our COGS field (and using PO as the matching field), I created thousands of NEW records because they didn't match due to the stupid spaces.

                   

                  I've read and tried to employ these suggestions, but I'm just stumped and need step-by-step hand holding here.

                   

                  Here's how far I've gotten.  (I'm using FMP V11)

                   

                  #1:  File > Manage Database

                  #2:  I choose my "PO" field and click "options"

                   

                  From there, I have "Auto-Enter" tab, the "Validation" tab, and of course, Storage and Furigana.

                  I don't know where to go from here.


                  FYI, in "Validation", there is already one thing there "require unique value".

                  I tried checking off "Validated by calculation" and I pasted the above Filter formula in:

                  Filter ( Self ; "0123456789abc…etcABC…etc-" )

                   

                  I saved this, and went and created a new record with a leading space and it didn't stop me or fix it.

                   

                  So clearly, I just need someone to tell me exactly where to go, and exactly what to paste/where (validation) so that anytime we create a new record and manually enter PO, it can ONLY be alphanumeric ... upper/lowercase letters, hyphens, numbers, but NO SPACES.

                   

                  Thanks a ton!

                  • 6. Re: How to remove hidden spaces or characters
                    philmodjunk

                    You are on the wrong tab it seems. What has been recommended is to be set on the auto-enter tab, not the validation tab.

                     

                    And it sounds like your staff are copy/pasting these values into one file and then importing them into FileMaker where you are doing an "import matching" option and you need this correction to take place in the FileMaker table into which you are importing the corrected data. Do I understand correctly?

                     

                    For importing, you'd need to enable the auto-enter option to get this correction to take place from an auto-enter calculation on the field. There's a small dialog that opens with a check box for this option just before the actual import starts. Even then, this correction won't take place in time to keep the mismatch of values from creating a new record. What you would need to do in this case is import into a temporary "Staging table" in FileMaker where your auto-enter calculation strips out the unwanted characters and then you'd do your "import matching" import from this staging table into the table where you need this info to be stored.

                    • 7. Re: How to remove hidden spaces or characters
                      rouelf

                      Undo “Validated by Calculation”. Return to “Auto-Enter”, then click the ChekBox by “Calculated Value”, or click the “Specify” button next to it. There you enter the Filter Calculations.

                       

                      I suggest using: Substitute ( Self ; Char (32) ; “” )   This removes all spaces in the field when data is entered in the field.

                       

                      Char (32) = Space.

                      • 8. Re: How to remove hidden spaces or characters
                        philmodjunk

                        Filter does a better job. You don't have to identify every undesirable character to list for substitute to remove. With filter, you just list the characters that are acceptable and filter removes all others.

                        • 9. Re: How to remove hidden spaces or characters
                          fmckinnon

                          Thanks Phil,

                          The staff are manually creating FM records within the IWP access.  So, for example, Amazon Order:

                          "102-111111-111111"

                           

                          Our remote staff will copy that order # and paste into PO -- but using Chrome or other browsers, and depending on where they copy from, maybe they carelessly paste the PO as:

                          " 102-111111-111111"

                           

                          Then, a week later, when I am uploading the Amazon statement that includes sales tax, etc, I have it setup to match the PO# (and add new records if it doesn't exist).   My uploads are fine - they don't have spaces and doesn't need to be validated.  But when I do the upload, and the file from Amazon correctly has "102-111111-111111", it creates a new record instead of updating the existing record, because the existing record was manually created with " 102-111111-111111" (the space); thus, creating an extra record.

                           

                          So, what I'm trying to accomplish is when/if my remote staff pastes in the PO# with a space, it won't let them save it, much like it won't let them save it if it's not a unique value.  (we have requires unique value on validation to prevent remote staff from entering the same item twice).

                           

                          So I'm going to try this "Substitute ( Self ; Char (32) ; “” )  to see if that will fix things.   If I'm still missing the mark let me know.

                          • 10. Re: How to remove hidden spaces or characters
                            fmckinnon

                            OK, I just tried it, and still doesn't work, so obviously i'm still missing something

                            philmodjunk rouelf

                             

                            First screenshot - applying the auto-enter value:

                            Screen Shot 2016-12-31 at 1.16.02 PM.png

                             

                            Second screenshot - after hitting OK and saving this, I went into my database and created a new record, and I intentionally put a space at the front of the PO# field:   " 111-111111-111111".  As you can see, it accepted the value and I hit "save and new record" and went back to make sure, and the record is there WITH the space.

                             

                            Screen Shot 2016-12-31 at 1.16.49 PM.png

                            • 11. Re: How to remove hidden spaces or characters
                              philmodjunk

                              From your screen shot, you didn't clear the "do not replace existing value..." check box.

                               

                              Note that your calculation only removes a single type of character the space character. If some other non printing character is pasted into the field, say a return, tab, just to name two of a great many other characters, they will not be removed. This is why several of us have recommended that you use Filter instead.

                              • 12. Re: How to remove hidden spaces or characters
                                fmckinnon

                                philmodjunk OK I will fix that.   I thought I WAS using filter, since the post above said "there you enter your filter calculations".   I really don't know the difference.  I'm a hack.

                                 

                                If you can write out what the filter code should be, I'll copy and paste it in exactly as you give me.   I'll even send you a virtual bottle of wine (or home brew!)

                                 

                                To recap, the only thing we put in our PO field is numbers, letters, and some symbols, like hyphens.  We don't want spaces or returns or tabs.

                                • 13. Re: How to remove hidden spaces or characters
                                  David Moyer

                                  Hi,

                                  your post motivated me to formalize a few custom functions for use with the Filter function.  If you do a lot of filtering, these may be useful ...

                                  Custom Functions for character filtering

                                  • 14. Re: How to remove hidden spaces or characters
                                    fmckinnon

                                    That's awesome, and I see already gathering comments and being useful.  But for me, I still need to know what to paste to accomplish my objective.  I just don't understand all the code.  (I dunno the difference between a function and a filter, etc) .. it must be painful for you guys reading these posts.   You are FM pros.   I run an e-commerce group and I get to answer all the newbie questions about how to list a product on eBay haha - so I get it.

                                    1 2 Previous Next