5 Replies Latest reply on Mar 18, 2010 6:52 PM by spinny

    Help Please - Pop-up Menu

    spinny

      Title

      Help Please - Pop-up Menu & Searching Across Multiple Tables

      Post

      Hi,

       

      I am a Field Hockey Coach who is trying to create a recruiting database.  I have a fair bit of experience in Access but am using Filemaker Pro for the 1st time.  I have managed to set the database up but have run into two problems.

       

      1. All of the data we receive is given in excel formats with 15 preset fields.  The problem is that we attend 15-20 tournaments every year and some of the recruits are at 10+ of these tournaments.  Is there a way I can set a layout up that it will search across 15-20 tables within my database.  At the moment I have set up a main screen so it shows an individual player with all their evaluations from every tournament but I cant get it to look at every table to search for recruits.  I have created a table for each tournament within the database.

       

      2. When I am recruiting at a tournament I would like to have a screen with 2 pup-up menu's on it so I can select the 2 teams that I am watching.  Is this possible?  I cant seem to figure out how to run a report based on selecting a team name on a pop-up menu.

       

      I am using FM Pro Advanced V10 on a Mac running 10.6.2 and as mentioned earlier am a complete novice on this.

       

      Thanks

       

      Andy

        • 1. Re: Help Please - Pop-up Menu & Searching Across Multiple Tables
          philmodjunk

          I suggest re-thinking your table structure.

           

          Instead of 15 -20 tables, with one table for each tournament, make that one table with an added field that identifies the tournament that records apply to. You can perform finds on this one table to find the data you need quite easily then. To see just the data for one tournament, use the new added field in a search to find all records that one tournament.

           

          There are several ways you can set up a report based on a value selected from a pop-up.

           

          1. You could simply format your team field as a pop up.
          2. Enter find mode and select the team name you want.
          3. Perform the find.
          • 2. Re: Help Please - Pop-up Menu & Searching Across Multiple Tables
            spinny

            Phil,

             

            If I do that I may end up with lots of records of the same player in the same table- i.e. Joe Bloggs may be at 10 of the tournaments.  Is there a way that I can merge the multiple records into one record and discard the duplicates?

             

            Thanks

             

            Andy

            • 3. Re: Help Please - Pop-up Menu & Searching Across Multiple Tables
              philmodjunk

              Since Joe Bloggs is in many tournaments, don't you need to see that? His Name and contact information may be duplicated, but the fact that he participated in "10 tournaments" (and his performance stats in each) would be signficant, correct? Keep in mind you can do sorts and searches by tournament to control what records you see at any given time.

               

              If possible, I'd set up a separate table for storing player names and contact info and only import tournament data into this combined table, with a link that allows me to see the contact data when viewing the tournament records. You can do two imports from your spread sheets, once into each table while selecting a different subset of the columns of data in the file for each table.

               

              Eliminating duplicates can get to be a bit of a challenge unless all your tournament data already comes with a value that uniquely identifies a player so you can easily find the duplicates. Simply comparing names and addresses may not work as two players can have the same name and addresses are subject to change.

              • 4. Re: Help Please - Pop-up Menu & Searching Across Multiple Tables
                spinny

                Is there anyway that I can eliminate duplicates easily?  I have numerous instances of the same player in my imported data.  I need to delete all bar one of these players so there is no more than one instance of each player.  I found the script below but wasnt sure if it would work.  I dont want to specify any values in the script as not every set f duplicates are the same.

                 

                Show All Records
                Sort Records [Ascending, Restore, No dialog]
                Go to Record/Request/Page [First]
                Set Variable [$Value; Value:MyTable::MyField]
                Go to Record/Request/Page [Next; Exit after last]
                Loop
                If [MyTable:: MyField = $Value]
                Delete Record/Request [No dialog]
                Else
                Set Variable [$Value; Value:MyTable::MyField]
                Go to Record/Request/Page [Next; Exit after last]
                End If
                End Loop

                 







                • 5. Re: Help Please - Pop-up Menu & Searching Across Multiple Tables
                  spinny

                  I figured this out.....  amazing what a bit of confidence with scripts will do!