4 Replies Latest reply on Feb 18, 2012 1:07 PM by Claw'd

    Need help with Relationships with other database files

    james_callery

      Hey everyone,

       

      I currently have 7 different databases that contain information about hockey players. We run tournaments and I'm trying to get a hang of using FileMaker to do most of the work that we've done by hand for over 10 years now.

       

      The 7 different databases ALL have the following fields:

      • ACC (short for accepted)
      • Event (hopefully I can use this later to create subsummary reports)
      • Year
      • Position
      • First Name
      • Last Name
      • School/Team
      • Address
      • City
      • ST
      • ZIP
      • Phone
      • DOB
      • Cost
      • Deposit
      • Balance
      • Email
      • Application Received (yes or no)

       

      My problem is that each of these databases were previously used by whomever created them mainly for mailing labels. We'd get names of about 300 kids and enter them into the database. Afterwards, we'd send to the kids, and we'd only get about 5% to respond by sending in an application. Afterwards, we update the previous record of the player with more accurate information (exact mailing address, DOB, the deposit they paid, etc...). I also mark under the application received field that we've received the application (go figure!). Herein lies my issue. Our boss is computer illiterate like you wouldn't believe, and he wants an updated report every other day with the exact numbers for each camp, how many at each position (forwards, defense, and goalies), as well as the total revenues from each different Event.

       

      I'd like to have the tournament summary database to display all the records that have Application Received with a YES value, and for the Tournament Summary to then sort the records based on Event name, to show us a summary for each event all in one location. Basically, it'd be a master report of all our tournaments. Now I know that the easiest way to achieve this is to just create ONE! database with 7 different tables for each event, but since the records are already there and everyone in our office is comfortable working with the separate database files, I'd like to keep it that way.

       

      In the Tournament Summary database, how can I grab all the records from the 7 different database files that have the Application Received value of YES? I would like these records to update automatically (I think dynamically is the word?), meaning if I go to Database 2 for Event 2, and mark John Doe that we received his application, I'd want all his information to be added to the Tournament Summary database without me having to import the record.

       

      If ANYONE can help me with this extremely difficult (or so it seems) task, I'd be extremely greatful. I appreciate everyone's response/help in advance! Thanks guys.

        • 1. Re: Need help with Relationships with other database files
          comment

          james_callery wrote:

           

          Now I know that the easiest way to achieve this is to just create ONE! database with 7 different tables for each event

           

          I think the easiest way to achieve this (and many other things) is to have all players in the same table and all events in another table - preferably both in the same file. This is assuming each player participates in one event only - otherwise you'll need one more table.

           

          I don't know of a way to summarize data from 7 tables, at least not an easy one. OTOH, it shouldn't be too difficult to import  data from 6 tables into one.

          1 of 1 people found this helpful
          • 2. Re: Need help with Relationships with other database files
            JohnReed

            If "everyone in our office is comfortable working with the separate database files, I'd like to keep it that way." is the primary hindrance, consider building the one DB you know you should have and provide the users with aliases to the TOs, layouts, etc. that they are accustomed to seeing. Unless they will always be allowed to create new DBs (bad form) they shouldn't need to know the source of the data.

            • 3. Re: Need help with Relationships with other database files
              LyndsayHowarth

              Sound like you are sacrificing any potential to build something durable and unbreakable for the sake of not wanting to change a bad work habit... You might be surprised... they might actually be relieved. If you decide to add a field or layout change... you have to make it 6 times to be consistent. This is crazy.

               

              Of course there are ways you can summarise all the databases... but it is tedious and a silly waste of time.

               

              Whatever the classification of the six databases that separates them... becomes an extra field. You can set it up so that when they go to view the data, they can only see the data for one of those classifications at a time... but ultimately the data is all in one table.

               

              - Lyndsay

               

              PS... I have a product that does all of this and much more. Contact me privately for more info.

              1 of 1 people found this helpful
              • 4. Re: Need help with Relationships with other database files
                Claw'd

                In order to stay sane if you have seven concurrent users you will need a server and one copy of the database hosted on that server. If you don't have that many just lots of files because there are lots of tournaments you may be able to share the one database from a client computer but make sure that it is properly backed up as a crash on the serving computer can wreck the hosted database.

                 

                To start take a good copy of the most usefull database of the seven (it may even be best to start a fresh DB).

                 

                What you will need as a basic starting point are three tables as you need a many to many relationship (one player can play in many tournaments, one tournament can have many players) This needs a piggy in the middle table so you will have a player table, a tournment table and a tournament- player line item table. I suggest you look at the Filemaker templates and read about creating a many to many relationship in the filemaker help as this is very informative.

                 

                Not only do different isolated databases create silos of unassociated but often duplicate date they are the very devil to maintain and report from.