Auto Upload Scripts

This section has 2 parts… the Core Upload and the Results Upload. Once set up you no longer need to do regular exports from KAMAR to upload into Assay, as it is all done automatically. The only times you’ll need to upload data is to load historical data eg: in Jan to load the final NCEA results from the previous years or when initially setting up.

Note 1: these instructions assume you have your FileMaker Server running on Windows… if you are running your FileMaker server on a different type of server let me know.

Note 2: If you need support with this please email us: [email protected] and we can give you a hand, as it is important you get this set up correctly. KAMAR has said they are happy for us to set this up, but don’t want to provide support for it, so if you need support ask us, not KAMAR.

Note 3: If you are hosting assay3 yourself you will also need to set up the autoprocess script

Note 4: The powershell scripts require the command “Invoke-RestMethod”.
PowerShell 2.0 (server 2008 R2) doesn’t even know what this command is, so will require updating the Powershell to Version 3. If you can’t do that contact me and I can sort out some vbs scripts for you, but these are not as reliable or you will need to download and install Net 4.5 framework (if not already) and Windows management framework  https://www.microsoft.com/en-nz/download/details.aspx?id=34595

 

Once finished you should end up with a folder that looks like this:

Have 2 new FileMaker schedules:

And 2 new Task Scheduler schedules:

 

Core Upload

The Upload Core script allows KAMAR to do a bulk of the exporting and upload it to assay automatically.

 

Under “Printing” on KAMAR go to “Export” go in and create a search for “All” students (as shown in the screenshot below), but before you press find give it a name on the left (AssaySearch with no spaces works well)

