Edline.StudentUPwd.Merge

It's always dependent on population fluctuation, but at Kenwood High School, we try to get logins, passwords, and Activation Codes for "New Entrants" sent out //en masse// every Friday for the first quarter, and every other Friday for the remaining three...

Create "Welcome to our School" tickets with students' BCPS usernames and passwords, and Edline Activation Codes

 * Before you Start**, you will need
 * Tech Liaison assistance to access and to export from DOT student login lookup website.
 * Ability to create New Variable Reports in STARS. //Your Principal should be able to assist with this.//
 * Intermediate/Advanced knowledge of Microsoft Excel //or really good "following directions" skills.//

Create File: Students' BCPS Usernames and Passwords

 * 1) **Export** a list of all student usernames and passwords in your building from the Department of Technology site. //Directions for Tech Liaisons are [|here]--authorized BCPS login required for access.// Leave this file named its default "Student_AD_Accounts.xls" and save it to the desktop.

Create File: Students Newly Enrolled
> //where// DATE is the most recent date Edline student logins were distributed using this Variable Report method. //NOTE: To run for the entire building, omit this selection criteria step.//
 * 1) In **STARS**, create a **New Variable Report**, called "[|New Students, need logins]" with the following Report Fields:
 * //from "Student Demographics" category,// **AccountID**
 * //from "Student Demographics" category//, **FirstName**
 * //from "Student Demographics" category,// **LastName**
 * //from "Current Enrollment" category,// **EntryDate**
 * //from "Current Schedule" category,// **TeacherFull**
 * //from "Current Schedule" category,// **CourseName**
 * 1) Add the following Selection Criteria to that Variable Report: **StudentEnrollment.Entry Date Greater Than or Equal To DATE**
 * 1) Change the Format Output by selecting the **MS Excel** radio button. //Leave the "Save Report settings for later use" checkbox checked.// Click the **Print** button, and **Save** the exported file to the desktop. Call the file something memorable, like "New Students.xls".
 * TROUBLESHOOTING (disappearing Excel exports in Internet Explorer): If the export window appears, loads, and then disappears, try printing the report a second time; however, before clicking the "Print" button, hold down the "Ctrl" key on your keyboard. Keep holding down the Ctrl key until prompted to Open/Save the report. //No idea why this happens or works...newly imaged machines 6.9.7 and some Admin-imaged machines printed reports to Excel just fine, and then after a while, started failing, but the Ctrl solution works--thoughts?//

Create File: Students' Unused Edline Activation Codes
> Select either "Grouping" radio button and click the "**Next >**" button.
 * 1) **Login to Edline** as a SuperUser. From "Command Center," select "**Manage Users**"
 * 2) At the bottom of the webpage, search for all users of type: "**Students**" with status: "**Unused**"
 * 3) Change the view from "Show 50" to "**Show All**"
 * 4) //When the page is finished loading,// click the "**Select All**" link to check all the boxes, scroll down and click the "**Print Activation Codes**" button.
 * 5) //Since there are only students on the list, it doesn't matter how they're grouped.//
 * 1) Select the "**As data I can copy into a mail merge program**" radio button and click the "**Print >**" button.
 * 2) //When the page is finished loading//, highlight all the text on the webpage [**Ctrl+A**], and copy it [**Ctrl+C**].
 * 3) Open a **new Notepad file**. //The Notepad program is usually found in Start->Programs->Accessories->Notepad// __or__ (//%SystemRoot%\system32\notepad.exe//)
 * 4) Paste [**Ctrl+V**] the data from the webpage into the new Notepad file.
 * 5) In Notepad, from "**File**," select "**Save As**"
 * 6) Change the "Save as type" to "**All files**"
 * 7) Name the file "**Edline.ActivationCodes .csv **" and save it to the desktop.
 * 8) **Close** Notepad.

