Platform to integrate scholarship application and process

Project number:	134086
Opened by:	jsheu
Opened on:	Sunday, June 23, 2013 - 6:03pm
Last modified:	Saturday, August 10, 2013 - 11:09am
Operations:	add Case \| view all project cases

Project summary: seek an online way of integrating scholarship application and process
People: Sister Jen
Project kickoff: 6/27/2013
Project due: November, 2013
Project phases:
Feature/Request:

	Feature/Request	Priority	Status	Note
1	applicants have to create a Drupal account first before submitting forms	High	Open
2	do not record the reason why the applicant is qualified for the scholarship	Medium	Close	6/27: Jen decided
3	record the names of Brothers or Sisters going to the Scholarship night	Medium	Open
4	record the counselor name, phone, email	Medium	Open
5	record applicants' birth day (year/month/day) or birth year (year)	High	Open
6	force applicants to submit and attach pdf files only	High	Open
7	notify applicants if documents are not complete	High	Open
8	record response from interviewers on attached file or in database	High	Open
9	record the names/email of interviewer(s)	High	Open
10	weighted GPA and unweighted GPA	High	Close	7/29: Jen unweighted GPA (aka GPA): required weighted GPA: entered by Regional Team

Database fields:

	Description	CCK Field Name	Scholarship Applicant	Scholarship TCNW Admin	Scholarship TCNW HeXin Team	Scholarship TCNW Regional Team	Scholarship TCNW Reviewer	Note
