5 Replies Latest reply on Apr 4, 2013 11:46 AM by Mike_Mitchell

    How to use data from separate record (Sports Team)

    rfaintich2013

      Sorry,

      I'm sure this is a very easy one to answer.... But I'm only 3 days into using Filemaker, so I apologize for the newbie status....

       

      Short summary—I am building a Stats Database for a H.S. Wrestling Team. I have created a record that serves as a "Match Stat Entry Form" which, when completed, enters in a match result, with many, many fields, all as one table..... So, in short, every match wrestled by each individual is stored into this table, each match a separate record.

       

      I want to know how, once I've created a separate table/layout for an individual player page, I can search through those matches/records and only pull information from rows/records/matches that involve that specific Wrestler.

       

      To be specific, but basic, let's say that each match has a field that is "Wrestler's Name" and also has separate fields for "Takedowns Scored" and "Escapes Scored". I want to be able to create new fields in the new Player page that pulls info, and SUMS all match stats in which the individual participated....

       

      Can someone please point me in the right direction, because I spent all day reading manuals, and online forums, and I haven't figured out the solution,

       

      Thanks so much for your help!!!

        • 1. Re: How to use data from separate record (Sports Team)
          Mike_Mitchell

          Okay, for starters, never apologize for being a newbie. We were all newbies once, and we are very newbie-friendly here.      

           

          What you're asking is a question of what's known as "data modeling". It's not really a question of FileMaker per se, but a question of how you organize the data in your solution. Basically, you'll need to grab hold of the concepts of "data entity", "table", "data attribute", "field", "relationship", and "key field".

           

          (Ouch, right?)         :)

           

          I'll give you the basics. A "data entity" is a "thing" you want to track information about. For example, "team", "wrestler", "match", "player", "school" ... these all might be entities, depending on exactly what you want to track about each one.

           

          A "table" is the database construct in which a data entity lives. Hence, you normally name a table after the thing it's tracking.

           

          A "data attribute" is a bit of information you're recording that describes a data entity. For example, "first name", "last name", "match start time" - these might be data attributes. (Note that the trick with data modeling often comes down to figuring out which "things" are entities and which are attributes.)

           

          A "field" is the database construct in which a data attribute lives. It's attached to a table.

           

          Okay, so what's a "relationship"? A "relationship" ties two tables together. It tells the database which records in one table match (are "related to") which records in another table. This is where the rubber hits the road in a relational system. For example, if I'm tracking information about Wrestlers, and information about Matches, I would have a relationship that ties the two entities - tables - together so the database knows which Wrestlers attended or participated in which Matches.

           

          How do I do that? By use of a "key field". This is a unique field that identifies the records in each table and ties them together.

           

          With all that background, I want to shift your perspective just a bit. You asked about how to create new fields in the new Player page that sums up match stats for a given individual. What you probably want to do instead is go back and look at your data structure instead. You'll want to split up your tables into appropriate entities, tie them together with relationships so they're properly normalized (which is database speak for "store the right stuff in the right way"), and then you'll be able to add the appropriate summary or calculation fields that will make sense.

           

          I've given you a basic (very!) primer in database modeling. My recommendation would be to pick up a copy of the FileMaker Training Series (you can download a copy here free if you're a TechNet member) and go through Module 3 carefully. (Of course the rest of the module is great, too!)

           

          HTH

           

          Mike

          • 2. Re: How to use data from separate record (Sports Team)
            mikebeargie

            There are some issues with what you're describing and standard relational database practices.

             

            If I was setting this up, I would create the following tables:

            -Wrestlers - holds all the wrestler's personal data, IE name.

            -Matches - think of it as a wrapper to the statistics - has two keys that relate to first and second wrestler.

            -Statistics - holds your takedown and escapes scored, related to matches based on one key, and wrestler based on one key.

             

            Think of your relationships between these tables in sentences:

            -A wrestler can have many matches

            -A Match will always have two wrestlers

            -A Match will always have two statistic records

            -A wrestler can have many statistics

            -Statistics can only belong to one wrestler

             

            From here you can set up your relationships based on serial keys, rather than relating on name as you were proposing above (what happens when you have two same-named wrestlers in that situation?)

             

            After the relationships are squared away, the layouts would be made as such:

            -Wrester Details layout, with related portal showing statistics. Calculations in the wrestlers table can sum totals of the related statistics record. Also, a portal showing the list of matches the wrestler has taken part of.

            -Match Details layout, with methods to assign wrestler 1 and wrestler 2 to a match, and a trigger that created a related statistic record after wrestler 1 and 2 are assigned. Since Match is a 1-to-1 relation to statistics (two statistics records based on wrestler 1 ID and wrestler 2 ID) you can just display the related statistic data on the match layout.

             

            Later on, you could even add a table above wrestlers for "teams", and group wrestler records by team (and their related stats as well). And keep going on and on (IE district, state, country).

             

            I'd recommend you get a solid hold on using primary and foreign ID keys to relate tables together. It's the base of which a lot of filemaker work is accomplished on.

            • 3. Re: How to use data from separate record (Sports Team)
              taylorsharpe

              To be honest with you, it sounds like you've taken the spreadsheet concept to FileMaker and have one big table with no relationships.  To be able to break things down, you need to dive into the database relationship model.  You might have something like a table with names and individual info of the wrestlers, then a table for each Match and a table that identifies which players were in each match and the a match results.  These tables would all be related to each other through unique ID's, usually called "keys".  Once you have everything in their appropriate table, it makes it easy to summarize results of matches or players or whatever.  As a big "spreadsheet", it will be difficult to make results the way you are talking.  You might post an example with some fake names and matches and I'm sure we can give you some suggestions. 

              • 4. Re: How to use data from separate record (Sports Team)
                rfaintich2013

                I just wanted to thank everyone who took the time to help me out—It is really, truly appreciated!  Late last night, my spreadsheet-focused brain finally clicked, and I got the concept of linking tables through a matching field, I guess what you are referring to as a "match key," and I have been able to put corresponding statistics into portals.  Thank you so much for your guidance and direction—Mike, I'll definitely be checking out your tutorials as I advance my understanding with Filemaker!  The functions and associated elements are something I'm experienced with, or at least, know how to search for a proper function via the guide or internet, I just wasn't sure how to relate databases.  You are all correct in that I am making separate tables/databases for Matches, Players, Team pages, and now that I know the basic element of relating them, a huge roadblock has been lifted....  Now I just need to figure out how to make fields only visible/active on tabs....  (the FM Manual is not very helpful when it comes to creating tabs.... i.e. how to use tabs on a player page to switch between player bio, detailed stats, video, etc....)  The other thing I don't understand is why there is no way to create a script that creates a new field.  It seems like you have to already have the field created—for example, if I have a button that says "add video(s) to page", I have to already have the container field made, (or create several rows of empty container fields, each with their own respective button....  (I'm too early into the process to figure out how to have the button just select the next open/unused field....  But I love the program so far, you are all correct when you stated that my headspace is still glued to a spreadsheet concept....  Again, thanks so much for all of your answers, it has really helped my understanding of FM!

                 

                All the best,

                 

                Ryan

                • 5. Re: How to use data from separate record (Sports Team)
                  Mike_Mitchell

                  Ryan -

                   

                  You're welcome.

                   

                  If you want to have multiple, for example, "videos per page", then you would build a related table and make each video occupy a record. That way, you have a single container field per record, but each parent record can have as many videos as you like.

                   

                  Think relational!   

                   

                  Mike