14 Replies Latest reply on Dec 15, 2010 10:53 AM by rlinsurf

    Problem Finding records



      Problem Finding records


      So I have a problem finding a bunch of records I can see are there, but FMP doesn't. Here's an example: The field reads:

      "Imagine that you were having a tug of war and the other end of the rope disappears behind a curtain. Somebody else is tugging on the other end; we'll call that dark energy," said Dr. Adam Riess from the Space Telescope Science Institute in Baltimore, US. 

       -- http://news.bbc.co.uk/2/hi/science/nature/6156110.stm

      I then perform a search in the field for the word Baltimore. I get the dreaded No records found alert.

      Can someone tell me what I'm doing wrong?

        • 1. Re: Problem Finding records

          Are you sure that this is either a field of type text or a calulation field that returns text as it's data type. You can check this in Manage | Database | Fields.

          One possible explanation is that this text is in a field of type number.

          • 2. Re: Problem Finding records

            Hi, Phil--

            No, it's definitely a standard Text Field.

            • 3. Re: Problem Finding records

              Is this a find you perform by hand or a script?

              If by hand, the index to this file may be damaged.

              You can turn indexing off and then back on in Manage | Database | Fields to rebuild this field's index.

              • 4. Re: Problem Finding records

                Ok, I set the field's Index to None, and unchecked Automatically.... I clicked OK to the Database dialog, then re-opened it, re-checked the Automatically... and then performed the same find for Baltimore. No Records.

                • 5. Re: Problem Finding records

                  What happens when you perform a recover on the file? Any problems reported? Does the recovered file work? (Even if no problems were reported during the recover?)

                  Best practice is to never use a recovered file, but to replace it with an undamaged backup if you determine the file was damaged.

                  If that doesn't reveal any issues, is this a file you can upload to a share site so that you can post a download link here where I can use it to examine your file?

                  • 6. Re: Problem Finding records

                    Hi, Phil--

                    I recovered the file, no errors were reported, and the issue still exists.

                    Thank you for taking a look at this. Here's the file:


                    • 7. Re: Problem Finding records

                      Interesting. I suspect that you have copied and pasted text into this field or imported from other sources. What looks like spaces between the words in this phrase: Dr. Adam Riess from the Space Telescope Science Institute in Baltimore, US aren't really spaces but some kind of "non breaking space". Thus FileMaker does not index this passage into individual words and a search for Baltimore fails, but a search for *Baltimore* will find it.

                      You can see this if you double click a word in this phrase, instead of just a single word highlighting, the entire phrase highlights.

                      • 8. Re: Problem Finding records

                        Wow. That's exactly what I did.

                        Can you tell me how to fix this?

                        • 9. Re: Problem Finding records

                          The simplest is to just include the * wild card at the beginning and end of each term you use in your search.

                          If you want to clean up the text, you'll need to do some detective work and this will be complicated by the fact that these characters look exactly like spaces.

                          You can use Substitute ( table::Notes ; Char ( ) ; " " ) to replace these characters with spaces, but you'll need to figure out what number to put inside the Char( ) function.

                          One way to do this is to add a text field, Character to your table and then define a cCode calculation field as Code ( Character ). Then you can copy and paste one of these characters into the Character field to see what number will be displayed in cCode.

                          You can fix existing data with a Replace Field Contents Step and can use this expression in an Auto-enter calculation on the notes field to fix the data when you paste it into the field.

                          • 10. Re: Problem Finding records

                            That did it. It was Char( 160 ). I did the Substitue, and now I can search for any string and have it found.

                            Thanks again, Phil :)

                            • 11. Re: Problem Finding records

                              <sigh> I guess I'm still having a problem. I just did a search for ! to get rid of the duplicates, and again, a bunch are coming up as duplicates, but then the second copies can't be found. Here's an example. This comes up as a duplicate:

                              Haiti: Desperate eyes wander through rubble and garbage lined, infested streets. This, the degradation and misery in which human beings thrive. And for which they ever hunger.

                               So they wait for the end of the world, praying, at last, for mercy, for this long, strange nightmare to cease.

                              When I copy the "spaces" into the character field, they come up as standard 32's. And I can't find what FMP thinks is the second record...?

                              • 12. Re: Problem Finding records

                                Sorry, I also have performed the search by doing *Haiti* and found no duplicate record.

                                here's a copy of the new file:


                                • 13. Re: Problem Finding records

                                  Learned something new with this one. I eventually had to define a relationship like this to figure things out:

                                  Notes::Note = Notes 2::Note AND
                                  Notes::ID ≠ Notes 2::ID

                                  Where Notes 2 is s second table occurrence of Notes.

                                  And then placed a portal to Notes 2 on the layout so that I could see what records were identified as duplicates of each other. What I found was that note fields that contained a paragraph that matched a paragraph in the Notes field of another record where found as "duplicates" when using the ! in a find request.

                                  Example: two quotes are attributed to Neitsche, since this source attribution is in a paragraph at the end of the passage and exactly the same in both records, they were found as duplicates--even though the preceding paragraph was for completely different quote! If I delete the carriage returns to run the text together into a single paragraph, the records no longer match as unique. Your "Haiti" note matches to a record that has just the second paragraph in it.

                                  This is not documented in FileMaker Help nor do I find an article on it in the knowledge base.

                                  I'm going to write this one up in Report an Issue. At the very least, this behavior should be clearly documented as it is not an intutively logical result.

                                  • 14. Re: Problem Finding records

                                    Wow. Glad I sent it your way :)