1	*Last Name	field_scholar_last_name	Read, Write	Read, Write	Read	Read	Read	* encrypted *
	*First Name	field_scholar_first_name	Read, Write	Read, Write	Read	Read	Read	* encrypted *
	*Middle Name	~~field_middle_name~~
	*Gender	field_scholar_gender	Read, Write	Read, Write	Read	Read	Read
	*Date of Birthday	field_scholar_birthday	Read, Write	Read, Write	Read	Read	Read
	*Address	field_scholar_home_address	Read, Write	Read, Write	Read	Read	Read	* encrypted *
	*City	field_scholar_home_city	Read, Write	Read, Write	Read	Read	Read	* encrypted *
	*State	field_scholar_home_state_tcnw	Read, Write	Read, Write	Read	Read		only tcnw-region states
	*Zip Code	field_scholar_zipcode_tcnw	Read, Write	Read, Write	Read	Read	Read	* encrypted * only tcnw--region zip codes
	Home Phone	field_scholar_phone_home	Read, Write	Read, Write	Read	Read	Read	* encrypted *
	Cellular Phone	field_scholar_phone_cellular	Read, Write	Read, Write	Read	Read	Read	* encrypted *
	*Email	field_scholar_email	Read, Write	Read, Write	Read	Read	Read	* encrypted *
	*Parent or Guardian	field_scholar_parent_or_guardian	Read, Write	Read, Write	Read	Read	Read
	*High School	field_scholar_high_school	Read, Write	Read, Write	Read	Read	Read	need generic name so college/university fields can be combined
	*Address of High School	field_scholar_high_school_address	Read, Write	Read, Write	Read	Read	Read
	*City of High School	field_scholar_high_school_city	Read, Write	Read, Write	Read	Read	Read
	*State of High School	field_scholar_hs_state_tcnw	Read, Write	Read, Write	Read	Read	Read	only tcnw-region state
	*Zip Codeof High School	field_scholar_hs_zipcode_tcnw	Read, Write	Read, Write	Read	Read	Read	only tcnw-region zip code
	*Counselor	field_scholar_connselor_name	Read, Write	Read, Write	Read	Read	Read
	*Phone number of High School's	field_scholar_high_school_phone	Read, Write	Read, Write	Read	Read	Read
	*College/University Planned to Attend	field_scholar_college_planed	Read, Write	Read, Write	Read	Read	Read	for 4-year renewal
	*Address of College/University planned to attend	field_scholar_college_planned_address	Read, Write	Read, Write	Read	Read	Read	for 4-year renewal
	*Planned Major	field_scholar_college_planned_major	Read, Write	Read, Write	Read	Read	Read
	GPA Weighted	field_scholar_gpa_weighted	None	Read, Write	Read	Read, Write	Read
	*GPA	field_scholar_gpa_unweighted	Read, Write	Read, Write	Read	Read, Write	Read	unweighted GPA
	Ethnic Group (optional)	field_scholar_ethnic_group (controlling field_ethnic_group_contry)	Read, Write	Read, Write	Read	Read	Read
	Ethnic Group Country	field_scholar_ethnic_group_country	Read, Write	Read, Write	Read	Read	Read
	*Extracurricular Activities in HS	field_scholar_extracurricular	Read, Write	Read, Write	Read	Read	Read
	*Household Income	field_scholar_income_range	Read, Write	Read, Write	Read	Read	Read	below $15,000 $15,001 - $30,000 $30,001 - $50,000 above $50,000
	*Number of People / Family size	field_scholar_family_size	Read, Write	Read, Write	Read	Read	Read
	Class Rank	field_scholar_class_rank	Read, Write	Read, Write	Read	Read	Read
	*Home Property Ownership	field_home_property_ownership	Read, Write	Read, Write	Read	Read	Read
	*Rental Property Ownership	field_rental_property_ownership	Read, Write	Read, Write	Read	Read	Read
	*Reference 1: Name	field_reference1_name	Read, Write	Read, Write	Read	Read	Read	* encrypted *
	*Reference 1: Relationship	field_reference1_relationship	Read, Write	Read, Write	Read	Read	Read
	*Reference 1: Phone	field_reference1_phone	Read, Write	Read, Write	Read	Read	Read	* encrypted *
	*Reference 1: Address	field_reference1_address	Read, Write	Read, Write	Read	Read	Read	* encrypted *
	*Reference 2: Name	field_reference2_name	Read, Write	Read, Write	Read	Read	Read	* encrypted *
	*Reference 2: Relationship	field_reference2_relationship	Read, Write	Read, Write	Read	Read	Read
	*Reference 2: Phone	field_reference2_phone	Read, Write	Read, Write	Read	Read	Read	* encrypted *
	*Reference 2: Address	field_reference2_address	Read, Write	Read, Write	Read	Read	Read	* encrypted *
	Transcript	field_scholar_transcript	None	Upload, Download	Upload, Download	Upload, Download	Upload, Download	pdf only, private upload
	*Tax Forms Selection	field_scholar_tax_form_selection	Read, Write	Read, Write	Read	Read		1040 current 1040 previously and w2 current other
	*Tax Form (currently year)	field_scholar_tax_form_currently	Upload	Download	Download	Download	Download	pdf only, private upload
	*Tax Form (previously year)	field_scholar_tax_form_previously	Upload	Download	Download	Download	Download	pdf only, private upload
	*Tax Form (W2)	field_scholar_tax_form_w2	Upload	Download	Download	Download	Download	pdf only, private upload
	*Tax From (Other)	field_scholar_tax_form_other	Upload	Download	Download	Download	Download	pdf only, private upload
	Recommendation 1	field_scholar_recommendation1	None	Upload, Download	Download	Download	Download	pdf only, private upload
	Recommendation 2	field_scholar_recommendation2	None	Upload, Download	Download	Download	Download	pdf only, private upload
	*Autobiography	field_scholar_autobiography	Read, Write	Read, Write	Read	Read	Read	private upload
	*Essay	field_scholar_essay	Read, Write	Read, Write	Read	Read	Read	private upload

	College/University Attended	field_college_attended	None	Read, Write	Read, Write	Read	Read
	Address of College/University attended	field_address_college_attended	None	Read, Write	Read, Write	Read	Read
	Planned Major	field_major_attended	None	Read, Write	Read, Write	Read	Read
	Review Package	field_scholar_review_package	None	Read	Read	Read	Read	[Internal] backend-generated merged pdf
	Region	field_region	None	Read, Write	Read, Write	Read, Write	Read, Write	[Internal] auto-selected or admin--selected region, TAC
	Household Gross Income	field_household_gross_income	None	Read, Write	Read, Write	Read, Write	Read
	Gross Income per Person	field_gross_income_per_person	None	Read, Write	Read, Write	Read, Write	Read
	Interest Income	field_interest_income	None	Read, Write	Read, Write	Read, Write	Read
	Dividend Income	field_dividend_income	None	Read, Write	Read, Write	Read, Write	Read
	Additional Note	field_additional_note	None	Read, Write	Read	Read, Write	Read
	~~?Application Paper file?~~
	Interview Report file	field_interview_report	None	Upload, Download	Download	Upload, Download	Download	pdf, doc, docx, txt
	Interview Score	field_interview_score	None	Read, Write	Read, Write	Read, Write	Read
	Number of Interviewers	field_number_interviewers	None	Read, Write	Read, Write	Read, Write	Read
	Time Spent for interviewing (Hours)	field_interview_hours_spent	None	Read, Write	Read, Write	Read, Write	Read
	Number of Tzu Chen/Commissioner Interviewers	field_number_interviewers	None	Read, Write	Read, Write	Read, Write	Read
	Number of Tzu Chen/Commissioner Interviewers (in Training II)	field_number_interviewers_training2	None	Read, Write	Read, Write	Read, Write	Read
	Number of Tzu Chen/Commissioner Interviewers (in Training I)	field_number_interviewers_training1	None	Read, Write	Read, Write	Read, Write	Read
	Number of General Volunteer Interviewer	field_number_interviewers_voilunteer	None	Read, Write	Read, Write	Read, Write	Read
	Review Score	fied_score	None	Read, Write	Read	None	Read
	Status from Reviewer 1	field_status_reviewer1	None	Read, Write	Read	None	Read
	Score from Reviewer 1	field_score_reviewer1	None	Read, Write	Read	None	Read
	Status from Reviewer 2	field_status_reviewer2	None	Read, Write	Read	None	Read
	Score from Reviewer 2	field_score_reviewer2	None	Read, Write	Read	None	Read
	Status from Reviewer 3	field_status_reviewer3	None	Read, Write	Read	None	Read
	Score from Reviewer 3	field_score_reviewer3	None	Read, Write	Read	None	Read
	Final Score	field_final_score	None	Read, Write	Read	None	Read
	Status Shown to Applicants	field_status_to_applicants	None	Read, Write	Read	Read	Read	status updated automatically by the system
	Status Internal	field_status_internal	None	Read, Write	Read	Read	Read	status updated automatically by the system
	Sequence	field_scholar_sequence	None	Read, Write	Read	Read	Read	sequence number generated by the system
	Status of Financial Document	field_scholar_financial_doc	Read	Read, Write	Read, Write	Read	Read	Not uploaded yet Uploaded, not approved Approved Rejected and wait for update Rule/Trigger
	duplication check	field_scholar_dupcheck	Read/Hidden	Read	Read	Read	Read	[Internal] system generated to do the duplication check if the same applicant has submitted the application more than once. Rule/Trigger

