Hi Becky,
Below is some SQL which will copy over attachments for a specific student (‘ABC12345678’ in this example) from ‘17/18’ to ‘18/19’. I’ve tested it on some students
here and it works. I would recommend testing it your end too. (I think the DataSourceID is the same for all colleges, but I could be wrong, either way it is easy to find and replace.)
If you want to run it as a batch job simply remove the reference to the student. Otherwise I would recommend triggering it when a student record is copied
over years so the attachments are immediately available to the user. (Also, if it is later deleted it won’t be recreated as part of the batch.)
I hope this helps. Any questions please let me know.
Jason Bushell
MIS Analyst | Sparsholt College Hampshire (incorporating Andover College Campus)
Andover College | Andover | Hampshire | SP10 1EJ
Direct line: 01264 360140
Andover Reception: 01264 360000 | Sparsholt Reception: 01962 776441
Andover Fax: 01264 360010 | Sparsholt Fax: 01962 776587
email:
[log in to unmask] |
www.sparsholt.ac.uk and
www.andover.ac.uk
USE [ProSolutionMessageStore]
GO
INSERT
INTO
[dbo].[_CCC_RecordAttachment]
SELECT
NEWID
()
,ra.[CreatedBy]
,ra.[CreatedDate]
,ra.[LastModifiedBy]
,ra.[LastModifiedDate]
,[FileID]
,sd2.StudentDetailID
,[RowDescription]
,[DataSourceID]
,[Caption]
,ra.[Categories]
FROM
[dbo].[_CCC_RecordAttachment]
ra
INNER
JOIN
ProSolution.dbo.StudentDetail
sd ON
ra.RowPrimaryKeyValue
= sd.StudentDetailID
and
sd.AcademicYearID
=
'17/18'
INNER
JOIN
ProSolution.dbo.StudentDetail
sd2 ON
sd.StudentID
= sd2.StudentID
and
sd2.AcademicYearID
=
'18/19'
WHERE
sd.RefNo
=
'ABC12345678'
and
ra.DataSourceID
=
'8781C83E-74B8-47CA-B102-0A3EB301F6B3'
and
NOT
EXISTS
(SELECT 1
FROM [dbo].[_CCC_RecordAttachment]
ra2
WHERE ra2.DataSourceID
=
'8781C83E-74B8-47CA-B102-0A3EB301F6B3'
and
ra2.RowPrimaryKeyValue
= sd2.StudentDetailID
and
ra2.FileID
= ra.FileID)
GO
From: ProSolution Users Mailing List [mailto:[log in to unmask]]
On Behalf Of Luke Gietzan
Sent: 17 July 2018 15:05
To: [log in to unmask]
Subject: Re: Moving attachments to another year
Hello Becky,
I thought I was on to something as you can run updates in the software against the Notes data set. Although you can change the Data Source ID from 'Student Detail' to 'Student (All Years)', what
you can't do is correct the Row Primary Key Value, which is what actually joins the Note to a record, as it won't let you select 'Student ID' from the linked data sources.
The only way I know of to do it is through the back, to run a similar update against the notes table to change those two fields to point to the StudentID rather than StudentDetailID.
Luke
From: ProSolution Users Mailing List <[log in to unmask]>
on behalf of Ward, Becky <[log in to unmask]>
Sent: 17 July 2018 13:53:37
To: [log in to unmask]
Subject: Moving attachments to another year
Hi everyone
We have some learners with attachments in Student Details for 17/18, that we want to be able to see in their 18/19 Student Detail record too. It must be possible without manually copying them but we can’t work it out. Has anyone done this
please?
Thanks
Becky
Becky Ward
Head of Central Information Services | Lincoln College Group
Information Services
Tel: 01522 876251
Email: [log in to unmask]
This footnote confirms that Microsoft Exchange Online Protection scanned this email message for the presence of unwanted code, vandals, and computer viruses. This mail list is run by users of the ProSolution Student Management & Information
System and is not directly connected with Compass Computer Consultants. Please ensure that you test any ideas provided on this list on your TRAINING SYSTEM before implementing them on your LIVE SYSTEM. The owners of this list and Compass Computer Consultants
Limited accept no liability for any loss resulting from this list. Should you be unsure, please contact ProSolution Support on 01928 725522 or email
[log in to unmask].
New City College is a dynamic further education college group. See
www.ncclondon.ac.uk for news, courses and information about our campuses.
DISCLAIMER: This e-mail and any attachments to it contain information that is private and confidential and should only be read by those persons to whom they are addressed. Neither
New City College nor the sender accepts any responsibility for viruses and it is your responsibility to check the email and attachments (if any). No contracts may be concluded on behalf of New City College by means of email communications. Any views or opinions
presented are only those of the author and not those of New City College. If this email has come to you in error please delete it and any attachments.
'.
This mail list is run by users of the ProSolution Student Management & Information System and is not directly connected with Compass Computer Consultants. Please ensure that you test any ideas provided on this list on your TRAINING SYSTEM
before implementing them on your LIVE SYSTEM. The owners of this list and Compass Computer Consultants Limited accept no liability for any loss resulting from this list. Should you be unsure, please contact ProSolution Support on 01928 725522 or email
[log in to unmask].