How do I omit a record based on a check box?

[Ben Feingold]

I have two databases:

Attendance is used to collect data about student attendance (go figure). Class Lists is used to compile the data and figure out grades. In the Attendance database there is a check box to determin if the absence is excused.

I was using this formula to count the number of absences but it doesn't take into account the excused field:

[color:"blue"]Sum(Attendance:#Absent)

What I would like it to do is, "If the check box is checked, don't count this record. Otherwise, count it." I think it should look something like this:

[color:"blue"]If(Attendance::Excused ="x",

Omit,

Sum(Attendance::#Tardy1))

Any advice would be appreciated.

- Ben Feingold

[Ben Feingold]

I changed the calculation around and now it reads:

[color:"blue"]If(Attendance::Status="Tardy (1st)" and Attendance::Excused = "",

Sum(Attendance:#Tardy1),

Sum(Attendance::#Tardy1)-1)

"If the Attendance is Tardy (1st) and the attendance is not excused (blank), then add up all the tardies. Otherwise (if the event is excused) subtract one from the total tardies."

This only works for one record per student. If I check off the boxes for any other occurances on the same student, it doesn't subtract one (1) from the total sum.

Hmmm...

[-Queue-]

Make the Absent and Excused flag fields a checkbox with a value list of 1. Then you can use Sum(Attendance::AbsentFlag) - Sum(Attendance::ExcusedFlag).

[Ben Feingold]

It's actually more complicated then just two check boxes (isn't it always!). I have a Status dropdown with 16 possible choices, "Absent, Dismissal 1-7, Tardy 1-7 & Suspended" and one Excused checkbox to say if the "Status" was excused.

My Excused check box has only one value, "Excused"

What I would eventually like to do is assign a point value to each of the Status options. That way, if a student were "Tardy (4th)" they would only get credit for having been in 4th, 5th, 6th and 7th period, rather then the whole day. In the event that the tardy was excused, they would get full credit for the day. Does this make sense?

What I want to do now is have the computer tally each of the unexcused "Status" records. I just assumed that the easiest way to do this was to tally them all, and then subtract the "Excused" ones.

Thoughts?

[-Queue-]

Count(rel::status) - Sum(rel::excused)

Change the Excused value list to just be a value of 1. When you click it, it enters a 1 and checks the box. Then the 1 can be summed for the calc.

The point value idea sounds like it will take a lot more doing.

[Ben Feingold]

Thanks for all your help! It's good to get the input of someone who understands this program. I'm sitting here with a book and this forum scratching my head going, "I don't get it!"

And I know. This is turning out to be a much bigger project the more I learn about FileMaker! Considering I've only been using it for two weeks, I think I'm doing pretty well! I have a feeling FileMaker has a steeper learning curve then I initally expected!

If you have any ideas or initial impressions, I'd love to hear them!

-B

[LaRetta]

Hi Ben,

I always create a Custom Value List called 'Select'. It's only value is a 1. I then use this same value list attached to many fields that would normally contain only one value - yes, excused, whatever. It also allows me to easily count and find those same flagged records. It makes using their boolean logic very easy in calculations (instead of having to always write If(checkboxfield = "Excused") etc, as I can then use If(checkbox) ... and it decreases the number of value lists required.

I then simply resize the checkbox to 17x17 px or whatever so only the checkbox shows (without displaying the 1) and place regular text (like Excused) next to it.

LaRetta