Edline.SUPwd.Merge(All.YearStart)

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 [Back] to our School" tickets with students' BCPS usernames and passwords, and //either// Edline Activation Codes //or// Edline account screen names and //if applicable// email address associated with Edline accounts==


 * Before you Start**, you will need
 * Tech Liaison assistance to access and to export from DOT student password reset website.
 * Ability to create New Variable Reports in STARS. //Your Principal or one of your Assistant Principals should be able to assist with this if your username does not have Variable Report permissions.//
 * Edline SuperUser assistance to view all student users and export all student user data.
 * 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: All Students
> //If all students in your building do not have a "HR" class, consider exporting everything, cutting out the "Current Schedule", filtering for unique "AccountID", and then looking up the first Teacher/CourseNo/MeetingTime for each, or filtering for a "MeetingTime" common to all students.// ==
 * 1) In **STARS**, create a **New Variable Report**, called "All Students" with the following Report Fields:
 * //from "Student Demographics" category,// ****StudentID**** NEW! //BCPS is using the StudentID instead of the AccountID for Edline//
 * //from "Student Demographics" category//, **FirstName**
 * //from "Student Demographics" category,// **LastName**
 * //from "Current Schedule" category,// **TeacherFull**
 * //from "Current Schedule" category,// **MeetingTime**
 * //from "Student Demographics" category,// **AccountID**
 * 1) Add the following Selection Criteria to that Variable Report: **StudentEnrollment.MeetingTime Contains HR**
 * 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 "**AllStudents.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?//
 * 1) **Open** in Excel, delete all leading and trailing junk rows of data so that **Row 1** of the worksheet contains header information, and row 2 is the first row of data. //Note: If you did not filter for "HR" classes in STARS, you may end up with thousands of rows--more than one row per student. If this is the case, it may be necessary to copy the worksheet, cut out extraneous schedule information (last three rows), filter for Unique only, copy filtered to a new worksheet, and save only __the filtered and cleaned__ (no hidden rows) worksheet as the Master "All Students.xls" worksheet.//

==

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**" //Uncheck the rest of the boxes//.
 * 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.

Create File: Students' Edline Account Usernames
> //NOTE: These directions test OK in Internet Explorer 7,...// > //Essentially, change the file extension/type, change it from a .txt file to an unknown-type file.// > > //Explanation: The ends of the dataset are regularly delimited and predictable, but the beginnings are unpredictably irregular [students with blank spaces in first and/or last name get extra initial columns])//. > Use the formula below as a guide. > **=IF(J2="A",G2,IF(I2="A",F2,IF(H2="A",E2,IF(G2="A",D2,""))))** > //How it works: Excel checks for an "A" (Activated), which means it's found the last bit of data in that student's row. If it finds an "A", it returns what appears **3 columns before** the "A", which will be the students' StudentID [needed for indexing later on], etc. Failure results in an empty cell. Using the// [Ctrl+"up arrow"] and [Ctrl+"down arrow"] //keystroke combination (which jumps to the first or last item in a column of data respectively), or by filtering the row for "Blanks", it's easy to catch broken formulas.// > > Use the formula below as a guide. > **=IF(J2="A",H2,IF(I2="A",G2,IF(H2="A",F2,IF(G2="A",E2,""))))**
 * 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: "**Activated**"
 * 3) Change the view from "Show 50" to "**Show All**"
 * 4) //When the page is finished loading,// from "**File**" in the menu bar of your internet browser, select "**Save As...**"
 * 1) Change "Save as type" to "**Text file (.txt)**"
 * 2) Name the file "**Edline.StudentUsers**" and save it to the desktop.
 * 3) **Open** the file in Notepad. Delete leading and training junk rows of data.
 * 4) In Notepad, from "**File**," select "**Save As**", change the "Save as type" to "**All files**", name the file "**Edline.ScreenNames**" and save it to the desktop.
 * 1) **Close** Notepad. //Suggestion: **Delete** the "Edline.ScreenNames.txt" **text** **file** from your desktop to avoid confusion!//
 * 1) **Open** Excel.
 * 2) From "File," select "Open" and change the type of file Excel searches for from "Microsoft Excel files (.xls, .csv, //etc.//)" to "All files *.*". Open the "Edline.ScreenNames" //(unknown-type)// file.
 * 3) When prompted, select both commas and spaces as delimiting characters.
 * 4) Delete any remaining leading and trailing junk rows of data so that **Row 1** of the worksheet contains header information, and row 2 is the first row of data.
 * 5) Save the worksheet as "**Edline.ScreenNames .csv **". //Suggestion: **Delete** the "Edline.ScreenNames" **unknown-type file** from your desktop to avoid confusion!//
 * 6) Insert two new columns before the existing Column A. In the newly inserted Column A, look for the letter "A" in the 10th, 9th, 8th, 7th, and 6th columns. If found, lookup backwards three columns to return an indexing variable, the student's StudentID.
 * 1) In the newly inserted Column B //(immediately to the right of the StudentIDs you just had Excel find)//, search for the "A" in the 10th, 9th, etc. columns and return what's **2 columns before**, the student's Edline screen name.
 * 1) **Fill down** Columns A and B to propagate the formulas. //There should be blanks for errors. Column A should contain Students' IDs, and Column B students' Edline screen names.//
 * 2) **Save** and **close** "Edline.ScreenNames.csv". //The looked-up values will be preserved, but the formula will be lost unless you save as an ".xls" file (Excel workbook format).//

