7 Replies Latest reply on Jan 30, 2011 5:53 PM by Frinholp

    Find records then create new data entry fields

    tomo17

      Title

      Find records then create new data entry fields

      Post

      i am using filemaker pro 11 and have had some limited success creating some simple databases.

      I am creating a database to record data in a school environment.  At the moment i have been able to create three tables -  a student information table that has name surname, year group and class number; a year/class table; and a table that shows the sports taught in specific years.

      I have been able to generate finds so that i can get students in a particular class or year.

      I am stuck on the next step where i want to search for a class and then add the assessment fields for a chosen sport ie yr 8 class 1 sport volleyball

      For each of the sports there is a number of skills that are given a numerical score ie for volleyball -  a set, a dig, a spike etc

      Once entered this information then feeds back into a summary page that generates some grades for reporting.

      My questions are:

      - Do i need to create a table for each sport with the skill areas that are being assessed or can i put it into one table?

      - will i need a layout for each sport?

      - how do i create a script so that finds the relevant students in a class then goes to an entry page for the sport?

      I hope this makes sense.  Any help greatly appreciated.

      Regards 

      Tomo

        • 1. Re: Find records then create new data entry fields
          philmodjunk

          - Do i need to create a table for each sport with the skill areas that are being assessed or can i put it into one table?

          Either approach is possible and which is best can't be answered from here. It depends on what else you need to do with your data. If you keep it all in one table, it's easier to set up a report that lists performance for mulitple sports on the same report, but can take more creativity in how you define and use the fields to record data from your different sports.

          - will i need a layout for each sport?

          You don't have to, but may find it useful to do so. If you use separate tables, you'll have to use separate layouts. If you use a combined table, you can use a single layout or specialized layouts.

          - how do i create a script so that finds the relevant students in a class then goes to an entry page for the sport?

          How you define "relevant"? There are two basic ways to pull up a found set of records in a script: Perform a Find or use Go To Related Records. Performing a find is easiest to do if you are new to scripting and relational databases. Such scripts can enter data passed to it in a script parameter and/or use data the user has entered into a field (usually a global field) to create and enter criteria for the search.

          • 2. Re: Find records then create new data entry fields
            FentonJones

            First, I have to say that you will need more tables. And it is not going to be all that easy to do. I don't really know how many sports nor how many statistics, so it's hard to say.

            There is a type of table I call a "reference" table. It has fairly static information. In the case of Sports, there would be a name field; it would have "Volleyball" in one record, "Baseball" in another record, etc.. It would have a unique primary key for each record (as most single "entity" tables will). So your question:

            "- Do i need to create a table for each sport with the skill areas that are being assessed or can i put it into one table?"

            All Sports would go in one table. 

            You also have a Students table. It is a "data entry" table (in most ways). In order to say what Sports a Student is involved in, you would need another table, a StudentSports "join" table. It would have (at the least) the StudentID, the SportID, and the date (period when this combo occurs).

            - will i need a layout for each sport?

            This is the tricky stuff, the statistics. Each sport would have its own kind of statistics. You might want to put them all into a separate SportStatistics (reference) table. Or just as fields in the Sports table, named so as to "group" them. Examples: v_Set, v_Dig, v_Spike. In either case, these are just references, so you know what the heck statistic types goes with each sport. There wouldn't really be any data entry into these statistics; unless you wanted reports on them. In fact, you don't really "need" these reference tables (unless the abstaction idea below works). 

            [Personally, I might (try to) make this more abstract. That is, have a SportsStatistics table which had 2 fields: SportID, and Name of the statistic, in each record, "Dig", "Spike", etc.. This could be used to create sets of records for a StudentSportStatistics, another data entry table. But that may require too much consistency, and not work, due to problems with different types of data entry fields, Text vs Number, etc.. Maybe work, maybe not.]

            You would certainly need another data entry table for StudentSportStatistics in any case, for the sport statistics for a student. If the abstract idea above did not work, then it would need a field for every possible statistic for every sport. (Hence I'd try the abstract.)

            So, the layout is going to have to show different data entry fields for each sport (unless the abstract worked, and likely even then). This could be done with either separate layouts, or possibly separate Tabs (of a Tab Object). You would need to use a Script Trigger to automatically switch to the correct tab with statistics for the sport you're entering data for. This could be done with an OnRecordLoad script trigger. It would then "Go to Object", to the correct named tab.

            • 3. Re: Find records then create new data entry fields
              tomo17

              Thanks to you both for your suggestions.  I will try and get my head around them and let you know how i go.  I am sure that there will be some more questions after jump these hurdles!!

              • 4. Re: Find records then create new data entry fields
                tomo17

                ok - i have given this a bit of a shot but am hitting a brick wall.  i have placed the sports separately with all the skills required for each.  i have included a student code that links the table to the student information table.  when i try to enter information for the skill it wont allow me to select the field so that i can enter.  i am only able to change the students information data.

                Fenton - i actually like the sound of your abstract idea but am unsure how i would be able to create the records for the student sport statistics table.  how would the relationships work?

                At the moment i am working with a small sample of the whole so that i can see which works best so i am happy to try a couple of different versions at this stage before i commit one way or the other.

                Thanks again - any more help will be gratefully accepted.

                • 5. Re: Find records then create new data entry fields
                  philmodjunk

                  Are you using a portal to record the skill data for a given student?

                  • 6. Re: Find records then create new data entry fields
                    tomo17

                    After having to leave it for a week or so i have had a go at a few of the suggestions.

                    I have got to the point where i have student information page that allows me to select a sport to record results through a goto layout option.  This takes me to the page for each sport with the relevant criteria to be assessed.  When i do this it does not "drag" the relevant student information across - i just get a screen with empty student info fields which i cant select and editable sports criteria.

                    I have the student info and sports data tables linked via student code.

                    I am hoping the workflow to be as follows - teacher chooses year and class -- students in class show up -- teacher chooses sport to be recorded --layout with students info AND sports criteria options opens.

                    At this stage i am not doing it as a portal - what advantage would this provide, Phil?

                    Once again - many thanks

                    • 7. Re: Find records then create new data entry fields
                      Frinholp

                      I have the student info and sports data tables linked via student code.

                      Does this mean you are not using an auto-enter serial as a primary key? If not, you are breaking a major rule in database construction. Best to leave FM deal with your primary key so that it guaranteed to be unique. You can still add an extra field that holds a student code.

                      At this stage i am not doing it as a portal - what advantage would this provide

                      A portal allows you to view multiple records from a related table. Say you have created a table for classes, on a class layout you would be able to view all students in that class from within the portal. You would also be able to edit student data from the class layout or similarly edit the sport data.

                      I am unsure how your assessment of students work. Do all students study all sports? I assume each sport has a different set of skills that are related to that specific sport?

                      Whether or not all students study all sports would determine the way I would structure my database.

                      Lee