Counting in Portal

[MikeKD]

Hi folks,

I've got a portal that's used to mark pupils as present / absent etc.

I'd like to count the number of "presents" so I can work out attendance rates.

I've had a go at a script that's triggered every time the attendance field is modified (attached). This updates a field in the same table so that the results can be compared with the summary count.

Does it seem like I'm going this right? It's not working at the moment; hopefully it's a trivial mistake I'm making with my script!

Cheers,

Mike

 

[bcooney]

MikeKD,

May I suggest a flag_present field in your join table that is equal to 1 if the student is present. Format as a checkbox with a value list that is simply contains the number 1. I usually call this value list "1," and use it for all my checkboxes/flag_fields that serve this purpose.

 

Now, in the parent record, which is what?, you can create a calc field = sum ( relationshiptojoin::flag_present). No scripting needed.

 

hth,

Barbara

[MikeKD]

Hi Barbara,

that sounds cunning - I'll give it a go!!

Cheers,

Mike

[MikeKD]

Hi again,

I've got quite a few attendance options, "Present" being one of them.

If I make the flag_present field a calc field, I can do a IF "present" then flag_present=1 else kind of script.

But, as my ability to script is pretty minimal I can't get that to work either!!

Cheers,

Mike

[MikeKD]

Yay; I've learnt how to do IF thingies!

Now to work out how to get the results to update a bit more speedily...

[eos]

Yay; I've learnt how to do IF thingies!

 

Don't hurt yourself! 

 

In the meantime, here's a more structured approach that calculates a list of attendance types plus count. It is a triggered script, seeing as you don't have Advanced; otherwise it could be a Custom Function.

 

Note the sort order of the relationship; List () works at the data level (i.e. relationship); to achieve a similar result for a differently sorted (and/or filtered) portal, you'd first have to collect the values from the portal (like in your own script). 

AttendanceSummary_eos.fp7.zip

[bcooney]

Have a flag field for each option or one field with a value list of attendance options (value list drawn from attendance options table ID Desc). I really am not seeing the need for triggers/scripts. What requirement am I missing?

[eos]

What requirement am I missing?

 

The attendance type count.

[MikeKD]

Hi both,

Yes, although at the moment I'm just looking at being able to work out attendance percentages, in the future it might be handy to spot other trends; lates, sick bay, excused etc.

At the moment I've used Barbara's idea of adding a flag field in a summary.  The flag field is a calculation field setting its contents to 1 if the attendance is "present". This works but doesn't update immediately; I have to move to another record and back to get it to update. (I tried refresh window as a triggered script on modify contents, but that didn't make any difference....

 

EOS, your solution updates immediately; hmph!

[bcooney]

What is a "field in a summary?" Do you mean that you created a calc field in the parent record that sum(flagfield)? If so, it will not recalc until the parent record is committed.

@eos - "I'd like to count the number of "presents" so I can work out attendance rates." I took that to mean that there's a checkbox in the portal a user would select if the student is present.

 

@mike - don't use text values for your attendance field. Use another table and a value list derived from that table. Store IDs.

[eos]

Barbara –

 

@eos - "I'd like to count the number of "presents" so I can work out attendance rates." I took that to mean that there's a checkbox in the portal a user would select if the student is present.

 

I would have presented the same solution as you, but then spotted this sentence …

 

I've got quite a few attendance options, "Present" being one of them.

If I make the flag_present field a calc field, I can do a IF "present" then flag_present=1 else kind of script.

 

and thought to elaborate on it.

 

And neither of us saw Mike using two fields in his pseudo code where only one is necessary … 

 

Mike –

 

here's a modified sample file, with an added portal that has a checkbox just for 'present'.

 

btw, what's your table structure? Attendance would be an attribute of PupilPerSchooldayPerClass, not PupilInClass?!

 

Oliver

AttendanceSummary01_eos.fp7.zip

[MikeKD]

Hi folks,

