Logical Mapping Tables and Columns

You have the ability to map data into the HIGH_SCHOOL, LAST_ORG_ATTEND, and GRAD_YR_LAST_ORG columns on the CANDIDATE table. However, you should be very cautious about using these mapping columns if you are also mapping data to the columns of the ORG_TRACKING mapping table. There is a tie in the J1 code between the data stored in the CANDIDATE table and the AD_ORG_TRACKING table in the database.  For example, if a change is made in one window (i.e., the Candidate tab of the Maintain Candidacy window) for the last high school or the last organization, the corresponding column(s) in the other window (i.e., the Organizations tab of the Maintain Candidacy window) is/are updated to ensure that the two tables stay in sync. Additionally, the Graduation Year column on the Candidate tab of the Maintain Candidacy window may be updated from the submitted organization data depending upon the value of the Candidate Grad Year configuration (set through the Configure Function window). Therefore, if data from a form is mapped to both sets of mapping columns, it is possible that the data may become out of sync or that one value may overwrite the other. Jenzabar recommends that you consistently use the mapping columns for one of the tables rather than using both mapping tables in the same form.

Logical mapping tables and columns are ones that are logically grouped together but actually map to a different table and column in the J1 database.

Mailing address, phone, and email data can be submitted using the ADDRESS_MASTER mapping table. However, the data is saved to different contact tables based on the data submitted for each mapping row.

 

Table Name

Column Name

Physical Table/Column

ADDRESS_MASTER

ADDR_CDE

Address Data

  •  NameAddressMaster.ADDR_CDE

  • NameAddressMaster.AddressCodeDefAppID*

Phone Data

  • NamePhoneMaster.PhoneTypeDefAppID*

Email and Other Contact Data

  • AlternateContactMethod.AlternateContactTypeDefAppID*

* Using the appropriate AppID value

ADDRESS_MASTER

ADDR_LINE_1

Address Data

  • AddressMaster

Email Data (where EMAIL_ADDR = 'Y')

  • AlternateContactMethod.AlternateContact

ADDRESS_MASTER

ADDR_LINE_2

AddressMaster

ADDRESS_MASTER

ADDR_LINE_3

AddressMaster

ADDRESS_MASTER

ADDR_PRIVATE

NameAddressMaster

ADDRESS_MASTER

ADDR_STS

NameAddressMaster

ADDRESS_MASTER

ADDR_TYPE

NameAddressMaster

ADDRESS_MASTER

ATTENTION_LINE

Address Data

  • NameAddressMaster

Phone Data

  • NamePhoneMaster.PhoneContactName

ADDRESS_MASTER

CITY

AddressMaster

ADDRESS_MASTER

COUNTRY

Address Data

  • AddressMaster

Phone Data

  • PhoneMaster

ADDRESS_MASTER

COUNTY

AddressMaster

ADDRESS_MASTER

DATA_PHONE

PhoneMaster.PHONE

ADDRESS_MASTER

DATA_PHONE_STS

NamePhoneMaster.PHONE_STS

ADDRESS_MASTER

DTE_CONFIRMED

Address Data

  • NameAddressMaster

Phone Data

  • NamePhoneMaster

Email and Other Contact Data

  • AlternateContactMethod.DateConfirmed

ADDRESS_MASTER

EMAIL_ADDR

AlternateContactMethod

ADDRESS_MASTER

END_DTE

Address Data

  • NameAddressMaster

Phone Data

  • NamePhoneMaster

Email and Other Contact Data

  • AlternateContactMethod

ADDRESS_MASTER

EXTENSION

PhoneMaster

ADDRESS_MASTER

FAX_PHONE

PhoneMaster.PHONE

ADDRESS_MASTER

FAX_PHONE_STS

NamePhoneMaster.PHONE_STS

ADDRESS_MASTER

LOCALITY

AddressMaster

ADDRESS_MASTER

PHONE

PhoneMaster.PHONE

ADDRESS_MASTER

PHONE_PRIVATE

NamePhoneMaster

ADDRESS_MASTER

