Register attendance

[jrRaid]

An inspector dropped an application on my desk/computer to make changes and I don't even know if it is possible.

I need to change the way attendances to events are registered.

There is a table with attendees, a table with events (every week Monday and Wednesday, Tuesdday and Thursday).

Attendees and events have all an ID.

Attendees are assigned to events with a technique that seem to be based on a demo on the NightWing site.

Two portals, left portal attendees to assign, right portal events. With a button the attendees are removed from the left pane to land in the right pane.

How it is now:

There is an attendance table.

A portal to this table is on a layout in the attendee table, with allow creation of records enabled.

The user makes a find for the event and lands on the register attendance layout, in the found set of attendees for the given event.

Fills in the date, fills in attend or absent from a drop down list, goes to the next record and repeats it all.

I'm talking about 350+ records.

Most of the attendees are attending.

So, they want an other way of working.

On a layout, make records from attendees who were absent, with date and eventID for the whole week, push a button and FileMaker makes all the needed records for the registered absents and for all the not absent make 'attend' records.

I can't post an example file, though I tried to pull just that part out of the application, but the relationships are so complex that it doesn't make sence to extract just that part.

So I'm only here for a few ideas or hints how to start and down the road to come back with more details.

I think to bring, some way or another, the dates and ID's of the events together with the attendeeID.

Compair that set of records with the absent list of records allready made.

Pull out the not absent and those would be the 'attend'. Loop through that set and make records.

The List() function could be a great help, but that is in version 8.5 and we have only 8.

No way to let them upgrade, we need a new government for that...

Anybody an idea, hint, custom function, calculation, script or a combination of all that ?

TIA

[Fenton]

It is rather late in the evening. But I don't think this is terribly difficult. The first thing you mentioned, assigning Attendees to Events via the portals is known as the "dwindling" technique (works best with portals, because of refresh issues). But that's fine.

But for marking attendence, you just need to reverse the method. Rather than "adding those who attend 1 at a time," you should add everyone who was signed up for the event at once (with an Import or Loop). Then just mark (or unmark) the people who did not attend; or you could just delete their "attendence" record. I'm not sure which would be better, likely a wash.

[jrRaid]

Thanks for the reply Fenton.

I already changed the script for registration:

Find the event (which will give all the attendees)

Fill in the date in the records of that found set and set the 'attend/absent' field to attend.

