8 Replies Latest reply on Jun 27, 2011 8:30 AM by philmodjunk

    Report dropdown menu filter (omitting other records)

    sbhex

      Title

      Report dropdown menu filter (omitting other records)

      Post

      Hello eveyone,

      I'm trying to create a filter script that will allow me to filter records in a report layout using a dropdown menu. This is for a stock inventory database where I wish to filter records based on the publisher field.

      I've attached an image to show you the script I've got so far. This script filters the records successfully, but only shows the first record for the publisher. However, I'd like to show all the records pertaining to that publisher!

      Any help would be greatly appreciated.

      Thanks,

      BTW I'm using FileMaker Pro 10 Advanced

        • 1. Re: Report dropdown menu filter (omitting other records)
          aammondd

          Is your layout in form view? This will only show one record at a time from the layout table.

          Typically you would use a found set (rather than omitting) and a layout in list view.

          The larger the body of a listview layout the more space is given to display the records you might want to check how much "extra" body you have in the layout.

          Report layouts can take on a lot of different looks. 

           

           

           

           

          • 2. Re: Report dropdown menu filter (omitting other records)
            philmodjunk

            Why do you have Omit multiple records in this script?

            • 3. Re: Report dropdown menu filter (omitting other records)
              sbhex

              ammondd, my layout is in list view.

              My apologies, below is a screenshot of my current script:

              Script

              This script only displays the one record.

              Rather than omitting records, how would I set up the found set script?

              Thanks.

              • 4. Re: Report dropdown menu filter (omitting other records)
                philmodjunk

                Assuming that gIDItem is your dropdown list field and it's a global field with this relationship:

                YourTable::gIDItem = IT_Items_Self_gIDOrg::IDItem

                and YourTable, IT_Items_Self_gIDOrg are table occurrences of the same data source table as the names indicate here...

                THen you only need the Go To Releated record step and it will pull up a found set of all the records where IDItem matches gIDITem.

                No need for the omit record/show only omitted--which is one method for isolating the current record in a found set of 1 record (but can fail in networked environments).

                • 5. Re: Report dropdown menu filter (omitting other records)
                  sbhex

                  Thanks Phil, I've got that to work.

                  However, is there any way of filtering other records which are not specific to that publisher? At the moment I've set the script to:

                  - Show all records
                  - Sort records [Restore]    // sorted by Publisher
                  - Go to related record [from table: "IT_Items_Self_gIDOrg"; Using Layout: <Current Layout>]

                  • 6. Re: Report dropdown menu filter (omitting other records)
                    philmodjunk

                    Can you give an example of what you mean by this? (There's lot's of different ways I could interpret "not specific to that publisher".)

                    • 7. Re: Report dropdown menu filter (omitting other records)
                      sbhex

                      Apologies for my ambiguity.

                      For example, when I select "Addiction" as Publisher, the report correctly sorts the records by Addiciton, however other records are still listed within the report (please see attached screenshot.) Ideally I'd like only records from the selected publisher to be visible when filtered by the dropdown menu.

                      Example

                      Thanks.

                      • 8. Re: Report dropdown menu filter (omitting other records)
                        philmodjunk

                        There's a difference between "sort" and "find". What you want to do here and what your script should be doing is "finding" records rather than "sorting" them. Wink

                        That said, your script should only be pulling up records with "Addiction" in the publisher field if your relationship is correctly defined here. The other records should not match and should not appear here.

                        If "Publisher" is a text field. You first need this relationship before Go TO Related Records will work for you:

                        LayoutTableOccurrence::GlobalTextField = 2ndTableOccurrenceOfSameTable::Publisher

                        Global text field must be a field of the same data type as publisher, but with global storage.

                        Your script would simply be:

                        Go to related record [Show only related records; from table: "2ndTableOccurrenceOfSameTable"; Using Layout: <Current Layout>]

                        (The sort records step you used doesn't have any purpose here.)