PHONE_STS

NamePhoneMaster.PHONE_STS

ADDRESS_MASTER

START_DTE

Address Data

  • NameAddressMaster

Phone Data

  • NamePhoneMaster

Email and Other Contact Data

  • AlternateContactMethod

ADDRESS_MASTER

STATE

AddressMaster

ADDRESS_MASTER

STOP_ADDR_MAIL

NameAddressMaster

ADDRESS_MASTER

UDEF_10A_1

NameAddressMaster_UDF

ADDRESS_MASTER

UDEF_11_2N_1

NameAddressMaster_UDF

ADDRESS_MASTER

UDEF_1A_1

NameAddressMaster_UDF

ADDRESS_MASTER

UDEF_1A_2

NameAddressMaster_UDF

ADDRESS_MASTER

UDEF_1A_3

NameAddressMaster_UDF

ADDRESS_MASTER

UDEF_2A_1

NameAddressMaster_UDF

ADDRESS_MASTER

UDEF_2A_2

NameAddressMaster_UDF

ADDRESS_MASTER

UDEF_3A_1

NameAddressMaster_UDF

ADDRESS_MASTER

UDEF_3A_2

NameAddressMaster_UDF

ADDRESS_MASTER

UDEF_5A_1

NameAddressMaster_UDF

ADDRESS_MASTER

UDEF_DTE

NameAddressMaster_UDF

ADDRESS_MASTER

UDEF_ID_1

NameAddressMaster_UDF

ADDRESS_MASTER

UPDATE_SOURCE

NameAddressMaster

ADDRESS_MASTER

YR_END_MON_DAY

NameAddressMaster

ADDRESS_MASTER

YR_START_MON_DAY

NameAddressMaster

ADDRESS_MASTER

ZIP

AddressMaster.PostalCode

BIOGRAPH_ETHNIC_RACE

ETHNICITY

ETHNIC_REPORT_DTL.ETHNIC_RPT_DEF_NUM

BIOGRAPH_ETHNIC_RACE

ETHNICITY_RACE_COMMENT

ETHNIC_RACE_REPORT.COMMENT

BIOGRAPH_ETHNIC_RACE

RACE

RACE_REPORT_DTL.RACE_RPT_DEF_NUM

CANDIDACY_ATTACHMENT

ATTACHMENT_TEXT

ATTACHMENT.ATTACHMENT

CANDIDACY_ATTACHMENT

ATTACHMENT_TYPE

ATTACHMENT.ATTACHMENT_TYPE_CDE

NAME_MASTER

BIRTH_NAME

NameMaster

NAME_MASTER

CURRENT_ADDRESS

NameMaster

NAME_MASTER

EMAIL_ADDRESS

AlternateContactMethod.AlternateContact (when an alternate contact type = '*EML')

NAME_MASTER

FIRST_NAME

NameMaster

NAME_MASTER

IS_FERPA_RESTRICTED

NameMaster

NAME_MASTER

LAST_NAME

NameMaster

NAME_MASTER

MIDDLE_NAME

NameMaster

NAME_MASTER

MOBILE_PHONE

PhoneMaster.PHONE

This is linked to the NamePhoneMaster record for the ID with a 'Mobile' phone type.

 

NAME_MASTER

MOBILE_PHONE_PRIVA

NamePhoneMaster.PHONE_PRIVATE (when phone type = 'Mobile')

NAME_MASTER

MOBILE_PHONE_STS

NamePhoneMaster.PHONE_STS

Data is saved with a 'Mobile' phone type.

 

NAME_MASTER

NAME_PRIVATE

NameMaster

NAME_MASTER

NAME_STS

NameMaster

NAME_MASTER

NAME_TYPE

NameMaster

NAME_MASTER

NICKNAME

NameMaster

NAME_MASTER

PREFERRED_NAME

NameMaster

NAME_MASTER

PREFIX

NameMaster

NAME_MASTER

PRIMARY_RESPONSIBL

NameMaster

NAME_MASTER

STOP_ALL_MAIL

NameMaster

