Video Transcript

Let's take a look at the intermediate project scenario that you'll be working from. What you're interested in doing here is assessing, one by one, whether respondents seem like a good fit for the program they've chosen, including whether they've met the basic eligibility criteria, and to assess at the same time their scholarship eligibility. Basic eligibility is determined first by whether they provided a letter of recommendation. If not, you want to skip over the record completely. Basic eligibility will be determined second by the criteria they checked in the "I certify" statements of questions 11, 18, and 21. They must certify all of the statements to be eligible. Eligibility is also determined in part by funding needs. Scholarships are only available to those who earn less than sixty thousand dollars per year, so, if respondents earn sixty thousand per year or more, and also indicate that they can only enroll if they receive scholarship funding, then they're no longer eligible. Beyond that, there are a number of subjective judgment calls to be made based on the respondents' statements of why they're a good fit for the program and what they're struggling with and what they hope to gain. If they applied for the group coaching program, there is an additional rating system that can be used to tally a score of sorts for fit with the program. If they applied for the boot camp, they should have given details of a project they'd like to work on. And then there's the scholarship information. You don't really want to see this at all if they didn't request funding. Otherwise, you want to know if they can only enroll if they receive scholarship funding for everything requested, followed by a list of only what they've requested. And then you want to know what they said about requesting funding from other sources and what they selected as their annual income range. You'll end the application information by listing any other additional information or documentation that they provided, and make a note of "none" if none was provided. And you'll wrap up the entire document with some identifying information about the applicant. Finally, to make this easier to review, you'll sort all of the responses by program name so you can easily review all of the responses for that program together. So, in completing the intermediate project, you'll develop the skills to: conditionally skip over entire responses (as an example, any that didn't include a letter of recommendation), conditionally display entire sections of a document based on data values (as an example, if a respondent did not request scholarship funding, you won't display that section of the review document at all for that respondent), conditionally display fields (as an example, respondents might have uploaded additional documents for consideration and they might not have - you'll link to supporting documentation only if they provided it and list none otherwise), conditionally display predetermined text based on data values (as an example, the eligibility criteria you'll list for reference on the document so you and any other reviewers don't have to look that up elsewhere as part of their review). And that will complete the intermediate project! Altogether, this is a little complex, but, by combining everything into a single document like this, you're giving yourself the ability to only have to change things in one spot - this single mail merge template - if you change your review process in the future, instead of having to update three separate mail merge template documents, one for each program. That's a lot to do, but we'll take this step by step and build up. I'll see you in the next video!