Hi folks,

I've got a register portal with options to mark pupils as Present, Absent, Late, Very Late, Excused.

 

I had a calculation field for each registration that marks how many pupils were present with this -

 

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

 

Unfortunately, this counts pupils who were late or very late as absent, when in fact they were present for at least some of the lesson. This is especially  important when viewing the data from the pupils own relationship, as kids whose bus is always late could have 0% attendance despite not missing a lesson!

 

Therefore, I'd like to add "Lates" & "Very Lates" as numbers to, maybe 0.75 and 0.5, but ideally these numbers would be user changeable; set in global setting field.

 

Am I on the right lines if I do something like:

 

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

#then set a variable for that

Set $Present

ValueCount ( FilterValues ( List ( JPupilClassAssess_forSelectedAssessment::Attendance ) ; "Late") )

#then divide this whole number by whatever has been set in the global field & set the late variable

divide by gLateSetting

Set $Late

#then do the same for other options; Very Late, excused etc.

#then add up the variables;

$present + $late etc..

 

Cheers!

Mike

If you don't need a weighted result, you could just try:

Let ( [
  attendanceList = List ( JPupilClassAssess_forSelectedAssessment::Attendance ) ;
  attendancePresentStates = List ( "Present" ; "Late" ; "Very Late" ) ;
  present = ValueCount ( FilterValues ( attendanceList ; attendancePresentStates ) )
  )
  "Present: " & present & "; absent: " & ValueCount ( attendanceList ) - present
)

Am I on the right lines if I do something like:

 

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

#then set a variable for that

Set $Present

 

Yes (I think so), but there are (usually) no $vars in a calculation … anyway, next time you have a bright idea, why not simply try it out?

 

Let ( [
  attendanceList = List ( JPupilClassAssess_forSelectedAssessment::Attendance ) ;
  total = ValueCount ( attendanceList ) 
  ] ;

  Case (
    total ; 

    Let ( [
     lFac = 0.75 ;
​     vlFac = 0.5 ;

     present = ValueCount ( FilterValues ( attendanceList ; "present" ) ) ;
     late = ValueCount ( FilterValues ( attendanceList ; "late" ) ) ;
     veryLate = ValueCount ( FilterValues ( attendanceList ; "very late" ) ) ;
     totalPresent = present + late + veryLate ;
     weightedResult = Case ( late or veryLate ; " (weighted: " & present + veryLate * vlFac + late * lFac & ")" )
     ] ;
    "Total: " & total & " – " & totalPresent & " present" & weightedResult & ", " & total - totalPresent & " absent"
   ) // inner Let()
 ) // Case()
) // outer Let()

Thanks so much for this eos,

(again! - you're often helping me out :-)

 

I've got it working exactly as I want. - using global fields to so the user can set the weighting.

But, there are a few bits and pieces spare that stop things working when I remove them!

 

Let ( [
  attendanceList = List ( JPupilClassAssess_forSelectedAssessment::Attendance ) ;
  total = ValueCount ( attendanceList ) 
  ] ;

  Case (
    total ; 

    Let ( [
     lFac = .75 ;
​     vlFac = .5 ;

     present = ValueCount ( FilterValues ( attendanceList ; "present" ) ) ;
     late = ValueCount ( FilterValues ( attendanceList ; "late" ) ) *GlobalSettings::LateWeighingG;
     veryLate = ValueCount ( FilterValues ( attendanceList ; "very late" ) )*GlobalSettings::VeryLateWeighingG ;
     totalPresent = present + late + veryLate ;
     weightedResult = Case ( late or veryLate ; " (weighted: " & present + veryLate * vlFac + late * lFac & ")" )
     ] ;
    totalPresent
   ) // inner Let()
 ) // Case()
) // outer Let()

The 

Let ( [
lFac = .75 ;
​ vlFac = .5 ;

is now not needed, but I'm struggling to know which (s and [s I need to get rid of.

Can I just get rid of the 

lFac = .75 ;
​ vlFac = .5 ;

to leave the rest in an inner Let?

And also, the

weightedResult = Case ( late or veryLate ; " (weighted: " & present + veryLate * vlFac + late * lFac & ")" )

line can go, but I'm struggling again with the brackets.

 

Thanks again!

Mike

Just remember, the last line inside square brackets should not end with a semicolon. So the only extra bit to do is remove the semicolon at the end of the "totalPresent" line.

 

That said, you might consider separate fields for Present, Absent, Late, Very Late, Excused. Make them number fields and format as checkboxes, with a value list of "1" (or empty). That would make it a piece of cake to add them up.

Hi Fitch,

many thanks too!

Yes that worked. :-)

 

The reason I've gone for a value list is that there are more options than just absent; things like Sick Bay, Visit etc.

Your idea does seem a lot easier though!!

 

Now to apply the same technique to working out individual pupils average attendance...

 

Cheers,

MIke

Your idea does seem a lot easier though!!

 

It isn't really; instead of a evaluating a single list five (three) times, you now evaluate five (three) fields once each – not exactly progress; also, now you have four additional fields in the schema and need space for them on the layout (and must be careful to fill in the right one …).

 

 

That would make it a piece of cake to add them up.

 

What is it now – rocket surgery? 

Oh well, I'll just be reassured that my original idea wasn't totally daft and that I'm smart enough to understand rocket surgery. :-)

 

Big thanks to both of you!!

Mike

Well then I might suggest a field in the same table as the Attendance field, which would be a calculation or auto-entry number field:

Case( 
Attendance = "Present" ; 1 ;
Attendance = "Late" ; 1 ;      // or .75
Attendance = "Very Late" ; 1   // or .5
)

Then all you have to do is Sum() that field.

@eos: Your point is well taken, however I'm not advocating gratuitous fields and schema clutter. I'm looking at what makes most sense for a specific situation.

 

I've put a lot of thought into similar solutions and have found that separate fields with Boolean values can be a highly useful structure.

 

If you know that you're going to want to total up the separate values, IMHO it's more efficient to build it that way. Otherwise you end up with multiple calcs like the one in my previous post, or calcs on the parent side that have to be carefully constructed for whatever eventualities may arise.

At the moment it's working fine, and I was brought up to not fix this that aren't broken!

 

Having said that, I think I can see what you're saying Fitch & it does make sense. When summing a, does the summed field have to actually be on the portal. (I suppose it could be behind everything else anyway though..)

 

Cheers!

Mike

You don't need to display the field or even the portal -- a portal and its fields have no effect on calculated fields in the parent table. It's the relationship that matters.

Ah, that makes sense.

In the last few days, I've gradually been coming to a slightly fuller understanding of why TOs are often necessary. 

 

Is it always best practice to filter via relationships rather than through a portal?

 

Thanks so much!

Relationship filters are more efficient when a file is hosted, because only the matching records are pushed from the host to the client. With portal filters, ALL records* that match the defined relationship are pushed, and then the client applies the portal filter.

 

That doesn't mean you shouldn't use portal filters. They're great. Just be aware of potential performance issues with large data sets.

Edited September 29, 201411 yr by Fitch
*It's actually slightly more complicated than that...

Many thanks - very helpful :-)