Open topic with navigation

ACES2 SQL Setup

LSAC Report/Conditions

Contact your LSAC representative to create a report with the following fields on your ACES2 database. Below are the required ACES2 fields for your ACES2 report and where the fields map to in CAMS Enterprise.

• Time_Long - maps to Expected Enroll Type in Student Admissions Information.
• WeekdayWeekendCombo_Long - maps to Attendance Type in Student Admissions Information.
• Type_Long - maps to Student Type in Student Admissions Information.
• Formatted_SSN - maps to Social Security # in Student Admissions Information.
• Date_of_Birth - maps to Date of Birth in Student Admissions Information.
• LSAC_Acct_No - maps to the LSAC # in Student Access.
• Gender_Applicant - maps to Gender in Student Admissions Information.
• Prefix_Applicant - maps to Prefix in Student Admissions Information.
• Suffix_Applicant - maps to Suffix in Student Admissions Information.
• Last_Name_Applicant - maps to Last (name) in Student Admissions Information.
• First_Name_Applicant - maps to First Name in Student Admissions Information.
• Applicant_Middle_Name - maps to Middle (name) in Student Admissions Information.
• Preferred_Name - maps to Preferred in Student Admissions Information.
• Previous_Last_Name - maps to Maiden Name in Student Admissions Information.
• Current_Street_1 - maps to Local Address Type, Address 1 in Student Admissions Information.
• Current_City - maps to Local Address Type, City in Student Admissions Information.
• Current_State - maps to Local Address Type, State in Student Admissions Information.
• Curr_Zip_Code - maps to Local Address Type, Zip Code in Student Admissions Information.
• Current_Day_Phone_Number - maps to Local Address Type, Phone 1 in Student Admissions Information.
• Permanent_Street_1 - maps to Billing and Home Address Types, Address 1 in Student Admissions Information.
• Permanent_City - maps to Billing and Home Address Types, City in Student Admissions Information.
• Permanent_State - maps to Billing and Home Address Types, State in Student Admissions Information.
• Perm_Zip_Code - maps to Billing and Home Address Types, Zip Code in Student Admissions Information.
• Permanent_Day_Phone_Number - maps to Billing and Home Address Types, Phone 1 in Student Admissions Information.
• Primary_Email - maps to Billing, Local, and Home Address Types, E-Mail 1 in Student Admissions Information.
• Occupation - maps to Occupation in Student Admissions Information.
• Permanent_State_of_Residency - maps to Resident State/Prov in Student Admissions Information.
• Citizenship_Type_Long - maps to Citizen in Student Admissions Information.
• Country_of_Citizenship_Long - maps to Country of Citizenship in Student Admissions Information.
• Applicant_Status_Long - maps to Previously Applied in Student Admissions Information.
• Ethnicity_1_Long - maps to In-House Ethnicity in Student Admissions Information.
• Native_Language_Long - maps to Home Language in Student Admissions Information.
• Yes_Bach_Degree - this field is required in your ACES2 report but is not mapped initially in CAMS. Contact Unit4 Education Solutions, Inc. for information on mapping this field in CAMS Enterprise. If used, it must be mapped to a logic field in Admissions User Defined.
• Yes_C_and_F - this field is required in your ACES2 report but is not mapped initially in CAMS. This field is for future use.
• Yes_Attend_LS - this field is required in your ACES2 report but is not mapped initially in CAMS. Contact Unit4 Education Solutions, Inc. for information on mapping this field in CAMS Enterprise. If used, it must be mapped to a free format text field in Admissions User Defined.
• Campus_Long - maps to Access Campus in Student Admissions Information.
• Schedule_Option_Long - this field is required in your ACES2 report but is not mapped initially in CAMS. This field is for future use.
• Term_Long - combined with the ACES2 Year_Entering field, this maps to Expected Entrance Term in Student Admissions Information.
• Year_Entering - combined with the ACES2 Term_Long field, this maps to Expected Entrance Term in Student Admissions Information.
• Application_Source_Long - this field is required in your ACES2 report but is not mapped initially in CAMS. Contact Unit4 Education Solutions, Inc. for information on mapping this field in CAMS Enterprise. If used, it must be mapped to a glossary field in Admissions User Defined.
• High_LSAT_Score - maps to Score1 of the LSAT Test in Student Admissions Information Test Scores.
• LSAT_Test_Date - maps to Test Date of the LSAT Test in Student Admissions Information Test Scores.
• Cumulative_GPA - data in this ACES2 field is moved directly into the StudentAdmScores.LSATIdxUGPA SQL table column.
• Undergraduate_Degree_Long - maps to Degree Name in Student Degrees.
• Undergraduate_LSAC_Code - maps to Transfer School Name in Student Degrees.
• Undergraduate_Major_Long - maps to Major 1 in Student Degrees.
• Date_Undergraduate_Degree - maps to Grad Dt in Student Degrees.
• Decision_Status_Short - maps to Applicant Status in Student Admissions Information.

