The File menu on the Admissions Import Utility window has the following options:

Option	Description	Notes
Load Profile	Opens the same pop-up window as the Microsoft generic File | Open option, and it allows you to select a specific profile to load. This profile will be used to replace any existing configurations on the Upload Criteria tab.	Save a copy of your current profile before running the Load Profile option if your current profile is specific to a particular source file format.
Save Profile	Opens the same pop-up window as the Microsoft generic File | Save option. Allows you to save different profiles that can be accessed at a later date and used in the Upload Criteria selection.
Clear Profile	Removes all of the rows from the Upload Criteria tab of the Admissions Import Utility window and the corresponding IU_UPLOAD_CRITERIA table.	Save a copy of your current profile before running the Clear Profile option.
Print Setup	Select and set up printers.
Exit	Closes the Admissions Import Utility window.

Rows

If you select the Duplicates tab or the Multiple Matches tab, you will notice that Print Duplicates and Print Multiple Matches are options on these tabs only. Also, the Insert and Delete options may not be available for all tabs.

The Rows menu on the Admissions Import Utility window has the following options:

Option	Description	Notes
Insert	Adds or inserts a row into the grid of the currently selected tab. The Insert option is not available for the Upload Criteria, Duplicates, and Multiple Matches tabs.
Delete	Removes a row from the IU table for the currently selected tab. The Delete option is not available for the Upload Criteria and Multiple Matches tabs.
Save Changes	Saves any Admissions Import Utility window tab changes you made to the Import Utility tables.	The Save Changes option does not actually import the data into the J1 database. You must run the Upload Data option to import the updated data.
Clear Candidate	Removes all rows for the currently selected Temporary ID from each of the Import Utility tabs.  You will be prompted to confirm the delete.  Once you've confirmed the delete, the rows are immediately deleted from the IU staging tables.	For example, temporary ID Number 2 has information in the Name Master table, Address Master table, and Candidacy table, this option clears all three tables of data for temporary ID Number 2.
Assign ID	If a temporary ID Number has been issued for data that has a known system ID Number, you can use this option to manually enter the known ID Number.  You will be prompted for the Temporary ID to update (defaults to the ID of the currently selected row) and the system ID Number.  After the Assign ID button is clicked, each of the tabs that have data for the specified Temp ID will have the Id Num column updated with the specified ID Number.	Because all system tables are updated interactively if this option is selected, keep in mind that this may be a time-consuming process.
Refresh Data	Updates the IU table corresponding to the currently selected tab with the data currently displayed on the tab, including any insertions, deletions, and edits.

Options for working with errors appear only on the Error Log tab.

Before uploading data, the error log should be clear. Any Temp ID numbers on the Error Log tab are ignored during the Upload Data process. Therefore, if there are errors, a decision should be made whether to upload and/or update the remaining information or to wait for more data so the errors can be corrected.

If you do not want to upload the rest of the data at this time, the errors should be left on the error log so you know which ones need to be corrected. If you wish to upload the remaining Temporary ID Numbers for now, leave the error log alone and proceed with the upload option. An exception to this is if these errors were caused by the upload process, you may not be able to continue uploading the other data.

The Database menu on the Admissions Import Utility window has the following options:

Option	Description	Notes
Check Database	Checks for required columns and displays any Temp ID Numbers with missing columns on the Error Log tab. Errors are appended to the list of any existing errors. It also checks for duplicates between the staging database and the J1 database. After the duplicate check is performed, the Duplicates tab is populated. The ID Number column is filled to show the system ID Number that is matched to the data for the Temp ID.	The ID Number on the other tabs is not populated until you perform the Upload Data process.
Clear Database	Completely empties all data from the IU tables except the upload criteria in the IU_UPLOAD_CRITERIA table.	This process clears the staging database; it does not clear the production database.
Clear Duplicates	Completely clears all information in the Duplicates tab from the IU_DUPLICATES table.
Use Soundex for Name	Allows a name search by Soundex during the search for duplicates between the IU tables and the J1 tables. Once you've selected the menu option, the preference is turned on. If you view the Database menu options, you'll notice that there is a check mark next to the Use Soundex for Name option. To turn it off, select the option again.	This process is very time consuming and should be used infrequently for performance reasons.

 

Press the Esc key to stop the upload. To continue, or if the process is terminated abnormally, simply restart the Upload Data process. It will pick up where it left off. Jenzabar recommends that you let the system automatically assign ID Numbers.

