4 Replies Latest reply on Sep 3, 2015 10:54 AM by KenHoopes

    Search Box for Portal

    KenHoopes

      I am in need of a way to search a table on a layout (without using any built in controls like the menu system, etc.) where it will return any records "like" the value entered into the search box. I have found a way to search where it is an exact match, but was hoping to loosen it up a bit, and give them more power.

       

      Below is my current scenario, please advise.

       

      I have a layout called "Drawing Lookup" that is tied to a table called "gtbl_dwglookup". On that Layout, there is one edit box that is tied to "gtbl_dwglookup::dwg", and one portal that is tied to "tbl_dwgs". The tables have a "tbl_dwgs::dwg = gtbldwglookup::dwg" relationship. I have tried changing the relationship, but none really do what I'm looking for.

       

      When using the "gtbl_dwglookup::dwg" edit box, and typing in the full drawing number, the portal populates with the appropriate drawing records. However, a lot of our drawings have a naming scheme where the first three characters are the same, on items that are of the same product code, and then the next two characters are of products that are manufactured on the same cell. By allowing the person to simply type in the first five characters and getting results, they can then look at one of the fields in the portal to identify exactly what drawing they are looking for, without having to know all eight characters.

        • 1. Re: Search Box for Portal
          bob_ellis

          Having the relationship based on the dwg field is causing the results you are seeing. Filemaker is looking for an exact match. To get around this, a Cartesian join can be used. Using this along with a global field and filtering the portal based on the contents of the global will give the results you are looking for.

          • 2. Re: Search Box for Portal
            KenHoopes

            I believe you are absolutely correct on this. Can you elaborate on the calculation for the portal filtering. if I do "tbl_dwgs::dwg = gtbldwglookup::dwg" in the calculation it behaves the same way as if the tables were joined without the Cartesian method. I cannot seem to find a "like" operator, and wildcards do not appear to be working.

            • 3. Re: Search Box for Portal
              jlamprecht

              Which version of FileMaker are you using?

               

              The first thing you can do is use the function Patterncount(). The filter calculation would be Patterncount(tbl_dwgs::dwg; gtbldwglookup::dwg)

               

              A second method is to base the portal relationship off the global field you are filtering. The method would work as follows:

               

              1. Add a ListOf summary field to your portal table. Make sure it gets a list of the primary keys of your portal table.

              2. On your layout , the user enters criteria in the filter.

              2. A script trigger (Likely onSave) performs a Find on the table defined in the portal.

              3. Set your global field to the value stored in your ListOf summary field. This is a list of all of the primary keys in the found set you just obtained. This will trigger the relationship between the tables and show the found records.

               

              The first method is the purer LIKE method as you can find phrases in the middle of words. FileMaker Finds searches start at the beginning of words, so you would not get the same results as a Patterncount. Additionally, the portal would not show any results until a value is entered in the global field. Your portal will show up empty to start.

               

              I hope this helps.

               

              Edit: I forgot to mention that ListOf is only available in 13 and 14. That is the reason I ask above.

              • 4. Re: Search Box for Portal
                KenHoopes

                I wish I could mark both as Best Answer! Thanks so much, it worked perfectly.