Count items in a seperate table

[Bruce Herbach]

I have a scripting problem that I haven't been able to work out. This is in Version 9 Pro ( Not Advanced). Here is the setup.

I have two tables. This first is a list of students and the second is a list of dates when the students are absent. The student table has a great deal of other information as well.

Then absence table has two fields, Student name(the Primary key) and the date of the absence.

I need to produce a report that shows the number of times each student has been absent in the last 7 days.

I have been able to use count(absence::student) to show all of the absences in the file but this doesn't limit it to the last 7 days.

I have also been able to find the first instance of an absence in table with

Set Field[student::Absence_cnt; if(Absence::Date > get(currentdate)-7;Student::Absence_cnt + 1;0)]

So far I have not been able to come up with a way to loop or another script that would step through all of the entries in the Absence table for a Student and increment the count for each instance that meets the date criteria.

Any help with this would be greatly appreciated.

Sculler

[CobaltSky]

Hello Sculler,

First, I have to say that using the student name as the primary key is not a great idea. If a name changes (or a spelling error is corrected etc), the related records will all be orphaned. Also, student names aren't guaranteed to be unique. A serial number or StudentID number is a better idea.

In relation to your specific question, I suggest you create an unstored calculation field called cRefDate with its result type set as date and enter the formula:

Get(CurrentDate) - 7

Then, on the Relationship Graph, add a new TO of your absences table called Last7Days_Absences. Then create a relationship between the students TO and the Last7Days_Absences defined as:

Student = Student

AND cRefDate :less: DateAbsent

(you'll have to double-click the box that bisects the relationship line to bring up the Edit Relationship dialog so you can change the operator for the second line of the relationship definition to a less-than-or-equals symbol).

Once the additional relationship is in place, you'll be able to set up another calc field with the formula:

Count(Last7Days_Absences::student)

...which will show only the absences in the preceding seven days, based on the current system date on your computer (that is, assuming future absences are not recorded). :wink2:

[Bruce Herbach]

Ray,

I wanted to thank you for your assistance on this. I agree about the ID number versus student name and that is an update to the system that will go in shortly. The school has to supply the numbers..

I ended up tweaking this slightly but was able to get it to work by adding two fields to the absence table, date -7 and Last seven days. Then a count in the Student record put it together. This is much more elegant then the scripting I was trying to do.

Again, thanks for putting me on the right track.

regards

Sculler