Workflow
Workflow-State-Transition

	Name of State/Transition	State / Transition?	Description	Note
1	[s:draft]	State	applicants are entering and modifying form data and are uploading required douments
2	[s:draft] -> [s:pending]	Transition	applicants marked the "acknowledge" checkbox and are saving the form	transition done by applicants
3	[s:pending]	State	applicants think they are done with all required data and documents. Do we have to review the required documents at this stage?
4	[s:pending] -> [s:complete]	Transition	committee people reviewed all attached documents and there is no missed required documents. The program automatically assigns the applicants/forms to predefined regions.	transition done by committee
5	[s:complete]	State	all required documents are done
6	[s:complete] -> [s:interview]	Transition	committee start the interviewing process. Also manually assign the applicants/forms to designated region if not automatically assigned
7	[s:interview]	State	Interviewing process is on going
8.1	[s:interview] -> [s:qualified]	Transition	Interviewing process is done and the applicant is qualified. Mark the applicant who is qualified for one-time or 4-year.
8.2	[s:interview] -> [s:not_qualified]	Transition	interviewing process is done and the applicant is not qualified
9	[s:not_qualified]	State	the applicant is not qualified
10	[s:qualified] -> [s:notified]	Transition	making the transition will trigger the system to send out email notification automatically
11	[s:notified]	State	mark the applicant whether he/she will come to Tzu Chi's ceremony
12	[s:notified] -> [s:done]	Transition	Tzu Chi's ceremony is done

Regions

	Region Name	Note
