1 2 Previous Next 16 Replies Latest reply on Mar 18, 2014 1:25 PM by steveald

    Portal to display records with a partially matching field

    steveald

      Title

      Portal to display records with a partially matching field

      Post

           I have a FMP 11 database with a large number of records, among which there are duplicates I would like to reveal - i.e., when someone creates a new record and enters "Act Now" in the Name field, the portal would display all the other records already created with "Act Now" in the Name field. Better yet, it should also display all records with "Act" in the Name field - like "First Act Theater" and "Act Industries" (though I don't think it needs to go as far as finding the "act" in "Reactor Electric").

           To be most useful though, it should probably reveal the partial duplicates as they type - i.e., type an "A" and records containing "Act Now" and "Ace Hardware" and "First Act Theater" and "Aluminum Products" appear; add a "c" and the portal list would shorten to just the first three entries; add a "t" and only "Act Now" and "First Act Theater" would be left.

           Following other posts I've found, I set up a self-join relationship using the Name field and I set up a filtered portal to display the duplicate results. I also tried setting up a script trigger to act on letters as they were typed in the Name field. But I'm not having any luck getting the portal to display everything I would like it to. Mostly what I end up with is records where the entire field is an exact match. Even when I set the portal filter to only look at the first word. The script trigger directions I found won't work because they are from 2011 and parts of it like OnObjectModify don't appear to exist anymore in FMP 11.

           Can someone get me pointed in the right direction? 

       

        • 1. Re: Portal to display records with a partially matching field
          philmodjunk

               The relationship should use the cartesian join operator and a portal filter expression such that when the search text field is empty, you see all records in the portal.

               OnObjectModify does exist in FileMaker 11.

               Here's a demo file you can examine and compare to yours: https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7

          • 2. Re: Portal to display records with a partially matching field
            steveald

                 Thanks. I'm looking at your demo file now. Yours was one of the older posts I'd referenced previously and the demo files there were no longer available.

            I'll have to look up cartesian join operator; that's a new one for me. As for the portal filter expression, can it not be set up so the portal is empty until something is typed into the Name field? That also makes me think. What if someone pulls up a record that's already in the database? I would want the portal to display any matches or near-matches then as well.

                 I must be missing something regarding OnObjectModify. Attached are two screenshots showing all of the options available under Script Triggers for my Name field or your gSearcgField field. The closest I found was OnLayoutKeystroke.

             

            • 3. Re: Portal to display records with a partially matching field
              philmodjunk

                   A Cartesian join uses the X operator instead of =. You'll find examples of this in the demo file if you open Manage | Database | Relationships.

                   For script triggers, you are looking in the wrong dialog. Those are the layout based triggers available in layout setup...

                   In layout mode, right click a field and select ScriptTriggers and you'll get a list of triggers appropriate for a field.

              • 4. Re: Portal to display records with a partially matching field
                steveald

                     I found the script trigger you were referring to. But when I started implementing the components of your demo and tried searching in my Name field, it was replacing each previous letter typed with the letter just typed.

                     I'm not sure I can use your demo to go by. The Name field cannot be global - it should be unique for each record. That's the purpose of this exercise - to expose duplicate/near-duplicate records when new records are added or existing records are searched for.

                • 5. Re: Portal to display records with a partially matching field
                  philmodjunk

                       The name could still be global if you used a script to update the field with each record--using a script performed by the OnRecordLoad trigger, but the method does not require using a global field as the search field. While that is very often a good idea for a search field as it keeps the criteria specified by different user, it's not a requirement for this method to work. In fact, the filtered portal will update more readily if the field is not global--you might not need some of the extra scripting in order to get the portal to update with each keystroke.

                       

                            it was replacing each previous letter typed with the letter just typed.

                       That suggests an error in one of your scripts or this would not happen. If you can't spot what's different, you'll need to post your script here.

                       To post a script to the forum:

                         
                  1.           You can upload a screen shot of your script by using the Upload an Image controls located just below Post a New Answer.
                  2.      
                  3.           You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here. (with this approach, you can get multiple script steps on the same line, please edit the pasted text by inserting some returns to separate those steps.)
                  4.      
                  5.           If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
                  6.      
                  7.           If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format.
                  • 6. Re: Portal to display records with a partially matching field
                    steveald

                         The only script in use is a duplicate of your Update Search script. I used the field that the trigger was applied to, as did you.

                         In the attached screenshot set, you can see the script, the script trigger, and the database relationship in use here. ABACUS_GuarIssueJuly04 is the database. Group Name is the field in question (which I have been referring to as "Name"). The script trigger is applied to the Group Name field. And you can see where Group Name is linked using the Cartesian join in a self-join relationship.

                         With all of that in place, whenever I type a letter in the Group Name field, the next letter I type replaces the previous letter, so there is never more than one letter in the field. This happens in Browse mode on a new record and in Find mode.

                         Thank you for your patience on this.

                    • 7. Re: Portal to display records with a partially matching field
                      philmodjunk
                           

                                With all of that in place, whenever I type a letter in the Group Name field, the next letter I type replaces the previous letter, so there is never more than one letter in the field. This happens in Browse mode on a new record and in Find mode.

                           a) this method is not to be used in find mode, only browse mode.

                           b) the set field script you've posted here, is not the source of this issue. Some other script is causing each keystroke to overwrite the previous letter instead of appending it to the end of the search text. You'll need to find and correct that script.

                           Might you have an onObjectKeystroke or OnLayoutKeystroke trigger set here?

                      • 8. Re: Portal to display records with a partially matching field
                        steveald

                             This is a mystery. I've just started using script triggers and don't have any running on this database. The field had Looked-up value checked with nothing specified for the lookup, but unchecking that made no difference. This issue only occurs when I activate the script trigger defined above.

                             I did wonder if my use of the script should require referencing the self-joined version of the field instead of the original version at some point - ABACUS_GuarIssueJuly04 2::Group Name instead of ABACUS_GuarIssueJuly04::Group Name.

                             If this can't be used in Find mode, then I may need to pursue another avenue anyway. I want my users to be able to see what matches or close-matches there are when they either add a new record or search for an existing record and then enter something in that field.

                        • 9. Re: Portal to display records with a partially matching field
                          philmodjunk

                               The script only sets the field to the value that it already has. This shouldn't result in overwriting data with the latest keystroke. this script may not even be needed with a non-global search field. Try removing the OnObjectModify script trigger and see if you get any change in behavior.

                               

                                    If this can't be used in Find mode,..

                               I am afraid that statement makes no sense. It can't be used in find mode, but there is also no reason to use it in find mode. You can get all the functionality you need from it without ever using find mode to do so.

                          • 10. Re: Portal to display records with a partially matching field
                            steveald

                                 I don't know what to tell you. As I said, the field behaves normally except for when the OnObjectModify script trigger is applied to it using that script. Further, I have been noticing significant delays in response time from the database since I started doing this. And I heard from a remote site of ours that said this database would not load up at all. So, I deleted the Cartesian self-joined relationship I had set up, along with the portal I had created to work with it. And the database started responding normally, both here and remotely.

                            Then I tried activating the script trigger on the field again - exactly as shown above - and the same issue came back. And it stayed until I deactivated the script trigger.

                                 As for not using this function in Find mode: this has been our primary database for over 16 years. Every time we want to add a new customer, we check to make sure they are not already in the database - if they are not, we create a new record and add them. Not always checking or not finding existing similar records (sometimes by not searching for the exact way an existing record is spelled) has resulted in some duplication. This creates a potential issue for how we deal with our customers. I thought this function would be a useful way for us to see similar existing records on the fly as we search for existing names or add new names to the database.

                                 Let me know if you have another suggestion. Apparently, the self-joined relationship (Cartesian, or not - I'm not sure) is going to be an issue based on the decreased response time it appears to create. And the script trigger renders the field unusable as is.

                            • 11. Re: Portal to display records with a partially matching field
                              philmodjunk

                                   As a test, I made a copy of the demo file and changed the storage options for gSearchField so that it is no longer global. The search portals continued to work correctly and I did not have any trouble entering search text into the search field. Subsequent letters typed in did not replace letters previously entered. Thus, some other factor is producing that result. Could there be an auto-enter calculation specified for your search field? Do you mean that if you enter text, exit the search field, then return, when you enter more text, it replaces the original text? That's caused by a field behavior setting: "select entire contents on entry".

                                   With regards to Find Mode:

                                   The point that I am trying to make is that this tool can be set up to do what you want and does so without ever using Find Mode to do so. Since it gets the job done in Browse Mode, the fact that it does not work in Find mode is irrelevant.

                                   Filtered portals and portals with Cartesian joins can have significant performance issues if you have a lot of records in your join table. The point at which responsiveness becomes unacceptable will vary with your hardware and network speed. Obviously, hosting the database from a remote site instead of your LAN will be a major negative factor in that calculation.

                                   Which version of the search portal are you trying to set up? Could the "starts with" version be acceptable? If so, there's an older method that uses neither Cartesian Joins nor Portal Filters to produce the same results. If you have Access to FileMaker Advanced, this method can be made even more flexible through the use of a custom function though a pretty decent version can be set up without a custom function.

                                   Other than that, I think that you are looking at using a find with wildcard operators to check for similar records. A scripted find might be able to pull up a found set of records in a different window or layout and then the list of ID's from that found set could be used to make those records appear in your portal. This won't, however, be something that updates with each keystroke.

                                    

                              • 12. Re: Portal to display records with a partially matching field
                                steveald

                                     "Select entire contents on entry" was exactly it. Now that I think about how this function works, that should have been obvious to me. Several text fields in this database that are accessed frequently have that option turned on to help speed things up. Mystery solved. But the behavior was occurring by simply typing in the field - in Browse or Find mode. Click in the field, type "Apple" and when you're done, all that's there is the "e". I saw the entire field highlight when I clicked in it, but it didn't occur to me that it was doing a whole-field replace every time I typed a letter. Something to keep in mind whenever I use this function, I guess.

                                     Setting the database up to make use of this function may require rethinking our work process. The field in question has always been just a plain text field, like a Company Name field in a contact list database - with Find mode as the only search function used. There are currently over 7000 records in this database (we got rid of over 10,000 outdated records a few years ago) which is hosted on a dedicated server using FMS 11. Each record contains 100's of fields - some of which involve indexing, and many require on-the-spot recalculation, or lookups involving other databases. And, we do have that other site which accesses the server remotely. So, I should probably look for an alternative to the filtered portals and Cartesian joins - based on what I saw yesterday.

                                     I had been concentrating on the Pattern Matching Search version from your demo. I'll take a closer look at your Starts With Search. Is the older method you mentioned available somewhere online? Or can you walk me through it? I do have FMP Advanced. Is there any chance it would break when we finally make the jump to FMP 13 this year? (Or, perhaps there's a reason to hold off on this function until we do make the jump?)

                                     Thanks again for your time.

                                • 13. Re: Portal to display records with a partially matching field
                                  philmodjunk

                                       The older method reproduces the "starts with" type of partial text matching by using a calculation to produce a return separated list of values in a field that is then used as a match field in the relationship used for the search portal.

                                       Without using a custom function, it looks something like this:

                                       List (
                                       Left ( name ; 1 ) ;
                                       Left ( name ; 2 ) ;
                                       Left ( name ; 3 ) ;
                                       Left ( name ; 4 ) ;
                                       and so forth....
                                       Left ( name ; 20 )
                                       )

                                       If Name has the text "Popodopulis", you'd get the values:

                                       P
                                       Po
                                       Pop
                                       Popo
                                       Popod
                                       and so forth...

                                       That would match up to 20 of the left most letters of Name. A recursive custom function can use a recursive loop to produce this list of values and then you are not limited to a specific maximum number of letters.

                                       If this field were called "cClairvoyanceList", your relationship for the search portal would look like:

                                       YourTable::SearchField = YourTable2::cClairfvoyanceList

                                  • 14. Re: Portal to display records with a partially matching field
                                    steveald

                                         I'm sorry. I appear to be missing something. I decided that using the calculation you provided out to 12 places would be sufficient for my purposes, so I don't think I need the custom function. But, I'm having trouble determining how to incorporate the calculation. Due to the performance issues discussed, I assume there is no self-join relationship involved. Is the calculation used to define a new field which is referred to in the portal? Or referred to in a value list?

                                    1 2 Previous Next