13 Replies Latest reply on Jul 21, 2010 1:09 AM by Brin

    Searching in text fields - more precision

    Brin

      Title

      Searching in text fields - more precision

      Post

      Using FMP 10.1..... We use our database, among other purposes, for catloguing our organisation's past newspapers, with one Record per issue. In a Details text field, we have one paragraph summarising the articles in each issue, followed by another para headed "Photos" listing the main photos of interest. If we want to search for any issue that contained a photo of Oxford, typing "Photos AND Oxford" in the Details field under Find will return those issues - but it will also return issues where there was an article about Oxford but no photo of Oxford. Is there any clever way of defining the second search term (Oxford) as "Return only if it occurs after the first search term" (Photos) ?  

      The database is already large and it is too late to change its design...

        • 1. Re: Searching in text fields - more precision
          ninja

          Howdy Brin,

          Housekeeping first...take off the self imposed shackles. 

          "The database is already large and it is too late to change its design..."  No.  It's never too late for that.  Don't limit yourself.  Importing records into a redesigned structure is pretty easy.  I don't think you need it for this case, but understand that your options are still open in the future.

          If your find event performs the following steps, you'll get what I think you are asking for:

          Enter Find Mode

          SetField [FindField ; "Photos"]

          Perform Find

          Enter Find Mode

          SetField [FindField ; "Oxford"]

          Constrain Found Set

          Constrain Found Set is a great tool for looking only within a found set.  I think that this is what you're after...let us know!

          • 2. Re: Searching in text fields - more precision
            Brin

            I don't quite understand what Constrain Set does.. but a "nested search" doesn't answer my problem. If the keyword Oxford occurs anywhere in a text field, a second search (once you have searched for Photos) will find it, whether it is in the text pargraph headed Articles or the paragraph headed Photos. The headings Articles and Photos occur in all the relevant Records. So Finding "photos" simply returns all the records.

            Your point about redesign noted, but the minor difficulty this problem poses is not, in our particular circumstances, worth the work of redesign. The newspapers component is one small element of an archive being assembled in the FMP database and so far it is all working very well. But contributors to this Forum have previously been able to come up with workable solutions to my occasional conundrums where I feel "there ought to be way of doing this, I just don't know what it is" and I live in hope. Sorry if I didn't explain myself well. And thanks for trying.

             

            • 3. Re: Searching in text fields - more precision
              sunmoonstar.13

              Hi Brin,

              Are you saying that the Articles and Photos "paragraphs" are all contained within a single text field called "Details"? And you want to be able to find records in which your search term only occurs in particular paragraphs within this single field? If that's the case, then I don't believe it can be done, because, by design, Filemaker searches the whole field. I don't think there's any way of restricting the search to certain "parts" of a field. To be able to do what you want to do, the only solution, as far as I can see, is to separate the Articles and Photos "paragraphs" into separate fields.

              Nick

              • 4. Re: Searching in text fields - more precision
                Brin

                Your understanding of our minor difficulty is exactly correct and what you propose as a solution would work, if the problem was important enough to warrant the work. What I (and edocx on the similar query below) were hoping was that there might indeed be some operator or formula which would define a search word by reference to its position (beginning/end) in a sentence/phrase (string ?) or its positional relationship (before/after) to another search term in a text field.

                • 5. Re: Searching in text fields - more precision
                  philmodjunk

                  Hmm, this might still trigger a few hits you don't want, but you might try Photos*Oxford and see what is returned...

                  • 6. Re: Searching in text fields - more precision
                    Brin

                    Good try; doesn't get us any further than Photos AND Oxford - it yields what you want but also some stuff you don't want. Fortunately for us the likely unwanted returns are going to be very few and easily filtered-out by wetware.  

                    I think this may be a red herring: what I was hoping to achieve is employing a database's information-sorting abilities superimposed on top of text-based info-sorting (a typed list with sub-headings) and there's a basic mis-match between the concepts. If it was a serious need, we would redesign the database to make it solve the problem in database fashion.

                    Thanks for trying, everyone.

                    Glad to see edocx's query was easier.

                    • 7. Re: Searching in text fields - more precision
                      ninja

                      Hmmm.  I'm noodling about the concept of 'Paragraphs' in the field and assuming that the break in paragraph is the only hard return in the field.

                      If you can split the field to everything LEFT of the hard return in one calc field, and everything RIGHT of the hard return in another, then you'd be home free.

                      I'm sure there's a way to do that, but I don't have the time just now to work out the details...surely there's got to be an easier way than a scripted loop culling character by character...

                      If a way is described how to split the field on the hard return, then the results of all the records could be imported into a modified structure so you're not searching a calculated field and slowing the system down.

                      Just thinking out loud...

                      • 8. Re: Searching in text fields - more precision
                        MartinBrändle

                        " What I (and edocx on the similar query below) were hoping was that there might indeed be some operator or formula which would define a search word by reference to its position (beginning/end) in a sentence/phrase (string ?) or its positional relationship (before/after) to another search term in a text field."

                        For that FileMaker is the wrong tool at the moment. Positional operators such as ADJ, NEAR, SAME (within same paragraph), left- or right-occurence etc. are supported by Information Retrieval Systems, which are another domain than relational databases such as FileMaker.

                        As Ninja suggested, there are workarounds such as looping through the records with a script to separate the paragraphs into separate records (I do something similar on words and sentences for text statistics, relevance ranking and text mining), but these will blow up your database considerably. And, if your database is modified often, you need to keep track of the changes.

                        • 9. Re: Searching in text fields - more precision
                          FentonJones

                          The Articles and Photos should be extracted (if possible; depends on consistency of data entry). This could be done via either via FileMaker Text Functions, or, if you're on a Mac, by using the Perform AppleScript step, and AppleScript using Unix text commands (which are geeky, but were built for this kind of thing). It does not necessarily change existing data; it can just extract/copy it to a better place (at least until you're sure it worked).

                          This is a common need for people who gather large amounts of text information in a large "multi-value" field (which is a known database design problem).

                          • 10. Re: Searching in text fields - more precision
                            MartinBrändle

                            Brin, Fenton is right. I had read again your original post. If you always have two paragraphs in your records (one paragraph being the abstract, the other one a description of the photos), the field holding the two paragraphs should be split into two fields abstract and photo_description. That can be easily done using "Records > Replace" and text calculation functions.

                            After that, searching is easy: abstract = Oxford and photo_description = Oxford.

                            However, if one really does have unstructured text, things get more complicated, and one should use IR systems or apply techniques of an IR system within FileMaker. At German FileMaker Conference, I had presented some ideas (which engaged some discussion and also lead to a comment of a FM representative if at FM Inc. one should think of how to deal with unstructured text, since many people also use FM as document management system. But this actually belongs to the Feedback forum).

                            • 11. Re: Searching in text fields - more precision
                              Brin

                              Thanks Martin and Fenton. I appreciate that one way to resolve the problem specified is to redesign the database by splitting the elements in the newspaper description into two fields. However,  as I explained, the newspaper-catalogue section is but one small element in a general archive database, that contains many other types of documents for which text has already been inserted in the Details field, and a solution for the newspapers element would damage the rest. You are quite right to clarify, as I have come to realise, that this "problem" is one of the fundamental nature of any relational database, and not easily solvable by pure ingenuity - not without having set the thing up differently in the first place, anyway.

                              But I am glad to hear from Martin that there are such things as "positional operators", even if they don't exist in relational databases - shows that my instinct was correct, that there "ought to be a way of doing it" !

                              I don't think I should trouble you all any further - unless any of you are "on a roll" with a purely intellectual problem - "noodling", as Ninja put it !!

                               

                               

                               

                               

                              • 12. Re: Searching in text fields - more precision
                                MartinBrändle

                                Brin,

                                it's not about searching, but about sorting. Sorting so that the most relevant records in the found set are on top (e.g. those that contain the most occurences of the term "Oxford" and of the term "photo"). The search is carried out with the same FileMaker tools as one already has, you enter "Oxford" and "photo" in your details field, but the records are sorted by a relevance measure that is calculated on the fly. 

                                If you look at the link I gave above (start with slide 38), you get an idea how it can be done. This solution does not change the field structure of the original main table. It would even allow to account for the position of your terms in the documents (haven't tried yet, but the formula for that is easy). However, there are caveats which depend essentially on the number and  size of your documents and the number of the search terms one uses. Actually, these are old methods developed in the 1970s for text retrieval systems that I applied to a FM database.

                                • 13. Re: Searching in text fields - more precision
                                  Brin

                                  Thanks Martin, but my German isn't up to deciphering your slides I'm afraid. I will save them and see if I can find someone who can help with that.