ACES2 Value Mapping

The ACES2ValueMapping SQL table allows you to map incoming ACES2 fields to the corresponding fields in CAMS. In most fields, if the incoming data text exactly matches what is set in the corresponding CAMS Glossary category or Reference table, it will not need to be mapped in the ACES2ValueMapping table. If your data text in CAMS does not match the incoming text, any value for each field can be mapped. This allows you to use any desired text in CAMS fields by simply mapping the incoming data to the CAMS data. Value mapping is done in this SQL table only and beyond the initial setup will rarely need to be changed.

There are occasions where multiple rows for the same value would need to be mapped. For example, multiple incoming values for the same ACES2FieldName such as ethnicities may require multiple entries of the same ACES2FieldName with differing entries in the CAMSMappedValue field. Or there may be situations where multiple values for an ACES2FieldName would need to be mapped to the same CAMS value in the CAMSMappedValue field. For example, it may be desirable to have multiple ACES2 values for Decisions_Status_Short map to the same Applicant Status in CAMS.

Columns in this table are:

• ACES2ValueMappingID - no user action required. This column is automatically populated upon creating a new row in the table.
• ACES2FieldName - The name of the ACES2 field must be entered here for items that are to be mapped. For multiple items using the same field, such as ethnicities or terms, there could be multiple rows.
• ACES2FieldValue - The incoming ACES2 field value is entered here. This will be the exact text or number in the ACES2 incoming field.
• CAMSMappedValue - This value will be either the unique ID of the CAMS Enterprise SQL table or, in some cases, the CAMS Enterprise text value.

Below is a list of all incoming ACES2 fields and the requirements for the ACES2ValueMapping SQL table. The fields are listed alphabetically. Note that some fields do not require any action in the ACES2ValueMapping table.

