9 Replies Latest reply on Oct 29, 2010 2:52 PM by philmodjunk

    Updating very very old Filemaker database



      Updating very very old Filemaker database


      While I have used FMP10 to do some nifty things in the past, I am an admitted novice with databases and am stumped over what may prove simple to experienced uses.

      In the sports museum of which I am the archivist, we have had an interactive database in the galleries that allows visitors to call up the results of major tournaments.  The database is well over 10 years old, written with an antiquated predecessor to Filemaker, using an ancient Macromedia Director as the interface on a touchscreen monitor, and huffing along on a Macintosh G3 from circa 1998.

      I have managed to export the data from the database and imported all the fields to a shiny new FMP10 database, and I would like to rebuild the database with a fresher interface on a newer computer and touchscreen before the diesel powered computer finally sputters to a halt.

      Here's my challenge--I have figured out how to redesign the part of the database that allows users to call up the tournament name and then scroll through the winners (Ex: French Open/Men's Singles = winner/loser/score/year), but I cannot figure out how to allow the user to do a search by player.  What I need, it seems, is a way for users to select a letter leading to a list of alphabetized players from which the user can then select a specific player to scroll through the records of his or her wins.  That is, choosing the "A" button results in a list of all the A players, which can then be selected one at a time to scroll through the records for that player.

      Admittedly, I am thoroughly confused and frustrated due to my ignorance.  The data from the old database seems to come from a single table, and I cannot figure out how to create a searchable list of players based on their last names.  Right now the data for player names is in 4 fields -- Winner1, Winner2, Loser1, and Loser2.  I have parsed out the Last names, but I still have two different sets of data for Winner1 and Winner2.

      I have 4 tennis tournaments each year, each with 5 events I must cover (mens singles, womens singles, mens doubles, womens doubles, mixed doubles), resulting in 8 winners and 8 losers for each tournament each year.

      I'd like to design it similar to how it runs now: Search by tournament/event, or search by player (alphabetically) to get the winners and losers (and scores) of the specific tournament events by year.

      Seems as if it should be simple enough, but my brain is tied in knots over this trying to get the data fields from the old database to behave. 

      Perhaps I need to design a new database from scratch? Any suggestions on simple strategies for designing this? Do I need multiple tables?  Does this involve portals? A point in the right direction would be greatly appreciated. 

        • 1. Re: Updating very very old Filemaker database

          Given that part of the old application was written in Director, I don't think you have much choice, other than to rewrite the database.  Plus, you can take advantage of the relational nature of the newer FM database files.  One good way to start is to split up the data into separate tables:




          Given the above set of tables, Tournaments would contain 1 record per tournament, while Players would contain 1 record per player.  Then, Matches would have 1 record per match, including the Tournament, both Players, score, and who won.

          • 2. Re: Updating very very old Filemaker database

            I would redesign from scratch. If you get FileMaker Pro Advanced, you would be able to copy/paste selected fields from the "one table" file to their proper homes in the new file. But likely there are not all the many fields, and they are simple, so you can create manually.

            Basically, whenever you see "something 1" and "something 2" fields, there's a good chance that it is not properly designed, especially if it is critical data. This kind of data really belongs in a separate "join" table. I'm assuming in this case that the 2 is because of "doubles"? 

            Each person should get their own record in the join table for a tournament (or whatever), with a person id and name (last) and the tournament id. I would not bring in the name of the tournament (which is a "composite" entity anyway; same name, different years, etc.), just the ID (maybe the year too, if you want to filter by year also).

            Join table fields: PersonID, TournamentID, winner or loser "role" (more than one way to do this, but just a single text field, shown as radio buttons would do). 

            Tournament <-> join table <-> People

            A filter based on a letter to the Last Name field in the join table would work.

            If you really must keep your old (incorrect, bad, etc.) design, you could create a calculation field, winner name & ¶ & loser name. That would also work (kind of); it can work as a "poor man's join table"; but will present many limitations. It is actually easier to use proper design in these cases than continually trying to work around inadequate design.

            • 3. Re: Updating very very old Filemaker database

              That single table with multiple fields for player names is the key issue.

              You need a separate table for players linked to a table for tournaments and likely a third table for matches played during a given tournament.

              Designing new tables and importing the data from the old into the new while splitting up match, tournament and player info into separate tables is probably your best bet.

              Learning how portals work is definitely a place to start as you can use a portal to players on a match or tournament layout that lists the players that participated in that match or tournament. Even more basic is to learn how to set up related tables of information in a relational database such as FileMaker Pro.

              You may want to invest in some tutorials or a book on FileMaker Pro and you are welcome to continue to post questions to this forum as you encounter issues that you need help with.

              • 4. Re: Updating very very old Filemaker database

                Thank you everyone for the quick and informative replies!

                All indications are that I should scrap the old database and simply build a better one from my newer tools.  This is what I suspected, but I really didn't have a handle on what was wrong in the first place, so was doomed to spend all that time and effort with the same problem.

                I do have FMP10 Advanced, which I've used to build kiosk interactives in the museum before, so I'm not totally lost.  It is the importing the old data and trying to make it work as before that was my mistake.  I will take everyone's recommendation to build a new and proper relational database, and import the old data to it.

                Again, thanks for the very helpful advice!  I'll certainly come back if (when) I have more challenges along the way.

                • 5. Re: Updating very very old Filemaker database

                  Once you have your new tables defined, there's no reason why you can't import your existing data into the new design though it may take multiple imports to get the data from multiple fields into the separate records of the new data.

                  • 6. Re: Updating very very old Filemaker database


                    That raises a question or two:

                    Presumably, I will build a separate table for Players, in which there will be a field for Player ID to act as a key.  If I set that to auto-generate numbers, will these populate properly upon importing the 2,000 or so records from the old database?  Also, the current database has 4 player fields--Winner1, Winner2, Loser1, and Loser2.  I imagine I will need to have a field on the Matches table that identifies the Winner(s) and Loser(s) of the match, so I see that I shouldn't separate players based on this criteria in the Player table.  All players will simply be players in that table, correct?  If so, will it be a challenge to consolidate the 4 different fields into a single field in the new Player Table when I import?  What should I be careful of, if anything?

                    • 7. Re: Updating very very old Filemaker database

                      To answer your last question first. You've got a challenge here, period and much of the detailed parts of the process can't really be answered through this forum as we can't fully see the structure of your old system nor fully visualize the optimum design for the new.

                      You'll likely need to set up The Matches table as a "join" table linking Tournaments with players. The win/lose outcomes for each match would be recored in Matches (and ultimately the winner of the Final Match record for a given category is the Tournament winner).

                      When you use Import Records to import data, you have an option that pops up that allows you to enable auto-enter actions during import. With This option selected, each imported player record will be automatically given a unique Player ID if the field is set to auto-enter a serial number. This is an "all or nothing" feature, so if you select this option, make sure that there are no other auto-enter options that you don't want enabled during the import. You can also use Replace Field Contents to load the Player ID field with a serial number (and update its next value setting) after importing the data.

                      Consolidating the data from the 4 different fields will likely require importing the data 4 times, specifying a different source field to map to the same target field.


                      • 8. Re: Updating very very old Filemaker database

                        I feel very thick-headed--rather stupid, in fact.

                        I've upgraded to FMP 11 Advanced, and I'm excited to start from scratch on this project, but I still don't seem to understand the idea of join tables--specifically, I  don't seem to understand the many-to-many restriction.  Does any kind  soul have the patience to try to clarify?

                        I have results from 4 major tournaments that happen each year over a period of years.  Each tournament has the same 5 final events I need to record the scores and winners/losers of.  Each event has either winner + loser or winner + winner + loser + loser, depending on whether a singles or doubles event.

                        The player table is evident, even to me.  Each player gets a PlayerID along with whatever unique information I want to add for the player, such as name, age, nationality, etc.

                        Where I get lost is in how to set up the other table(s).  Everyone here suggests a Tournament table and a join table of some sort for the matches.

                        Tournament A has happened, say, each year for 100 years.  Each of those years it has consisted of 5 final events, each with its winner(s) and loser(s) and the score.  The same can be said for Tournament B, C, and D.

                        No matter how I try to imagine these tables (and I've gone through lots of paper trying to diagram this), I always end up with that forbidden Many-to-many relationship somewhere.  That is, each year each of the 4 tournament will have 5 events, and each event will be in all four tournaments (many-to-many).  Or, each player can win many events, sometimes in the same tournament, and each event will have either 2 or 4 players (again many-to-many).

                        To be specific, let's say that Tournament A in 2007 has final events MS, MD, WS, WD, and XD.  Results for MS were Player1 won, Player2 lost, and score was 3-1.  Results for MD were Player1 and Player2 won, Player3 and Player4 lost, score 4-3.  WS had Player5 win, Player6 lose, 5-4. WD had Player5 and Player7 win, Player8 and Player9 lose, 3-2.  And the final event XD had Player1 and Player5 win, Player2 and Player6 lose, 6-4.

                        Tournament B in 2007 will have similar results, as will Tournaments C and D.  The next year the same, and so on.

                        At the very least, I want to set up this database so that a user can call up PlayerX's win record in all tournaments, or call up Tournament X to find all the winners in the 5 events over the years for that tournament.  The data I have consists of 8 winners and 8 losers for each of the 4 tournaments each year for over 100 years, but this is in a flat spreadsheet.

                        As I wrote above...I feel thick.  I'm sure I'm missing something glaringly obvious, but I just can't seem to grok the concept of relational tables.  How do I resolve the many-to-many of events to players?  Maybe I'm so far off the mark that I'm not even asking the right question. 

                        I can't seem to get my head around the problem.

                        • 9. Re: Updating very very old Filemaker database

                          "many to many" relationships aren't "forbidden" they just require an extra table between the two tables so you can link the records such as linking a specific player record to a specfic tournament record.

                          Here's a simple demo file of a many to many relationship with a join table:  http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

                          It matches contracts to companies so that many companies can be part of one contract and many contracts can list the same company. You could think of these tables as matching players to tournaments, with the join table recording the match results.

                          You might also look over this discussion: Understanding how to resolve many-to-many conflict. where we were discussing many to many relationships just today.