The Upload option copies data from holding tables (e.g., IU_NAME, IU_BIOGRAPH,  IU_ADDRESS) into live J1 tables (e.g.,  NameMaster, BIOGRAPH_MASTER, AddressMaster) using the following steps:

1.         A check verifies that all columns marked as REQUIRED in the UPLOAD_CRITERIA table have non-null values. Records are created in an ERROR_LOG for those that have null values.

2.         If there are multiple ID Numbers for a specific Temporary ID Number, an error is displayed. This error forces you to resolve multiple matches. Until there is a one-to-one match, the upload cannot proceed. Use the Multiple Matches tab to determine the correct person.

3.         Once any multiple matches have been resolved, then for all records in the IU_DUPLICATES table, the ID_NUM from IU_DUPLICATES is inserted into all the IU tables for the corresponding Temp ID. For the remaining IU records that do not have ID Numbers, new ID Numbers are generated.

4.         One at a time, a Temporary ID is processed:

a.         If there is an error row for the Temporary ID, it is skipped.

b.         If there is a duplicate row for the Temporary ID,

i.           If M is found for a table, the existing data is merged (i.e. overwritten) with the data in the specified IU table that was defined as USED. During the merging process, the status bar looks similar to: "Temp ID: 1 ID number: 99 Merging: John Q. Student. (Esc to cancel)."

ii.         If M is not found for a table, the table is skipped.

5.         If errors or duplicates are not found, the ID number generated in step 3 is inserted as a new ID number into the columns in the IU tables that were defined as USED. During the inserting process, the status bar looks similar to the following: "Temp ID: 1 ID number: 99 Inserting: John Q. Student. (Esc to cancel)."

a.         Any SQL errors that occur during the insert are written into the ERROR_LOG.

b.          The row(s) are deleted from the IU tables that held the data that was inserted into or updated in for the current Temp ID Number. If data existed in an IU table for a particular Temp ID but none of the columns in the table were marked as USED or the data was not merged with an existing ID Number, the data remains in the IU table rather than being deleted.

c.         The process repeats for the next Temp ID Number.

 In addition, as data is imported from the holding tables into the live J1 tables, the following steps take place for each ID Number as it is being processed:

1.         If data is imported into the TEST_SCORES and/or CANDIDACY tables but not into the CANDIDATE table for an ID Number, a row is automatically created in the CANDIDATE table for the specified ID Number. Only the USER_NAME, JOB_NAME, and JOB_TIME columns are updated in this case.

2.         If the table currently being processed is IU_CANDIDACY, then a check is made to see if a row exists in IU_CANDIDACY for the current Temp ID Number being processed. If not, the process skips to Step 3. If a row for the current Temp ID Number does exist in the table, the following steps are done:

a.         The following columns for the ID_NUM are updated from the IU_CANDIDACY table to the CANDIDATE table:

i.           IU_CANDIDACY.YR_CDE to CANDIDATE.CUR_TR

ii.         IU_CANDIDACY.TRM_CDE to CANDIDATE.CUR_TRM

iii.       IU_CANDIDACY.PROG_CDE to CANDIDATE.CUR_PROG

iv.       IU_CANDIDACY.DIV_CDE to CANDIDATE.CUR.DIV

v.         IU_CANDIDACY.LOCA_CDE to CANDIDATE.CUR_LOC

vi.       IU_CANDIDACY.DEPT_CDE to CANDIDATE.CUR_DEPT

vii.     IU_CANDIDACY.STAGE to CANDIDATE.CUR_STAGE

viii.   STAGE_LVL from STAGE_CONFIG where STAGE = IU_CANDIDACY.STAGE to CANDIDATE.CUR_STAGE_LEVEL

b.         The CANDIDACY.CUR_CANDIDACY is set to "Y" for the ID_NUM where the following is true:

i.           CANDIDACY.YR_CDE = IU_CANDIDACY.TR_CDE

ii.         CANDIDACY.TRM_CDE = IU_CANDIDACY.TRM_CDE

iii.       CANDIDACY.PROG_CDE = IU_CANDIDACY.PROG_CDE

iv.       CANDIDACY.DIV_CDE = IU_CANDIDACY.DIV_CDE

v.         CANDIDACY.LOCA_CDE = IU_CANDIDACY.LOCA_CDE

c.          If the IU_CANDIDACY.STAGE column is marked as USED and if it contains a value, then the following occurs:

i.           A row is added to the STAGE_HISTORY_TRAN table to track the change in the stage.

