How do I update my pupil setup without UPNs?
Updated 2 years ago
by
Mark Welham
The best way to update your pupils if your setup currently does not have UPNs would be to link your Purple Mash setup to your MIS System using Wonde or Groupcall's Xporter-on-Demand. This is the quickest, most efficient, and hassle-free way to ensure that your school setup is constantly up-to-date. Once you have synced your setup to your MIS system, any updates or amendments to your setup in future will normally only take a minute or two.
The alternative to this would be to use the End-of-Year migration wizard if your classes are set up correctly and are assigned to year groups. This is recommended if you have a one-form entry school. However as you approach two-form entry or higher, and classes may become mixed, this can also increase in complexity and take more time.
This guide will focus on ‘untangling’ a setup where children are not in their correct classes (or simply need to be moved up to new classes), or where UPNs are not assigned to each pupil in the class and may be causing duplicates. You can read about the importance of UPNs and their purpose in a Purple Mash setup in the guide: Are UPNs required?
However, once the setup is fixed and UPNs are added, future setups in the new academic year will become a much easier task.
The screenshot below is an example of a setup where the UPN field is missing for each pupil.
With a setup like the one above, you would not be able to import updated pupil information (to change their classes for example) unless the importer can match the imported details over to either the UPN field or the Username field. This is because these two fields must be unique, and without them you may run the risk of creating duplicates.
Below is the updated spreadsheet with the new information we want to import into Purple Mash. As you can see in cell C3, pupil Anita Schaaf is moving up to Year 6 in the Mahogany 6 Class. With reference to the screenshot above, Anita Schaaf used to be in the Year 5 class Teak 5.
However, in our new data, the UPN field is missing, and we do not know their usernames. For this reason, you will receive the error below when trying to import the pupils.
This is because the data in the spreadsheet could not find a username or a UPN to match up to and there is no unique field. The red screen indicates that the accounts could not be created at all and therefore nothing happens.
Another problem is that if your new spreadsheet does have UPNs, as in the screenshot below:
Then importing this information will simply create new accounts and will not match the information over to existing accounts. This will create duplicates once you run through the import wizard like so:
A green screen as above indicates the creation of new accounts. What this means is that if we go back to our setup after carrying this out, you are confronted with the following duplicates:
As you can see in the screenshot above, there are multiple pupils with the same name. Some of these have UPNs and some of them do not. The duplicate accounts that we just imported have a number at the end of their username because we used the same Forename + surname initial username format and usernames must be unique. This is a deliberate feature to ensure that two entirely different pupils with the same name still have unique usernames, however it also helps to spot duplicate accounts as you can sort by the Name column in 2Lasso+.
Ideally, we would want to see a yellow screen which indicates that although no new accounts have been created, the existing accounts have been updated with the new information. You may also see some green accounts which means that some new pupils have been added to your setup, but they most certainly should not all be green and absolutely none of them should be red.
To do this, we must carry out some procedures in Excel to match the old information currently on your setup to the new information in your spreadsheet, as the import wizard is not clever enough to do this on its own and we must bridge the gap.
The first step is to select all pupils in your pupil tab and export them to an excel spreadsheet using the purple button on the top right of the screen as below:
In the dialogue box, ensure that you select Excel list from the Export type dropdown menu, then click OK.
You will then be presented with a spreadsheet similar to the one below.
However, we only need to work with some of these columns and not all of them, so delete all of the columns and only keep the ones below.
Next we need to move this spreadsheet and put it next to our new spreadsheet of information so that we can compare the two side-by-side. To do this, highlight the table and copy it. A quick tip to select-all is to click any cell in the data and tap Ctrl+A on your keyboard, then then Ctrl+C to copy it.
Paste (Ctrl+V) your data into the spreadsheet like so with the new information on one side and separate to the old information you have just exported. In the screenshot, the old information is on the right, and the new information we want to import is on the left.
There are distinct characteristics of each spreadsheet above. The new information on the left has UPNs but has no usernames and it does not exist in Purple Mash yet. The old information on the right has usernames but no UPNs, and does exist on Purple Mash.
What we need to do is match the correct UPN from the new information on the left, to the existing accounts on the right so that we can update any existing accounts with the correct UPN.
To do this, we need to find a common value between both spreadsheets that is unique and somewhat reliable. The best field to use is their full name.
Create two additional columns to the left of each table of data like so:
Then, in cell A2, you will need to get the values of the first name and last name column and concatenate the data into the corresponding cell.
The formula used for this in cell A2 in the example is =LOWER(TRIM(B2&C2))
You will also need to do the same for the other data. In cell H2, use the following formula =LOWER(TRIM(I2))
The next step is to click and drag this formula down to the bottom of your spreadsheet of data to fill in all the cells with the correct formula. (Please note that the cell values may vary based on where you have pasted your data in Excel.)
Your data should now look like this:
However, it is still not perfect and there are spaces, apostrophes, and hyphens that need removing. This can be done quickly. First select all of the data in the entire spreadsheet (both tables) and copy it. Then paste it into the top left A1 corner cell, ensuring that you have selected Values under the Paste Options as below:
This ensures that the formulae has been removed from the cells. The next step is to select all of column A and all of column H like so, and hit Ctrl+F on your keyboard. Then click on Replace. This will show the following interface:
In this interface, we can search for specific characters and replace them. We need to replace all spaces, apostrophes, and hyphens. First type in a single space character in the Find what box, and click the Replace All button. Repeat this for the hyphen symbol, and repeat this again for the apostrophe symbol. Your data should now look like this:
The two columns A and H should now match quite closely and allow us to carry out a VLookup to find the corresponding UPN that we can populate into column L. However, to make the formula simpler, we should first name the ranges of both data sets.
The data on the left is the new data so this can be called NEWDATA, and the existing pupil accounts on the right can be called OLDDATA. To name a range, select it entirely and change the value in the top right as shown below, then use the return key on the keyboard to confirm the change.
The next step is to carry out the VLookup formulae as below in cell L2:
=VLOOKUP(H2,NEWDATA,5,FALSE)
This formula may vary depending on your cells, however what it is doing is looking for the ID in H2, and trying to find it in the whole of the NEWDATA range, then locating the fifth cell in that whole range related to the cell that contains the value from H2. It is searching for “anitaschaaf” in the whole of column A, and finding the UPN that belongs to that ID.
One consideration is that not all of the pupils may be returning to the school and some may have left before the new academic year. (This could be old year six pupils moving on to secondary school for example). This means that the leavers will not appear in the new spreadsheet and we will not be able to find their UPN from the new spreadsheet, so their UPN field will give an N/A value like so if they do not exist:
To further amend this formulae, I have encompassed the VLookup in an =IFNA() to make sure that if it cannot find the pupil, the cell stays blank, like so:
=IFNA(VLOOKUP(H2,NEWDATA,5,FALSE),””)
This is the result when the formula is dragged down and copied to the rest of the table
We can now re-import this data into Purple Mash like so:
Now all 80 pupils in the spreadsheet have been updated with UPNs. If you sort by UPN, you can see which pupils have not been assigned a UPN and delete them accordingly, as they are likely leavers.
Now that the UPNs have been added onto the existing Purple Mash accounts, the next step is to import the rest of the data on the left hand side (the new data) so that the classes can be updated. This may also add any new pupils in the spreadsheet, like so:
This now completes your setup. UPNs have been assigned to existing accounts, leavers have been deleted, any new information has been updated, and any new pupil accounts have been created.
