Grid design in Facility and ePortal - Part 3 (conditional formatting)

This installment explains conditional formatting - how to make the appearance of your grid change when certain conditions are met.

We can use conditional formatting to make the text and background of a cell appear change when certain circumstances apply, such as when a field in the database is equal to a particular value.

You can see this in operation in the default attendance grid - cells have a different background depending on whether the student is present, late, authorised absent or unauthorised absent.

This technique can be applied to the left hand column that contains the student names as well. Let's carry on using the grid we used last week.

1. From the main menu in Facility, select Attendance | Advanced | Grid design for Roll Call attendance
2. Highlight ‘MyGrid'
3. Click Configure
4. Click Conditional

The Conditional colouring window will appear, which is blank by default. This is where we specify our conditions, and what display settings to use when they are met. This is done as follows:

1. In the Type drop-down box, select General Condition
2. In the Condition box, enter the formula that specifies the condition you wish to test. The result must be either true or false - i.e. student.forename='JOHN'. You can use the F9 key to bring up the data tree.
3. Click on the Font button and choose Display font. Specify the font settings you wish to apply when the condition is met.
4. Click OK
   
5. Click on the Font button and choose Selected font. Specify the font settings you wish to apply when the condition is met AND the user has selected the cell with the mouse.
6. Click OK
   
7. Click on the Border button and choose Display border. Specify the cell background and border settings you wish to apply when the condition is met.
8. Click OK
   
9. Click on the Border button and choose Selected font. Specify the cell background and border settings you wish to apply when the condition is met AND the user has selected the cell with the mouse.
10. Click OK
11. Click New. The condition will be added to the list
12. Click OK to close the Conditional colouring window
13. Click OK to close the Design grid view window
14. Click Modify to save your changes to the grid design
    

Let's say we wanted the highlight those students who are on the SEN register. Go through each stage above, making the following settings at the appropriate point:

1. Set the Condition field to Student.Sen.Stages.Stage.Code<>'N' and Student.Sen.Stages.Stage.Code<>''
2. Set the Display font to Verdana, Italic, 10 point, Maroon
3. Set the Selected font to Verdana, Italic, 10 point, White
4. Set the Display border to Orange background with Silver border
5. Set the Selected border to Orange background with Silver border

You should end up with something like this:

The students with an orange background are those on the SEN register.

Other formulas 

You can let your imagination run wild as to how you can utilise this feature to highlight particular students. How about bringing back information from student events? If we had a student event called 'Detention' we could use this formula to highlight students who'd had more than 10 detentions since they started school:

   len(collapse(apply(Student.Events.Evdef.Code ,c , if(c='Detention',1,''))))

A bit more useful (and a bit more complicated) might be to highlight students who had had more than 10 detentions within the last year:

len(collapse(apply(Student.Events.Evdef.Code,c,if(c='Detention' and getat(right(Student.Events.Datewhen,4) +mid(student.events.datewhen,3,2) +left(student.events.datewhen,2) ,applyposn) >=right(adjustdate(today(),-365),4) +mid(adjustdate(today(),-365),3,2) +left(adjustdate(today(),-365),2),1,'')))) 

The scenario I described here uses Student events to track phone calls home, then uses this formula to highlight students who'd already had a phone call home today:

right(listmax(apply(Student.Events.Evdef.Code,c, if(c='ATTPHONE',getat(right (Student.Events.Datewhen,4)     +mid(student.events.datewhen,3,2) +left(student.events.datewhen,2) +student.events.datewhen,applyposn),''))),10)=today()   

 

Multiple conditions

You can add more than one condition in the Conditional colouring window. CMIS will check the conditions in order from top to bottom until it meets a condition that is TRUE, at which point it will apply that condition.

Rather than just highlighting the students on the SEN register as we did earlier, we might want to use a different background colour for each stage. We would do this by adding three separate conditions to the list:

    Student.Sen.Stage.Stage.Code='S'

    Student.Sen.Stage.Stage.Code='P'

    Student.Sen.Stage.Stage.Code='A'       

...setting the font and background formatting so that they are different for each one.

The above example is fairly straightforward in that only one of the conditions can be true for each student. So what happens if more than one condition is true? In such cases, the formatting that gets applied will be the first one that matches.

If we had two conditions...

Student.Sen.Stages.Stage.Code<>'N' and Student.Sen.Stages.Stage.Code<>'' (students on the SEN register)

and

    Student.Statistical.Fsmelig='Y' (students eligible for free school meals)

...students who were both on the the SEN register and eligible for free school meals would appear with the 'SEN' formatting, as that would be the first matching condition.

 

What we would probably want to do in a situation like this would be to build a set of conditions that would give a different format to all possible combinations:

• Students on the SEN register AND eligible for Free School Meals
• Students on the SEN register but not eligible for Free School Meals
• Students not on the SEN register but eligible for Free School Meals
• Students not on the SEN register and NOT eligible for Free School Meals     

Visually, we'd want to give be consistent in our formatting choices to make it easy for the user to take in what the display is trying to say. So let's use the orange background to signify students on the SEN register, and a bold font to indicate those on free school meals.

We'd need to following conditions:

Student.Sen.Stages.Stage.Code<>'N' and Student.Sen.Stages.Stage.Code<>'' and Student.Statistical.Fsmelig='N' (orange background, italic font)

Student.Sen.Stages.Stage.Code<>'N' and Student.Sen.Stages.Stage.Code<>'' and Student.Statistical.Fsmelig='Y' (orange background, bold font)

Student.Sen.Stages.Stage.Code='N' or Student.Sen.Stages.Stage.Code='' and student.Statistical.Fsmelig='Y' (maroon background, bold font)

We don't need to add the final condition as if none of the first three conditions are met the default formatting will apply, which is already maroon background with italic font.