I want to calculate a total number for each value for each record.
And what record is that? We'd need to know how you've structured your system, what tables, what relationships, before we can suggest a method that would work for you.
A difference in table structure and/or relationships could explain why the posts you've read don't seem to work for you.
If you are looking for a report that looks like this:
It can be done quite easily with a summary report--provided that you have a table structure that will support it.
Thank you for posting Phil. I'm reading through some of your other posts and I think I may find some answers there Creating a student attendance register. I have 425 students in 10 classes, so it is rather complicated setting this up. I'm going to change my tables and relationships to the ones you suggested in that post. I'm missing two of the tables you mentioned that will help simplify things and also help me wrap my head around related tables. Here are some brief answers to your questions. I will update my posting after I have read your other postings on this topic so you won't have to explain anything twice.
And what record is that?
I originally intended to have a record for each student in the attendance table. The records have a text field for each class with a pull down menu with values of Present, Absent and Late and calculation fields for the total of each in each record, like I would do in a spreadsheet.
After thinking about it and trying to get my head out of spreadsheet mode and into relational database mode, perhaps the solutions mentioned in other posts would be appropriate if the attendance table had a record for each class instead of one for each student and a field for each student related to the students table.
We'd need to know how you've structured your system, what tables, what relationships, before we can suggest a method that would work for you.
Currently I have three tables: a table for students, with each student's, number, picture, contact info etc.; a table for grades, with a record for each student (perhaps this would be similarly better served with a record for each project/test with fields for each student); and the attendance table.
Any advice on how to structure the tables and related fields would be greatly appreciated.
I'm still getting my head around related tables and FMP in general. I appreciate your patience. Thank you.
<<Edited this post to turn link text into hyperlink--PhilModJunk>>
I think the advice in the link you found provides a good table structure for what you describe here. Feel free to respond to this link with any follow on questions you might have about how to set this up. The key here is that it's much easier to count records than it is to count values from a set of fields defined with in a single record.
I'm stuck on writing the script to create records in the Attendance table with dates for each student. I have a lot of dates and a lot of students, I want to write a script so I can copy each student record numerous times adding each date of each class to each student. I also need a script to add a records to Attendance for each student with each additional class, for dates not yet defined. I'm trying to figure it out, but I'm stuck.
Structurally You would create a set of nested loops
The first (outside loop) would be based on your student table
You would go to the first record and loop through each student
you would set variables for the Student ID
Inside that loop you would start another loop that would loop through a set of dates (you could do this from a table of dates such as school days)
You would set a variable to hold the date
Then you would move to the attendance table and create a new record
set the student id and date fields
Return to the inside loop and go to the next in loop
once you had completed that loop you go to the next in the outer loop which would repeat the process for the next student.
Now loops in and of themselves can be tricky to control
Usually in this method you need to set a counter variable and advance it once during each loop then compare the counter to the found count
If the counter becomes greater than the found count then you exit the loop.
I dont have a handy link to a loop tutorial but Phil or someone else might.
It wouldnt matter which table is the inner loop and which is the outer loop as both will create the same number of records
To add classes you might want to work from the date through the student to the student classes table
You can nest a number of these loops you just need to keep track of each loops exit independtly and realize that it gets exponentially longer the number of inner loops you add.
You can also use a begin and end dates entered at the time you run the script throgh an input box. For your date looping
And add if logic around the inner loops to skip certain dates. There are a number of ways to write this logic
Something like if your input begin date was a monday
Set Variable (Sloopdate;Value = begin date from input box) [not sure of the syntax for this yet]
Set Variable (Sweekcounter; Value= 1)
If ($weekcounter; <= 5 )
[ Loop Stodent processing loop ]
Set Variable ($weekcountr; Value = $weekcounter +1)
If ($weekcounter; >5)
Set Variable ($loopdate;$loopdate + 3)
Set Variable (weekcounter; Value = 1)
Set Variable ($loopdate;$loopdate + 1)
Set Variable ($weekcounter; Value = $weekcounter + 1)
Exit Loop If ($loopdate > $enddate)
Dont take the syntax as gospel at the moment I dont have FM handy this is just logical coding
SOmeone else might have a better more syntax perfect example.
Thank you very much. I'm working on it and hopefully unstuck.