Create File: Students' Edline Account Email Addresses
> //To do this quickly, click on any visible cell, use the "Ctrl"+"A" keyboard shortcut to select all visible data, the "Ctrl+"C" keyboard shortcut to **copy all** filtered [visible] data (//odd dancing outlines will appear around groups of filtered cells)//. From "Insert," select "Worksheet." Click to select cell A1 in the new worksheet. From "Edit," select "**Paste** **Special**," highlight the "**Values**" radio button, and click "OK".// >
 * 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: "**Activated**"
 * 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 "**Download all User Data**" button.
 * 5) //When prompted//, click the "**Save**" button.
 * 6) Change the "Save as type" to "**All files**"
 * 7) Name the file "**Edline.StudentUserData .csv **" and save it to the desktop.
 * 8) **Open** in Excel, delete any leading and trailing junk rows of data so that **Row 1** of the worksheet contains header information, and row 2 is the first row of data. //You may need to add Headers if they are not in the export. On 08/18/2010, the data appeared to be as follows:// School#, StudentID, LastName, FirstName, Grade#, UserType, EmailMessaging?_0(yes)or1(no), EmailAddress, VoiceMessaging?, Voicemail_Phone#, TextMessaging?, TextMessage_Phone#
 * 9) From "**Data**," select "**Filter**" and then "**AutoFilter**"
 * 10) In the email column, filter for "**(NonBlanks)**". //This is usually the last in the list.//
 * 11) Copy all filtered data and paste to a clean worksheet (//to eliminate hidden rows//).
 * 1) **Save** and **close** the "Edline.StudentUserData.csv" file. //Only the active (which should be the filtered and newly copied) worksheet will be saved.//

