Membership Application and Renewal System
applications applications_temp members memberClass renewals renewalBatch renewalMonth Data relationships The figure below defines the relationships of the data tables above.
(Figure TBD) Rights & Roles Section TBD Membership process overview
Applying for membership
An individual may apply for membership online or via a paper form. In either case, the application is stored in the applications_temp table. (In the case of a paper form, the application is entered by the staff or a volunteer into the applications_temp table.) If there is a charge for membership, this transaction is handled here. The results of the e-commerce transaction are stored in the applications_temp table. If the person does not complete the process including the e-commerce transaction, the application is considered incomplete. (For organization that do not charge a membership fee, a dummy value is entered in this field at the end of the process, indicating that all the required fields have been completed.)
The application form is a publicly available page with no logon required.
The application is then process by an AFIDS user with the proper permissions. During this process, the data is validated and the name checked against the persons table to prevent duplicate entries. Once the data is verified, the script creates all the appropriate records in all necessary tables and links them together via primary and foreign keys. This process transfers the application data into the applications table, which becomes a part of the member’s record.
If the application is incomplete, the AFIDS user with appropriate permissions can remove the application, and if an email address was provided, send the applicant an email with other options for completing the application.
The renewal process is handled in much the same way as the application process. When it is time to renew, the member completes a renewal application form, essentially verifying all the member information in the database, signing their pilot affirmation, and if applicable, paying their renewal fee.
The renewal form is only accessible to the member if they have logged into AFIDS. If the member’s membership status has been changed to inactive, the member cannot log in to renew.
The renewal application also goes into the applications_temp table for later processing by an AFIDS user with appropriate permissions.
Upon processing, the data in the appropriate tables is updated, and the applications_temp record is copied to a new applications record.
Each renewal application is stored in the members record. This creates a snapshot record of the member’s personal and member information at the time of the application, and it provides record of payment, affirmation agreement, and so forth. Process Detail
New member application. A new member joins via a public web page called membership_application.asp. This script captures the membership application information and stores it in the applications_temp table.
For applications received in paper form, the staff enters the application data though a script called reports/member_application_staff.asp. This script is essentially the same as the membership_application.asp script, except that it allows the staff member to continue without agreeing to the affirmation statement, which sometimes happens. In this case, the staff can follow up with the member after processing to get the affirmation signed. This script also allows staff to enter dues paid by check, which then skips the commerce processing.
New member application processing. When a new member application is posted either by the individual or by a staff member, it appears on the list of unprocessed membership applications. This list is accessible by persons with the XXXXX rights.
List of unprocessed applications. This list will contain completed applications and incomplete application. A complete application is one in which the member has successfully submitted all the pages in the process, including the commerce charge if applicable. The final step in the script sets the value of the ccardApprovalNumber, which is the value that determines whether an application has been completed. If the organization does not use e-commerce, a dummy value is inserted to indicate that the application was completed.
For incomplete applications, the staff member has the ability to send an email to the application (if there is an email address) encouraging them to complete their application. The staff member can also remove the incomplete applications.
To prevent duplicate entries, the script does a check of the existing members by name, and does not allow a new application to be submitted if there is a match. If a member whose membership has lapsed wishes to re-activate him or herself, the system does allow for this.
When an application is processed, either complete or incomplete, the value of the processedDate field is set to the current date, and the application no longer appears on the list. The application is still in the table, and can be re-set to the unprocessed state by the database administrator.
Member renewal application. A link appears in a member’s left hand navigation links to “renew membership”. This link is used by members to renew their own membership electronically. Because the member must log in using their username and password to access this link, the affirmation is considered a digital signature.
For users with advanced member rights, a link appears on the member view to renew the member. This link is used by staff to enter a paper renewal.
Member renewal processing. When a renewal application is submitted using either of the methods above, it appears on the list of unprocessed membership renewals.
Member Renewal Reminder Emails. A Visual Basic application run as a scheduled task sends emails to all members scheduled to renew in the coming month. This process runs on the first day of the month, asking members to renew online before the first letter is sent to save the cost of the mailing. The email directs the member to log in to AFIDS and renew online.
Member Renewal Reminder Mailings. Members receive up to four mailings for renewal. The Mailings are processed by a mailing house called MailersClub. Two Visual Basic applications run on the 15th of each month. The first creates the renewal files to be sent to MailersClub on the server hard disk, and it creates the renewal records in the database used to track the renewals. This process also sets the membership status to inactive (active is false) for any members who have received all four notices.
The second routine sends those files and the mailing information to MailersClub via the web service.
The members are selected for the four mailings according to the following schedule:
Month before renewal date Month of renewal date Month after renewal date Two months after renewal date Three months after renewal date First notice, asking for return of form by the 15th of next month Second notice Third Notice Fourth (final) notice Membership set to inactive
The tables that track the renewals are:
Renewals renewalBatch renewalMonth
The renewals table captures all of the MailersClub related information for tracking prior to printing. After printing a separate VB application is run against the web service to update the order details, including price, which can be used to track the costs of membership mailings.
The letters are based on MS Word Templates with mail merge fields. The first mailing contains a complete form for the members to correct their membership and pilot data, as well as the affirmation. The letters are also set up to know whether a member owes renewal dues based on their member class, and if they are a spouse member.
The template files are:
The directory structure for the files used by the VB applications are:
Member Affirmation Notices. Members whose most recent application, either initial application or renewal, with an affirmation value of 0 (i.e. not signed), will receive an email indicating that they need to log on and sign their affirmation. The email also has attached an affirmation in printable form which can be signed and mailed in. This mailing goes out on a weekly basis.
Member Badges and Notebooks. AFIDS provides a list of members who have not yet received their badge or notebook. This tracked through two fields in the members table. On the list is a link to update each of these as having been done. This script enters a date in the appropriate field. When both fields are not null, the member no longer appears on the list.
New member Notification Emails. So that wing leaders and membership volunteers in the wings can proactively reach out to new members, they receive an email once per week with a list of the members who have joined that week. The email contains a complete member application, providing the wing volunteers with all contact information, as well as information about their areas of volunteer interest and pilot qualifications. Customization
The application processes allow for customization between AFA organizations through variables in the functions.inc include file, an a series of include files containing the text that is presented to the applicant on the form pages. These are in a directory called \member_application_files.
Is a fee charged to join the organization? This is controlled by the newApplicationPremium variable in the functions.inc file. If a fee is charged, the membership_application.asp script directs the user to an ecommerce provider called Novapointe (currently the only one provided). The membership_application_staff.asp script also allows the staff to process credit card payments in this same manner.
Is a fee charged to renew membership? This is controlled by the newApplicationPremium variable in the functions.inc file. If a fee is charged, the membership_renewal.asp script directs the user to an ecommerce provider called Novapointe (currently the only one provided). The membership_renewal_staff.asp script also allows the staff to process credit card payments in this same manner.
Is a premium item offered when joining the organization? This is controlled by the newApplicationPremium variable in the functions.inc file. If a premium item is offered the membership_application.asp script creates and posts an order to merchandise fulfillment company Novapointe (currently the only one supported).
If a premium incentive offered when joining the organization? This is controlled by the newApplicationPremium variable in the functions.inc file. If a premium incentive is offered, the user’s choice of premium item is stored, but no order is placed through the fulfillment company. Member Classes
There is a table of membership classes that define the renewal fees and frequency.
name A name or description of this member class initialFee The initial fee charged to join the organization for the master member renewalFee The fee charged the master member to renew membership subInitialFee The initial fee charged to a spouse member subRenewalFee The renewal fee changed to a spouse member renewalPeriod The period of the renewal (in months) flyMissions Boolean. Is this class of member permitted to fly missions? newsletters Boolean. Does this class of member receive a newsletter? subNewsletters Boolean. Do the spouses of this member class receive a newsletter?
The values in these fields govern how new member applications and renewals are processed. Family Memberships
The system is also set up for memberships to have a spouse attached. This is tracked via the masterMemberID field. If that field is not null, these individuals can be charged differently for renewal fees, and they can be eliminated from household mailings. Workflow Processes
Obtaining an affirmation signature upon login. A workflow process checks for the most recent application with an affirmation of 0. If that condition is detected, the member is redirected to the affirmation_only.asp script, which essentially forces the member to sign the affirmation before continuing.
Membership status checks on login. (TBD)
Membership renewal reminder. (TBD) Commerce Integration For Angel Flight West, which charges an application and renewal fee, the system processes the charges to the member’s credit card by posting the transaction details to Novapointe. The membership_application.asp and membership_renewal.asp scripts redirect the user to a secure page on Novapointe’s server to enter their credit card information. The Novapointe server redirects back to the originating script with an approval number from the bank. That approval number is stored back in AFIDS, and is the confirmation (from AFIDS’s perspective) that the application was completed and completed successfully.
When the membership application is processed, the script also posts an order for the premium of choice (t-shirt or hat) which is then drop-shipped from Novapointe. The Novapointe server then redirects back to the application processing script with a transactionID. This transactionID is stored in the application record.
Later, a batch process looks up the order on the Novapointe server to update the shipping information. The shipping information (ship date and tracking number) is stored in the application record, which is accessible to the AFIDS user in the application view. Reference Include files
The following customization parameters in functions.inc are:
newApplicationPremium = (1/0) Where: 1 = true and 0 = false. Some organizations offer a premium upon joining. This parameter controls whether the user is given premium options for joining in the online membership application.
newApplicationIncentive = (1/0) Where: 1 = true and 0 = false. Some organizations offer an incentive premium upon joining. This parameter controls whether the user is given incentive premium options for joining in the online membership application.
newApplicationFee = 0 Where: 1 = true and 0 = false. Some organizations charge a fee to join the organization. This parameter controls whether the user is taken to a commerce site to pay a fee as a part of the membership application.
renewalApplicationFee = 0 Where: 1 = true and 0 = false. Some organizations charge a fee to renew membership. This parameter controls whether the user is taken to a commerce site to pay a fee as a part of the membership renewal application.
Content templates To allow for customization by different organizations, the membership application and renewal scripts pull page text from include files:
application_page_title page1a_text page3a_text affirmation_intro affirmation page5a_text final_success_text
page1a_text affirmation_intro affirmation final_success_text Scripts
Membership_application.asp Reports/membership_app_proc.asp Reports/incomplete_application_proc.asp Membership_renewal.asp Reports/member_renewal_proc.asp Reports/incomplete_renewal_proc.asp Reports/new_member_fulfill.asp Reports/new_member_update.asp
XSL stylesheets for emails:
For the member renewal emails: memberRenewalFirstNotice.xsl memberRenewalSecondNotice.xsl memberRenewalThirdNotice.xsl memberRenewalFourthNotice.xsl
memberRenewalDueNotify memberRenewalLateNotify missingFormRenewalNotice cleanupInactiveMembers deleteNonrenewingMembers
Installation and Configuration Process
Make sure all the following tables are configured:
Create include files for application and renewal form
Set variables & parameters in includes/functions.inc
Create or amend the following stored procedures and views:
Set up roles:
Add member classes
Make sure that all members have a join date, renewal date, and appropriate active status.
Create workflow items
Install VB Apps
Create XSL templates
Member application form