1	TCNW, Fresno
2	TCNW, Modesto
3	TCWN, Oakland
4	TCNW, Portland
5	TCNW, Reno
6	TCNW, Sacramento
7	TCNW, San Francisco
8	TCNW, San Jose, Cupertino
9	TCNW, San Jose, Fremont
10	TCWN, San Jose, San Jose
11	TCNW, Santa Rosa
12	TCNW, Seattle
13	TCNW, Stockton
14	TCNW, Ukiah

Scholarship Specification (updated by Jen and Alex on 7/28/2013)

Tzu Chi Scholars Online Application Requirements

1. Applicant Account for Online Submission

a. Each applicant needs to create an online account by entering a user name, password and passing CAPTCHA verification

b. Need to define online applicant account open period (e. g., 12/1/2014 – 3/9/2015).

c. Need online instruction for application procedure

d. Each applicant submits Application Form, Bio, Essay and Financial Document (pdf) through his/her account.

e. Applicant can check application status by logging in his/her online account for the following:

i. Application Paper (as defined in 2.a.i) below, Status – Received check box for receiving status of each item in Application Paper

ii. Interview Process Status - check box when interview is completed

iii. Award Status – check boxes for Awarded and Not Awarded

iv. Status Summary

2. Applicants’ Application Package Organization

a. Each applicant’s application material contains two parts:

i. Application Paper, including

1. Application Form – submitted online by applicants

2. Transcript – mailed in from schools or applicants and uploaded by He-Xin Scholarship Team (need tool for uploading)

3. Financial Document – uploaded by applicants with one of three options

a. The year’s 1040

b. The previous year’s 1040 and the year’s w2

c. other

4. Two Recommendation Letters – mailed in from teachers or applicants and uploaded by He-Xin Scholarship Team (need tool for uploading)

5. Bio – submitted online by applicants

6. Essay – submitted online by applicants

When the Scholarship Team confirms (through Application Paper Status Accepted check boxes) that all six items listed above are complete from an applicant, an Application Paper file shall be automatically generated for the applicant by merging all the above documents into one following the order listed above. The file name shall be of the following convention: Application Number_Region Name_Applicant Last Name_App. The Application Numbers shall be consecutive and generated automatically. In the meantime, an email message shall be automatically sent to Administrator and Regional Scholarship Team Leader to alert them to start interview process for the applicant.

ii. Interview Report - uploaded by Regional Scholarship Team when ready(need uploading tool). One Interview Report file for each applicant.

When the Interview Report is uploaded, an Interview Report file (pdf or doc or docx) shall be generated for the applicant. The file name shall be of the following convention: Application Number_Region Name_Applicant Last Name_Int. The Application Numbers shall be the same as that of the Application Paper file for the same applicant.

b. An Applicant Entry shall be created for each applicant containing the following data fields and files:

i. Application Number

ii. Applicant’s Region (1 of 14 TCNW Regions – 11 branches and 3 Nor Cal Ho Chis), Applicant’s First Name, Last Name, Gender, Date of Birth (mm/dd/yyyy), Parent or Guardian, Counselor, College plan to attend, Planned Major, Ex-curriculum Activities, Household Income Range (4 ranges), Reference 1 Name, Reference 1 Relationship, Reference 1 Phone Number, Reference 1 Address, Reference 2 Name, Reference 2 Relationship, Reference 2 Phone Number, Reference 2 Address, Ethnicity, Home Street Address, City, State, Zip Code, Home Phone Number, Cell Phone Number, e-mail, High School, School Name, School Street Address, School City, School State, School Zip, Weighted GPA, Un-weighted GPA, Class Rank, Family Size, Home Property Ownership, Rental Property Ownership, Application Date (mm/dd/yyyy) – from Application Form

iii. Applicant’s Actual Attending College, Actual Major – to be entered by He-Xin Scholarship Team

iv. Household Gross Income, Gross Income per Person (computed from Gross Income and Family Size), Interest Income, Dividend Income, Additional Note – to be entered by Regional Scholarship Team from Financial Document

v. Applicant’s Application Paper file

vi. Applicant’s Interview Report file.

vii. Interview Score, Number of Interviewers, Time (Hours) Spent for Interview, Number of Tzu Chen/Commissioner Interviewer, Number of Tzu Chen/Commissioner in Training II Interviewer, Number of Tzu Chen/Commissioner in Training I Interviewer, Number of General Volunteer Interviewer – to be entered by Regional Scholarship Team from Interview Report.