ii.         The STAGE_CONFIG.ACTION_LST_CDE column is checked for the specified stage. If the ACTION_LST_CDE column is non-null, the specified action list is attached to the candidate's Notepad.

iii.       The STAGE_CONFIG.REQ_LST_CDE column is checked for the specified stage. If the REQ_LST_CDE column is non-null, the specified requirement list is attached to the candidate's imported candidacy.

iv.       The STAGE_CONFIG.CREATE_JICS_ACCT column for each CANDIDACY.STAGE is checked to determine if any of the candidate's candidacies are in a JICS creation stage.

1)         If Y, rows are inserted into the TW_WEB_SECURITY and the TW_GROUP_MEMBERSHIP tables so the API can pass information to JICS to create a JICS account. If the rows already exist in both tables, no rows are added.

2)         If N, a check is performed to see if the ID Number has any other Candidacy rows where CREATE_JIC_SCCT = 'Y'. If not, then rows are deleted from the TW_GRP_MEMBERSHIP table so the API can pass information to JICS to delete the Candidate role.

3)         If X, nothing is done for the Temp ID Number.

3.         After all the data has been imported and if CANDIDATE and/or CANDIDACY rows were created during the import, counselor assignment is determined and the CANDIDATE.COUNSELOR_INITIALS for each imported ID Number is updated.

All of the imported data related to names (last name, first name, middle name, etc.) and is marked as Used on the Upload Criteria tab  appears on the Name tab. This is the first complete check that is made between the outside data source and the system database).

Using the columns marked as Dup Search on the Upload Criteria tab, the Import Utility looks at all of the specified columns that appear on the data source and compares them with all of the corresponding columns that exist in the J1 database. This process creates the initial association between the temporary ID Number and one or more existing ID Numbers in the J1 database and stores the matches in the IU_DUPLICATES table. This paired association is then be used for all further comparisons between data except for the Biograph data.

Address columns marked as Used on the Upload Criteria tab display on the Address tab.  Address data for individuals included on the Name tab shows on the Address tab and has the same Temporary ID Number as is shown on the Name tab.

If any Address columns are marked as Dup Search on the Upload Criteria tab, the Import Utility compares the data to import against any ID Numbers shown on the Duplicates tab that match the corresponding Temporary ID's data.

IfBiographical columns marked as Used on the Upload Criteria tab display on the Biograph tab.  Biograph data for individuals included on the Name tab displays on the Biograph tab and has the same Temporary ID Number as the Name tab.

If any Biograph columns are marked as Dup Search on the Upload Criteria tab, the Import Utility compares the data to import against the corresponding columns in the BIOGRAPH_MASTER table in the database and adds any matching IDs to the IU_DUPLICATES table.  The data for these ID Numbers, along with any matching IDs found from the Name tab, is then  compared against the imported data for any additional matching data.

All of the imported data related to a candidate (current year, current term, current program, current division, current stage, etc.) and in a Candidate column marked as Used on the Upload Criteria tab appears on this tab and has the same Temporary ID Number as is shown on the Name tab.  Any Candidate columns marked as Dup Search are compared against the IDs shown on the Duplicates tab for the corresponding Temporary IDs.

All of the imported data related to a candidacy (year, term, program, division, current stage, admission decision, class code, etc.) and marked Used appears on this tab and is compared against IDs on the Duplicates tab.

All of the Used imported data related to test scores (test code, test sequence, etc.) appears on this tab and is compared against the IDs on the Duplicates tab.

All of the imported data for Used Test Score columns related to test score details (Test Code, Test Sequence, Test Element, Test Percentile, etc.) appears on this tab.  To import data on this tab, a corresponding row for the Temp ID needs to be included in the Test Scores tab.  Multiple test score detail rows can exist for a single row on the Test Scores tab.

Data in Test Score columns marked as Dup Search is compared against the data for any matching IDs on the Duplicates tab.

All of the Used imported data related to Ethnic Race Report appears on this tab, and those marked as Dup Check are compared against the corresponding IDs on the Duplicates tab.

All of the imported data related to Ethnic Report Detail appears on this tab for columns marked as Used.  To import data on this tab, a corresponding row for the Temp ID needs to be included in the Ethnic Race Report tab.  Columns marked as Dup Check on the Upload Criteria tab are compared to the IDs on the Duplicates tab.

All of the Used imported data related to Race Report Detail appears on this tab.  To import data on this tab, a corresponding row for the Temp ID needs to be included in the Ethnic Race Report tab.  Multiple race report rows can exist for a single row on the Ethnic Race Report tab. Columns marked as Dup Check on the Upload Criteria tab are compared to the IDs on the Duplicates tab.