thanks for this; sorry to confuse you both with unclear language. TBH, I'm not sure how I'd use a separate table of attendance catagories, but will mull it over.

 

Here's the structure.

 

While I'm thinking about structure, the next thing on my ToDo list is to sort out the MaxScore, which is set in Assessments and is transferred to Pupil_Class_Asses_Join by lookup.  I'm pretty sure I'd be better doing this by relationship, as alterations to the grade aren't always updated. Do I just do that with another relationship in two already existing TOs?

 

Cheers,

Mike

[bcooney]

Does a student in a class have multiple assessments for THAT class? Can an assessment be related to many pupilClass records? This RG shot does not show us enough info to further discuss the structure that you have for attendance.

 

Do you have a screen shot of your UI for attendance?

[MikeKD]

Hi there,

Yes, a student could have many assessments/attendances for each class (and could be in more than one class). Assessments and attendances are the same; a pupil could be given a grade every time they attend. Most assessments would also have an attendance, but some (long term coursework e.g. might not).

Hope that helps, I've attached a screenshot.  The database is full of pupil info, so I've pixelated out names.

 

Cheers,

Mike

[comment]

How about something simple? Define a calculation field in the parent table (Classes?) =

ValueCount ( FilterValues ( List ( Assessments::Status ) ; "Present" ) )

No scripts or calculation fields in the join table are required.

 

 

This is assuming that  the Status field can hold values other than "Present" or "Absent" - which seems strange to me, as I would think these two to be mutually exclusive.

[MikeKD]

Hi Comment,

Thanks for that. I've given it a try and it's nice and snappy, and doesn't need me to leave the record and come back for it to update.

However, it counts the "presents" in all assessments/attendences, not just the selected one.

I do have a global field to set the selected assessment, so I reckon I could use that to filter the results, but I'm not sure how!

Cheers,

Mike

[comment]

I do have a global field to set the selected assessment,

 

I think we need to define (or perhaps redefine) some terms here, because that makes no sense to me - and I have difficulty understanding your RG, too. I suspect that when you say "assessment" you actually mean an "assignment" - i.e. something that each student in a class will have (at most) one grade in?

[MikeKD]

I'm a music teacher in the UK.

For me, a pupil could have many different assessments, possibly one every week. They could also be in many different classes.

From what you're saying, to you an assignment is an end of year report to me.  I'm not intending to deal with those in this DB, but want to use attendance and grades (from assessments).

