8 Replies Latest reply on Aug 9, 2015 6:12 PM by DEC

    How to Find Exact Field Contents?


      Hi Everybody!


      I have a database where in the Project Title field is often comprised of serialized data.  E.g.:  "Project 1," Project 2" and so forth.


      If the user conducts a search for "Project 1,' the database returns anything that includes the value "1".


      I need to fix it such that if the Project Title field isn't empty, then the database will return only the record(s) for which the Project Title field exactly matches the user-entered data for that field.


      For example, if the user searches for "Project 1," then the found set shouldn't include "Project 10" or "Project 100."


      Is there any way to do this?  Assuming so, is there any way to ensure that if the search criteria leaves the Project Title field blank that the found set won't search only among records that are blank in that field?


      Many thanks!



        • 1. Re: How to Find Exact Field Contents?

          Hello There!


          Have you tried to search "==Project 1" instead of "=Project 1"?


          The operator "==" stands for Exact Match




          • 2. Re: How to Find Exact Field Contents?

            Hi sicosys!

            Thanks for chiming in my friend…

            I did consider that, but I'd like to fix it so that the users won't have to use operators in the course of conducting a search. We're talking about a group of folks who are not very tech savvy and don't find databases generally intuitive.

            So I'm looking to set up the search so that the exact phrase will be found seamlessly, and won't bring in similar terms.

            I'd like a scenario where in the course of performance search the project title field is automatically updated with the necessary operators.


            -Sent from Outlook for the iPhone

            • 3. Re: How to Find Exact Field Contents?
              Markus Schneider

              nothing to be fixed here (-:

              FileMaker will index every word in a text field (what is really important). Therefore, it will find 'project*' and '1*' (text field), what is correct.


              If You want to get an 'exact' match, You have to use a script trigger on the search fields in find mode (for fields that need 'exact' match) that alters the query or a script for the 'find'-task that steps through the fields and alters the entered queries in those fields right before the 'find' - easy


              If FileMaker would allow to enter a 'mode' for the 'find' command, it would be great (ie mode=1 would be the 'wildcard match' and mode=2 would be the exact match (etc.) - but it's an easy task to get the 'right' results (although everyone runs into that from time to time: Error reports by users because of finding not the expected data..)

              • 4. Re: How to Find Exact Field Contents?

                Hi again DEC!!


                In that case I would use a global field to enter the text (which by the way could contain all available project names via value list) and use a "search button" in order to perform find script based on the contents of the global field, that way the user doesn't have to do anything but enter the search string


                Another way that i personally use is to set a relationship between the global field and the field containing the project name, after that I put a portal in the layout that will display only the related records as I change my selection (from the value list)



                • 5. Re: How to Find Exact Field Contents?

                  Part of the problem is that project title is most likely a text field.

                  Searching for 1 will also return 100, for example.

                  Sorting ascending will place 100 right after 10.

                  If you make the project number a number field it will find only the right number.

                  Unless you you specify an operator like > greater than or smaller than.

                  If it's not a generic find but within the projects layout. You could place the project number field

                  on the layout showing only in find mode.

                  • 6. Re: How to Find Exact Field Contents?

                    D --


                    I can see two ways to address your issue:


                    1) Subvert the normal FileMaker search process by scripting it.

                    2) Normalize the data-entry process in some way so that the data entered conforms to FileMaker's native search methods.


                    For the first, sicosys suggests using global field and value list (which will display all unique values contained in your field) with a relationship based on the global field.    Then, when the user 'searches', a script fires off that does a Go To Related Record instead.  You need to design the layouts and/or work flow to get the user typing in the correct field for the 'search' and then clicking a button or using a script trigger to capture the 'intent' to search.


                    One can also use custom menus to take over the Find menu command with a script that would conditionally replace the  text that the user types in a search field with something that results in the expected behavior (e.g. "==" & the rest of what the user typed).  Here, you need to be careful that you take care of all of the possibilities that a 'native' FileMaker search allows (multiple requests, the omit option, other possible modifiers that the user may have included themselves, etc.) or remove those possibilities from your particular solution.




                    For the second, you could:


                    Teach the users about the == in their find request (you can cue this with a tool tip, field placeholder text, on hidden text on the layout that would appear in find mode and when the user has typed something in the field).


                    Institute a unique record ID for this table (always a good idea, really), and have the users search by ID, not name.  FileMaker's value lists can ease that process along by showing both the ID and the project title or even just the title while actually entering the ID (pop-up menu does this the most cleanly, but there are other workarounds).  Imagine two overlapping fields, one available in Browse mode (for data entry only assigned to the 'Project Title' field), and one available in Find mode (for searching only, assigned to the 'projectID' field).  Put the search version behind the data-entry version, format the search version with a value list, and make sure the data-entry version has a background color to cover up the value in the search-by-ID field.  Now, when the user enters find mode, they click in the same place, but get a value list of the available names and have to pick one.  Here, you will be removing the ability for the user to search for partial words, and type ahead only helps a little, especially if the user can remember a middle work of the title, but not the first word.  f the value list gets very long, it confuses the users and can even slow down the system.  You could also place the ID and Title fields on the layout and teach users how to search both (in other words, ID is an exact search and title is a 'fuzzy' search).



                    So, I've got at least four solutions.  You don't say whether project titles are required to be unique in your database, which might help make one or another suggestion stand out.


                    Hope this helps,

                    Drew Tenenholz

                    • 7. Re: How to Find Exact Field Contents?

                      When dealing with users that are allergic to the ways man-machine interaction is working, there is no middle way solution.


                      You can teach them how to express themselves in the terms the database engine is expecting ("==" etc, mixed success bag) or you can design around their expectations and craft something they can smile upon every time they use it.

                      Choose the second one, because you can. Replicate the way google answers to a search. What does google do ? Well, it delivers a clickable list of results, so you should do the same. On field modify, do a Quick Find and present the potential results in a list, or a portal. Always think interface first, then engine. You can't fight google, so you better replicate it - your users google all day and they now expect your software to behave the same way, so implement it.

                      • 8. Re: How to Find Exact Field Contents?

                        Well said, siplus; your thoughts echo the intent of the other good folks who kindly chimed in : )


                        I think that it's cracked via the below script, though I'd love to hear anyone's thoughts if you spot something that could give the users trouble...


                        Thanks to you all for your help!






                        Allow User Abort [ Off ]

                        Set Error Capture [ On ]

                        Enter Find Mode [ ]

                        GotoField[ ]
                        [ Select/perform ]

                        Pause/Resume Script [ Indefinitely ]
                        If [ not IsEmpty ( GeneralSubmissions::Data_Title ) ]

                        Set Variable [ $ExactTitle; Value:GeneralSubmissions::Data_Title ]

                        Set Field [ GeneralSubmissions::Data_Title; "==" & $ExactTitle ] End If

                        Perform Find [ ]
                        If [ Get ( FoundCount )=0 ]

                        Show Custom Dialog [ Title: "No Records Found."; Message: "No General Submission records found in connection with the specified search criteria.'"; Default Button: “OK”, Commit: “Yes” ]

                        Enter Browse Mode

                        Show All Records

                        Go to Record/Request/Page

                        [ First ]

                        Halt Script

                        Exit Script [ ]
                        Else If [ Get ( FoundCount ) > 0 ]

                        Enter Browse Mode

                        Sort Records [ Keep records in sorted order; Specified Sort Order: GeneralSubmissions::Data_SubmissionDate; descending GeneralSubmissions::Data_Title; ascending ]
                        [ Restore; No dialog ]

                        Go to Record/Request/Page

                        [ First ]

                        End If