13 Replies Latest reply on Dec 28, 2010 9:03 AM by philmodjunk

    Dropdown to Find Records

    MikeHaddon

      Title

      Dropdown to Find Records

      Post

      I'm looking for assistance on how to use a dropdown box on a layout to find a recordset.  The idea is the user would be able to select an item from a dropdown box, upon selection, the recordset would narrow to those matching records.

      First, I just don't know how to do it.  I tried my luck with a script tied to the OnModify action of the dropdown list.  It looks like it tried to work as I get a message telling me no such records meet the criteria.  One of the problems may be the ID field I am trying to use.  I have a table of committees, and each committee has a unique identifier.  Obviously, I would want to do the match on the Committee ID, but I need to populate the dropdown with the actual committee name (e.g. my users will have no idea what committee 14H is).

      On the dropdown, can it include two fields, one hidden (e.g. the ID field).  If so, how do I tell the script to match based on that hidden field?

      I hope this makes sense.  Thanks for any assist.

        • 1. Re: Dropdown to Find Records
          philmodjunk

          Sounds like you have your value list set up correctly. (The hidden field is the value that will be entered when the user makes a selection.)

          You just can't "get to" the value while in find mode.

          Simplest solution is to make the field you've set up with this drop down a global field. In Manage | Database | Fields, find this field, double click it and select the global storage option on the file tab.

          Then a script like this (gCommitteeID is the global field) should work for you:

          Enter Find Mode[]
          Set Field [YourTable::committeeID ; YourTable::gCommitteeID ]  // this step will not work unless gCommitteeID is a global field
          Set Error Capture [on]  // suppresses the "no records found" dialog if no records are found
          Perform Find []
          Sort [No dialog ; restore] // use if you want the records in a specific order

          • 2. Re: Dropdown to Find Records
            MikeHaddon

            I'm new to FileMaker, but not databases in general.  The problem I can't seem to get around is that setting the field to a global field will only seem to allow me to have one value in that field for every row of the table.  I'm not sure how that helps me with a dropdown and search.  For example, the following would be an example of the data in the table I am using:

            CommitteeID     Committee

            1H                     Management Committee

            2H                     Appropriation Committee

            3H                     Operations Committee

            After changing CommitteeID to a global field, my table now looks like the following:

            CommitteeID     Committee

            1H                     Management Committee

            1H                     Appropriation Committee

            1H                     Operations Committee

            Clearly, I no longer have an ID field tied to a specific committee.  Instead, I have the exact same CommitteeID tied to multiple tables.  What am I doing wrong?  Thanks!

            • 3. Re: Dropdown to Find Records
              philmodjunk

              You need a field that is separate from the field used for storing the CommitteeID for each of your records. This global field is just used for the search drop down. Think of it as a text box you would put on an access form.

              If you check my script over, you'll see that YourTable::gCommitteeID is the field formatted with the drop down, but YourTable::committeeID is the field that is searched on. This second field corresponds with column one in your example above.

              • 4. Re: Dropdown to Find Records
                MikeHaddon

                I made some progress; however, I am stuck in what I think is an important area.  It is with the following instruction:

                Set Field [YourTable::committeeID ; YourTable::gCommitteeID ]  // this step will not work unless gCommitteeID is a global field

                When I am working on the Script and add the Set Field [], it asks me to Specify.  I am able to add one field from one table.  For the life of me, I can't see how you are able to add two fields in this area.  It just doesn't let me - and I do have one of the fields set up as a global field.  In any event, I get one or the other:

                Set Field[tblStanding:Committee]

                or

                Set Field[Committees::gCommitteeID]

                I can't see the flexibility to make it:  Set Field[tblStanding::Committee; Committees::gCommitteeID]

                Am I missing a place where I can just type it in?  I'm stuck.  Thanks again for your help.

                • 5. Re: Dropdown to Find Records
                  philmodjunk

                  Yes you are missing a place. Many new users hit the same problem.

                  Select the Set Field step in the script editor and look at the bottom right corner of the editor. There are two specify buttons. Click the first button to select the target field, CommitteeID. Click the second one to enter the expression for the value to be assigned to your target field, in this case you can select or enter the second field, YourTable::gCommitteeID.

                  • 6. Re: Dropdown to Find Records
                    MikeHaddon

                    That did the trick, in terms of allowing me to have the field and global field in the Set Field parameters.  When I run the layout, drop down, and select the committee from the list, it seems to be performing something more like a Find First.  The layout goes to the first record of an individual who is a member of the committee I selected, but I am not getting the narrowed recordset.

                    • 7. Re: Dropdown to Find Records
                      philmodjunk

                      What view mode are you in? Form, List or Table?

                      In form view, you will only see the first matching record. In List or table view, you should see all the records that match your find. In the status area, you should see something like 5 / 300 in all view modes, which means you found 5 records out of a total of 300 in the table.

                      • 8. Re: Dropdown to Find Records
                        MikeHaddon

                        I am in List mode.  In the status area, I get 6 of 105.  This I took to mean it did a find first, and the first record it found was on the 6th row.  In this view, all 105 records still appear.  I can scroll down and see them all.  What I am looking for is the List view containing only those who are members of the committee I selected.  I then can choose another committee and see only those who are members of that committee.

                        The way it is working (or not working) now is the List view contains all 105 members.  

                        • 9. Re: Dropdown to Find Records
                          philmodjunk

                          Not if you see 6 of 105. That indicates a found set of 6 records were found--not the 6th record of 105.

                          By any chance, are these 105 records displayed in a portal?

                          If so, you need a completely different approach as finds are perfomed against the Layout's Table and when those records are found, each such record displays all related records in any portal--so the find won't change what is actually listed in them.

                          • 10. Re: Dropdown to Find Records
                            MikeHaddon

                            PhilModJunk

                            First, I want to thank you so much for all of your input and patience.  I'm still struggling to figure out the whole FileMaker thing.  At this point, I still can't get the dropdown find to work.

                            1.  I'm not sure in which table I need to put the global field.  From what I have gathered, your script, in the Set Field method, the first parameter is the field to be searched.  The second parameter is the value it is searching on.  I would like it to search on the value I have selected in the dropdown box, but I can't include a global field in the dropdown - as it holds only one value at a time - FM seems to prohibit me from identifying a global field as part of the dropdown box.

                            2.  The concept, I understand.  As you explained, the global variable would basically hold in its contents the value I selected from the dropdown box - to then use as the search criteria.  But I can't seem to get from point A to point B.  I created a dropdown box that includes a listing of committee names and their committeeIDs.  I can select a committee from that dropdown.  However, somehow, I need to push the selected committeeID into a global variable in order to use it in your script as a search criteria.

                            You have already helped me a ton, so I understand if you are wanting to wash your hands at my whole mess.

                            • 11. Re: Dropdown to Find Records
                              philmodjunk

                              I'm not sure in which table I need to put the global field.  

                              The Global field can be defined in any table in your file. Global fields are accessible from any layout regardless of whether you have any relationships linking to the global field's table or not. Often, developers will define a table just for listing all global fields that aren't part of a relationship just to make it easier to keep track of them.

                              From what I have gathered, your script, in the Set Field method, the first parameter is the field to be searched.  The second parameter is the value it is searching on.  

                              That is correct. That's why you should place your global field on a layout and format it with your value list of committee's so that a use can select a committee for the script to then use to perform the find.

                              I would like it to search on the value I have selected in the dropdown box, but I can't include a global field in the dropdown - as it holds only one value at a time - FM seems to prohibit me from identifying a global field as part of the dropdown box.

                              That makes no sense to me. I use global fields in this fashion frequently and with no trouble. Make sure you follow these steps when placing the global field on your layout and formatting it:

                              1. Enter layout mode.
                              2. Use the field tool to add a field to your layout. Select the global field from the dialog that pops up.
                              3. Use the inspector to change this field's format from "edit box" to drop down list or pop up menu. Select a value list so that it lists the committees. If you do not have such a value list, click the pencil button to open manage | Value lists... where you can then create on.

                              If you are still unable to get this to work, post back with a description of exactly where and how this fails for you.

                              • 12. Re: Dropdown to Find Records
                                MikeHaddon

                                PhilModJunk

                                .....I saw the light!  Thank you so much!  It is working perfectly now.  On a related subject, I have two questions if you have the time.

                                1.  I have tried to add a second dropdown to the header on the layout, and it doesn't work like I have the first one working.  I have completed the exact same steps as the first, but I am not getting anywhere.  Is there a limit to the number of these filtering dropdowns on a layout or something?

                                2.  Once I select the committee (on the one that is working), it substitutes the reference ID in the dropdown box, rather than the committee name.  For example, I select the dropdown, and it lists each committee by name (e.g. Appropriations Committee).  Once I select it, the layout filters to just those members of that committee.  However, if I look back up in the dropdown box, rather than reading "Appropriations Committee" it will read something like H15 (the CommitteeID).  Is this easy to fix?  

                                Thanks so much for seeing me through!

                                • 13. Re: Dropdown to Find Records
                                  philmodjunk

                                  1. There's no limit, so you have  something different in the setup.

                                  2. You can either format the field as a pop up menu or you can place the related committee Name field next to the drop down. If you choose this second option, you can resize the drop down field until only the arrow is visible and place it next to the name field so that it looks like one field. You'll also want to use Behavior in the Inspector to prevent the user from entering this name field while in browse mode so that they can't accidentally edit a committee name in the related table.