• Applicant_Middle_Name - Moved directly into the Student Admissions Information >Admin 1 tab >Middle name field and does not need to be mapped.
• Applicant_Status_Long - Moved directly into the Student Admissions Information >Admin 2 tab >Previously Applied field with the CAMS default value of No. It is not necessary to map ACES2 values that should be "No" in this field. Incoming values that should be set to "Yes" will need to be mapped. To map a value, the exact incoming text should be entered in the ACES2FieldValue column and "Yes" (without the quotes) should be entered in the CAMSMappedValue column.
• Application_Source_Long - This field is required in your ACES2 report but is not mapped initially in CAMS. Contact Unit4 Education Solutions, Inc. for information on mapping this field in CAMS Enterprise. If used, it must be mapped to a glossary field in Admissions User Defined.
• Campus_Long - Incoming text that is exactly matched in CAMS Campuses Reference Lookup table does not need to be mapped. For example, if the text "North" is imported and you have a Campus named "North" you will not need to map it. If the incoming campus, "North", should be set in the Student record as another campus, such as "Main", it will need to be mapped. To map a value, the exact incoming text must be entered in the ACES2FieldValue column of the ACES2ValueMapping table; the CampusID from the Campuses table should be entered in the CAMSMappedValue column.
• Citizenship_Type_Long - This field imports as the CAMS default value of "Yes". Incoming values that should be set to "No" must be mapped in the ACES2ValueMapping table. If mapping a value to "No", the exact incoming text needs to be entered in the ACES2FieldValue column and the value "No" (without the quotes) should be entered in the CAMSMappedValue column.
• Country_of_Citizenship_Long - Incoming text that is exactly matched in CAMS Country Codes Glossary Lookup table does not need to be mapped. The incoming Country Name must be in the Display Text field of the Country Codes Glossary Lookup Table for exact matched text. If mapped, the exact incoming text must be entered in the ACES2FieldValue column of the ACES2ValueMapping table and the UniqueID of the Country (Glossary Category 1002) you wish to map the value to should be entered in the CAMSMappedValue column.
• Cumulative_GPA - Moved directly into the StudentAdmScores.LSATIdxUGPA SQL table field and does not need to be mapped.
• Curr_Zip_Code - Moved directly into the Zip Code field of the Local Address Type and does not need to be mapped.
• Current_City - Moved directly into the City field of the Local Address Type and does not need to be mapped.
• Current_Day_Phone_Number - Moved directly into the Phone 1 field of the Local Address Type and does not need to be mapped. The phone number will be formatted in CAMS Enterprise as per the Domestic Phone Mask settings in CAMS Manager >Configuration >General 2 tab.
• Current_State - Moved directly into the State field of the Local Address Type. Incoming text that is exactly matched in CAMS does not need to be mapped. The two-letter State Code can be either in the Display Text field or in the ISO Code field in the CAMS Enterprise State Codes Glossary Lookup Table. If CAMS does not have the two-letter value in either the Display Text or ISO Code field, you can map the values. To map a value, the exact incoming text should be entered in the ACES2FieldValue column of the ACES2ValueMapping table and the UniqueID of the State (Glossary Category 1003) you wish to map the value to should be entered in the CAMSMappedValue column. A blank entry must be mapped in the ACES2ValueMapping table in order to accommodate blank values coming in; for blank values, the ACES2FieldValue field should be blank and the CAMSMappedValue field should contain "0" (without the quotes).
• Current_Street_1 - Moved directly into the Address 1 field of the Local Address Type and does not need to be mapped.
• Date_of_Birth - Moved directly into the Student Admissions Information >Demogr. tab >Date of Birth field and does not need to be mapped.
• Date_Undergraduate_Degree - Moved directly into the Student Degree >Grad Dt field and does not need to be mapped.
• Decision_Status_Short - Must be mapped in the ACES2ValueMapping table. To map a value, put the exact incoming numerical value in the ACES2FieldValue column. In the CAMSMappedValue column, enter the UniqueID from the ProspectApplicantStatusRef SQL table of the Applicant Status value that you wish to use. The ProspectApplicantStatusRef table contains the values from the CAMS Prospect/Applicant Status Lookup Reference table.
• Ethnicity_1_Long - Incoming text that is exactly matched in the CAMS Origins Glossary Lookup table does not need to be mapped. To map a value, enter the exact incoming text in the ACES2FieldValue column of the ACES2ValueMapping table; the UniqueID of the ethnicity (Glossary Category 81) you wish to map the value to should be entered in the CAMSMappedValue column.
• First_Name_Applicant - Moved directly into the First Name field in Student Admissions Information and does not need to be mapped.
• Formatted_SSN - Moved directly into the SSN field in Student Admissions Information and does not need to be mapped.
• Gender_Applicant - Gender must be mapped in the ACES2ValueMapping table, mapping "F" and "M" to the CAMS values. These fields are entered in the mapping table by default and do not require further action.
• High_LSAT_Score - Moved directly into the Score1 field of the LSAT Test in Student Admissions Information Test Scores and does not need to be mapped.
• Last_Name_Applicant - Moved directly into the Last (name) field in Student Admissions Information and does not need to be mapped.
• LSAC_Acct_No - Moved directly into the LSAC # field in Student Access and does not need to be mapped.
• LSAT_Test_Date - Moved directly into the Test Date of the LSAT Test in Student Admissions Information Test Scores and does not need to be mapped.
• Native_Language_Long - Incoming text that is exactly matched in the CAMS Native Language Glossary Lookup table does not need to be mapped. To map a value, enter the exact incoming text in the ACES2FieldValue column of the ACES2ValueMapping table; the UniqueID of the language (Glossary Category 56) you wish to map the value to should be entered in the CAMSMappedValue column. A blank entry must be mapped in the ACES2ValueMapping table in order to accommodate blank values coming in; for blank values, the ACES2FieldValue field should be blank and the CAMSMappedValue field should contain "0" (without the quotes).
• Occupation - Moved directly into the Occupation field in Student Admissions Information and does not need to be mapped.
• Perm_Zip_Code - Moved directly into the Zip Code field of the Billing and Home Address Types; does not need to be mapped.
• Permanent_City - Moved directly into the City field of the Billing and Home Address Types; does not need to be mapped.
• Permanent_Day_Phone_Number - Moved directly into the Phone 1 field of the Billing and Home Address Types; does not need to be mapped. The phone number will be formatted in CAMS Enterprise as per the Domestic Phone Mask settings in CAMS Manager >Configuration >General 2 tab.
• Permanent_State - Moved directly into the State field of the Billing and Home Address Types. Incoming text that is exactly matched in CAMS does not need to be mapped. The two-letter State Code can be either in the Display Text field or in the ISO Code field in the CAMS Enterprise State Codes Glossary Lookup Table. If CAMS does not have the two-letter value in either the Display Text or ISO Code field, you can map the values. To map a value, the exact incoming text should be entered in the ACES2FieldValue column of the ACES2ValueMapping table and the UniqueID of the State (Glossary Category 1003) you wish to map the value to should be entered in the CAMSMappedValue column. A blank entry must be mapped in the ACES2ValueMapping table in order to accommodate blank values coming in; for blank values, the ACES2FieldValue field should be blank and the CAMSMappedValue field should contain "0" (without the quotes).
• Permanent_State_of_Residency - Moved directly into the Resident State/Prov field on the Student Admissions Information. Incoming text that is exactly matched in CAMS does not need to be mapped. The two-letter State Code can be either in the Display Text field or in the ISO Code field in the CAMS Enterprise State Codes Glossary Lookup Table. If CAMS does not have the two-letter value in either the Display Text or ISO Code field, you can map the values. To map a value, the exact incoming text should be entered in the ACES2FieldValue column of the ACES2ValueMapping table and the UniqueID of the State (Glossary Category 1003) you wish to map the value to should be entered in the CAMSMappedValue column. A blank entry must be mapped in the ACES2ValueMapping table in order to accommodate blank values coming in; for blank values, the ACES2FieldValue field should be blank and the CAMSMappedValue field should contain "0" (without the quotes).
• Permanent_Street_1 - Moved directly into the Address 1 field of the Billing and Home Address Types; does not need to be mapped.
• Preferred_Name - Moved directly into the Preferred field in Student Admissions Information and does not need to be mapped.
• Prefix_Applicant - Moved directly into the Salutation field in Student Admissions Information and does not need to be mapped.
• Previous_Last_Name - Moved directly into the Maiden Name field in Student Admissions Information and does not need to be mapped.
• Primary_Email - Moved directly into the E-Mail 1 field of the Billing, Home, and Local Address Types and does not need to be mapped.
• Schedule_Option_Long - This field is required in your ACES2 report but is not mapped initially in CAMS. This field is for future use.
• Suffix_Applicant - Moved directly into the Suffix field in Student Admissions Information and does not need to be mapped.
• Term_Long - This is combined with the ACES2 field Year_Entering and must be mapped to the TermCalendarID using Term_Plus_Year. See Term_Plus_Year below.
• Term_Plus_Year - This is a combination of the ACES2 fields Term_Long and Year_Entering. This must be mapped in the ACES2ValueMapping table and will populate the Exp Entrance Term field in Student Admissions Information. To map a value, enter the ACES2 Term_Long and the ACES2 Year_Entering values (with no spaces or other characters) in the ACES2FieldValue column (for example, SEP2012, where the ACES2 values would be SEP and 2012 for the two ACES2 fields) and the TermCalendarID from the TermCalendar table for the Term you wish to map to.
• Time_Long - Incoming text that is exactly matched in the CAMS EnrollmentType Glossary category Lookup table does not need to be mapped. To map a value, enter the exact incoming text in the ACES2FieldValue column of the ACES2ValueMapping table; the UniqueID of the EnrollmentType (Glossary Category 1010) you wish to map the value to should be entered in the CAMSMappedValue column.
• Type_Long - Incoming text that is exactly matched in the Student Type Glossary category Lookup table does not need to be mapped. To map a value, enter the exact incoming text in the ACES2FieldValue column of the ACES2ValueMapping table; the UniqueID of the Student Type (Glossary Category 77) you wish to map the value to should be entered in the CAMSMappedValue column.
• Undergraduate_Degree_Long - Incoming text that is exactly matched in the Earned Degrees Reference Lookup table does not need to be mapped. To map a value, enter the exact incoming text in the ACES2FieldValue column of the ACES2ValueMapping table; the UniqueID of the Degree from the DegreeNameRef table should be entered in the CAMSMappedValue column. A blank entry must be mapped in the ACES2ValueMapping table in order to accommodate blank values coming in; for blank values, the ACES2FieldValue field should be blank and the CAMSMappedValue field should contain "0" (without the quotes).
• Undergraduate_LSAC_Code - The incoming Undergraduate_LSAC_Code number must be in the CollegeCode field of the Colleges table for the incoming college. To map a value, enter the College information in the Colleges Reference Lookup table (or Admissions >Lookups >College Names); enter the exact incoming Undergraduate_LSAC_Code number in the College Code field. A blank entry must be mapped in the ACES2ValueMapping table in order to accommodate blank values coming in; for blank values, the ACES2FieldValue field should be blank and the CAMSMappedValue field should contain "0" (without the quotes).
• Undergraduate_Major_Long - Incoming text that is exactly matched in the Major/Minor Reference Lookup table does not need to be mapped. To map a value, enter the exact incoming text in the ACES2FieldValue column of the ACES2ValueMapping table; the MajorMinorID of the MajorMinor table you wish to map the value to should be entered in the CAMSMappedValue column. A blank entry must be mapped in the ACES2ValueMapping table in order to accommodate blank values coming in; for blank values, the ACES2FieldValue field should be blank and the CAMSMappedValue field should contain "0" (without the quotes).
• WeekdayWeekendCombo_Long - Incoming text that is exactly matched in the Attendance Type Glossary category Lookup table does not need to be mapped. To map a value, enter the exact incoming text in the ACES2FieldValue column of the ACES2ValueMapping table; the UniqueID of the Attendance Type (Glossary Category 12) should be entered in the CAMSMappedValue column.
• Year_Entering - This is combined with the ACES2 field Term_Long and must be mapped to the TermCalendarID using Term_Plus_Year. See Term_Plus_Year.
• Yes_Attend_LS - This field is required in your ACES2 report but is not mapped initially in CAMS. Contact Unit4 Education Solutions, Inc. for information on mapping this field in CAMS Enterprise. If used, it must be mapped to a Free Format field in Admissions User Defined.
• Yes_Bach_Degree - This field is required in your ACES2 report but is not mapped initially in CAMS. Contact Unit4 Education Solutions, Inc. for information on mapping this field in CAMS Enterprise. If used, it must be mapped to a Logic (or bit) field in Admissions User Defined.
• Yes_C_and_F - This field is required in your ACES2 report but is not mapped initially in CAMS. This field is for future use.

