Managing Mapping Tables & Columns
When you create Form Builder forms in JICS, you have the ability to map questions within your forms to tables in the J1 database. That is, when you design your forms, you can specify exactly how questions should be mapped to the database. Eventually, the data that users enter into their forms will be stored in these tables according to the way you mapped them. By default, Form Builder lists several tables to which you may want to map questions. However, you may want to make modifications to the list of available tables. For example, you may want users to be able to map to a custom table that is not listed within JICS and/or to restrict the availability of a table that by default is made available.
Key Concepts
There are three key concepts:
Physical Mapping Tables vs. Logical Mapping Tables
Mapping Column Configurations
Maximizing Mapping Rows
Physical mapping tables are those tables available for mapping whose mapping columns match one-for-one to the actual J1 database table of the same name. For example, NameMaster and BIOGRAPH_MASTER are physical mapping tables. Most of the mapping tables available by default are physical mapping tables, but there are several logical mapping tables defined by default as well. All of the available _UDF tables and any customer-defined tables are physical tables.
Logical mapping tables are tables comprised of mapping columns from multiple related tables. Logical mapping tables are necessary when related information needs to be processed together. For example, the TEST_SCORES mapping table is a logical table made up of columns from the TEST_SCORES and TEST_SCORES_DETAIL tables. The test code required for the TEST_SCORES table is also required for the TEST_SCORES_DETAIL table when referring to a single test. Additionally, multiple element scores need to be associated with that same test. Therefore, putting the columns together into a single logical mapping table makes the processing of the data much simpler.
Note
There are currently two logical mapping columns that are associated with physical mapping tables: NAMEMASTER.ORG_NAME_COMPUTED and CANDIDATE.SOURCE. Both of these columns have special processing available to them.
The NAMEMASTER.ORG_NAME_COMPUTED column combines the data from the LAST_NAME, FIRST_NAME and MIDDLE_NAME columns to show an organization’s name in a single text string rather than showing the data in three separate text strings. This column should only be used when entering or updating an organization's name for the organization's ID Number. Other mapping columns should be used when entering an organization that has an association to the ID Number for whom the data is being submitted. For example, RELATED_PERSON.ORG_NAME should be the mapping column used to indicate the name of an organization related to the ID Number for whom data is being submitted, whereas NAMEMASTER.ORG_NAME_COMPUTED would be used to display the name of an organization whose data is being updated with the submission.
The CANDIDATE.SOURCE column is used to indicate that the submitted source value should be put into the first available source column in the CANDIDATE table. If you wish to put the source value into a particular source column, then the desired source column should be specified in the mappings instead.
When you work in J1 Desktop to manage the availability of tables for mapping, you have the ability to make several choices.
You can allow the system to pre-populate response fields in forms with values from the J1 database. This option is available for all of the physical mapping tables representing J1 Desktop model tables, as well as the BIOGRAPH_ETHNIC_RACE logical mapping table. For example, if you have mapped the BIRTH_DTE column from the BIOGRAPH_MASTER table to a question in a form, you may want the system to pre-populate the form with information for the logged in user from that column (if any exists), which eliminates the user having to type in his or her birth date again. This preference is controlled by a field labeled Is Available to Pre-populate on the Manage Mapping Tables window.
On the window that submission reviewers use to process data from the submission holding tables, you can allow a blank response in a submission to override a value that may already exist in the J1 database (or conversely, you can specify that a blank value cannot override an existing value). Whether this value should be selected depends upon the question. For example, for a question such as “Address Line 2,” you may prefer to let the blank value override the data currently stored in the database. However, if the user leaves his or her birth date blank, you may prefer that this blank value not override the value in the database. This preference is controlled by a field labeled Allow Blank to Override DB on the Manage Mapping Tables window.
You can restrict the columns available for mapping. A table may be available for mapping, but you do not need to make every column available. You can indicate that specific columns are or are not available to designers of forms. This preference is controlled via the Is Available for Mapping checkbox on the Review Submission Mapping Details window.
You can modify a column to specify that if a question maps to that column, the question will be required. This preference is controlled by the Nullable field on the Review Submission Mapping Details window. If the Nullable checkbox is selected, the question will not be required. If it is not selected, the question will be required. By default, the Nullable checkbox is not selected for certain columns in J1 Desktop model tables. Therefore, you may want to review the setup for each column in the table(s) you are using and, if appropriate, make changes.
You can enter default text for a column. This value is relevant if a form designer maps the column to custom data. (For a definition of custom data, see the Hidden Data and Custom Data topic). If default data has been defined within J1 Desktop and a form designer in JICS maps custom data to the column, the text box that is displayed for the entry of custom data will default to this value. This value is controlled through the Default Value text box on the Review Submission Mapping Details window. The Default Value column can also be used with the Donor Profiles feature to provide a default value with which to pre-populate a question when creating a new row for a category. This allows for a Form Builder question to display a static value for a new entry and any current database value for the selected ID Number when editing an existing data row.
Note
The Is Available to Pre-populate and Allow Blank to Override DB settings apply to the mapping tables regardless of which form is using it. Therefore, you need to coordinate with other offices using the more common mapping tables (i.e., NAMEMASTER and ATTRIBUTE_TRANS) to ensure that the settings are appropriate for all offices. For example, the Admissions Office may not need to be able to pre-populate a question with the DEATH_DTE column, but the Advancement Office may need that column available for pre-population. Therefore, it is better to mark the column as available for pre-population even though a particular form may not make use of it. The other three configurations described above (Is Available for Mapping, Nullable, and Default Value) are specific to a particular form type, so less coordination among offices is needed for these settings.
When mapping a Form Builder question to a mapping column, you will need to decide which mapping table to use. Once you have selected a specific mapping table, you will be taken directly to the screen to select a mapping column if no other questions have been mapped to the table. If one or more questions are already mapped to the table, you will be presented with a screen from which to select a row from the selected mapping table. You may then elect to use an existing mapping row or create a new mapping row for the table. Once you have chosen a mapping row option, you will then be shown the screen to select a mapping column for the question.
Each mapping table in J1 Desktop is defined to allow either a set number of complex mapping rows or an unlimited number of mapping rows
Notice
You may only submit data for one NAMEMASTER row per submission, so the maximum number of mapping rows is set to 1 for the NAMEMASTER mapping table. On the other hand, a single submission may submit an unlimited number of mapping rows for the ADDRESSMASTER table. If you map more rows to a mapping table than is allowed, you will be notified of the problem when the form is validated.
Note
The Donor Profiles forms are unique in that the forms should only ever submit one mapping row of data for each mapping table for each submission.
Although the ADDRESSMASTER mapping table, for example, allows for multiple mapping rows to be defined on a form, only one row should be used for Donor Profiles forms.