NAME_MASTER

SUFFIX

NameMaster

NAME_MASTER

TITLE

NameMaster

NAME_MASTER

UDEF_10A_1

NameMaster

NAME_MASTER

UDEF_11_2_N_1

NameMaster

NAME_MASTER

UDEF_1A_1

NameMaster

NAME_MASTER

UDEF_1A_2

NameMaster

NAME_MASTER

UDEF_1A_3

NameMaster

NAME_MASTER

UDEF_1A_4

NameMaster

NAME_MASTER

UDEF_1A_5

NameMaster

NAME_MASTER

UDEF_2A_1

NameMaster

NAME_MASTER

UDEF_2A_2

NameMaster

NAME_MASTER

UDEF_2A_3

NameMaster

NAME_MASTER

UDEF_3A_1

NameMaster

NAME_MASTER

UDEF_3A_2

NameMaster

NAME_MASTER

UDEF_3A_3

NameMaster

NAME_MASTER

UDEF_5A_1

NameMaster

NAME_MASTER

UDEF_5A_2

NameMaster

NAME_MASTER

UDEF_DTE_1

NameMaster

NAME_MASTER

UDEF_DTE_2

NameMaster

NAME_MASTER

UDEF_ID_1

NameMaster

NAME_MASTER

UDEF_ID_2

NameMaster

NAME_MASTER

UPDATE_SOURCE

NameMaster

ORG_TRACKING

AD_ORG_TR_UDEF_10_1

AD_ORG_TRACKING.UDEF_10_1

ORG_TRACKING

AD_ORG_TR_UDEF_11_2N_1

AD_ORG_TRACKING.UDEF_11_2N_1

ORG_TRACKING

AD_ORG_TR_UDEF_2A_1

AD_ORG_TRACKING.UDEF_2A_1

ORG_TRACKING

AD_ORG_TR_UDEF_3A_1

AD_ORG_TRACKING.UDEF_3A_1

ORG_TRACKING

AD_ORG_TR_UDEF_ID_1

AD_ORG_TRACKING.UDEF_ID_1

ORG_TRACKING

ADDR_CDE

NameAddressMaster.ADDR_CDE

NameAddressMaster.AddressCodeDefAppID (with the appropriate AppID value)

ORG_TRACKING

ADDR_LINE_1

AddressMaster.ADDR_LINE_1

ORG_TRACKING

ADDR_LINE_2

AddressMaster.ADDR_LINE_2

ORG_TRACKING

ADDR_LINE_3

AddressMaster.ADDR_LINE_3

ORG_TRACKING

BEGIN_DTE

ORG_TRACKING.BEGIN_DTE

ORG_TRACKING

CITY

AddressMaster.CITY

ORG_TRACKING

CREDITS_EARNED

AD_ORG_TRACKING.SELF_REPORTED_CREDITS_EARNED

ORG_TRACKING

DEGREE

AD_ORG_TRACKING.SELF_REPORTED_DEGREE

ORG_TRACKING

END_DTE

ORG_TRACKING.END_DTE

ORG_TRACKING

GPA

AD_ORG_TRACKING.SELF_REPORTED_GPA

ORG_TRACKING

GRAD_DATE

AD_ORG_TRACKING.SELF_REPORTED_GRADUATION_DTE

ORG_TRACKING

HAS_GRADUATED

AD_ORG_TRACKING.SELF_REPORTED_HAS_GRADUATED

ORG_TRACKING

LAST_HIGH_SCHOOL

AD_ORG_TRACKING.LAST_HIGH_SCHOOL

ORG_TRACKING

LAST_ORG

AD_ORG_TRACKING.LAST_ORG

ORG_TRACKING

ORG_NAME

NameMaster.LAST_NAME + FIRST_NAME + MIDDLE_NAME

The ORG_TRACKING.ORG_NAME can be mapped to the name fields (as is currently specified above) OR it can be mapped to a NameMaster.ID_NUM value. The Review Internet Submission window handles the processing differently for a name versus a value containing all digits because J1 Desktop assumes it is an ID Number if all digits are sent.

