CPPP attendance badges for members

COPA recognizes CPPP attendance by adding a Role badge to a member's profile. This requires administrative rights on the COPA web forum, so that's a big deal.

Attendance is accumulated in the CPPP registration history spreadsheet. For the first several years, registration was handled by the COPA 1.0 software and later by Regonline.  To properly credit members with CPPP attendance, both sets of registration records need to be accumulated.  The COPA profile id is used to combine records.

The current spreadsheet can be found in the COPA Safety Dropbox Folder: CPPP registration history-username counts.xls

Only the first 6 columns are maintained, with the additional columns from historical COPA 1.0 data.

• idx is now the Regonline record locator
• course is of the form xxxyy for a location xxx and year yy
• username is the COPA member id
• coursetype is the Regonline registration type: Flight, Ground, Non-Owner CFI (should exclude Partners)
• name is First Last from Regonline
• email is the email address from Regonline

Caution: the COPA member id is the sort key, yet Regonline truncates long ones (sigh!), and registrants are sometimes sloppy about entering this information.  This requires a bit of sleuthing occasionally, but generally isn't too big of a hassle! [;)]

There are several steps to update the registration history spreadsheet from a new Regonline event:

1. For the event, use the Export Everything report and save it as an Excel spreadsheet
2. Open the Export Everything spreadsheet
3. Sort by Registration Status and Registration Type
4. Delete any cancelled or non-attended status
5. Delete all Partners (they don't get badges)
6. Delete the first column
7. In the Event Number column, fill it with xxxyy for the course name (now the second column)
8. Delete Event Title to Registration Status columns keeping the Registration Type column
9. Find the COPA profile id column and insert that prior to the Registration Type column
10. Find the Full Name (First Last) column and insert after the Registration Type column
11. Find the Email column and insert after the Full Name column
12. Verify that you have 6 columns in order: Confirmation Number, Event Number, COPA id, Registration Type, Full Name, Email

That data for all participants will be copied into the registration history spreadsheet after you prepare it by removing the current subtotals as follows:

1. Open the registration history spreadsheet
2. Select everything
3. In the Data menu, click on Subtotals... and click Remove All
4. You should now have a spreadsheet sorted by username with at least 6 columns of useful data

Next copy the new Regonline data at the end of the registration history spreadsheet and verify that the new data matches the expected column content.

To create new totals by username, insert subtotals as follows:

1. Sort by username
2. Select everything
3. In the Data menu, click on Subtotals...
4. Choose username for At each change in: menu
5. Choose Count for the Use function: menu
6. Unclick and choices in Add subtotal in: menu
7. Click on username in Add subtotal in: menu
8. Ensure Replace current subtotals checkbox is checked and Summary below data is checked
9. Click Ok

What we have now is a historical count of CPPP events attended including history beyond Regonline.

To update a Role badge for attending a CPPP, search for each new record for the event number xxxyy, and update the role with CPPP Attendee ZZ, where ZZ matches the subtotal in the registration history spreadsheet.