All of the imported data related to military service for columns marked as Used on the Upload Criteria tab appear on this tab and those marked as Dup Check are compared against the corresponding IDs on the Duplicates tab.

All of the imported data related to a candidate's Alternate Identifier appears in this tab if the column is marked as Used, and Dup Check columns are compared against the IDs on the Duplicates tab.

1.       Ensure that the data has been put into the following tables as desired and with codes or values that are defined and allowed in the J1 tables.

??     IU_NAME

??     IU_ADDRESS

??     IU_BIOGRAPH

??     IU_CANDIDATE

??     IU_CANDIDACY

??     IU_TEST_SCORES

??     IU_SCORES_DETAIL

??     IU_ETHNIC_RACE_RPT

??     IU_ETHNIC_RPT_DTL

??     IU_RACE_RPT_DTL

??     IU_MILITARY_SERVICE

??     IU_ALTERNATE_IDENTIFIER

2.       Open the Admissions Import Utility window.

3.       Click the Upload Criteria tab.

4.       Select the Used, Required, and Dup Search checkboxes, depending on the criteria you would like to use for this import and save the data on the tab.  If desired, select File | Save Profile to save the current settings to a .txt file for use during a later import.

If you have another profile to be used, select File | Load Profile. Select the appropriate .txt file and click the Open button. This modifies the Upload Criteria tab to contain the Used, Required, and Duplicate checkboxes that have been associated with the respective profile.

5.       Select Database | Check Database.

6.       If errors already exist on the Error Log tab, the following alert displays: "Delete all records from error log?"

a.       Select Yes to delete the rows from the IU_ERROR_LOG table before continuing with the Check Database process.

b.       Select No to leave the existing errors and append any new errors to the end of the list.

7.       The following message appears: "Check staging database for required columns?" Click Yes.

8.       If no errors have been found, a message stating "No records in Error Log table" appears. Otherwise, the errors are written to the Error Log tab.

9.       The following message appears: "Check staging databases for matches in database?" Click Yes.  However, if errors have been found, you may wish to click No at this prompt so that you can skip the duplicate checking and immediately correct the errors.

10.   Check the Error Log tab to review any reported errors. Once errors have been corrected, repeat the Admissions Import Utility steps starting with Step 1 to ensure that the data is now as the Import Utility expects (i.e., required columns are filled in and entered values match their corresponding definition tables).

11.   Check the Multiple Matches tab to see if any duplicates exist where the search for criteria for a particular Temp ID matches more than one ID in the J1 database (Remaining to Verify will be greater than 0 if there are any Temp IDs with multiple matches). If more than one match is found for a Temp ID, check each matching ID in the Multiple Matches found by Duplicate Search grid to see if one of the existing IDs is the same individual as the Temp ID.

a.       If one of the displayed existing IDs is the correct ID to use for the specified Temp ID, then select the Match checkbox for the matching row and click the OK button.

                                                               i.      At the "Remove those indicated as not matched?" prompt, click Yes. This removes all IDs except the one with the Match checkbox selected from the Duplicates tab.

                                                             ii.      Click Yes to the "Would you like to Save Changes?" prompt. The Verified box is increased by 1, and the Remaining to Verify box is decreased by 1. If any matches remain, the next one is automatically displayed for review and can be processed as noted in Step 13.

b.       If all of the identified IDs in the Multiple Matches found by Duplicate Search grid are different individuals from the Temp ID being reviewed, then click on the Duplicates tab and scroll to the rows matching the person shown on the Multiple Matches tab.

                                                               i.      For each duplicate row, select the Not Match option from the Action drop-down.

                                                             ii.      Select the Rows| Save Changes option.

                                                           iii.      At the "Remove those indicated as not matched?" prompt, click Yes.

                                                           iv.      At the "Would you like to Save Changes?" prompt, click Yes. All of the rows marked with 'Not Match' are removed from the Duplicates tab as well as the Multiple Matches tab. The counts on the Multiple Matches tab are updated accordingly.

                                                             v.      Return to the Multiple Matches tab to continue processing rows with multiple matches until the count in the Remaining to Verify box is 0.

In the following example, ID 199 John Quentin Student is the correct name. Select the Match checkbox beside ID 199 and click OK. IDs 100 and 119 are deleted from the Duplicates tab after confirming the two prompts.

Incoming	ID and Name
John Student	100 John Student
	119 John Q. Student
	199 John Quentin Student

