1 2 Previous Next 25 Replies Latest reply on Dec 9, 2013 1:53 PM by DouglasCourter

    Drop Down box to filter report

    DouglasCourter

      Title

      Drop Down box to filter report

      Post


           I am very new to FM, but I have tried to read posts similar to my need, but can't find an answer. I have several reports that require someone to pick one value from a drop down box and the report would be filtered by that selection. Example: I have a Travelers table and a Trips table which are linked by Traveler ID. I want to see details on trips that only one person has taken. I can get the report to list every trip that everyone has taken, but I need a way to select a Traveler and see only his/her trips. Any help would be greatly appreciated.

        • 1. Re: Drop Down box to filter report
          DouglasCourter

               I have found one way that seems to sort of work for my first question, but I have a follow-up. In the same database as before, I need to filter a report with a portal by date. I need to be able to pick from a Value List of dates, then have the portal display only records in which a field matches the date picked from the list. I tried using a Global field, but couldn't find out how to set a Global field based on my selection from the Values List. Thoughts?

          • 2. Re: Drop Down box to filter report
            philmodjunk
                 

                      I tried using a Global field, but couldn't find out how to set a Global field based on my selection from the Values List.

                 The field formatted with the value list should be your global field. Then selecting a value in it can cause your portal to update automatically with the selected value. The global field should be included as a match field in the portal's underlying relationship.

            • 3. Re: Drop Down box to filter report
              DouglasCourter

                   I thought that a Global field could only contain one value. This Value List will include every date that any person took a trip. Maybe I'm missing something. Is it possible to upload my sample database so that you can see what I'm talking about? Thanks for any help you can be. I'm trying, but I'm just so used to how things are done in Access.

              • 4. Re: Drop Down box to filter report
                philmodjunk

                     It depends on what you mean by "value".

                     A  global field will store the same data no matter what record or layout is current. The field itself will store what ever data you enter by selecting a value or values from the value list and that should be exactly what you need here.

                     Once you have a value in a global field, that value can be the match field that used in a portal's underlying relationship to control what records appear in the portal or it can be used in a scripted find to pull up or modify a found set.

                • 5. Re: Drop Down box to filter report
                  DouglasCourter

                       I think I'm confused by your comment "The field formatted with the value list should be your global field."  I have a global field called "Departure Date". In my Trips table I have a field called "Leave Date" (which is really the same idea, but with a different name). On my report/layout which is going to display the trip details for a particular Departure/Leave date, I have created a pop up menu using the Field/Control tool. In the Inspector (under the Data tab) what should I pick as the "Display Data From" box and what should I pick from the "Values from" box? Also, I created a Value List from the "Leave Date" field in the Trips table, which is what I had picked for the "Values from" box. These are only enabled in Find mode. I then set up the portal to filter by Leave Date. When in the layout, the find pop up lists the dates as needed and correctly finds a date, but the portal continues to list all leave dates, not just the one I selected in the find. That's why I thought I could pick a date from the pop up menu and have it write that selection to the Global field and then filter the portal by the global field, but that hasn't seemed to work. Has this helped or confused things even more?

                  • 6. Re: Drop Down box to filter report
                    philmodjunk

                         It reveals more things to straighten out.

                         When you place a portal on a layout, the layout references data via the Tutorial: What are Table Occurrences? name selected in Layout setup | Show Records From. The Portal references data via the table occurrence name selected in Portal Setup | Show Related Records from.

                         When you enter find mode, specify search criteria and perform the find, the find is searching the layout's table for records. Any records in the layout's table that match the specified criteria are displayed as a found set for that layout (and all other layouts that specify the same table occurrence name.) Once that found set is produced, FileMaker returns to Browse Mode and any records shown in the portal will be shown as specified for the relationship between the layout's selected table occurrence and the portal's selected table occurrence. The find criteria specified for the find has no direct effect on what records appear in the portal.

                         To selectively control what records appear in a portal must be done via other means that do not use find mode at all.

                         If you are on a layout for Travelers and you want to see records from Trips that fall on a specific date, you can set up this relationship:

                         Travelers::__pkTravelerID = Trips|Date::_fkTravelerID AND
                         Travelers::SelectedDate = Trips|Date::Date

                         By setting up SelectedDate with a value list of dates, you can select a date and see only the trips records for that traveler that have that date in the Trips|Date::Date field in a portal that refers to Trips|Date in the Show Related Records From drop down in portal setup.

                         I am using Trips|Date as the name of a new table occurrence of trips on the assumption that you already have a relationship between traveler and trips that matches records by traveler ID. Using a new occurrence allows you to keep the existing relationship in use unchanged in other parts of your database file.

                         In this example, SelectedDate could be a global field and often, it is a good idea to set it up that way, but it doesn't have to be in order for this to work.

                         For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                         On the other hand, you can use TravelerID and a specified date to perform a scripted find on a layout based on the Trips table instead of the Traveler table to pull up the same records.

                    • 7. Re: Drop Down box to filter report
                      DouglasCourter

                           This seems very workable and within my grasp of FM. I will work on it and get back to you. Much thanks!

                      • 8. Re: Drop Down box to filter report
                        DouglasCourter

                             OK. I think I"m getting there, but the filter doesn't seem to be working in the portal. In Relationships I set up a 2nd occurrence of both the Travelers and the Trips tables. I then created a relationship between both Traveler ID fields and also the Trip:Departure Date and Global Departure date fields. On the layout the pop up menu now does list the available dates and does actually assign the chosen date to the Global field. The portal selects from the Trips2 table and filters on: Trips:Departure Date = Global: Departure Date. However, when I pick a date it doesn't actually invoke the filter correctly. If I remove the filter then all records are shown, which is what I would expect, but when I check the filter as above, it doesn't seem to work. What am I missing? Once I get this working I can use it several different ways throughout the database. Thanks for your patience!

                        • 9. Re: Drop Down box to filter report
                          philmodjunk

                               In Relationships I set up a 2nd occurrence of both the Travelers and the Trips tables.

                               I only suggested making a new occurrence of Trips. If you make a new occurrence of Travelers, your layout must be based on the new occurrence of Travelers instead of the original occurrence of travelers or your portal to the new occurrence of Trips isn't likely to work as expected.

                               And I haven't specified using any portal filter as this method filters the records at the relationship level--which eliminates the need for a portal filter.

                          • 10. Re: Drop Down box to filter report
                            DouglasCourter

                                 I think I have tried everything you said, but it still won't filter my portal. I am trying to upload a snippet of my relationships graph and my problem layout.

                            • 11. Re: Drop Down box to filter report
                              DouglasCourter

                                   Here is the problematic layout.

                              • 12. Re: Drop Down box to filter report
                                DouglasCourter

                                     See how it still displays all the trips. I just can't figure out what I'm doing wrong.

                                • 13. Re: Drop Down box to filter report
                                  philmodjunk

                                       The relationship between Global Values and Trips 2 has no affect on what records appear in the portal. Only the relationship between Travelers 2 and Trips 2 controls what appears there.

                                       Define the global departure date field in Travelers. Drag from Travelers 2::Departure Date to Trips 2::Leave Date so that your relationship now uses 2 pairs of match fields linking Travelers 2 to Trips 2.

                                       Make sure that Travelers 2 is selected in Layout setup | Show Records From.

                                       Make sure that Trips 2 is selected in Portal Setup | Show Related Records From.

                                  • 14. Re: Drop Down box to filter report
                                    DouglasCourter

                                         Wow. We are really close. This doesn't seem intuitive to me, but it works. Now, just one more problem. As you can see it is now only showing the trips for the selected date, but it is still showing the other person, though his trip list is empty. I don't even want to see the other person if that person has no trips on that date.

                                    1 2 Previous Next