Combine the information from all files into a single Workbook and then onto a single Worksheet
>> > **=** IF(ISERROR( **VLOOKUP(A2,Student_AD_Accounts!A$2:G$1641,7,FALSE** ) ),"",VLOOKUP(A2,Student_AD_Accounts!A$2:G$1641,7,FALSE)) // What's in //**red** // is the essential bit. //// How it works: Excel checks to see if the formula is going to fail to find a password, and if it will fail, a blank space is entered in the cell (instead of a "#N/A" error); otherwise, if the lookup succeeded, Excel does the "vertical lookup" and puts the found value in the cell. // >> //The "**$**" simply locks the first and last cell so that when you fill down the formula--see step 7, below--the reference array does not also descend a row with the lookup value.// > > ** = **IF(ISERROR(** VLOOKUP(A2,Edline.ScreenNames!A$2:B$948,2,FALSE) **),"",VLOOKUP(A2,Edline.ScreenNames!A$2:B$948,2,FALSE)) > > ** = **IF(ISERROR(** VLOOKUP(A2,Edline.StudentUserData!B$2:H$799,7,FALSE) **),"",VLOOKUP(A2,Edline.StudentUserData!B$2:H$799,7,FALSE)) > ** = **IF(ISERROR(** VLOOKUP(A2,Edline.ActivationCodes!A$2:F$685,6,FALSE) **),"",VLOOKUP(A2,Edline.ActivationCodes!A$2:F$685,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 from Tech Liaison), "**AllStudents.xls**" (STARS export), "**Edline.ActivationCodes.csv**", **"Edline.StudentUsers.csv**" and **"Edline.StudentUserData.csv**". **Move/Copy** the worksheets to a single Excel workbook. //After you Move all to a single workbook, save it, and then you may delete all other .csv and .txt files created so far from your desktop.//
 * 2) It's easiest to work from the **STARS export file**, which should be called "AllStudents.xls". Select the "Sheet 1" tab in that workbook, //which should be the one with the data//. Rename "Sheet 1" as "**MASTER**".
 * 3) **CHECKPOINT:** You should now be working with only **1** Excel Workbook file called "**AllStudents.xls**" with the following **5**Worksheets
 * "**MASTER**" which contains the information exported from STARS (//all students in the building, junk header rows deleted so 1st row is header, 2nd row is data)//
 * "**Edline.ScreenNames**" which has the as the students' StudentIDs in Column A and the student's Edline screen names in Column B
 * "**Edline.StudentUserData**" which contains //at least// the students' email addresses, and //possibly also// students' Voicemail and TextMessaging phone numbers (//for schools who purchased the messaging module)//
 * "**Edline.ActivationCodes**" which contains unactivated students' activation codes
 * "**Student_AD_Accounts**" which contains the students' BCPS usernames and passwords
 * 1) //To make it easier to keep following these directions,// in the "**MASTER**" worksheet, **rearrange columns** so they are in this order: (//Column A//) StudentID, (//Column B//) LastName, (//Column C//) FirstName, (//Column D//) Grade, (//Column E//) TeacherFull, and (//Column F//) AccountID. Delete data in any other columns of the MASTER worksheet.
 * 2) Label the first blank column to the right of the AccountID (//Column G//) with the header **Password**.
 * 3) In the first data cell in this column (//cell **G2**)//, lookup the student's password. Use the following formula and explanation below as a guide:
 * //where// **A2** is the cell containing the first student's StudentID on the MASTER (//STARS export//) worksheet
 * //where// **Student_AD_Accounts** is the name of the worksheet exported from the Department of Technology [DOT] student password reset website [DOT export worksheet]
 * //where// **A2:G1641** is the range (//7 columns)// of data on the DOT export worksheet with all StudentIDs in the first column, and all Passwords in the seventh column.
 * //where// **7** is the number of the column in the reference array (//selected from DOT 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. NEW //The **TRIM** function is no longer necessary, since the StudentID is a numeric (and not alphanumeric) variable--woot!//
 * 1) **Fill Down** the formula.
 * 1) Label the next blank column (//to the right of the newly looked up Passwords//) with the header **Edline_SN**
 * 2) In the first data cell in that column (//this should be cell **H2**//), lookup the student's Edline screen name using the following formula as a guide:
 * 1) **Fill Down** the formula.
 * 1) Label the next blank column (//to the right of the newly looked up Edline screen names//) with the header **Edline_email**
 * 2) In the first data cell in that column (//this should be cell **I2**),// lookup the student's Edline email address using the following formula as a guide:
 * 1) **Fill Down**the formula.
 * //Suggestion: Schools with Voicemail and Text messaging options may wish to add additional lookups for those phone number//
 * 1) Label the next blank column (//to the right of the newly looked up Edline email addresses//) with the header **Edline_AC**
 * 2) In the first data cell in that column //(this should be cell **J2**)//, lookup the student's activation code using the following formula as a guide:
 * 1) **Fill Down** the formula.


 * Distribute** to the student via emails to the teacher [copy and paste the Spreadsheet columns into a form email] or by Mail Merging with a [|template] and printing to a Oce or SAVIN copier. (//Remember that the "MASTER" worksheet in the workbook now contains __all__ the needed data if you're Mail Merging! The Mail Merge wizard will ask you to select a single worksheet from the workbook )//

Not sure how to Mail Merge using Microsoft Word? Check out this tutorial. If something is //definitely// not working properly in the directions, please email Hilary Short and let her know (//and/or edit the page//), thanks!