The Data Field Links tab houses the information that connects each Data Dictionary field with column names on the database tables or queries.

Select a field: displays all Data Dictionary field names. An asterisk (*) denotes that the field has not been linked. The New button allows you to make a new Data Dictionary field. Clicking on a field will show the Database Field Linkage dialog box where the following information is entered:

Field: displays the name of the field selected.

From (table): enter the name of, or select, a table that includes the column containing the values. Clicking the button allows you to select a table from the database. The Lookup dialog box presents the list of available tables, from which you Select a table by clicking on it.

Column (value): enter the name of, or select, the column from the selected table which contains the values for the field. Clicking the button allows you to select a column name from the selected table. The Lookup dialog box presents the list of available columns from which you can Select a column from the table: where the name of the table is presented. Clicking on the name of the column will put a check mark in front of its name. Clicking OK saves the selection.

Column (eff date): is used for effective date arrays. Enter or select a column that contains the effective dates for the corresponding values of the field.

Column (start date): is used for Start and Stop date arrays. Enter or select a column that contains the beginning (start) dates of the period that correspond to the values of the field.

Column (stop date): is used for Start and Stop date arrays. Enter or select a column that contains the ending (stop) dates of the period that correspond to the values of the field.

Where: allows you to filter the data and exclude unnecessary values.

Example: suppose the earnings table has five columns: Person ID, Start_date, Stop_date, Earnings and EarningsType. In the EarningsType column, a value of “BE” represents basic earnings; “BO” represents bonus earnings and “ME” represents moving expenses. If the earnings needed were basic earnings, the Where clause would contain: EarningsType = “BE”. This filters out bonus earnings and moving expenses.

If you are using Include global WHERE on the PersonID tab, the global WHERE clause will be appended to the the Where specified here using an AND.  If WHERE is not used (i.e., empty), then the global WHERE clause becomes the ​de facto ​WHERE.

Person ID Column: needs to be provided only if the column containing the Person ID in the table has a different name from what was provided on the Person ID tab, otherwise it is left blank. For example, suppose almost all tables have a column called SocSecNumber and the other tables have a column called SSN. SocSecNumber would be entered on the Person ID tab and SSN would be entered as the Person ID on only those fields where the social security number is denoted by the column name SSN.

Override SQL allows you to enter SQL code to extract the data for this field. It uses Structured Query Language (SQL) rather than using the linkages discussed. Checking the Override SQL box will ghost out the previous entries to show they won’t be used. Clicking on the SQL Statement… button will present the SQL Override Statement box in which you can provide an SQL statement. See the SQL Override Statement article for more information. If you are using Include global WHERE on the PersonID tab, you will have to manually add the global WHERE to your SQL override. 

Translate character input allows you to translate character values from the client to integers for array and scalar fields in ProAdmin. When a column (field) on the database is defined as a character field, this line will be available (un-ghosted). Otherwise, this line will be ghosted and unavailable. This feature is especially useful if the database has several character strings that represent the same thing. Checking this box allows access to the Table... button where you can enter values on the Character to Numeric Translation dialog box. 

The Clear button erases all fields and statements from the Database Field Linkage dialog box.

Click OK to save your changes. When the OK is clicked, any entry in the SQL Override Statement box is saved even if the Validate button on the SQL Override Statement produces errors.