12 Replies Latest reply on Feb 6, 2011 7:59 AM by Sorbsbuster

    remove extra spaces

    NimrodMargalit

      Title

      remove extra spaces

      Post

      Hi.

      Im having some trouble in my portal because of some fields that i import from excel that has an empty space after.

      For example if i have a field that the value is: "12345 " and in another table i make a portal that searches: "12345" it wont find it because of the empty space after the value in the first table.

      My question is if there is somekind of script that could find and remove all the extra spaces in a certain field in a certain table.

      Thanks!

        • 1. Re: remove extra spaces
          Sorbsbuster

          You could use the Filter function to filter for those characters that you want to keep.

          You could use the Substitute function to remove those characters you want to remove.

          If your field is set as a number field in both tables it will ignore the space anyway.

          Whichever of those ways you want to use could be used in a 'Replace Field Contents' action, so no script is necessary.  Note that you should test this on a copy, as 'Replace..' CANNOT be undone.

          • 2. Re: remove extra spaces
            NimrodMargalit

            Thanks.

            Im a newbie in filemaker, can you please guide me step by step how to perform these actions in the right method? I'd really appreciate it.

            Thanks

            • 3. Re: remove extra spaces
              Sorbsbuster

              TEST ON A COPY OF THE FILE.  THIS STEP CANNOT BE UNDONE.

              - As you are having trouble with the match I assume that the fields on either end of the relationship are defined as 'text'.  If you expect them only ever to have numbers then change them to be type 'number' and the problem will be solved.

              - Otherwise, if you want them to contain text, but no spaces:

              - Go to the table with the data, in Browse mode.  Choose Records -> Show all records.

              - Click into the field you want to remove the spaces from.

              - Choose Records -> Replace Field contents...

              - In the dialogue choose 'Replace with calculated result'

              - Set the formula to be Filter ( textToFilter  ; "0123456789") (replace texttofilter with your field name)

              - Click OK.  It will go through all the records and remove all characters from that field that are not a 0 or 1 or 2... or 9.  If you want to leave text values in as well you can change it to be Filter ( textToFilter  ; "0123456789abcdefg[...and the rest of the alphabet, including upper case]")

              • 4. Re: remove extra spaces
                NimrodMargalit

                Hi.

                My only problem with what you said is that i can't click into the field i want to remove. I have a tables with probably tens or houndreds of records that have spaces... and i cant see them. is there anyway to perform this in a script that will find firstly the records that has spaces and then perform  the replace with calculated result?

                Thanks

                • 5. Re: remove extra spaces
                  Sorbsbuster

                  I'm a bit thrown now. When you posted: "Im having some trouble in my portal..." I assumed that the portal was based on a relationship between fields in two tables, one of which contained unwanted spaces (one of the fields, that is).  Do all the unwanted spaces appear in one field?  (I don't mean on one record - the spaces can appear in a million different records, just always in that one field)?

                  You don't need to isolate the records that have a space in that field - just let Filemaker check and clean that field in every record.

                  When you say you can't click in the field, do you mean you can't find a record with a space in that field, or when you go to that field on the record you can't click the cursor into it?

                  • 6. Re: remove extra spaces
                    NimrodMargalit

                    Let me explain myself.

                    If i understood correctly the method you showed me is how to clear a space from one record.

                    I dont know how to select multiple records in filemaker.

                    Lets leave the portal thing for now. what i want to do is take a field named ISRC in a table and make a script or action that will check what records under ISRC has empty spaces after the value in there and remove the spaces.. this will solve all of my problems.

                    I hope im a bit more clear now and sorry if i made a mess...

                    • 7. Re: remove extra spaces
                      Sorbsbuster

                      The first step was to select 'Records -> Show all records' from the menu bar.  That effectively 'Selects All records'.

                      The next step 'Replace Field Contents..' will replace the field contents with the cleaned data in all of those records.

                      If the field name is IRSC, then do this:

                      - Go to the table with the data, in Browse mode.  Choose Records  -> Show all records.

                      - Click into the IRSC field.  Leave the cursor flashing in it.

                      - Choose Records -> Replace Field contents...

                      - In the dialogue choose 'Replace with calculated result'

                      - Set the formula to be Filter ( IRSC  ; "0123456789" )

                      • 8. Re: remove extra spaces
                        NimrodMargalit

                        Ok

                        One main issue, I dont understand what field to select under ISRC. I dont know a way to select all records under ISRC.

                        • 9. Re: remove extra spaces
                          Sorbsbuster

                          "I dont understand what field to select under ISRC"

                          You select the ISRC field - the one you referred to here: "what I want to do is take a field named ISRC in a table"

                          "I dont know a way to select all records under ISRC"

                          The first step was to select 'Records -> Show all records' from the  menu bar.  That effectively 'Selects All records'.

                          I know I am only repeating myself, and that is not the best way to explain some thing, but I don't know if you have done what I suggested and it didn't work, or you haven't done it because you think it won't work.

                          • 10. Re: remove extra spaces
                            NimrodMargalit

                            Ok.

                            Your way seems to delete all the data under ISRC...

                            Ive tried the same way with the trimall action and it seems to work...

                            Anyway, Thanks a lot for all your help! Well appreciated!

                            • 11. Re: remove extra spaces
                              Sorbsbuster

                              The Trim function should also have worked.  Well done!

                              • 12. Re: remove extra spaces
                                Sorbsbuster

                                I apologise - my fault - the function should of course have been 'Filter', not 'Filtervalues'.  My fault, sorry.