*When 2.b.vi is completed, the application status for Interview Process shall be automatically updated in the online Applicant Account, When 2.b.i, ii, iv, v, and vi are completed, an email message shall be sent to alert Administrator and Regional Team to start the review process for the applicant.

viii. Review Score, Pass Y/N for Reviewer 1; Review Score, Pass Y/N for Reviewer 2 and Review Score, Pass Y/N for Reviewer 3 – to be entered by Administrator

ix. Final Score, Pass Y/N

3. User Role and Privileges

a. Administrator

i. Read, Upload/Download/Export – all data fields and files

ii. Write/Change - all data fields

iii. Need a management tool to create user accounts and assign privileges (Read, Write/Change, Download/Export, Upload)

b. He-Xin Scholarship Team Member

i. Upload/Download/Read – all files and data fields except 2.b.viii, 2.b.ix

ii. Write/Change - 2.b.iii, 2.b.iv, 2.b.vii

c. Regional Scholarship Team Member

i. Read, Upload/Download/Export all files and data fields, except 2.b.viii, 2.b.ix for Applicants from assigned Region

ii. Write/Change – 2.b.iv, 2.b.vii for Applicants from assigned Region

d. Reviewer

i. Read, Download/Export – all files and data fields

*The review process will for now follow the current procedure. Administrator will distribute the applicants’ information to the reviewers and the reviewers will send back the review scores to Administrator to enter into 2.b.viii. Future improvement will be implemented in Phase II of this project.

Scholarship process: (provided by Terry, Stephanie, Liwen)

1. 公佈獎學金章程，並開始接受申請 (11/15/12 -3/9/13)

公佈獎學金章程在西北合心區網頁,並傳真或志工親自拜訪學校共600多所高中，包括北加州，西雅圖 (華盛頓州),波特蘭(奧瑞崗州),雷諾(內華達州),愛達荷州,蒙塔納州,懷俄明州,阿拉斯維加斯州

1. Scholarship announcement to all Nor Cal branch offices and begin accepting application.

Posting scholarship application and applying detailed information on Nor Cal Tzu Chi website. Volunteers begin faxing, emailing or visiting about 600 high schools announcing TC scholarship including Nor Cal, Seattle Washington, Portland Oregon, Reno Nevada, Idaho, Montana, Wyoming, and Alaska.

2. 收件 (3/8/13- 3/28/13 TCNW會所)

拆信,並在信封和申請書上標示所屬慈濟支連點及編號，檢查申請文件是否齊全，含：申請表，稅表，成積單，倆份推薦書，自傳，慈濟論文。完整者，進入電子檔.

3/8結止日前我們就集合了10位師姐開始處理信件(拆信,確認齊全,編號,傳真或 scan上傳電子檔,email通知收件.若不齊全,即email 或電話通知補件) . 陸陸續續,花了師姐們兩週辛苦煎熬的時光. 有興趣的人歡迎報各來嚐試 !

2. Receiving Applications (3/8/13 - 3/28/13)

Open envelopes enclosed with applications, labeling each application by branch office, numbering the pages and assign an app number. Checking to see all documents are complete or incomplete. Documents included application, tax return, transcript, 2 recommendation letters, auto-bio, Tzu Chi essay. Upload complete applications into shared folders as electronic files.

Before the due date of 3/9, scholarship team gathered 10 volunteers (sisters) processing these applications. Processing tasks were, unsealed, confirming all docs are in, numbering, fax or scan into share folder, email and/or call incomplete applicants to supplement their docs, email complete applicants confirming application received. It takes 2 weeks of hard work with only Sundays off to finish the processing tasks. If any volunteers who are interested in trying our first phase of scholarship processing, please don't hesitate to join us next year!!

3. 家訪／面談 (3/16/13 - 4/10/13)

各社區志工透過實地深入或拜訪了解學生的家空兄或另約見面時間地點或電話詳談...若遇特殊案例，即可列為社區個案,做深入長期的關懷與協助.

3. Home Visiting/In Person Interview (3/16/13 - 4/10/13)

