14 Replies Latest reply on Apr 5, 2011 1:21 PM by mgores

    Filter Portal Search Results by Partial Match

    rmaltsberger

      Title

      Filter Portal Search Results by Partial Match

      Post

      So I've got one database that's a contact list, and another database that tracks received mail.

      We'll be working primarily in the mail database unless we're adding and editing contacts.

      The mail database has a functioning portal that displays name/cube number of contacts when their last name is typed in a search field.  For example, I can type in "Jones" so Jason Jones and Matthew Jones show up in the portal, along with their cube, etc.  I achieved this by filtering the portal results so only last names that match the search field are displayed.  I used AC22::last_name=Database2::Search

      However, I want to be able to search by the first 3 characters of the last name.  For example, I would like to be able to type in "Jon" and get back Jason Jones, Matthew Jones, Ryan Jonson and Pat Jontille.  What filter calculation can I use to achieve this?  Or can I even since the result has to be boolean?

      And once I do show all the results I want, how can I use them (well, 1 contact's results) to populate related fields in the new record?

        • 1. Re: Filter Portal Search Results by Partial Match
          philmodjunk

          Is this a repeat post? It looks familiar. In any case, it'll get a similar response as the one I remember:

          If you are using FileMaker 11, you can set up this kind of matching with a filtered portal. Your portal can use this filter expression:

          Database2::Search = Left ( AC22::Last_Name ; Length ( Database2::Search) )

          I could have use 3 instead of Length ( Database2::Search ) , but using that function lets you match on 1, 2, 3 or however many letters you enter into the search field.

          You then need a script trigger on the field to force the portal to update.

          Commit Record
          Refresh Window [Flush cached join results]
          Set Selection [Database2::Search ; Start: Length ( database2::Search) + 1 ; end: 0]

          You can place a button in your portal row to perform a script that enters data from that portal row into other fields in your database.

          Here's a demo file I just uploaded today that demonstrates this technique for an Invoice with line items plus 3 other methods:

          http://www.4shared.com/file/umvOy55n/EnhancedValueSelection.html (click the blue download button)

          • 2. Re: Filter Portal Search Results by Partial Match
            rmaltsberger

            Hi Phil,

            Thanks for your suggestion.  I have no problem placing the expression but can't figure out how to get the script trigger on the search field.  The search field still seems to run the same way.

            In layout view, I right click on the search field, select "Set script triggers..."

            When do I want the script to run?  OnObjectExit, right?  So I check the box, then the specify script dialogue comes up, and I click the + to add a new script, right?  Sorry, newb here, but where do I place:

            Commit Record
            Refresh Window [Flush cached join results]
            Set Selection [Database2::Search ; Start: Length ( database2::Search) + 1 ; end: 0]

            I'm impressed I've made it this far and need so badly to cross these last few hurdles.

            • 3. Re: Filter Portal Search Results by Partial Match
              philmodjunk

              Use OnObjectModify. This way the portal updates with each keystroke and that's what the script is set up to do. Set Selection puts the cursor back into the field at the end of the entered text after Commit Record exists the field.

              I've always used manage Scripts to create the script first, then use Script Triggers to link to it. Force of habit as this option is new to FileMaker 11 and I've been using FileMaker since 2.5...

              Once you've selected new script, give it a better name than "new script" and then add the steps I've posted by finding them in the list on the left and double clicking them. Items shown in the brackets have to be selected or entered by clicking a specify button while the script step is highlighted in your script. If you download the demo file, you can compare the script found there to what you create to check for discrepancies if you have trouble getting this to work.

              • 4. Re: Filter Portal Search Results by Partial Match
                rmaltsberger

                Thanks for your help Phil.  The portal updates without having to exit, but I still have a few issues.

                Since my portal records don't always show, nothing still shows up until an entire last name is typed in.  How can I have results begin to appear when I type?  For example, all last name that starts with P, then all results with Pa, then results with Pat, so I'll see Patrick, Patterson, and Patton?

                I compared to yours, and it seems your portal results are always showing, whereas mine don't unless they match the search criteria.

                Thanks again!

                • 5. Re: Filter Portal Search Results by Partial Match
                  philmodjunk

                  If it works in the demo file, but not in your file, there must be something different in how you set this up. I can't see that difference from here. Wink

                  The script trigger on the field is what enables the portal to update with each keystroke so the difference likely lies there. Do you have the script set to run with OnObjectModify? Does it run the correct script?

                  • 6. Re: Filter Portal Search Results by Partial Match
                    rmaltsberger

                    The Search field's script trigger is set as OnObjectModify, and it sure does port the FullName and Cube from the contacts database into the portal fields IMMEDIATELY after I finish typing in the full last name in the Search Field.

                    So if the last name matches, the full name and location are displayed.  That's awesome, but since many items arrive partially or incorrectly labeled, it's nice to be able to type in a partial last name.  Unfortunately I cannot link to source files for analysis but your help has been immense.

                    • 7. Re: Filter Portal Search Results by Partial Match
                      philmodjunk

                      I can't tell what the problem is from here. What filter expression are you using?

                      • 8. Re: Filter Portal Search Results by Partial Match
                        rmaltsberger

                        My portal is using this filter expression that ports records when the search box text equals the left-most text to the digit defined by the length of text in the search box.

                        Database2::Search = Left ( AC22::Last_Name ; Length ( Database2::Search) )

                        • 9. Re: Filter Portal Search Results by Partial Match
                          philmodjunk

                          And Database2 is the table occurrence selected in "Show Records From" in Layout setup... Correct?

                          That's identical logic to my demo--which works for you correct?

                          And Database2::Search has the OnObjectModify trigger specified?

                          Apologies for obvious questions, but we need to make sure before we look at other options.

                          With that approach, if you type L into Database2::Search, you should see a list of matching records such as:

                          Lance Lathum
                          Fred Lefferts
                          Johnny L

                          and so forth.

                          Hmmm, what relationship does the portal use? The demo uses the cross product (cartesian join) operator X instead of = so that all records will match until the filter expression starts excluding records from the portal.

                          • 10. Re: Filter Portal Search Results by Partial Match
                            mgores

                            Thanks again Phil,  was trying to do something similar and found this post.

                            I also discovered that if you add some "or" statements in the portal filter you can have it display matches from first name, last name or company so that as the user types John into the search field the portal will show

                            John Smith at any company

                            Jim Johnson at any company

                            Anybody at Johns Hopkins

                            and as you type more in the filter narrows down farther and farther.  Set a button in the portal row that grabs the contactID and drops it into the new record in my quote or phone log database and presto a new record is created with that contacts info already filled in.

                            • 11. Re: Filter Portal Search Results by Partial Match
                              philmodjunk

                              Since this thread was created, I've set up a demo file that demonstrates two variations of this method plus two different ways to implement a drop down list with auto-complete enabled. All methods enter ID numbers even though the data entered into the search field is text.

                              http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html

                              • 12. Re: Filter Portal Search Results by Partial Match
                                mgores

                                Just downloaded it. Nice Phil.

                                Have you ever done the "or" in the portal filter?  It doesn't really apply in a invoice/product description type application, but does seem useful for searching either name or company from a single search field.

                                • 13. Re: Filter Portal Search Results by Partial Match
                                  philmodjunk

                                  Yes, I've played with more sophisticated expressions that use OR, or other operators such as AND. A more sophisticated expression might be useful if you orgainized your products into categories and selected a category in addition to entering search text.

                                  • 14. Re: Filter Portal Search Results by Partial Match
                                    mgores

                                    Just thinking it would be good to use for my quote and phone log solutions. That way the user can start entering first name, last name or company name to find the desired contact.