2 Replies Latest reply on Jul 10, 2009 1:06 AM by wigz

    Creating Radar Graph Data Series from a Table of Dates

    wigz

      Title

      Creating Radar Graph Data Series from a Table of Dates

      Post

      I have probably bitten off more than I can chew with my 1st FileMaker Database (have dabbled with MS Access before) but I was enjoying the challenge of learning on the fly using the 'Missing Manual' as my guide - until now - I just can't get my head round how to solve a problem of tracking student progress by year.

       

      My database tracks school children's' progress through school as they reach objective standards laid out in the National Curriculum. There are 21 subject areas and each area is subdivided into 5 main levels which are then further subdivided into between 1 and 7 'sub-levels/objectives (totalling 349 sub-levels across all 21 subjects).

       

      I have created a table 'Student Achievement' with the primary key 'StudentID' (one to one join with 'Student Details' table which contains their address, date of birth etc) and 349 date fields, one for each sub-level; when a child reaches the standard for each sub-level the teacher enters the date that the level is achieved into the database. 

       

      Using calculation and summary fields to add up the number of fields with dates in I have managed to create a Radar Graph (using Fusion Charts) with 21 data points (x-axis) representing each of the 21 subject areas and the y-axis values are simply a factored percentage of how many of the data fields for each subject contain dates. This gives me one data series summarising where the child is today and I can use it to look at one or many children sorted by Class, Age etc what it can't show is progression over time.

       

      I have already embedded a single series Radar Chart on the 'Student Details' layout which shows the 'today' picture for the active student record.  What I would like to be able to do is to break out data for each child which shows how they have progressed each school year by using additional data series' to show where a child was at the end of each school year - ie Series 1 = Reception year, Series 2 = Reception + Year 1, Series 3 = Series 2 + Year 2 etc.

       

      I tried to write a script that set a variable for $StartSchoolDate, to use as a reference when trying to identify which school year a particular sub-level was attained, and zeroised 7 variables for each subject, eg $ReadingYearReception=0, $ReadingYear1=0 etc; once variables had been defined I thought I would step through each field and, using 'Case', increment the appropriate 'Year' variable by evaluating in which school year the level was achieved (eg case: ..... Date >start of year 3 and < start of year 4 ; $ReadingYear3=$ReadingYear3+1 ; ....).  Once all reading levels had been evaluated I would write the resulting variable values into 7 fields corresponding to each of the 7 $ReadingYear variables before moving onto the next subject and starting again.  Once all 147 fields were populated the additional data series for the chart could be calculated using these fields ----- unfortunately I didn't get very far as my knowledge of scripting and is still at novice levels!!!  Am I wasting my time with this approach/is there a simple way of doing what I want or should I just stick to a single data series (it would definitely be less hassle but it seems like the easy way out and I am keen to learn more!!)?

       

      Any help/pointers greatly appreciated - Thank You in advance,

       

      Wigz

        • 1. Re: Creating Radar Graph Data Series from a Table of Dates
          philmodjunk
            

          Ever read about Alexander the Great's solution to untying the Gordian knot? (He cut it with his sword.)

           

          You've got a real gordian not here that needs (in my opinion) the same solution.

           

          "I have created a table 'Student Achievement' with the primary key 'StudentID' (one to one join with 'Student Details' table which contains their address, date of birth etc) and 349 date fields, one for each sub-level; when a child reaches the standard for each sub-level the teacher enters the date that the level is achieved into the database. "

          When you first start learning how to design a database it easy and comfortable to simply keep adding fields to a given table when in reality, you'll be able to create a database that's much simpler and easier to use if you add more tables and link them with relationships.

           

          Create a new table where each "sub-level" is a separate record that uses StudentID as its primary key and use it to link to the Student Details table. With this simpler structure, you will find it much easier to manage your data and chart it.

          • 2. Re: Creating Radar Graph Data Series from a Table of Dates
            wigz
              

            Thanks Phil', when it took me nearly 30 mins just to compose my post I had a feeling that I was tying myself in knots!!

             

            The main reason I went for the 'mega table' is because I had tried something similar to your suggestion, albeit using MSAccess, and struggled to collate and summarise the 349 records per student in a simple layout. What I have at the moment works well for simple analysis but clearly I need to go back to the drawing board.

             

            No doubt I will be back on the forum when I get bogged down in Version 2.