12.   Click on the Duplicates tab to process any displayed duplicate rows. Displayed rows have a Y (Found) indicating in which table(s) a duplicate value was found for each listed Temp ID.  You should review each record on the Duplicates tab to verify if the matched ID is the same person for whom the data is being imported.

a.       If you are certain that the Temp ID from the import record does not represent the same individual as the displayed ID Number, select the No Match option from the Action drop-down. When the data on the Duplicates tab is saved, the Temp ID  is removed from the Duplicates tab, thereby allowing the Temp ID to be imported as a separate individual.

b.       If the Temp ID is the same individual as noted in the ID Number, then you have several options:

                                                               i.      If you do not wish to merge any data for the Temp ID, you can either select the row on the Duplicates tab and choose the Rows | Clear Candidate menu option to remove all rows from the IU tables with the specified Temp ID, or you can set each of the columns on the Duplicates tab that represents a database table to Y (found) or leave it blank (Not Found) for the specified row, which indicates that the import process should ignore the import data for that Temp ID.

                                                             ii.      If you would like to merge all of the data for a particular Temp ID (i.e., insert or update any existing data for the specified ID Number with the import data for the Temp ID), you can select the Match option from the Action drop-down to automatically set the columns on the Duplicates tab representing the various database tables to M (Merge) for the specified row.

                                                           iii.      If you would like to merge some of the data for a listed Temp ID, select the M (Merge) option from the drop-down in the column for each database table in which you would like to insert or update the existing row with the imported data. If you do not wish to use the import data for a table, leave the drop-down value as Y (Found) or blank (Not Found). For example, if you wish to merge the Test Score and Scores Detail information for Temp ID 10 but not the rest of the tables, select M for the row containing Temp ID 10 in both the Test Score and Scores Detail columns but leave the remaining columns with a value of Y or blank.

 

If the value initially displayed for a table is Y (Found), then you can be sure that data already exists in that table for the ID Number and a match was found with the duplicate criteria and you can make an informed decision about whether or not to merge the imported data into (i.e., overwrite) the existing record. However, if the table has an initial value that is blank (Not Found), it could either indicate that no row exists for the ID Number in the table or that no match was found based on the specified duplicate criteria. Therefore, you should be cautious when choosing to merge data for a table with a blank (Not Found) value so that you do not accidentally overwrite valid information with the import data.

13.   Once all duplicates have been handled, you can then import the data into the J1 tables by selecting Upload | Upload Data from the menu.

14.   If errors already exist on the Error Log tab, you will be prompted with "Delete all records from error log?" Select Yes to delete the rows from the IU_ERROR_LOG table before continuing with the Check Database process. Select No to leave the existing errors and append any new errors to the end of the list.

15.   The following prompt appears: "Check staging database for required columns?" Click Yes.

16.   If no errors have been found, the "No records in Error Log table" message appears. Otherwise, the errors are written to the Error Log table.

17.   The following prompt appears: "Check staging database for matches in the J1 database?"  Click Yes if you wish to check for new duplicate IDs.

You can run this check again as part of the Upload Data process, but ideally, you should have run the Check Database option first and already reviewed and made decisions for any matching IDs.

18.   If multiple matches are found that have not yet been handled, the following prompt appears: "There are one or more TEMP_IDs that match with more than one ID_NUMs. Check Multiple Matches tab for details. Press OK to view Multiple Matches and Cancel to stop." Clicking OK or Cancel will stop the upload process and either return you to the current tab or place focus on the Multiple Matches tab.

19.   At this point, all of the data in the IU tables and columns that have been marked as available (i.e., Used is selected on the Upload Criteria tab for the column) is either inserted into the appropriate J1 table or used to update an existing row in the corresponding J1 table. Other processing may also be done depending on the table into which the data is being put.

If a new row is being inserted into the CANDIDACY table, the typical stage change processing will occur.

 

20.   After each Temp ID has been successfully inserted or updated in the J1 tables, all rows in the IU tables associated with the Temp ID are deleted from the IU tables. Rows that were not successfully put into the J1 tables are deleted from the IU tables. Any errors that occur during the import process are appended to the Error Log tab, and focus is on the Error Log tab so that you can see the errors that have occurred.

1.    Open the Admissions Import Utility window.

2.    On the Multiple Matches tab or the Duplicates tab, choose Rows | Print Multiple Matches.

3.         The Error IDs Report window appears.

4.    You may close the report or print the report via the buttons at the bottom of the window.