ORG_TRACKING

ORG_TR_UDEF_10A_1

ORG_TRACKING.UDEF_10A_1

ORG_TRACKING

ORG_TR_UDEF_11_2_1

ORG_TRACKING.UDEF_11_2_1

ORG_TRACKING

ORG_TR_UDEF_11_2_2

ORG_TRACKING.UDEF_11_2_2

ORG_TRACKING

ORG_TR_UDEF_1A_1

ORG_TRACKING.UDEF_1A_1

ORG_TRACKING

ORG_TR_UDEF_1A_2

ORG_TRACKING.UDEF_1A_2

ORG_TRACKING

ORG_TR_UDEF_1A_3

ORG_TRACKING.UDEF_1A_3

ORG_TRACKING

ORG_TR_UDEF_1A_4

ORG_TRACKING.UDEF_1A_4

ORG_TRACKING

ORG_TR_UDEF_1A_5

ORG_TRACKING.UDEF_1A_5

ORG_TRACKING

ORG_TR_UDEF_1A_6

ORG_TRACKING.UDEF_1A_6

ORG_TRACKING

ORG_TR_UDEF_1A_7

ORG_TRACKING.UDEF_1A_7

ORG_TRACKING

ORG_TR_UDEF_2A_1

ORG_TRACKING.UDEF_2A_1

ORG_TRACKING

ORG_TR_UDEF_2A_2

ORG_TRACKING.UDEF_2A_2

ORG_TRACKING

ORG_TR_UDEF_2A_3

ORG_TRACKING.UDEF_2A_3

ORG_TRACKING

ORG_TR_UDEF_2A_4

ORG_TRACKING.UDEF_2A_4

ORG_TRACKING

ORG_TR_UDEF_3A_1

ORG_TRACKING.UDEF_3A_1

ORG_TRACKING

ORG_TR_UDEF_3A_2

ORG_TRACKING.UDEF_3A_2

ORG_TRACKING

ORG_TR_UDEF_3A_3

ORG_TRACKING.UDEF_3A_3

ORG_TRACKING

ORG_TR_UDEF_5A_1

ORG_TRACKING.UDEF_5A_1

ORG_TRACKING

ORG_TR_UDEF_5A_2

ORG_TRACKING.UDEF_5A_2

ORG_TRACKING

ORG_TR_UDEF_DTE_1

ORG_TRACKING.UDEF_DTE_1

ORG_TRACKING

ORG_TR_UDEF_DTE_2

ORG_TRACKING.UDEF_DTE_2

ORG_TRACKING

ORG_TR_UDEF_ID_1

ORG_TRACKING.UDEF_ID_1

ORG_TRACKING

ORG_TR_UDEF_ID_2

ORG_TRACKING.UDEF_ID_2

ORG_TRACKING

RANK

AD_ORG_TRACKING.SELF_REPORTED_RANK

ORG_TRACKING

SIZE

AD_ORG_TRACKING.SELF_REPORTED_SIZE

ORG_TRACKING

STATE

AddressMaster.STATE

ORG_TRACKING

ZIP

AddressMaster.ZIP

RELATED_PERSON

ADDR_CDE

Address Data

  • NameAddressMaster.ADDR_CDE

  • NameAddressMaster.AddressCodeDefAppID (with the appropriate AppID value)

Phone Data

  • NamePhoneMaster.PhoneTypeDefAppID (with the appropriate AppID value)

RELATED_PERSON

ADDR_LINE_1

AddressMaster.ADDR_LINE_1

RELATED_PERSON

ADDR_LINE_2

AddressMaster.ADDR_LINE_2

RELATED_PERSON

ADDR_LINE_3

AddressMaster.ADDR_LINE_3

RELATED_PERSON

CITY

AddressMaster.CITY

RELATED_PERSON

COUNTRY

AddressMaster.COUNTRY

RELATED_PERSON

EMPLOYER

NameMaster.LAST_NAME + FIRST_NAME + MIDDLE_NAME

RELATED_PERSON

