Help me count the invalids.
May 10, 2011 3:03 PM   Subscribe

Help me solve a database design stumbling block. How can I track # of consecutive absences in my membership attendance database?

I am designing a filemaker pro database for an organization whose bylaws state that any member who misses three consecutive monthly without being excused meetings is dropped as a member.

I've created separate table for meetings (one record for each meeting), attendance (one record for each member for each meeting attended - possible values are Present or Excused. I assume no record = absent.), and members (with all their personal data). All tables relate to each other appropriately.

My first instinct was simply to count the number of days since a particular member has appeared at a meeting rationalizing that any value greater than 90 days would make them invalid. This fails hard though when the organization decides to skip a monthly meeting as happens once or twice a year.

How would you design the db to count number of consecutive absences without calculating based on number of days?
posted by jlowen to Computers & Internet (9 answers total)
Count the number of meetings (in the meetings table) since the date the member joined, then count the records for that member in the attendance table. If there is a discrepancy, it's an absence. If there are more than three absences, you need to check whether they're consecutive. Loop through an array of attendance, checking the number of meetings between this attendance and the next.
posted by sonic meat machine at 3:09 PM on May 10, 2011

Don't know about FileMaker, but in SQL this would be my first attempt:

`meetings` INNER JOIN `members` LEFT JOIN `attendance`

Count where the attendance data is NULL (that is, the LEFT JOIN failed). I think it's easier to do the policy enforcement (3 consecutive missed meetings) in the client side code rather than SQL or PL/SQL.
posted by sbutler at 3:18 PM on May 10, 2011

What happens if a member is permitted to attend a meeting despite having missed three or more consecutive monthly meetings without being excused? If this is considered to be an issuance of an implicit excuse by the organization, then there is any easy solution. Assign sequential increasing meeting index numbers to the monthly meetings. You can then calculate the number of consecutive absences for a member by subtracting their latest attended meeting index number from the last meeting index number.
posted by RichardP at 3:19 PM on May 10, 2011

And yes, as sbutler says, it's better to do the policy enforcement in procedural code as opposed to the database.

An algorithm:
  1. M ← meetings since the member joined.
  2. R ← member's attendance records.
  3. If length(M) >= length(R) + 3, then:
  4. ... C ← 0
  5. ... for every m in M,
  6. ... ... if there is no r in R such that =, then:
  7. ... ... ... C ← C + 1
  8. ... ... else,
  9. ... ... ... C ← 0
  10. ... ... end if
  11. ... ... if C = 3, then:
  12. ... ... ... return True
  13. ... ... end if
  14. ... end for
  15. end if
  16. return False

posted by sonic meat machine at 3:28 PM on May 10, 2011

The problem with the above algorithm and approaches is that when run, it will drop someone as a member even if they've since been reinstated, because at some point in the past they missed three meetings in a row. So person misses 3 meetings, is dropped, but pleads their case is readded and starts attending regularly... and then the algorithm above declares that they (at some point) missed 3 meetings in a row and drops them as a member, again.

I'd suggest you instead add a field to the members table of "Status" (Active, Dropped, et al), and possibly a "Status change date". You could even have an entire table for these status transitions of memberID, date, fromStatus, toStatus, so you could track the history of users and member additions/removals. That's up to you, and people might like to know "How has our membership changed over time?", but it's not important for the specific question you're asking.

