pconkie Posted January 17, 2019 Share Posted January 17, 2019 (edited) Step 4 Add and run the macro. Here is the macro to download.Exam Timetables Excel macro.txt Watch the video to see where/when to paste this and how to run the macro. It is important that when you create a new tab for the formatted csv, that you call that tab "Formatted" with a capital F. You must also switch back to the tab with the data on it before you run the macro! Ignore any run-time errors and check the formatted tab. Do not change then name of any of the column headers as these are used by the frog widget! running the macro.mp4 Edited January 17, 2019 by pconkie Link to comment Share on other sites More sharing options...
pconkie Posted January 17, 2019 Share Posted January 17, 2019 Step 5 Go back to SIMS (New Report) and create a report that lists each students Exam Number and their corresponding UPN. Add this to a new tab in the excel file. add lookup.mp4 Link to comment Share on other sites More sharing options...
pconkie Posted January 17, 2019 Share Posted January 17, 2019 Step 6 Replace Exam number on the formatted tab with the students UPN using the following VLOOKUP formula... The new column must be called UPN. complete lookup.mp4 Link to comment Share on other sites More sharing options...
pconkie Posted January 17, 2019 Share Posted January 17, 2019 Step 7 By this point the data should be anonymised. Use the online tool at the following site: http://www.convertcsv.com/csv-to-json.htm to convert it from csv to keyed JSON. Use column 8 (UPN) as the key as shown in this video... Once completed you can use ctrl+a and ctrl+c to copy the JSON ready to paste into the widget. csv to json.mp4 http://www.convertcsv.com/csv-to-json.htm is a third party (external) site. Paste your csv at your own risk. Link to comment Share on other sites More sharing options...
ADT Posted January 17, 2019 Author Share Posted January 17, 2019 Phew thats some guide... not for the faint of heart!!!! Thanks as always Conkie!!! Link to comment Share on other sites More sharing options...
gbligh Posted January 21, 2019 Share Posted January 21, 2019 Thank you so much Paul, @pconkie, for this. I sat down with our data manager today (Excel wiz) and we worked it out using your instructions. All looks great, and will really help the kids out. Cheers Link to comment Share on other sites More sharing options...
ADT Posted January 22, 2019 Author Share Posted January 22, 2019 10 hours ago, gbligh said: Thank you so much Paul, @pconkie, for this. I sat down with our data manager today (Excel wiz) and we worked it out using your instructions. All looks great, and will really help the kids out. Cheers @pconkiedoes it again!!! Now to persuade our data/exam team it's a good idea and isnt as difficult as it looks......... its not is it @gbligh!!!! Have you got them to do it or are you doing some of it? Link to comment Share on other sites More sharing options...
gbligh Posted January 22, 2019 Share Posted January 22, 2019 31 minutes ago, ADT said: @pconkiedoes it again!!! Now to persuade our data/exam team it's a good idea and isnt as difficult as it looks......... its not is it @gbligh!!!! Have you got them to do it or are you doing some of it? The only bits I did was converting the CSV to JSON and the copy and paste job! Link to comment Share on other sites More sharing options...
ADT Posted January 22, 2019 Author Share Posted January 22, 2019 1 minute ago, gbligh said: The only bits I did was converting the CSV to JSON and the copy and paste job! OK... so now to work out how to sweet talk the team........ and plan when to do it!! Link to comment Share on other sites More sharing options...
pconkie Posted January 22, 2019 Share Posted January 22, 2019 It should be so much simpler than it is! We have already had a few timetables change and so I have had to repeat the process to keep frog up to date. Only good news is that it gets faster the more times you do it! 1 Link to comment Share on other sites More sharing options...
gbligh Posted January 22, 2019 Share Posted January 22, 2019 16 minutes ago, pconkie said: It should be so much simpler than it is! We have already had a few timetables change and so I have had to repeat the process to keep frog up to date. Only good news is that it gets faster the more times you do it! Is there a way we can get parents to view it? 1 Link to comment Share on other sites More sharing options...
ADT Posted January 22, 2019 Author Share Posted January 22, 2019 See thats why i was hoping the extractor could do it..... the data is there... it just needs bringing into Frog in a way it can be used by a widget!! Link to comment Share on other sites More sharing options...
adamw Posted January 22, 2019 Share Posted January 22, 2019 1 hour ago, ADT said: See thats why i was hoping the extractor could do it..... the data is there... it just needs bringing into Frog in a way it can be used by a widget!! Just FYI, we've not forgotten about this. I'll keep pushing for it. Link to comment Share on other sites More sharing options...
ADT Posted January 22, 2019 Author Share Posted January 22, 2019 Just now, adamw said: Just FYI, we've not forgotten about this. I'll keep pushing for it. I would expect nothing less.... Link to comment Share on other sites More sharing options...
pconkie Posted January 22, 2019 Share Posted January 22, 2019 3 hours ago, gbligh said: Is there a way we can get parents to view it? Yes, but it would require a new widget! Only issue is time! Perhaps frog can take the one I've made and make the necessary adjustments? Link to comment Share on other sites More sharing options...
ADT Posted January 22, 2019 Author Share Posted January 22, 2019 Just now, pconkie said: Yes, but it would require a new widget! Only issue is time! Perhaps frog can take the one I've made and make the necessary adjustments? Doesnt seam unreasonable..... what with being in "The Frog Academy" launch year!! 1 Link to comment Share on other sites More sharing options...
ADT Posted January 24, 2019 Author Share Posted January 24, 2019 Clearly im doing something wrong.... followed it to the letter... checked your video to make sure the code looks similar to the one im producing.......... when i kid checks the page they get.... I quit............................... Link to comment Share on other sites More sharing options...
pconkie Posted January 25, 2019 Share Posted January 25, 2019 Did you change the column headers in the excel file? Even a change in capitalisation would break it like this. Link to comment Share on other sites More sharing options...
ADT Posted January 25, 2019 Author Share Posted January 25, 2019 2 hours ago, pconkie said: Did you change the column headers in the excel file? Even a change in capitalisation would break it like this. Looks the same to me.... Link to comment Share on other sites More sharing options...
pconkie Posted January 25, 2019 Share Posted January 25, 2019 54 minutes ago, ADT said: Looks the same to me.... Agreed. Can you show me a snippet of the final json you pasted into the widget? Link to comment Share on other sites More sharing options...
ADT Posted January 25, 2019 Author Share Posted January 25, 2019 4 minutes ago, pconkie said: Agreed. Can you show me a snippet of the final json you pasted into the widget? { " ": { "Date": "Fri 01 Feb", "Time": " 9:00AM", "CompCode": "21117K01", "CompTitle": "Health and Wellbeing", "Duration": "2h 00m", "Room": "215", "Seat": "B3 " }, " ": { "Date": "Wed 01 May", "Time": " 9:00AM", "CompCode": "DA20101", "CompTitle": "Developing Web Products", "Duration": "2h 30m", "Room": "GALLERY", "Seat": "C10 " } } Link to comment Share on other sites More sharing options...
ADT Posted January 25, 2019 Author Share Posted January 25, 2019 5 minutes ago, pconkie said: Agreed. Can you show me a snippet of the final json you pasted into the widget? Its going to be another one of those dead easy answers..... one of those Doh moments!! Link to comment Share on other sites More sharing options...
pconkie Posted January 25, 2019 Share Posted January 25, 2019 4 minutes ago, ADT said: { " ": { "Date": "Fri 01 Feb", "Time": " 9:00AM", "CompCode": "21117K01", "CompTitle": "Health and Wellbeing", "Duration": "2h 00m", "Room": "215", "Seat": "B3 " }, " ": { "Date": "Wed 01 May", "Time": " 9:00AM", "CompCode": "DA20101", "CompTitle": "Developing Web Products", "Duration": "2h 30m", "Room": "GALLERY", "Seat": "C10 " } } It's not quite the right format (although hard to read on the forum).... Perhaps not the issue but check that: { "firstupn": [ { "Date": "Mon 14 Jan", "Time": " 9:10AM", "CompCode": "BOILH", "CompTitle": "Biology Higher", "Duration": "1h 45m", "Room": "Sports Hall", "Seat": "R3 " }, { "Date": "Mon 14 Jan", "Time": " 1:30PM", "CompCode": "MUSW", "CompTitle": "Music W", "Duration": "1h 30m", "Room": "DM2", "Seat": "G2 " } ] } There should be square brackets around each student (red) as well as braces around each exam. Has your json got this? Link to comment Share on other sites More sharing options...
ADT Posted January 25, 2019 Author Share Posted January 25, 2019 Hmmmm no..... { " ": { "Date": "Fri 01 Feb", "Time": " 9:00AM", "CompCode": "21117K01", "CompTitle": "Health and Wellbeing", "Duration": "2h 00m", "Room": "215", "Seat": "B3 " }, " ": { "Date": "Wed 01 May", "Time": " 9:00AM", "CompCode": "DA20101", "CompTitle": "Developing Web Products", "Duration": "2h 30m", "Room": "GALLERY", "Seat": "C10 " } } I will retrace my steps!! Thanks mate!! Link to comment Share on other sites More sharing options...
ADT Posted January 25, 2019 Author Share Posted January 25, 2019 I've ran a different exam report..... it looks ,,,, as usual... we do things differently to... well... everyone else!! I'm going to need to adapt your macro to fix some issues!!! At least this is one thing i can get my head round......... i get excel!! Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now