EMPLOYER_RELATIONSHIP

RELATION_TABLE.REL_CDE

RELATED_PERSON

FIRST_NAME

NameMaster.FIRST_NAME

RELATED_PERSON

LAST_NAME

NameMaster.LAST_NAME

RELATED_PERSON

MIDDLE_NAME

NameMaster.MIDDLE_NAME

RELATED_PERSON

ORG_CONTACT_NAME

AddressMaster.ATTENTION_LINE

RELATED_PERSON

ORG_NAME

NameMaster.LAST_NAME + FIRST_NAME + MIDDLE_NAME

RELATED_PERSON

PHONE

PhoneMaster.PHONE

RELATED_PERSON

REL_CDE

RELATION_TABLE.REL_CDE

RELATED_PERSON

STATE

AddressMaster.STATE

RELATED_PERSON

WORK_ADDR_CDE

NamePhoneMaster.PhoneTypeDefAppID (with the appropriate AppID value)

RELATED_PERSON

WORK_PHONE

PhoneMaster.PHONE

RELATED_PERSON

ZIP

AddressMaster.ZIP

TEST_SCORES

DTE_TAKEN

TEST_SCORES.DTE_TAKEN

TEST_SCORES

REQUESTED_SCORES_BE_SENT

TEST_SCORES.REQUESTED_SCORES_BE_SENT

TEST_SCORES

TEST_ELEM_CDE_1

TEST_SCORES_DETAIL.TST_ELEM

TEST_SCORES

TEST_ELEM_CDE_10

TEST_SCORES_DETAIL.TST_ELEM

TEST_SCORES

TEST_ELEM_CDE_2

TEST_SCORES_DETAIL.TST_ELEM

TEST_SCORES

TEST_ELEM_CDE_3

TEST_SCORES_DETAIL.TST_ELEM

TEST_SCORES

TEST_ELEM_CDE_4

TEST_SCORES_DETAIL.TST_ELEM

TEST_SCORES

TEST_ELEM_CDE_5

TEST_SCORES_DETAIL.TST_ELEM

TEST_SCORES

TEST_ELEM_CDE_6

TEST_SCORES_DETAIL.TST_ELEM

TEST_SCORES

TEST_ELEM_CDE_7

TEST_SCORES_DETAIL.TST_ELEM

TEST_SCORES

TEST_ELEM_CDE_8

TEST_SCORES_DETAIL.TST_ELEM

TEST_SCORES

TEST_ELEM_CDE_9

TEST_SCORES_DETAIL.TST_ELEM

TEST_SCORES

TEST_ELEM_SCORE_1

TEST_SCORES_DETAIL.TST_SCORE

TEST_SCORES

TEST_ELEM_SCORE_10

TEST_SCORES_DETAIL.TST_SCORE

TEST_SCORES

TEST_ELEM_SCORE_2

TEST_SCORES_DETAIL.TST_SCORE

TEST_SCORES

TEST_ELEM_SCORE_3

TEST_SCORES_DETAIL.TST_SCORE

TEST_SCORES

TEST_ELEM_SCORE_4

TEST_SCORES_DETAIL.TST_SCORE

TEST_SCORES

TEST_ELEM_SCORE_5

TEST_SCORES_DETAIL.TST_SCORE

TEST_SCORES

TEST_ELEM_SCORE_6

TEST_SCORES_DETAIL.TST_SCORE

TEST_SCORES

TEST_ELEM_SCORE_7

TEST_SCORES_DETAIL.TST_SCORE

TEST_SCORES

TEST_ELEM_SCORE_8

TEST_SCORES_DETAIL.TST_SCORE

TEST_SCORES

TEST_ELEM_SCORE_9

TEST_SCORES_DETAIL.TST_SCORE

TEST_SCORES

TEST_REPORT_TYPE

test_scores.test_report_type

TEST_SCORES

TestLocationTypeDefinitionAppID

test_scores.testlocationtypedefinitionappid

TEST_SCORES

TST_CDE

TEST_SCORES.TST_CDE