1 2 Previous Next 17 Replies Latest reply on Sep 14, 2015 5:29 AM by beverly

    Enter non breaking space using Find / Replace

    sccardais

      How can I enter a non-breaking space using Find / Replace in a script?

       

      I'm importing a spreadsheet that includes Full_Name with each word separated by a normal space in the format of First Middle Last.

       

      A calculation field rearranges the Full_Name field to sort the names alphabetically by last name. e.g. "John Smith" becomes, "Smith, John".

       

      In several cases, I want Last_Name to show the last two words. e.g. "Michael Van Heusen". My calculation rearranges this to "Heusen, Michael Van". I want "Van Heusen, Michael".

       

      Inserting a non-breaking space (option-shift-space) between Van Heusen works if I enter it manually but copying this and pasting into Find/Replace does not work - from the menu or in a script step.

       

      Is there a way to insert a non-breaking space using Find / Replace in a script or can anyone recommend another way to accomplish the same?

        • 1. Re: Enter non breaking space using Find / Replace
          beverly

          see if these help:

               http://www.fileformat.info/info/unicode/char/00a0/index.htm

               https://en.wikipedia.org/wiki/Non-breaking_space

           

          I think I would try Char(160).

           

          And there are other ways to parse out a name like this. There may be Custom Functions to make sure the last name is extracted correctly.

           

               http://help.filemaker.com/app/answers/detail/a_id/11956/~/extracting-title,-first-name,-middle-name,-last-name-from-a-field-in-filemaker

           

               https://www.briandunning.com/cf/663

           

          ...

           

          beverly

          • 2. Re: Enter non breaking space using Find / Replace
            sccardais

            Thank you Beverly. A calculation including Char (160)  almost worked.

             

            This calc below works in DataViewer but not when used in Find/Replace script step.

             

            LeftWords(Employees::Name_Full;3)

            &

            Char(160)

            &

            RightWords(Employees::Name_Full;1)

             

            If I paste the results in DataViewer to Full_Name, the calculation field correctly joins the 2 right words with a non-breaking space.

             

            If I paste the calc into the Find/Replace script step, it doesn’t add the non-breaking space.

             

            Any ideas or suggestions?

            • 3. Re: Enter non breaking space using Find / Replace
              carlosilvia0

              I modify the calculated field:

               

              LeftWords ( FirstName ; 1) &

                 Case(       WordCount (FirstName )>1;

                     " " & MiddleWords (FirstName;2 ; WordCount (FirstName )-1);

                    "")

              & ", " & LastName

               

              I hope you helpful

              • 4. Re: Enter non breaking space using Find / Replace
                keywords

                I'm afraid I can't quite see the logic of your calc so am not surprised it's tripping you up, but it seems to me that you may be attacking the issue at the wrong point. If it were me I think I would be going back to the First Middle and Last name fields and using Substitute( ) to replace any standard spaces with non-breaking spaces. After that the Full Name calc should work.

                 

                In the example you give, my hunch is that Van has ben imported into the Middle name field instead of as part of the Last name. Depending on the extent of this kind of issue you could fix this and any other similar anomalies (e.g. you might have imported Lisa-Maree into First and Middle name fields where they belong together as a single First name) manually, or if there are many instances you could Find the relevant records (e.g. all records with Van as a Middle name) and then use Replace Field Contents in the Last name field to concatenate the two into the Last name field complete with non-breaking space, then RFC on the Middle name field to blank the Van from there.

                • 5. Re: Enter non breaking space using Find / Replace
                  sccardais

                  In my last post, I was asking why a calc field that works in Data Viewer is not working in a Find / Replace script step.

                   

                  To clarify -  there are no First, Middle or Last name fields in the file that I am importing. Just one name field, FullName that contains each person’s full name with first name, middle initial (some) and last name all in one field. There are no other name fields such as First, Middle or Last Name.

                   

                  I want to alphabetize the list by last name. (e.g. Smith, John). I use a calc field c_LastNameSort for this. It works fine except in cases like my example where the last name is the last two words. Inserting the non-breaking space between the last two words solved the problem.

                   

                  The calc I posted is part Find/Replace step in a script that finds specific FullNames such as John Van Heusen that need the non-breaking space between Van and Heusen.

                   

                  If I paste the results of this calc from Data Viewer into the FullName field, the calc field works fine. The same calc in a Find / Replace script step does not work.

                   

                  Substitute is a good suggestion though. I’ll try it.

                  • 6. Re: Enter non breaking space using Find / Replace
                    user19752

                    "Find / Replace" don't allow any calculation, it is treated as literal text.

                    ("Find Records" then) "Replace Field Contents" will do.

                    • 7. Re: Enter non breaking space using Find / Replace
                      keywords

                      OK I misunderstood your description. Nevertheless, the setup I described would give you much simpler control and I suggest you consider it. You might use you current full name field simply for import purposes—to contain the name exactly as imported from the spreadsheet. Then you could create the separate name fields and populate them by calculation—using Replace Filed Contents, if you wish. Thereafter you can proceed as per my last post. I usually create two calculated name fields to concatenate name data in various ways (e.g. First name & " " & Last name; or Last name & ", " & First name & Middle initial; etc).

                      • 8. Re: Enter non breaking space using Find / Replace
                        user19752

                        "any calc" isn't correct in script, I should say "calculation is evaluated once before replace, not on each record".

                        And, if you replace a field, you need to go to the field before replace.

                        "Find / Replace" works as word processor, it is used only limited situation.

                        • 9. Re: Enter non breaking space using Find / Replace
                          jormond

                          How do you distinguish between someone that has First  Middle  Last and someone that is First  TwoWord Last ( no middle name )?

                          • 10. Re: Enter non breaking space using Find / Replace
                            user19752

                            This may be question for sccardais, I think he has list of 2 words last name.

                            Then for each the last name, replace last space to Char(160)

                             

                            Enter Find Mode[]

                            Set Field [FullName; "*" & "LastName thatHas2Words"]

                            Perform Find[]

                            Replace Field Contents[FullName; Replace ( FullName ; Position ( FullName ; " " ; 1 ; PatternCount ( FullName ; " " ) ) ; 1 ; Char(160) )]

                             

                            I prefer having separate field that keywords wrote.

                            • 11. Re: Enter non breaking space using Find / Replace
                              beverly

                              this is a better custom function that should help:

                               

                              <http://www.briandunning.com/cf/1020>

                               

                              "c. 2012 Drewsol. This CF parses specified name parts (first, last, middle) from a single name field for the flexibility of sorting and/or display. fnNameStraight now handles Spanish names, and some middle-eastern names."

                               

                              it looks like there is a section for adding these multiple-word-lastname, so may need to be edited.

                               

                              beverly

                              • 12. Re: Enter non breaking space using Find / Replace
                                sccardais

                                First - thank you to everyone who has replied.

                                 

                                User19752 - I think you nailed it. I didn't know that Find/Replace couldn't accept calcs. Did I understand you correcctly? This is surprising since the calc engine is available with this script step but it would explain why Find / Replace didn't work with my original calc or when I tried Substitute. I'm going to try Replace Field Contents.

                                 

                                Joshua Ormond: I search for specific names that I know have the issue. This is a payroll system with relatively few records in each batch and even fewer with the problem that started this thread. Each batch has the same small number of records with this issue. In my script "After Import", I search for specific people and apply corrections to each individually.

                                 

                                CarlosSilvia0: The values in FullName don't have the same number of "words." Some have 2 - only First and Last. Some have 3 with a middle initial or name. Some have 4. Because of this, I don't think the modified calc field you suggested will work in my situation.

                                 

                                Beverly Voth: Thanks very much for providing the various links and resources. I'll look at CF #1020. It would be great if the last two words in my "problem names" were separated by a hyphen (as mentioned in CF# 1020)!

                                 

                                All - just to be clear - I am importing from a spreadsheet that contains a single field for FullName. The number of words in this field can be 2, 3 or 4. Some of the FullNames have the problem I mentioned. I have a script, "After Import" that performs various maintenance functions on each batch of imported records. One part of this script looks for specific people that use two words in their last name. I don't scan the entire batch of imported records. I know who to look for and I find their records specifically. My script finds these people one by one. Once found, I want to add the non-breaking space between the last two words in the FullName field so the calc in another field, c_LastNameSort works for all records. Normally, I'd be breaking FullName into separate fields but that is not necessary in this case.

                                 

                                Thanks to all for taking the time to reply.

                                • 13. Re: Enter non breaking space using Find / Replace
                                  carlosilvia0

                                  The proposed function manages any number of words.

                                  • 14. Re: Enter non breaking space using Find / Replace
                                    sccardais

                                    Just to close this out ...

                                     

                                    User19752 suggested using "Replace Field Contents" rather than "Find/Replace" and it worked.


                                    In my "After Import" script, I first find the people with the "problem names" than I Replace Field Contents with a calculation that inserts Char(160) - non-breaking space - between the last two words in the FullName field. The makes the calc in c_LastNameSort work perfectly. e.g. "Heusen, John Van" becomes "Van Heusen, John".


                                    Thanks again to all.

                                    1 2 Previous Next