Combine the information from all files into a single Workbook and onto a single Worksheet
> ** = IF(ISERROR( VLOOKUP(TRIM(A2),Student_AD_Accounts!F$2:G$1745,2,FALSE) ),"",VLOOKUP(TRIM(A2),Student_AD_Accounts!F$2:G$1745,2,FALSE))** > //What's in red is the essential bit. The rest simply puts blanks in the place of missing information instead of a "#N/A" error.// >> //For some reason, spaces occasionally appear in STARS's AccountIDs--the **TRIM** function is necessary to eliminate them--see also the FALSE explanation, below.// >> //The "**$**" simply locks the first and last cell so that when you fill down the formula--see step 5--the reference array does not also shift with the lookup value.// > ** = IF(ISERROR( VLOOKUP(TRIM(A2),Edline.ActivationCodes!A$2:F$514,6,FALSE) ),"",VLOOKUP(TRIM(A2),Edline.ActivationCodes!A$2:F$514,6,FALSE))** > //What's in red is the essential bit. The rest simply puts blanks in the place of missing information instead of a "#N/A" error.//
 * 1) **Open** all exported files in Microsoft Excel: "**STARS_AD_Accounts.xls**" (students' BCPS usernames and passwords), "**New Students**" (STARS export of students enrolled after DATE), and "**Edline.ActivationCodes.csv**". **Move/Copy** the worksheets to a single Excel workbook.
 * 2) It's easiest to work from the **STARS export file**. Select the "Sheet 1" tab in that workbook, //which should be the one with the data//.
 * 3) Insert a **new column** to the right of the AccountID, and give it the header **Password**.
 * 4) In the cell to the left of the first student's name in your new Password column (//Delete the three rows of junk header STARS adds, and this should be cell **B2**)//, lookup the student's password using the following formula:
 * //where// **A2** is the cell containing the first student's AccountID on the STARS export worksheet
 * //where// **Student_AD_Accounts** is the name of the worksheet exported from the Department of Technology [DOT] student login lookup website
 * //where// **F2:G1745** is the range (//two columns)// of data on the DOT student login export worksheet with all StudentIDs in the first column, and all Passwords in the second column.
 * //where// **2** is the number of the column in the reference array (//selected from DOT student login export worksheet//) which contains students' passwords
 * //where// **FALSE** allows for error messages (//rather than junk data)// to be returned if the AccountID is not found __exactly__ in the DOT student login worksheet as it appears in the STARS export worksheet. //If the TRIM function is not used on the lookup value, because of trailing and/or leading spaces in STARS, many "#N/A" errors will be returned.//
 * 1) **Fill Down** the formula to propagate the lookup to the rest of the students in the STARS export worksheet. //Because the STARS Variable report pulls every student multiple times, you should see the same password copied for each student. If not, go back and check the formula for Step 4.//
 * 2) Insert a **new column** to the right of the BCPS password, and to the left of the student's First Name. Give it the header **EdlineActivationCode**
 * 3) In the cell to the left of the first student's name in your new EdlineActivationCode column //(this should be cell **C2**)//, lookup the student's password using the following formula:
 * //where// **A2** is the cell containing the first student's AccountID on the STARS export worksheet
 * //where// **Edline.ActivationCodes** is the name of the worksheet copied from Edline's webpage
 * //where// **A2:F514** is the range (//six columns//) of data on the Edline Activation Code worksheet with the StudentIDs in the first column and the Activation Codes in the sixth column.
 * //where// **6** is the number of the column in the reference array (//selected from the Edline Activation Code worksheet)// which contains students' passwords
 * //where// **FALSE** allows for error messages (//rather than junk data//) to be returned if the AccountID is not found __exactly__ in the Edline Activation Code worksheet as it appears in the STARS export worksheet. //If the TRIM function is not used on the lookup value, because of the training or leading spaces in STARS, many "#N/A" errors will be returned.//
 * 1) **Fill Down** the formula to propagate the lookup to the rest of the students in the STARS export worksheet.


 * Distribute** to the student via emails to the teacher [copy and paste the information into a form email] or by Mail Merging with a [|template] and printing. (//Remember that Sheet 1 contains __all__ the needed data if you're Mail Merging!)//

Not sure how to Mail Merge using Microsoft Word? Check out this tutorial.