And then copy the list of fields from the Admin -> Upload Data section on Assay, and paste the list of fields (do not look for them manually… just do a copy from assay and a paste into the “Export Order” on KAMAR, but before you press export give it a name on the left (AssayExport with no spaces works well)

Press Export.

 

We then need to set up the script so it exports the results automatically. Go to schedules, and the click on the button in the top right and “Create a Schedule”

There are then instructions on KAMAR’s website about how to schedule this export

https://www.kamar.nz/104071#page-3

 

Follow these through… the parameters you’ll need will be something like:

JW,AssaySearch,AssayExport,filewin:/E:/Exports/assayexport.csv

JW is the user – note: this is the teacher code, not the login username (may be different)

AssaySearch is the saved search

AssayExport is the saved export

filewin:/E:/Exports/assayexport.csv is the location

 

The time limit should probably be quite long… 120 minutes is not a bad idea.

 

Suggest you run at 5am every day, otherwise it can interfere with KAMAR updating

The final script settings will look like this:

You can then run the script to make sure it works (can take a bit of time depending on the size of your school) … a school of 1500 takes around 20 minutes depending on server specs. It will show in the scheduler when finished.

 

You then can set up the uploadcore.ps1 script.

You should download this from:

https://hosted.assay.co.nz/demo/uploadcore.ps1

You should save this in the same folder as the exports are going to.

If you right click on the file you can click edit.

You will need to edit the 3 lines at the top:

The $Url is the url to your copy of assay plus the “uploadcore2.php”

The $file is the file that you set up to export

The $authentication needs to match your authentication on your copy of assay (Admin -> Config).

Press the run button (green triangle) at the top of the screen to run it to confirm it is working.

It should output something like

Max Size: 256
515593
Uploaded

 

Once you have confirmed it is working you can then set this up as a scheduled task as well using the Windows Task Scheduler (screenshots below for the Results upload if you are unsure of the steps)

 

Make sure:

  • You set it up to run whether the user is logged in or not
  • You set it up to repeat every day (suggest 15 minutes before the autoprocess.php script is scheduled – ours is set for 6:25am) – if hosting with me make sure it is uploaded before 6:30am as that is when the auto process scripts start.
  • You tell it to stop running after 1 hour (doesn’t auto quit for some reason)
  • You have the Auto Process script set up as well (this is done automatically if you are hosting with me)

 

Results Upload

For this you need 2 files.

  • assayexport.fmp12
  • uploadresults.ps1

You can copy them from the root of the , or download from:

assayexport.fmp12 needs to be put into the same folder as the KAMAR.fmp12 file, so it looks like this:

Once you have done that you need to go into the FileMaker Server Admin Console, go to activity, click on assayexport.fmp12 and open the database by clicking on the folder icon in the top right -> press open so it should look something like this:

We then need to set a password for the KAMARCustom access

On KAMAR go to Main Menu ⇒ Setup ⇒ KAMAR ⇒ KAMAR ⇒ Custom Security and create a password (can be anything you want… just make sure you know what it is)

Once you click out of the password field it will set this password in all the files

You can then open the assayexport database by going to File -> Open on FileMaker, choosing hosts and choosing your KAMAR Server.

The username for the assayexport database is “Admin” and the password is “Assay”

It will then might give you another password prompt for the ko_StUnits database. The username for this is “kamarcustom” and the password is whatever you set earlier on KAMAR. (some schools do not get this popup)

This will let you into the database so we can set the password. To set the password go to File ⇒ Manage ⇒ Security

This will again prompt you for a username and password… this is just “Admin” and “Assay” again, which will give you this screen:

You can then set the password by clicking on the kamarcustom on the left and pressing the change password on the right. Change the password to whatever you set in KAMAR and press Set Password.

Then press OK on the account window and then close the database by going File ⇒ Exit

We then need to set up the script so it exports the results automatically. On the FileMaker Admin Console go to schedules, and the click on the button in the top right and “Create a Schedule”

Choose FileMaker Script and then press Next:

Choose the assayexport database and set the Account Name and Password as above.

Choose “Export” set the time limit to 120 and then press Next

Set the start time to 5:05am and press Next

Give the script a name, make sure it is enabled and press next

Press Finish

This should give you the new schedule showing up like this:

Run the schedule now by clicking on the schedule and then on the “Run Schedule Now” option (can take a bit of time depending on the size of your school)… a school of 1500 takes around 25 – 45 minutes depending on time of year and server specs. It will show in the scheduler when finished.

It should then have created a file at:

C:\Program Files\FileMaker\FileMaker Server\Data\Documents\assayresults.csv

Check to make sure this exists.

 

You then can set up the uploadresults.ps1 script… we suggest putting this in the same place as your core export and the uploadcore.vbs file:

If you right click on the file you can click edit.

You will need to edit the 3 lines at the top:

The strURL is the url to your copy of assay plus the “uploadresults2.php”

The file is the file that you exported (probably doesn’t need to be changed)

The authentication needs to match your authentication on your copy of assay.

 

You can run the task by pressing the run button (green triangle) at the top of the powershell editor.

 

Once you have confirmed it is working you can then set this up as a scheduled task as well using the Windows Task Scheduler

On the General Tab give the task a name and set it to run if logged in or not and run with highest privledges

On the Triggers tab create a new trigger… set time to 6:28am, Repeat Daily, Stop the task if it runs for more than 1 hour and make sure it is enabled.

This should give you a triggers tab that looks like this (time different)

On the “Actions” tab create a new action:

In the program/script you want to run Powershell.exe

In  the Add arguments section you want to add

-ExecutionPolicy Bypass E:\Exports\uploadresults.ps1

(or uploadcore.ps1 if you are doing the core upload)

 

This should give you an “Actions” tab that looks like this:

You shouldn’t need to change anything on the conditions tab

On the “Settings” tab set the “Stop the task if it runs longer than” to “1 hour” and then press OK. This will prompt you to put in your password so it can run if you are there or not.

This will give you tasks that look like this:

You can right click on the task and choose run to make sure they work correctly

 

For some reason it doesn’t auto finish… it only takes a few seconds to run, so don’t be alarmed if it seems to keep running.

 

And that’s it… you’re all set up… your data will automatically get uploaded into Assay from KAMAR on a daily basis.