Suggestions for an Attendance Tracking application?

Hi Jerry,

This all looks good. My only suggested change would be at point 3, instead of using a text field I would use a multi value list field that gets options from the attendees section to store who attended a lecture in the lecture section. This way you will be able to easily manage the  records in CMS Builder. 

Let me know if you have any questions.

Thanks!

Greg

Hi Greg,

I'm sure that you're right, but I'm not sure exactly what you have in mind.

Could you elaborate a bit more?

Jerry Kornbluth

Interesting idea.

Thanks.

I'll have to think on that

Jerry Kornbluth

Hi.

Well, I made a great deal of progress over the weekend.

I set up a multi record editor to hold records for each participant.

There’s a checkbox field for each lecture series to indicate that a participant as attended at least one session of a series (this could probably go away and I could test if the date fields for a lecture series was not empty)

Dates attended are entered into a text field reserved for each series, in the format mm-yy (lectures are only held once in any month)

There’s a multi record editor with lecture series titles and another for lists of dates.

The attendance report page (attached) pulls date search options for each lecture series from the dates editor and searches the appropriate “dates attended” field.

STILL TO GO... Ideas gratefully accepted.

1) Since the lecture series list is dynamic, how can I add fields to the participant editor dynamically when a lecture series title is added to the “Class Title” editor.

Once that’s set up, I think I’ll be able to populate the search selects dynamically.

THE SIGN IN PROCESS

2) How can I choose which “lecture series attended” text field to append the current year and month to, from the front (or back) end, so the code in the form doesn’t need to manually be changed.

3) How can I append data to the end of a text field.

Thanks for your help,

Jerry Kornbluth

Hi Jerry,

1) Since the lecture series list is dynamic, how can I add fields to the participant editor dynamically when a lecture series title is added to the “Class Title” editor.

Is the "lecture series list" a pulldown?  If so you can have a separate table of lecture titles and have it load from there (I think I'm probably not understanding correctly, though).

2) How can I choose which “lecture series attended” text field to append the current year and month to, from the front (or back) end, so the code in the form doesn’t need to manually be changed.

It's difficult to visualize your setup, but if you had tables for: lectures, attendees, and attendance_log you could add records to "attendance_log" with the attendee num and lecture num and represent it in the backend with related records fields.

3) How can I append data to the end of a text field.

In MySQL UPDATE statements you can say yourfield = yourfield + 'new value' or just load the old value and set it at the same time such as: yourfield = mysql_escapef($_REQUEST['old_value'] . $_REQUEST['new_value']);

Usually how I start with something like this is by creating different sections in the CMS backend and moving them around until I'm happy with the layout of the backend, so that if someone needed to they could just use that.  Then I create the front-end to submit data to the backend.  

Hope that helps!

Thanks Dave,

I've got a better idea how to proceed now. I think the easiest approach for the lecturer might be to have a separate sign in form for each lecture series and use current date to generate the date information.

Still a few details to work out, but the related records and possibly the show/hide dependent fields plugins and the  MySQL UPDATE statement will be very helpful.

I'll post my solution here for others to use and comment.

Jerry Kornbluth

Hi Dave, Greg, et al,

I’m almost ready to post the solution to this but I still have 2 issues that I can’t seem to get my head around.

1) (line 37, 59, 80 of the attached file)

I have a text field in the sign in record called salon_name (the lecture series name) that can have any of 4 values. These values are eventually used in sorting email lists.

Blank (used for those who have shown an interest but have never attended a lecture)
AWC (used for those who have only attended an AWC lecture)
MMM (used for those who have only attended an MMM lecture)
AWC/MMM (used for those who have attended both an AWC and an MMM lecture)

THEORY
When someone signs in at say, an MMM lecture (that’s the attached sign in form) and I update an existing record (the email address matches the submitted email address or their Attendee ID Number matches their record number ) I need to be able to check the existing value of the salon_name field, and:

A) If the value is blank insert the value MMM in the field
B) If the value is MMM do nothing
C) If the value is AWC append /MMM to the end of the value (so it becomes AW C/MMM) or replace the value AWC with AWC/MMM (whichever is easier)
_____________________________________

2) (line 86 & 93 of the attached file)

If an attendee knows their attendee ID number (record number) because they’ve attended a lecture before, they can just enter that number in a form field and then submit the form for an express sign in.

The “on success” message should include the attendee’s first_name so that they know they’ve actually signed in, but I can’t seem to get a handle on how to do that either.

Thanks for your help...

Jerry Kornbluth

Hi Jerry,

I've attached an updated version of salon-sign-in-mmm.php. This is the main update I've added to it:

    $loggedInUser = mysql_get('attendance_test', $_REQUEST['attendee_number']);
    $salon_name = @$loggedInUser['salon_name'];
    if(!$salon_name){
      $salon_name = 'MMM';
    }elseif($salon_name == 'AWC' ){
      $salon_name = 'AWC/MMM';
    }

Also on lines 37, 59 and 80 I've added the amended the MySQL statement to look like this:

salon_name       = '$salon_name',

This is code is untested, so you might have to make a few changes to get it working. 

So the mysql_get function (highlighted in blue) retrieves the users data if they have got past the error validation stage. Then the code sets the value of salon_name for the user to the salon_name variable.

Then an if statement checks if any value has already been set to $salon_name, if it hasn't it changes to MMM. If the current value is AWC, it sets a value of AWC/MMM to $salon_name. Finally if MMM or AWC/MMM has already been set to the value it does nothing.

The $salon_name variable is used to set the salon_name variable in the MySQL statement.

Let me know if you have any questions.

Thanks!

Greg