CAMS Fields Exported to ACES2

Two CAMS fields are exported to ACES2 after successfully importing ACES2 records into Student Admissions Information.

• StudentUID - This field is exported to the Main 1 user defined field in ACES2.
• StudentID - This field is exported to the LSAC Tracking # field in ACES2.

ACES2 Custom Defaults

Any time CAMS saves to a table in the ACES2 import, CAMS can override a field in that table with a value you define in the ACES2CustomDefaults SQL table. You can define when these defaults will be used, either on an insert or update. Defaults override any import values brought in from the ACES2 import. Populating this table is optional and should only be used to override data imported from ACES2. Custom Defaults, if populated, will override imported data for all records imported.

You may add custom defaults for any of the following tables:

• Address
• CAMSUserDefined
• Greeting
• Student
• Student_Address
• StudentAdmScores
• StudentByGPAGroup
• StudentDegree
• StudentDemoByGPAGroup
• StudentDemographics
• StudentHighSchool
• StudentStatus

Any method used by the institution to insert the data into this table must ensure that fields are mapped correctly and use the correct layout.

Below are the ACES2CustomDefaults fields and their descriptions.

• ACES2CustomDefaultID - This column is populated automatically by SQL and cannot be modified.
• CAMSTableName - The CAMS table name of the table you would like to update.
• CAMSFieldName - The field name of the CAMS table you would like to update/override.
• CAMSFieldValue - The value you would like to insert into this table. Note: There is no processing done to this field. In the example in Figure 1 above, a value of 18 would be entered into the ProgramID field of the Student table with no glossary lookups, etc. In this example, the Program with the ProgramID of 18 from the Programs table will be added to all new records imported since DefaultOnInsert is set to “True” but not added or changed for updated records since DefaultOnUpdate is set to “False”.
• DefaultOnInsert - Enter “True” (without the quotes) in this field to indicate that CAMS will use this default when new records are inserted into the table entered in the CAMSTableName column. Enter “False” (without the quotes) if this value is not to be used when new records are inserted.
• DefaultOnUpdate - Enter “True” (without the quotes) in this field to indicate that CAMS will use this default when records are updated in the table entered in the CAMSTableName column. Enter “False” (without the quotes) if this value is not to be used when records are updated.

ACES2 Default Student Activities

Each time a new student record is inserted into CAMS Enterprise via the ACES2 process, CAMS will check the ACES2DefaultStudentActivities table and attempt to create new Student Activities from the entries in this table. If there are no entries in this table or if the ActivityTypeID's do not exist, no Student Activities will be created.

ACES2DefaultStdentActivitiesID - This column is populated automatically by SQL and cannot be modified.

ActivityID - The ActivityID from the ActivityLookup table. This table is what populates the Activity drop-down in CAMS.

ActivityTypeID - The activity type you wish to use.

Notes - The text you would like placed in the Notes section of the activity. This text will be placed in all student records where this activity is created.

DocumentUID - Only applicable if the Activity Type is Export. This is the DocumentUID from the DocumentRef table. A DocumentUID of 0 (zero) must be entered for the activity if it is NOT an Export activity.

EmailRefID - Only applicable if the Activity Type is Email. This is the EmailRefID from the EmailRef table. This value must be NULL if the activity is NOT an Email activity.