Published: 16th February 2018
Background & Aims and Objectives
Before the API process is run next, the SubjectDict table in this database will need updating as per the Subjects and Degrees section (Page 7) of this manual. Starting from the text “The query SubjectDictBlankSubject shows”.
******It is VITAL that the processes are run in the order stated. Any change in the order WILL result in errors******
Files will be added to the folder (\\VM-DDARRdSP01.ds.man.ac.uk\Integrations) on the 1st of each month
Run the merge file through the constituent matching programme. (Open UManchester.exe in O Drive>Data Team>API and choose Merge as your incoming file and Mergeoutput as your outgoing) This can take several hours, particularly in June and December, so is best run overnight.
The Import of Tables
Delete the tables: Addresses, education, emailAddresses, merge, personal and phones from the database MergeProcess.accdb located at O:\API.
Import addresses.csv, education.csv, emailAddresses.csv, personal.csv and phones.csv from this folder to the same database.
Before importing the table Phones, it should be opened in Notepad++ and the character + should be erased by replacing with Null.
During the import process, the Advanced options should be used and the code page changed to Unicode (UTF-7).
Tick the option box for First Row contains Field Names.
Any date fields will need the data type changing to Date/Time.
When the merge file process has completed, import the Mergeoutput file and ensure that Party Number is formatted as Text.
The “Let access add primary key” option should be selected and then finish the import
Once the files have been imported, create a folder in \\VM-DDARRdSP01.ds.man.ac.uk\Integrations\Archive of the format YYYYMMDD (today’s date) and place all the files in that folder to make room at the top folder for next months files
Add a new field, AddedToRE of type yes/no to each imported table in the database MergeProcess.accdb. These will be used to ascertain if a record has been updated in Raiser’s Edge. The default value should be No for the AddedToRE field
Add a text field named CAREER to the Education table. Run the query AddCareerID, this creates a unique education ID based on Consituent_ID, Career_ID and Career_Number
Adjusting Merge Criteria
The next query to run is TheIDS_MatchReally. This uses the previous history to match those which the matching process cannot logically apply a match to, but further investigation has previously shown to be a match.
Next is to run MatchingType6_1; this shows all IDs in the imported files that already exist on Raiser’s Edge, but the data seems to be for a different person. Investigate these and change the matching code to MatchingType8 if the individual is the same as the one on RE
The final matching constituent step is to run the query AliasMatch which selects those IDs that do not exist on Raiser’s Edge as a constituent ID, but do exist as an alias. The process to undertake is to check that the individual is the same. If so, the ID in Raiser’s Edge needs to be changed to the ID in the merge file. The matching code needs changing to MatchingType8 in the table MergeOutput for those where a change has been made.
If they are currently making a regular donation then this has an impact on the colleague in charge of SOs and DDs (Katie Leatherbarrow as of 03/09/2017). Katie said on 03/09/2017 that there is no longer a need to inform her of changes in ID; it will be picked up elsewhere in her processes. Please be aware that if someone else takes over the role, they may need to be informed.
Names – As part of the process of running UManchester.exe, any names with special characters in the table Merge are changed to ¿½ in the table MergeOutput. There is nothing we can do to alter the process as it was created by a third party vendor. There is an easy fix by running NamesUpdate in the MergeProcess database
Addresses – The address file will need cleaning to ensure Postcode, City and Country are all appropriately formatted and in the correct fields. There will be addresses with UK as a country, but an obvious non-UK postcode. Similarly there will be UK addresses with a country other than UK. There are some cleaning queries to assist here.
- NewCountries adds any countries that are not stored as countries in Raiser’s Edge into the table Countries. It then needs to be decided if the country should be added to Raiser’s Edge or a translation added in the field RE_Country in the Countries If a new country is added to RE, then the record should be deleted from the table Countries
- Running the macro UpdateCountries will find address lines that contain country data and delete them. It will also change all non_RE countries to an appropriate value.
- To remove addresses that are duplicated, run RemoveDuplicateAddresses_1 and then run RemoveDuplicateAddresses_2. Repeat this process until the first produces no new records in the table
- Blank Postcode will find all address lines with no postcode. Please complete these if possible from the other address lines
- Running the macro UpdatePostcode will find UK postcodes that have no space and add it in the right place
- The tables Cities and Non_UK_Cities, contains all UK cities and non-UK cities that have previously been seen to commonly appear in earlier address lines. Running the macro UpdateCities find address lines that contain this city data and delete them whilst updating the data in the City field. This will also update the city based on the postcode district for UK cities.
- The table Counties, contains all UK counties. Running the macro UpdateCounties will find address lines that contain county data and delete them whilst updating the data in the County field. It will also use a look-up based on postcode to find a County where it is currently blank.
- CountiesNotInRE adds any counties that are not stored as counties in Raiser’s Edge into the table County Dict. It then needs to be decided if the county should be added to Raiser’s Edge or a translation added in the field County1 in the County Dict If a new county is added to RE, then the record should be deleted from the table County Dict
- Run the query UpdateCounties to change any counties with incorrect data to appropriate names
- NonUKCityInUK will find cities that may have been wrongly given the country United Kingdom. Search for the city on the internet and complete the country field accordingly
- PostcodeCountry finds UK style postcodes that have a different country. Change the country if appropriate
- UK_NoCity gives UK addresses with no city. Please complete as appropriate
- UK_NoPostcode gives UK addresses with no postcode. Complete where possible
- Care needs to be taken to ensure that the final Address Line field containing data has no blank address lines with a lower number. To this end, Blank Address Line 1, Blank Address Line 2_3or4_Data and Blank Address Line 3_4_Data pick such data that needs to be cleaned. These should only be run after the city and county cleaning is completed
- The final cleaning in address is to capitalise all cities and postcodes. This is done using the query UpperCase_Cities_Postcode.
Subjects and Degrees –
Run the query AppendNewSubjects. This will add subjects from Campus Solutions not previously seen in the API process into the table SubjectDict. This table is used to transform the rather convoluted subject data in CS into the Subject and Degree used in RE.
E.G. B.Eng (Hons) Mechanical Engineering with Industrial Experience in Europe (IEE) is transformed into the subject Mechanical Engineering and the degree BEng.
The query SubjectDictBlankSubject shows rows where the Subject column is blank. These need an appropriate subject adding. There should be no rows with a blank entry in the Subject field.
The query SubjectDictBlankDegree shows rows where the Degree column is blank. These need an appropriate degree adding where possible. There is a drop-down list of available degrees, most CS subjects will have a possibility here.
If a subject is blank, then the data for that CS_Subject will come through with a blank subject in RE. Similarly with blanks in Degree
Check qry_SubjectsNotInRE for any subjects that do not appear in Raiser’s Edge. These subjects either need adding to RE or editing to something that does appear in RE
Other education – Open the query CurrentStudent_ClassOf which will show education records that have a Class Of recorded while having a status of Active in Program. These will need checking in Campus Solutions to see if the programme has been finished and the status should be Completed Program. Or if still active, then the Class Of, Date Left and Date Graduated need removing
Run the macro UpdateEducation, this will remove the date left and date graduated if a student is still active in the programme. It also makes the date left today’s date if it was a future date for those that have completed, cancelled or discontinued. It also updates any old faculty data. It finally makes the date graduated null if it was complete for those that have cancelled or discontinued.
There are a series of queries needed to clean the phone numbers before they are ready for import
PhoneLengthUnusual_long will pull out any phone number with greater than 11 characters. Browse through these and deal with any that have multiple numbers in the same row by deleting appropriate data and adding as a new row. Any UK country codes contained in numbers should be dealt with by later cleaning queries, so please ignore these for now
PhoneLengthUnusual_short will pull out any phone number with less than 6 characters. Browse through these and delete if appropriate
The macro UpdatePhones cleans the phone data
Run PhoneLengthUnusual again to check if any unusual length numbers still exist and need editing
When importing data, the Raiser’s Edge Login of API should be used with the password of BigBr0ther
In all cases save exception files to be dealt with at a later date
All date fields in import files need to be of the format DD/MM/YYYY, eg 14/07/2017
A decision was taken to only add individuals with a completed or current education record. To find these individuals, run the query AddIndividualMatching. This data needs exporting to a csv file to be used in the import of new individuals and their main address. Dates need to be formatted to DD/MM/YYYYY.
The created csv is imported using the IMPORTOMATIC plug-in on Raiser’s Edge. The profile to be used is ImportNewAndPreferred.
The query UpdateEducation will create a table UpdateEducation_Import that allows the update of all education records that match for individual and career id. There does have to be some cleaning done first to ensure that the Awarded category does not exceed the 50 character limit imposed by Raiser’s Edge; the query AwardedTooLong selects these for editing. Once this cleaning is complete export the data from the table UpdateEducation_Import, use the import process API_UpdateEducation in the Import section of Administration on Raiser’s Edge.
The query UpdateEducation2 will give the data to create a csv file that allows the import of all education records that are new to any new constituent.
[Use RE Import API_NewEducation]
Run CreateAllEducation and then the query NewEducation will give the data to create a csv file that allows the import of all education records that are new to any existing constituent.
[Use RE Import API_NewEducation]
There may be new addresses to add to RE
First we need to identify any addresses that already exist on RE. Run the query UpdateAddressesOnRE. This creates a table of all the existing constituent addresses on RE. This may take over an hour to run. Then run UpdateAddressesOnRE_1
The query AddNewAddresses will produce the data for a csv file to import using the Importomatic profile API_AddNewAddresses
The query Email_NotValidFormat in the MergeProcess database will show email addresses that are not in a valid format.
Run the query CreateAllEmail followed by UpdateEmailAlreadyExist, this will stop any email that already exists on a record from being imported
To remove duplicates run the query EmailDuplicatesToDelete. Open the table DuplicatesEmail in design view and change the field type of the field ToDelete to Yes/No. Open this table and then select the tickbox for whichever of the duplicate records should be deleted.
Run the query Email_Add which can be used to create a csv file for the import of new email addresses
[Use IoM Import API_Emails]
Run the query UpdatePhoneAlreadyExist, this will stop any phone number that already exists on a record from being imported
To remove duplicates run the query PhoneDuplicatesToDelete. Open the table DuplicatesPhone in design view and change the field type of the field ToDelete to Yes/No. Open this table and then select the tickbox for each record that is duplicated.
The query Phone_Add in the MergeProcess database can be used to create a csv file for the import of new phone numbers
[Use IoM Import API_Phone]
Having been imported, the IDs from Adding Constituents are now a match and will need their matching code changing to 1.
Run the query AddNewImportProcessRecords.
Run the query UpdateNewIndividualMatching_2.
Updating Data in Raiser’s Edge
The import and update of information from the Integration Hub means that some fields in RE are now incomplete. These need amending or adding
Adding Certificate Constituent Code
In the Admin section of Raiser’s Edge there is a section for Globally Add Records. In this section Add a Constituent Code include Selected Constituents from the query UpdateCertConCode and the constituent code information is: Description = Certificate/Diploma, Date From = mm/yyyy where mm is the current month and yyyy is the current year. Untick Create control report and click the Add Now button
Adding Degree Constituent Code
In the Admin section of Raiser’s Edge there is a section for Globally Add Records. In this section Add a Constituent Code include Selected Constituents from the query UpdateDegreeConCode and the constituent code information is: Description = Degree, Date From = mm/yyyy where mm is the current month and yyyy is the current year. Untick Create control report and click the Add Now button
Updating Primary Education Record
The first award from the University is allocated as the Primary Education Record. To update this information, export the query UpdatePrimary from the API folder of Raiser’s Edge as UpdatePrimary.csv to that month import folder on O:\API. The column Preferred should be changed from No to Yes on all rows and saved. The Raiser’s Edge import process API_Primary should then be used to import this data.
Adding Alumnus Constituent Code
In the Admin section of Raiser’s Edge there is a section for Globally Add Records. In this section Add a Constituent Code include Selected Constituents from the query UpdateAlumnusConCode and the constituent code information is: Description = Alumnus, Date From = mm/yyyy where mm is the current month and yyyy is the current year. Untick Create control report and click the Add Now button.