Show the found set to the user (the layout with the portal with all the 'attend/absent) and sort the portal in reverse order, with the last date as first portal row.

The user 'walks' through the records and reset the few records to 'Absent'.

But even that seems to be too much work for the user....

They insist for a method of create only records for 'absent' and the system creates the records for the 'attend'.

The creation of those records is now with a loop.

If I add a 'control step' in that loop, something along these lines:

if the given date/attendee for the event = the date/attendee in the 'absent' table, set the date and set the attend/absent field to 'absent'.

Now I have to find a dictionary to translate the plain English...

[Fenton]

I don't really get what the problem is. I would present the data entry person with a portal of those who'd signed up for the event (imported into "attendence"), sorted alphabetically, with a checkbox/or/button for "absent". I don't see how it could done any easier. I do not see how an "absent" table would make any difference, unless they are in love with drop-down lists, in which case it would. But I would prefer a portal with a button; considering there can't be all that many people signed up for a particular meeting.

[jrRaid]

I don't see it as a 'problem', more a difficulty.

It's an application where I have to find my way to see how things were done.

It turns out to be something that was started in FM 3 and went all the way up to FM 8, where somebody, don't know who, merged the files together in FM 7.

Now I'm nr. n + 1 who have to implement a requirement.

There are no records of the eventdays. Just a record with the event name, and a field with dates per month (return separated list) when the event will take place.

A table with attendees, linked to the events.

Requirement is (did you ever tried to change a 'government' requirement) to make records in a table with eventID, date and attendee name who were absent.

Create records for all the attendees for all the events on the given dates (the return separated list), flag all those attendees as 'attend', exept the ones in the 'absent' table. Those has to be flagged as absent.

There is nearly no way around it, I have to start from the 'absent' table.

I managed to create records for the events with the dates in the date list. I made a separate table for that, with a loop script for each event record and an inside loop for the dates in the list. At 32 events, twice a week, is 64 x 4, an approx of 254 records a month.

Now I'm looking for a way to put eventsID/date together with attendeeID to generate all the attendrecords and flag the attendees from the absent table as absent.

I'm close and again, after years I just stopped to question requirements made by government people. Waste of time and energy.

Anyway, thanks for your reply Fenton.

Edited October 28, 2007 by Guest
Correct typos

[comment]

The real question is what do they intend to do with this after it has been recorded. Because just recording the absentees for an event is really simple: go to a list layout of Attendees (should be Registrants really), put the EventID in a global field, and click the absentees to enter/remove their ID into an AbsenteeIDs multi-line field in the Event table (accessed via a relationship using the global EventID).

If necessary, you can then run a script to create records in the real Attendees table. But you could just settle for showing the attendees in a portal of Events, using a filtered relationship.

[Fenton]

When a person signs up for an "Event," does that mean they are registered for ALL dates of the Event, or just for one of them? It is not clear (to me anyway). If they only need to attend 1, then you could not consider them absent until after all the dates were over. And there would be only 1 Attendence record for that. I'll assume however that they're signed up to attend all of them.

Is the final result is supposed to be an Attendence table, with a record for every person, their event(s), the date attended (or not)?

If so then you will need a loop (or import) to create a record for every person-event-date; much like your loop to create the Event-dates, but obviously more records.

Your Absent table should have person-event-date fields also. You can see that a relationship would match against the Attendence table, using all 3 fields. First you'd Find for the dates. Then you can use Go To Related Record [ Attendence; Show only related; Match all records in current found set ].

That will land you on the absentees for that event-date in Attendence. Mark them as not-attendended. You might also want to delete those records in the Absent table, so as not to do them again (waste of time). Though the next Find should not find them (use the latest date in Attendence as a cut-off date).

Edited October 28, 2007 by Guest

[jrRaid]

@Comment and Fenton

When a person signs up for an "Event," does that mean they are registered for ALL dates of the Event, or just for one of them? It is not clear (to me anyway). If they only need to attend 1, then you could not consider them absent until after all the dates were over. And there would be only 1 Attendence record for that. I'll assume however that they're signed up to attend all of them.

I went deeper in the application (wish I didn't)and read a sort of 'manual', while the relationships diagram is a real hell to find your way, or mine. I think that all the previous developers just added the relationships they needed. I'm afraid I have to do the same, just to prevent to break something....

They are registered for all the dates of an event and those events are during 8 to 10 months.

Is the final result is supposed to be an Attendence table, with a record for every person, their event(s), the date attended (or not)?

Yes, each person has for each date of the event a record with attend or absent. In the 'Attendance' table there is a field (just found that) to enter the reason of absent or a short comment (it is called 'conduct' ) , this field is related to a personnel table. There is a sort of calculation of attendances. I think (not sure) that each person needs to be above a given % of attendance.

I found that field back on a 'performance' layout for each person....and that layout has restricted access....

If so then you will need a loop (or import) to create a record for every person-event-date; much like your loop to create the Event-dates, but obviously more records.

Your Absent table should have person-event-date fields also. You can see that a relationship would match against the Attendence table, using all 3 fields. First you'd Find for the dates. Then you can use Go To Related Record [ Attendence; Show only related; Match all records in current found set ].

That will land you on the absentees for that event-date in Attendence. Mark them as not-attendended. You might also want to delete those records in the Absent table, so as not to do them again (waste of time). Though the next Find should not find them (use the latest date in Attendence as a cut-off date).

So far I'm with you, nearly the same idea.

I have the feeling that Comment's reply can be a good approach, but I need some time to understand and find out what his thought is...

Anyway, thank you both.

[comment]

My suggestion will only work if there is a separate record for each occurrence of an Event. Otherwise, you clearly need to record not only the absentee's ID, but also the date of absence - and this requires a separate table of Absences.

In addition, the method will NOT provide a good way to gather aggregate data about a registrant, such as how many times was X absent - that's why I said it depends on what's the purpose of recording this.

It would also be helpful to know the full structure of the solution. It seems like people register for a course (event), with each course having a series of lectures (dates). It's not clear how multiple registrations of one person to many courses are handled.

In any case, I don't think it matters whether one records attendances or absences. In the end it comes down to the same thing, since attendance = not absence, and registrations = attendances + absences.

[jrRaid]

Thanks Comment to clarify that.

I did some more digging to try to see clear.

And like you said, I don't completely agree with some used terminology...

As far as could find out now, there is a table (out of 222 !!) with data from 'members': name, address, department etc.

Each 'member' can 'enroll' in one and just one event.

There is a layout, based on that table, called 'registration'.

Basic information here, like name, ID.

A portal linked to the 'attendance' table, allow record creation.

A button that calls a script:

shows a new window with two global fields:

one for a dropdown list of all the events,

one for a dropdown (FM)calendar.

The user gives the eventname and the date and clicks on a button 'create'.

The script does a find for the event given in the first global.

This returns a found set of 'members'.

A loop creates for each 'member' a record with the date given in the second global and set the 'attend/absent' field to 'attend'.

Now the user has to scroll through the 'member' records to find the 'absent' one and change the 'attend' value to absent, fill in the reason in a 'conduct' field in the portal.

I understand that with 32 to 35 events twice a week that can be a hell of a job for a data input victim.

That's probably why they want a table to fill in only the absentees, with the eventID and the date and the reason in one table/go. Push a button, and FM has to create all the needed records.

Who is not in the absent table is 'attend' and the absent gets an 'absent' value in the record.

It boils down to me now to find/construct a script that does the task.

Every idea or hint, even in plain English, is welcome.

[comment]

Where (if at all) are the dates of an Event kept?

[jrRaid]

In a separate table 'EventDates', the date and the eventID.

[Fenton]

If you already have the Attendence records (which you just said you have), each would have an:

person-event-date

Then you should be able to set an "absent" flag in that table from the Person's record, via a relationship. If, as you say, a person only EVER has 1 Event, then you'd only need the date. But "EVER" means "for all time, past and future." So you might want to use both the Event and the date. You say you've already got global fields for these.

Person                                              Attendence

PersonID AND gEventID AND gDate = PersonID AND EventID AND Date

You don't even have to go to Attendence. Just fill in the global event and date, then run the script:

Set Field [ per_Attendence~Person|gEvent|gDate::Absent_flag; 1 ]

Commit Records/Requests

You'd want some IsEmpty() tests, to make sure they'd actually put the globals in. And you'd likely want to clear the globals afterwards; or not, if you're subsequently doing several people for the same event and/or date.

So they'd just do a Find for the absent person, put the event ID and date, then hit the button. Then do the next person.

This is much the same procedure as populating an Absent table, so that become superfluous.

"..fill in the reason in a 'conduct' field in the portal.

Yeah, create a global for this also, so you can set it. Clear the global each time (unless everyone tends to use the same reason -]

Edited October 30, 2007 by Guest

[jrRaid]

Thanks Comment and Fenton.

What I can do now:

Close the Month October 'the old way' to have a clean starting point.

Create Event date records for the month November.

Create the 'Absent' table with the needed fields.

Then, the 'Absent' is a sort of every day job to register.

Once a week, the user clicks a button to activate a looping script that isolates the events for that 'weeknumber'.

Gives a found set of 'members'.

Create records for the given eventID, date and members and set the attend/absent field to attend.

Except for the related records (Fenton's suggestion) those are set to Absent.

Go to the next event untill end of list.

Flag the eventDate records as already registered.

Fiddle a little with the conduct field.

This way I think I can capture on time the possible new 'members' to an event during the week (is an additional problem).

Did I cover it all in plain English ?

[comment]

It sounds a bit too complex to me. I think that if you want to create Attended/Absent records, you only need one kind. For example, 50 people registered for an Event, 45 showed up on certain date - that's 5 records in the Absences table, each having a PersonID and a date (or EventDateID). There is no need to have additional 45 records to record something that is already known.

You could create the absence records by a script run from a portal to People on a layout of EventDates. This way you need no globals and no finds - just go to the correct EventDate record, and everything you need is already there.

Coming back to my earlier post, here's a demo of essentially the same thing, but without an Absences table. It records the absences directly into a multi-line field in the EventDates table. As noted before, the reporting features for such approach are limited - but some basic stats ARE available.

EventAbsenteesSans.fp7.zip

[jrRaid]

Thank you for the demo Comment.

If I can grab the ID in the AbsenteesID field I can still make the % calc for each person.

I see light at the end of the tunnel, if it is the exit or a train...future will tell.

I will keep you posted.

Thanks

[comment]

If you take a look at the People table, I have already included a calc to count a person's absences. The same formula, when evaluated from the context of People, will count the event's dates. These two will give you the attendance percentage.

[jrRaid]

You're right, sorry.

Long days and part of nights are start counting...

Thanks for help.

[jrRaid]

Just a thank you note for Fenton and Comment.

I changed the database in the way you suggested and it all turned out very well.

Thanks to both of you.