Portal Row - Look at row 10

[TaiChi56]

I have a portal that has three fields.

Date

Class Period

Time Difference

I am trying to write a script that will look at the 10th row, this being the 10th tardy the student recieved and have it email me that this student has received his/her 10th tardy. Of course this has to work for 2500 students. I looked at go to portal row but not sure what to do from here.

[bruceR]

You do not have to go the row. You just need to use the Count function.

Script roughly like this:

Go to record [first]

loop

If count( Tardies::studentID) > 9

send mail

End if

Go to record [ next; exit after last]

End Loop

[TaiChi56]

Ok,the script works. But I have to send individual emails. How can I get it to put all the studentID on one email and then send the email?

Edited July 30, 2010 by Guest

[bcooney]

I would imagine that Bruce's proposed script is looping thru the Student table. So, when you Send Mail, you are in the context of Student, and can get their email address(es). Where do you have their email address(es)? In the Student table or in a table related to Students?

Also, I would add some mechanism that records that you've sent this email. From a simple Date_TardyEmailSent field, to a more sophisticated Activities table.

[TaiChi56]

The email being sent is to one person, my data clerk. What I need is one email that takes each student's studentID from the table showing that, that student has reached 10 tardies or more. So when I run the script I want one email sent not 20 of them showing each student individually. All the students would be listed on the one email.

[comment]

I am not sure you are taking into account the timing issue here. Adding a tardy record to a student is an action, and so is sending mail. Having 10 or more tardies is a state.

Since (as I recall) you are creating the tardies by a script, it should be easy to check how many previous tardies the student has and send mail if they passed the limit.

But if you want an overall report, you should think about when such report should be produced, and what (or who) should make it happen. Keep in mind that a student can have 10 tardies, with the last one received 3 months ago - should they be included in such report?

[TaiChi56]

Yes, we want to see the student's tardies. We want to see everyone of them and we want the student to see them everytime they get a tardy slip. Even if the prior tardies are two months old. Every time a student gets more than 9 tardies we want the email. When that same student gets 11 tardies we want an email, when that student gets 12 tardies and so on we want that email. Two major problems I have now are:

1. How do I have the script run through each record check for 10 or more tardies, put all the student's ID's on one email and email it to my clerk.

2. When we print a tardy slip we want each prior tardy to show. No problem unless the student starts getting 15 or more. Then it will not show on the tardy slip because it is in a portal with a scroll bar. How do I get past that.

Please advise. If this is something that will take awhile, please provide a quote. Thank you.

[comment]

I am afraid we may not be talking about the same thing. You say:

Every time a student gets more than 9 tardies we want the email.

When a student gets their 10th tardy, that's ONE student only. At that time there may be other students that already have 10 or more tardies. So that when you say:

have the script run through each record check for 10 or more tardies, put all the student's ID's on one email and email it to my clerk

it would mean that when Adam gets his 10th tardy, your clerk gets a report with the ID's of both Adam and Betty - even though Betty didn't get a tardy recently.

Re point 2: it's best to print directly from the Tardies table.

[TaiChi56]

I see what you are saying with the first point. I have to think how I am going to work with that. The second point I understand also but we really would like the student to know how many tardies they have. So is there a way we can just add how many? For instance.

Adam is tardy during 1st period. Adam gets a printout to take back to class that has the tardy for that period. But also on that form would be something like: Adam this is your 5th tardy this year. That way I don't have to list each tardy I just let him know that he now has five tardies and if he wants to look then I could print out a report showing the exact dates and the periods.How does that sound?

[comment]

on that form would be something like: Adam this is your 5th tardy this year.

You can add a calculation field in the Students table =

Count ( Tardies::StudentID )

and put this field on the tardies form layout.

However, the "this year" requirement may call for another relationship between the two tables, filtered by year - so that only this year's tardies are counted.