Looping through records and summing the fields

[luiscovar]

Hello all,

 

I have a student database that has the total of intervention hours for each student. I am trying to do a calculation which is basically sum of the total hours of all their students divided by the total amount of students a teacher has times 24. Each teacher has a certain amount of students so I what i am doing is doing a find mode with the teachers name on the students side table and multiplying whatever I find times 24. Then this is how I am unsure on how to loop through the records and keep adding each field in the records until it hits the last records. 

 

So basically I want to sum the hours of each student that the is assigned to a certain teacher. If a teacher Ms. Zoe has 12 students then her found count would be 12, 12 * 24 = 288. If each of those 12 students had 2 hours (keep it simple) then the sum would be 24. Now after I would divide the sum of the hours, 24, by the total students 24, 288. How do I keep adding each hour field when I loop through the found records?

 

This is what I have so far.

 

  

[OlgerDiekstra]

Unless I misread, I would say you're taking the wrong approach. You're trying to create a teacher report showing the amount of hours assigned. Not sure where the 24 comes in, but that doesn't really matter.

What you need is a summary report. 

I'm assuming you have a teacher table and student table, and that each student can only ever have one teacher. The student table would then have a field linking it to the teacher table (ie id_Teacher). You can then create a summary field (summaryCountofStudents) in the student table that counts all the students. Simply count all the id fields in the student table for instance will do the trick. (I'm assuming here also that you use field naming convention as described here: http://filemakerstandards.org/display/cs/Field+naming)

 

Now create a layout based on the teacher table with a subsummary part when sorted by teacher name (header and footer are optional), and no body part. Place the teacher name field somewhere on the subsummary part, and also place the summaryCountofStudents field on there.

You should now have an overview of teacher names, with student counts.

You can then create a calc field in the teacher table that takes the summaryCountofStudents result, multiplies it by 24 and show that on the layout as well.

 

Similarly, you can add a field to the student table with the amount of hours allocated to each student, then total that in a summary field and show that again on the layout for each teacher.

[comment]

I am trying to do a calculation which is basically sum of the total hours of all their students divided by the total amount of students a teacher has times 24.

 

Assuming you want the result to appear in a field of the Teachers table, all you need to do is define a calculation field along the lines of =

Sum ( Students::Hours ) / ( Count ( Students::TeacherID ) * 24 )

This will aggregate the data across the Teacher-Student relationship: no finding or looping is necessary.

 

 

---

Note:

 

what i am doing is doing a find mode with the teachers name on the students side table

 

You should base the relationship on a meaningless TeacherID, not on a name that can change.

[luiscovar]

 

Assuming you want the result to appear in a field of the Teachers table, all you need to do is define a calculation field along the lines of =

Sum ( Students::Hours ) / ( Count ( Students::TeacherID ) * 24 )

This will aggregate the data across the Teacher-Student relationship: no finding or looping is necessary.

 

 

---

Note:

 

 

You should base the relationship on a meaningless TeacherID, not on a name that can change.

Would that sum calculation sum every related student that teacher has or only the record that is being browsed? 

 

Edit: Thanks that worked! Let say if the student had zero hours done then he is basically a no show. How would I remove that from the count of the amount of students they had? So if in their roster they had 5 students it would first remove the count by 5 then multiply that by 24.

[comment]

Would that sum calculation sum every related student that teacher has or only the record that is being browsed? 

 

A relationship ignores any found set you might have - so this calculation will always take into account all related students the current teacher has.

 

 

Let say if the student had zero hours done then he is basically a no show. How would I remove that from the count of the amount of students they had?

 

Would there be an actual "0" entered into the Hours field, or would it be empty? If it's empty, then the Count() function will not count it - so you could just change it to =

Sum ( Students::Hours ) / ( Count ( Students::Hours ) * 24 )

[Wim Decorte]

In situations where you do want to loop through a found set and do a sum, keep in mind that you can use the GetNthRecord() function to avoid physically having to visit each record.  

[LaRetta]

This was intriguing, Wim, so I wanted to run a speed comparison.  In summarizing 1,000,000 records, the loop took 33 seconds.  But GetNth was still running after 5 minutes and had only processed 120,000 records when I finally stopped it.

Would using GetNth depend upon other factors, making it a valuable consideration?  Would you like to see my speed test?  Or am I misunderstanding your suggestion?  :-)

[Wim Decorte]

No, I wasn't implying that GetNthRecord() would be faster than a summary field.  But sometimes adding relationships and fields is not an option.

 

But GetNthRecord() should be faster than an actual loop through the records since you are not changing context and triggering whatever FM does in the background when you change records.

[LaRetta]

Nope, I wasn't saying GetNth was faster than summary field either - I was only comparing GetNth to the loop.

 

That is what made sense to me as well ... without having to visit each record, GetNth might be faster but that is not what my file shows.  Admittedly I just woke up and the speed tests were roughly created but still - the difference was significant! 

 

I had to delete the records so it was small enough to attach so to run the rest, run Creator first.  Do I have something wrong in my thinking or my tests?

GetNthversusLoop.fmp12.zip

[luiscovar]

 

A relationship ignores any found set you might have - so this calculation will always take into account all related students the current teacher has.

 

 

 

Would there be an actual "0" entered into the Hours field, or would it be empty? If it's empty, then the Count() function will not count it - so you could just change it to =

Sum ( Students::Hours ) / ( Count ( Students::Hours ) * 24 )

 

There is an actual zero on the field. What I am thinking is making a calculation field that if the hours is zero then leave it blank and then counting that field.

