Printable View
Hi Matt, is there any way this could be expanded to use the UPN as well as / instead of the Admission Number please? We currently have our pupil UPNs in AD anyway so our Parental Portal knows who they are, and now I need to bulk-import their Exchange e-mail address as defined in AD into SIMS. It would be very handy to use the UPN as the common reference point as I can then dsget the UPN and e-mail address from AD and use it with SIMS Bulk Import to do the lot all at once.
The issue is it uses the SIMS Business Objects and they don't currently allow you to find a students (PersonID) using only the UPN. If you go into SIMS and try searching for a student - you can't find them by UPN. The ideal solution is to log a CR with Capita and ask them to add it to SIMS - assuming it doesn't already exist. Maybe @PhilNeal can find out for us :)
The only workaround I can think of is to pull out all the students just to get a list of UPN numbers!!! Which is rather messy to say the least. There was a discussion somewhere as what reference number to use for sync'ing with SIMS - should it be the UPN, AdmissionNo, PersonID or whatever. I know I've had historically issues with UPN as the school generates a new UPN only for the LA to find out that, that student already has UPN from a previous school or they've entered the UPN incorrectly.
I exported an list of admission numbers and UPNs and used a lookup to make the CSV for import. I agree, potentially messy solution, but it worked for me.
Okay, thanks.
How do you mean "used a lookup"? In AD, SIMS or the Import Tool?
I'm guessing it was an Excel lookup.
as for using UPNs the Capita patch for importing student emails does use UPNs. it has two options, either provide a list of students by UPN and email address, or escape any specified students and apply a mask to all. worked really well for me.
it's a bit of a faff having to apply the patch. then apply another patch to remove the tools / logs, but it worked.
Right, I see, cheers for that info. Do you know how I might go about looking for that?
I can get a list of UPNs/e-mails from AD with: dsquery user "OU=Pupils,OU=*** Users,DC=**********,DC=local" -limit 0 | dsget user -pager -email> "%UserProfile%\Desktop\Pupils.txt"
So that would work once I clean the text file up a little.
If you want the patches from Capita, you need to request them from your support unit. It's called the bulk email import utility. patches 16799,16800.
But, you might as well use Matt's tool.
Export a list of admission numbers and UPNs from SIMS into Excel. open your text document in Excel and make sure the columns are separated. Then stick in a lookup formula, you'll want a VLOOKUP, with the first column containing UPN and second column containing the admission number. Make sure the fourth parameter is FALSE. And use $A$3:$C$50 format to specify the table so when you copy down the reference doesn't change.
Right, thanks for that! :)
worked for me ;) - foul or not. :p luckily, i just want them all to fit a strict mask, so no need to supply a list.Quote:
Originally Posted by matt40k
Hi Matt,
When I run this it all matches up but then when the HTML file opens it says "Import Failed" with nothing in the notes.
Any idea what this is?
Thanks
Lewis
Going to have a wild stab in the dark and ask, is it UDF you are importing?
Thanks for the speedy reply! No its importing emails using the admission number.
Lewis
Weird, can you ping over the log file - matt [at] matt40k.co.uk please
Will do. Thank you!