Volunteers from each local community set up meeting time to visit the applicants either by phone or in person for a better understanding of the applicants' living condition and financial needs. If the applicant and the family shows special needs, they can be considered as a case and receive more of long term assistance.

4. 評審 (3/15/13 - 4/30/13)

審核團隊綜合各組所有申請文件,家訪/ 面談等詳情報告一一討論，做成獎學金得獎人的決議。確定每一份獎學金,能確實幫助這些清貧的學生得到更多機會上學，充實自己的能力,再回饋給社會幫助更多需要幫助的人.

4. Review Committees (3/15/13 - 4/30/13)

Review committees makes decisions based on the application documents received and home interview reports. Committees are making sure that the scholarship is truly going to help students with need through colleges for a higher education and better their skills. Then, return to communities to help others.

5. 通知得獎學校及得獎人 (5/ 1/13－5/ 8/13)

通知各支聯點得獎學生名單,即早通知學生及學校以確定參加"畢業班頒獎典禮"的時間和志工人數.

5. Notifying Scholarship Award Recipients and Their Schools (5/1/13 - 5/8/13)

Notifying all branching office and providing scholarship recipient names. Letting the schools and recipients know about the result ahead of time and confirming number of volunteers attending the senior award nights at the recipients' high schools.

6. 參加各地高中學校頒獎典禮 (5/1/13 - 6/10/13)

各支聯點的社區志工參加得獎學生的學校的畢業班頒獎典禮,並加強和校方及其它組織團體互動,更借此機會將上人愛灑人間的善念,傳播出去到社會各個社區的角落.

6. Attending High School Senior Award Ceremony in Local Area (5/1/13 - 6/10/13)

Volunteers in all branches attending senior award nights to reinforce the relationship with the schools and other organizations. Taking this opportunity to spread our master’s idea of compassion and love and reach out to every corners of the community.

7. 慈濟西北合心區獎學金頒獎典禮 (6/22 /13 TCNW會所)

頒獎典禮之前準備,當天的節目程序表和PPT分享的編排和印製工程,會場內外及舞台佈置,音效/燈光組,三合一組,香積組,參加表演組,獎狀及結緣品準備,和邀請師兄姐參與當日接待/導覽/交通指揮,及各支聯點安排陪伴學生及家人的師兄姐carpool等的事前協調.及當天手語師姐,慈青表演,以及得獎學生上台分享等等.

7. Tzu Chi Northwest Region Scholarship Award Ceremony (6/22/13 at TCNW Campus)

Preparation for the ceremony includes orchestrate a ceremony program with many other supporting teams, program and award certificates printing, Power Point making, making corsages for the attendees a day ahead, setting up stages and decorating, 35 round tables and chair set up with table cloth and table decor with flowers, ordering Tzu Chi Jin Si products and packaging into small gift favors for the awardees, requesting 3-1 photography team, media and lighting, luncheon prep, food team, coordinating performers from hand sign and Tzu Ching (college volunteers)

8. 寄發獎學金支票 (7/1/13 －9/30/13)

依照得獎學生入學許可和課程註冊單, 填妥及簽准請款單, 送財務組核准支票,再寄發獎學金支票及通知書(註明學生姓名,學號, 及支票寄出曰期)至各得獎學生的大學。

8. Distributing Scholarship Checks (7/1/13 - 9/30/13)

Checks will be distributed according to the awardees college acceptance letters and courses registration. Processing team has to confirm that the awardees had included the two documents in order to request for their scholarship checks. Requesting a check from finance department needs to filled out a fund requesting form and signed by the charity group lead. Checks can be picked up after a few days of process in financial office and mail out with a letter to the school stating name of the awardee and schools attending. Each check has to be recorded on the spreadsheet with the awardee's school, major and mail out date for track record.

9,匯集資料, 提供得獎學生名單與西北合心及總會 (9/30/13)

匯集2013所有得獎學生及大學名單, 提供西北合心與總會慈善組,以便日後連繫學生的參考.

9. Forwarding Scholarship Awardees Contact Info to TCNW and TC HQ Charity Teams (9/30/13)

Scholarship team forwards awardee's contact and college information to TCNW charity group leads for future follow up as part of scholarship program's mission to maintain a relationship and planting a compassionate seed with the awardees. Follow up with the awardees by our TC offices in the area where they're attending school is a good way to carry out that mission.