Creating Radar Graph Data Series from a Table of Dates
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,