Print

Print


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]

LinkedInTwitter

Lincoln College Group

 

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].

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]