Once you do that, you would implement a server-side job. Meetings happen about once a month apparently so a server-side job that runs periodically could handle this easily- every night, once a week, once a month after the typically scheduled meeting, whatever. This server side job does the following:
"Get latest N meetings by date descending, get latest attendances for each meeting (this is similar to sbutler's inner join above, to produce all member attendances of the last N meetings by date descending)." Alternately a simple "UNIQUE memberID FROM (attendances where meetingID in ( get last N meetings by date desc) )" would produce a unique list of memberIDs who attended at least one of the last N meetings, and thus is not a candidate for deletions.
Next, loop through your list of all members where Status = Active, and if not found in the first list, they have missed all of the last N meetings. If my SQL-fu were better, you could probably structure the whole thing as one query, as Select memberID where status=active and not in (unique memberID from (attendances where meetingID in (get last N meetingID by date desc) ) )"
However you generate that list of "People who haven't attended the last N meetings", that final list could be used to then trigger whatever responses you want, including setting their member Status to "Dropped" or whatever, as well as for example a mail trigger to notify the member and the organization heads of this status change. You could even add in a separate job of "For all members who are not set to active but attended the last meeting, set them to active".

The nice thing is if you have this run nightly, even an erratic meeting schedule once every month 'or so' won't result in repeated notifications or status transitions, thanks to the "members whose status = Active" filter.

If you implement these two jobs, then if a member misses 3 meetings in a row he or she is automatically dropped, and if they are reinstated by agreement and attend the next meeting, they are automatically moved to "Active" again- and their past history of absences won't cause them to be deactivated again.
posted by hincandenza at 4:09 PM on May 10, 2011

Crap. These are all excellent answers that seem slightly outside of my skill set. Perhaps I should have asked how I find someone to pay to solve this one particular problem. I don't know how to find a local filemaker expert.
posted by jlowen at 4:30 PM on May 10, 2011

the only way to solve this using a pure relational approach requires there be incremental sequential meeting numbers and then is still as ugly as sin - procedural code is the correct answer.

there's a "find a developer" search page at that might help you find someone local.
posted by russm at 5:49 PM on May 10, 2011

I have solved a similar problem in FileMaker Pro 11 Advanced using a two column sorted array, mapping a complete set of events in one column, and then the number of occurrences (or non-occurrences in the case of NULLs) in the next column. In your case, the first column would be actual meeting dates and the second would be actual attendance. You can then chart and/or parse the output to look for consecutive gaps in attendance, or use a calc to allow the member records to flag themselves when they violate your rule.

My dataset was much less normalized than yours will be. Putting my own solution together required a combination of scripting and custom functions, and it was neither simple nor straightforward (and I'm a long-time FileMaker developer, for whatever that's worth). My situation was complicated by a need to also use FileMaker's native charting tool to display the results, which added an extra layer of complexity to it, but still...I can honestly say that it was a pain in the ass to do this in FileMaker. Take that for what it's worth.

One potential time saver that could be helpful to you are some of the very nice, free custom functions that you can repurpose for your needs. For example, there's an excellent collection of 1200+ free custom functions that you can peruse at Brian is a FileMaker consultant, but the custom function database is a group effort with many contributors; I am not affiliated with his site or his company in any way, but I do admire his efforts to share knowledge. The particular functions I used as starting points for my solution were SortArray and SortArray_Merge.

Since FileMaker often allows for many different potential solutions to a single problem, I'm sure there are some other clever ways to do this. If the above sorted array approach is still too abstract to be helpful you might want to visit the forums at and ask for some opinions there. There are a some excellent FMP developers that participate on that site that will be happy to steer you towards a solution you can manage (or tell you conclusively that this is harder in FMP than you think it should be).
posted by mosk at 6:01 PM on May 10, 2011

I think I have a working solution. First I added "absent" as a value for attendance and then from the members table I created a calc field that went:

GetAsText ( GetNthRecord ( Attendance::AttendanceStatus; 1)) & GetAsText (GetNthRecord ( Attendance::AttendanceStatus; 2)) & GetAsText (GetNthRecord ( Attendance::AttendanceStatus; 3))

And then if the result is "Absent Absent Absent" Then they are marked as invalid. Any other string would be valid.

There are probably downsides, but it seems to work.
posted by jlowen at 6:26 PM on May 10, 2011

« Older How to keep birds away from potted plants?   |   +1 Smoke -1 Fire? Newer »
This thread is closed to new comments.