API from integration hub to Raiser’s Edge

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******

Step 1

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.

Step 2

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

 

Step 3

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.

Step 4

Cleaning data

NamesAs 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

 

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

  1. 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
  2. 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.
  3. 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
  4. Blank Postcode will find all address lines with no postcode. Please complete these if possible from the other address lines
  5. Running the macro UpdatePostcode will find UK postcodes that have no space and add it in the right place
  6. 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.
  7. 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.
  8. 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
  9. Run the query UpdateCounties to change any counties with incorrect data to appropriate names
  10. 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
  11. PostcodeCountry finds UK style postcodes that have a different country. Change the country if appropriate
  12. UK_NoCity gives UK addresses with no city. Please complete as appropriate
  13. UK_NoPostcode gives UK addresses with no postcode. Complete where possible
  14. 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
  15. The final cleaning in address is to capitalise all cities and postcodes. This is done using the query UpperCase_Cities_Postcode.

 

Step 5

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

 

Phone numbers

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

Step 6

Adding Data

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

 

Adding Constituents

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.

 

Updating Education

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.

 

New Education

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]

 

Addresses

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

 

New Email

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]

 

Phone numbers

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.

Step 7

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.