In the DB I'm treating assessments and attendances as the same thing, because they often coincide. It's like a small snapshot of data from a lesson; there will usually be attendance data (unless it's long term coursework) and often grade data. This data on attendence and grades will then be used to spot trends in achievement and attendence for pupils and classes.

I hope that helps!

Cheers,

Mike

[comment]

I am still struggling here. Let us work out a simple example: suppose I am taking a class in "Classical Composers" and this class is divided into three sub-topics (lessons? meetings? assignments?) : Bach, Beethoven and Mozart. This would mean that I can have at most 3 attendance records related to this class. IIUC, you want to count the number of students that were present in a selected sub-topic. e.g. Beethoven?

[MikeKD]

I'd have a class e.g of 12 yr olds. I'd teach that class all year. We would cover composing, listening, theory and performing. At the end of the year I'd typically have about 20-30 grades for each pupil and about 40 marks for present / absent etc.

 

The data collated will give me info on each individual pupil's attendance (and grade percentage), the class average, the attendance average for each teacher etc.

 

Is that any clearer? It sounds as is our education systems are quite different!

 

Cheers,

Mike

[comment]

I still don't understand what is an "assessment" in your vocabulary.  I am quite sure that even in the UK, an assessment involves the evaluation of a student's achievement in some task or field or ... ? This is the missing part here.

For example, "Adam did well in composing" would be an assessment record of (at least) three fields:

• PupilID (or EnrollmentID) representing Adam (or Adam's enrollment in a specific class);
• MysteryObjectID - representing Composing;
• Evaluation = "Well" (or a numerical representation thereof).

You say that assessments and attendances are the same thing, so I guess there would be another field for this purpose. In any case, Assessments/Attendances must be a join table between Pupils (or Enrollments) and the MysteryObjects table.


Now, when you say you have "a global field to set the selected assessment" - that makes no sense, because there will be only one selected assessment, so nothing to count. What you need to be selecting is the MysteryObject, so that you can filter the relationship to the join table by that selection.


 

It sounds as is our education systems are quite different!

I am not familiar with any education system, so that's not the obstacle here...

[MikeKD]

Hi again!

The assess_class_pupil_join is this individual record for each pupil for each assessment. It has UID + score, date, attendance & notes. It's a join table between pupils and assessments.

I've also got a join table between classes and assessments.

I've got another join table between pupils and classes.

The category (i.e, performing, composing etc) the assessment is in doesn't have a field, though I can see that could be potentially useful.

I do have a radio checkbox field for attendance/assessment so I'll have a way to filter out records that don't have assessments in them later.

 

Sorry that I'm so confusing; I've got the odd glimmers of understanding how FM works, but nowhere near the full picture, so I'm probably using misleading terms by accident because I don't really understand what I'm talking about!!

 

Have I answered your questions OK?

Cheers,

Mike

[comment]

LOL, if I finally understand what you're saying, your Assessments table doesn't contain any actual assessments (i.e. evaluations or appraisals) - these are stored as Scores in the join table with the tongue-twisting name.

If I were you, I would rename the join table to Scores, and the Assessments table to ... well, can you give us an example of an assessment? Perhaps Tasks or Meetings would suit? This may seem like a purely semantic issue, but I do recommend being fanatically accurate with your naming - it really helps when you need to revisit your structure.


Now, I see in your RG that you already have a filtered relationship between Classes and a TO of the join table, based on matching ClassID and gSelectedAssessment (although it's not clear to me why the matching field in the join table is called GradeID, rather than AssessID). So just change the calc formula to point to the filtered TO, i.e. =
 

ValueCount ( FilterValues ( List ( JPupilClassAsses_forSelectedAssessment::Attendance ) ; "Present" ) )

IIUC, your portal is also pointing to the same TO? If not - why not?

[MikeKD]

Many thanks, trying this now.

You're absolutely correct; Assessments contains the generic information, but no actual scores!

[MikeKD]

That works nearly all the time, though there are two records with no attendance marked in (blank attendance fields) where they're all counted as present.

The calculation is

ValueCount ( FilterValues ( List ( Pupil_Class_Assess_Join 3::Attendance ) ; "Present" ) )

 

I'm now attempting to use

ValueCount ( FilterValues ( List ( Pupil_Class_Assess_Join 3::Attendance ) ; ) )

To count the total number of records.

 

At the moment I've done a lookup to get the max score from the Assessments table to Pupil_Class_Assess_Join (where the scores go) so I can work out a percentage. Would I be better doing this with another calculation field too?

[MikeKD]

Spotted it, got the wrong TO (despite you pointing out what it should be!!)

Now using the same theory to get attendence / grade %s.

[comment]

I'm now attempting to use

ValueCount ( FilterValues ( List ( Pupil_Class_Assess_Join 3::Attendance ) ; ) )

To count the total number of records.

 

That's not going to work (invalid expression) - but a simple =

Count ( TheCorrectTO::NonEmptyField )

will. This will count all related records where the field is not empty, so pick a field that cannot be empty - such as SerialID or ClassID.

 

 

 

I've done a lookup to get the max score from the Assessments table

 

Why not use the field in Assessments directly? For example =

Score / Assessments::MaxSxore

will return the score as percentage of max score.

 

 

 

Also keep in mind that these techniques are suitable for live on-screen display of a few stats; before you end up adding a maze of relationships and fields dedicated purely to stats, examine the options offered by producing a summarized report of the join table.

[MikeKD]

Many thanks,

Good tips those!

It's easy for me to keep going down a path, not so easy to see I was lost!

Cheers,

Mike