5 Replies Latest reply on Apr 21, 2014 10:31 AM by philmodjunk

    Easy search in related table



      Easy search in related table


           As a new FileMaker user, I'm not even sure how to state the question correctly.  I want to insert a record key in a join table.  Rather than pop over to the other table, do a full lookup, write the record key down, I'd like to "auto browse".

           In other DB tools I have used, it was possible to create a field into which you could type that record key, but next to the field would be a simple down arrow which would expand to a browse window which would include the important data to allow you to find the correct matching record.  These would allow you to scroll down, or even key the opening letters of the search which would skip forward.

           In my case, I'm looking up music, so the browse window needs to show the title, the composer and the arranger (because our library includes several duplicate appearing (by title) versions of the same music.

           Is this form of "field" available in layouts, and if so, what is it called?  If not, I'm assuming looking at what I've seen that a new window could be opened in a script that would exit by returning the appropriate into the correct field in a portal row.  Does an example of that sort of query exist in the Knowledge base that I could build on it.

           I have attached a picture that could demonstrate what I am asking about.


        • 1. Re: Easy search in related table

               The typical method in FileMaker is:

               Assuming these relationships:


               First enable "allow creation of records via this relationship" for Join in the table1 to Join relationship.

               Then format the _fkTable2ID field in the Join table as a drop down list of ID's and a name/description from Table 2. This is more primitive than the multi-column combo box you show, but it does work. It's also the limited "Beginner level" method for finding a record in Table 2 to which to link your new Join table record. So try that approach first then report back when you either get it working or "get stuck" trying to.

               If you are then interested in more sophisticated methods for searching out a record from Table2 we can try that after the first method works for you--which will also confirm that you have your relationships correctly established.

          • 2. Re: Easy search in related table

                 Found the value list option this morning in the knowledge base.  The knowledge base article I found was not v. 13 so I had to play around a bit to find how to adjust the parameters in v.13, but it now works clean.

                 is there a way to use a value list to perform a find instead of set a value?   It does work if you first click on the Find menu option, then select the record you want, then click on perform find, but that's extra clicks.  I like the browse type find much better than having to click in part of the field, get a solution set, then have to browse through that if I'm searching on a natural sequence field.  The more capable find works very nicely when trying to find random records (like what music was copywriting in 1938.)

            • 3. Re: Easy search in related table

                   When you click on the drop down list formatted field in browse mode (I would not use find mode with this field located in a portal row as I described in my first post), it would seem that you are using the drop down list as a "browse type of search".

                   As I stated in my first post, there are alternatives that can be used where entering some text "filters" the list down to a much smaller list of possible choices. Both a conditional value list (where you select a category in one field and the list then displays only values from that category), a drop down list that "auto-completes" as you type in a name or description or a "search portal" where typing in some text limits the choices in the portal to those that either start with or contain the entered text are all possible options.

                   But I'm not quite sure that you have the first option working for and the others all build on that set up to some degree. So we need to make sure that your basic method is working first.

                   Can you select a Table 2 value in the portal while in Browse mode to link that Table 2 record to your portal's join table record?

              • 4. Re: Easy search in related table

                     Sorry Phil, my last question was poorly worded.  I have attached a picture of where I am at the current time.

                     The portal now works correctly.  You can see the Value picker near the right edge of the portal, and that correctly looks up a title and sets the correct value in the join database.  When the time comes, I'd like to talk with you about how I could be 3 "extra" fields in that value list, but we can postpone that.

                     My question relates to the fields I have highlighted in red letters.  I'd like to be able to click on the arrow of that value picker, browse through the available concerts, and have that do a search to initialize this page.  As I mentioned in my last note, I've had to set that such that it works only in Find mode (or it will attempt to change the Concert ID of the page it is currently on.)  This does work if I first click on the magnifying glass, then select the data I want, then click on Perform Find.

                     In addition to answering this question, is there a good tutuorial in the knowledge base on scripting.  I have more than 50 years of IT background, so can probably work my way through that easier than bothering you w/o sufficient background.


                • 5. Re: Easy search in related table

                       With that much experience, I wouldn't expect you to need a good tutorial on FileMaker Scripting. Most is very typical of what you find in any procedural scripting or programming "language". And about 80% of the script steps have corresponding menu options so if you can manually string together several menu picks to do what you want, a script of steps with the same names should produce the same result.

                       I would not use the same field for both data entry and your search. It's too easy to either change data because you forgot to enter find mode or to lose data because you entered it while in find mode and didn't notice that you were.

                       I'd add a global field for selecting your concerts and format it with your value list. A script trigger such as OnObjectSave can be set on that value list to perform a script that enters find mode and uses the data in the global field to set up and perform the needed find. With the right relationship using the global field as a match field, got to related records might also be used to bring up that same record or group of records.

                       Here's a thread with a lot of different scripted find examples--most of which work from user criteria that was entered into a global field while in Browse mode: Scripted Find Examples