Overview of Reference Tables - Externally Supplied Data

Table of Contents

Also See:

• Maintaining community setup data

• Maintaining lookup tables

• Adding and changing entries in lookup tables

• Loading lookup tables for external systems

Reference table 

A reference table is a type of database table used to store standard values provided by an external source. The data in a reference table is known as reference data. Having reference data sets helps maintain data consistency, reduce redundancy, and improve efficiency.

All reference table names start with refExternalSystem, eg refExternalSystemSchool, refExternalSystemParish, refExternalSystemVisa, etc. The minimum data set for a reference table is these fields: ExternalSystemType, ExternalSystemCode, Description, ActiveFlag.

In the main reference tables are used to store data that populate certain lookup tables. Not all lookup tables are populated by reference tables, and not all reference tables populate lookup tables, but generally they do.

For instance, a State based religious authority would define the data set for Parishes in that State.  This data would then be used to populate the luParish lookup table.

The process to load data from reference tables into lookup tables is on this page: Loading lookup tables for external systems).

Reference data is linked to lookup data via two fields:

• ExternalSystemType

• ExtrernalSystemCode

External System Type

ExternalSystemType indicates where the data is sourced from, it is the external authority who own the data and provide it for loading into Synergetic. Examples are CEOVIC which is the code for Victorian Catholic Schools, NZMOE which is the code for the New Zealand Ministry of Education, MCEETYA and AUSGOV which are codes for the Australian Government, etc.

External System Code

This stores the code provided by the external authority that they want reported to them by External Systems for each data item. For instance, Country data is maintained by the Australian Bureau of Statistics (ABS), who uses the code 1101 for Australia and by NZMOE who use the code ‘AUS’.

Multiple Codes For One Data Item

Multiple external authorities can provide data for the same lookup table. This results in one reference table having multiple entries for the same data item. Schools only need to load the lookup table data which is applicable to them. To enable this reference data is grouped by the ExternalSystemCode column. In this case Australia appears three times in refExternalSystemCountry:

Reference tables are maintained by Synergetic staff. Reference data is not visible to Users through the Synergetic User Interface and database access is required to see reference data. There is no requirement for schools to directly access or edit reference data. If an issue with reference data is found, Support should be contacted.

Main External System Codes Explained

FAQ’s

1. I have loaded an externalSystemType(or multiple externalSystemTypes) into a lookup table which is not applicable to my school. How can I delete these records

A: Lookup table Records can only be manually deleted via Synergetic Lookup table maintenance.

2. Can I load records from an external system type which is not related to my school

A: Yes, however most likely the ExternalSystemType and ExternalSystemCode columns wont be applicable, only the code value can be used to reference these records.

3. How do I manually enter a record(ie a new school) into a lookup table that does not exist in a reference table

A. You can manually enter a record into a lookup table . You will not be able to update the ExternalSystemType and ExternalSystemCode fields if they don't exist in the reference table.

4. I have manually added a new school, I have hit the load button but the ExternalSystemType and ExternalSystemCode columns do not update.

A.Selecting [Load] will only add new records, any existing records will not be updated. Any existing records will need to have the ExternalSystemType and ExternalSystemcode selected manually.

5. Can I delete an existing lookup table record

A.Yes, however any record which has been linked to a student(current , future, past or previous file year) cannot be deleted

6. How do I modify the code of an existing lookup table record

A. Lookup table codes can only be updated if it has not been linked to a student(current , future, past or previous file year)

7. I get the following error when trying to delete a lookup table entry

   Open

   

A:This means that lookup records has been assigned to a student and cannot be deleted

8. How do I know if I have the latest up to date data in all my lookup tables and how do I keep up to date with recent lookup changes.

• Upgrade to the latest Synergetic Release: Upgrade Portal link

• Load reference data into all relevant lookup tables: Load Reference Data

• Check Synergetic hotfixes wiki page: Hotfixes

9. I believe a lookup table is missing a required External Code. What should I do?

A:

• Confirm that the External Authority has defined the code you think is missing for this lookup data.

• If so, Confirm the value is missing from the reference data, either by viewing the available codes in the External System Code drop down for the relevant External System Type, or if your school has the required skill via SQL to the relevant reference table.

• If the value is missing from the reference table, raise a case with support, including the evidence that the External Authority has defined the missing code.

• The External System Code will not be able to be added to the lookup table until it has been added to the reference table by Synergetic.

10. I wish to bulk load into a lookup table entries which are not associated with an external system type (ie Luschool or Ludestinationlocation)

A: It is not possible to bulk load non Externalsystemtype codes via Synergetic Lookup table maintenance. These records can only be added manually. If you have them in a file (xls or csv format) our Professional services team can also assist