[Mark Scott]

LaRetta and Wim,

 

Thanks for the demo file, LaRetta, which I admit I have yet to take a close look at.  That said, I wonder if some of the observations Todd Geist (and Sam and Jesse Barnum) made in conjunction with his HyperList technique are relevant here.  In that file, speed tests showed that techniques using either simple looping or GetNthRecord (implemented through a Custom List custom function) began to choke (about equally badly) as record counts increased.  The explanation seems to relate to the way FileMaker copies, rather than modifies, string variables with each iteration/recursion.  (Mind you, in HyperList, the iterations/recursions are building a list that increases in size (obviously) with each iteration/recursion; analogous techniques that simply change a sum with each pass may not incur the same exponential penalty.  For that reason, their observations may not be relevant here at all.)  That said, the insight that led to HyperList was to perform operations on subsets (chunks) and then "chunk the chunks" (using a combination of looping and GetNthRecord).  The speed tests and graph included in the HyperList file are pretty eye opening.

 

Again, might not be relevant for simple sums or counts, in which case a thread detour has led to my even-more-off-topic detour.  Testing is in order, which I'm sadly forced to delay at this moment.

 

@Luis - If you're wondering how this discussion impacts your choices here, it probably doesn't.  Unless I've (we've) badly misread your question, it seems that comment has provided the right answer (as usual  ;-).  Use that!

 

hth,

 

Mark

[LaRetta]

Hi Mark,

 

I agree that Comment's solution is the best.  I was simply struck by Wim's suggestion.  I have had bad/similar experiences using GetNthRecord() where it was too slow (in other situations) but the logic suggests it would be faster here (as Wim suggested) compared to a loop which was why I ran the test.  I used 1,000,000 records because - in many tests not just looping - FM can slow as the record-set increases so I wanted a large number for testing.  

 

Thanks for pointing out the link (I am very familiar with it but others may not be) and for your input - it is always welcome. :-)

[Wim Decorte]

Thanks, Mark and L.

 

As was shown in the original HyperList demo and as Mark points out, many functions behave differently in different found sets, not everything is linear.  For that reason I'm reluctant to say that one approach is always better than another.

 

One potential benefit of looping/GetNthRecord is that you can collect many things at the time, which may tip the scales vs. many summary fields on a layout.

 

With a solution that has many records I think I would choose an approach that does nightly pre-aggregates to avoid the user feeling the pain with reports on that data.

[David Nelson]

when I try to download the attachment on post 9 it says it does not exist but it shows it exists and two people have downloaded it.  Was it removed?

[LaRetta]

It wasn't removed.  I have reported the issue.  :-)

[comment]

There is an actual zero on the field. What I am thinking is making a calculation field that if the hours is zero then leave it blank and then counting that field.

 

I don't think that's necessary. Try instead modifying the calculation above to (untested) =

Let ( [
students = Count ( Students::TeacherID ) ;
zeros = ValueCount ( FilterValues ( List ( Students::Hours ) ; "0" ) )
] ;
Sum ( Students::Hours ) / ( ( students - zeros ) * 24 )
)

[Mark Scott]

Very clean, comment, nicely handling either 0 or empty string in the Hours field!

[Charity]

 

I don't think that's necessary. Try instead modifying the calculation above to (untested) =

Let ( [
students = Count ( Students::TeacherID ) ;
zeros = ValueCount ( FilterValues ( List ( Students::Hours ) ; "0" ) )
] ;
Sum ( Students::Hours ) / ( ( students - zeros ) * 24 )
)

 

Ok so from reading I think there is extra set of parenthesis Comment, right?  

 

Sum ( Students::Hours ) / ( ( students - zeros ) * 24 )

 

this value Sum ( Students::Hours ) 

divided by

this value students - zeros

times 24

 

was that a typo because you do not normally make mistake like this but I have to ask because I thought I was just now understanding how these brackets worked and this throws me off again.  I am hoping I am right since it would ruin my perception of how it works otherwise, like one step forward and two steps back.  If right, can you explain why you have those extras there please?

I mean parenthesis not brackets and the the ones after the / and before (students - zeros.  Hard to explain.

You did it again in post #3.  I guess I will crawl back to first grade in calculations again.

[comment]

was that a typo because you do not normally make mistake like this

 

I said it was untested, but you tell me:

 

Parentheses change the default order of evaluation. WRT to post #3:

Sum ( Students::Hours ) / ( Count ( Students::TeacherID ) * 24 )

Let's replace the aggregate expressions with some values:

 

Sum ( Students::Hours ) = 480 ;

Count ( Students::TeacherID )  = 10 ;

 

to get:

480 / ( 10 * 24 )

Calculate the expression in parentheses first:

480 / 240

Result = 2.

 

Without those parentheses, it would have been:

480 / 10 * 24

and this would have been evaluated from left to right (since division and multiplication have the same precedence) as:

48 * 24

with the  result being = 1152.

 

 

WRT to post #36, you want to subtract the count of zeros from the total number of students before you multiply by 24, so that gets a pair of parentheses of its own; after that, it's the same as before.

[Charity]

Hello Comment,

 I had to look up WRT which means "with respect to."  This is a very clear explanation but still it took me two hours this morning to try it and understand.  I created many math tests to be sure I see how this happens I mean changing the order of precedence part.

 

I said it was untested, but you tell me:

 

What you have listed works right and I should have known but if I had not asked then I would never have known.  Thank you for being patience and giving me such a great example and thank you Luiscovar for letting me ask on your post.