The Additional Fields tab is used to add and specify additional columns in the resulting Portfolio instance. After a Portfolio object is saved and executed, the resulting Portfolio instance is formed containing the additional columns specified on this tab.
The Additional Fields tab contains a mini toolbar and a table. The mini toolbar buttons are grouped according to their purpose. They are identical to the mini toolbar buttons on the Models tab.
Initially, the Additional Fields table is empty. To add an Additional Field entry, click the Add row button to add a new row and specify the settings: Additional Field Name, Description, Title, and so on (see the following descriptions).
Additional Field Name is used for the column headings in the resulting Portfolio instance if the Description and Title fields are left blank. No space characters are allowed in this field.
Description is used for the column headings in the resulting Portfolio instance if the Title field is left blank. This can be a more detailed definition of the column to supplement the title.
Title is the default column heading in the resulting Portfolio instance.
Type is used to specify a data type for the data contained in the Additional Column in the resulting Portfolio instance. The Type list contains the following data types.
- “VARCHAR” defines string characters limited by size. These characters include uppercase and lowercase Latin letters, numbers, punctuation marks, and special characters.
- “INTEGER” defines numbers without a fractional or decimal component (e.g. 12, -1, 0, 10, and 222).
- “FLOAT” defines numbers with a decimal component (e.g. -1.2478, 0.26, and 10.4).
- “DATE” defines data expressing date and time (e.g. ‘dd/MMM/yyyy HH:mm;ss’ and ‘MM/dd/yyyy’).
- “TEXT” defines an unlimited size of a string of VARCHAR symbols.
Size is used to set the maximum number of characters allowed in the Additional field so as to not put extra load on the system. This parameter is relevant only when the data type is “VARCHAR” or “TEXT”.
Allow Nulls is used to save a space character or an empty string on the Manual Adjustments screen in the resulting Portfolio instance (if the checkbox is selected).
Clearing the Allow Nulls checkbox instructs the system to not accept missing data and to display the error message (see the following figure) when you attempt to save an empty string or space character on the Manual Adjustment screen. For example, if you work with the Oracle database system and you do not want a space character or an empty string to be saved, you should clear the checkbox.
This parameter is also important for the process of loading data from tables or files. Records with empty cells are rejected, so the process fails.
Default value is used to display a user-specified value between the two single quotes in the field in the additional column cells. The value must be compatible with the data type you select in the Type property. Clicking the Lookup button in the field opens the Value dialog box with a set of SQL macro commands predefined by AxiomSL. The Default Value property replaces missing data source values with the specified default value.
Show Subtotal is used to display the sum of selected values from the lookup source on the Manual Adjustments screen (if the checkbox is selected).
Lookup Source is used for inserting the lookup source values on the Manual Adjustment screen, allowing you to avoid errors in adjusting the required values. Clicking the Lookup button of the Lookup Source property opens the Select Data Source dialog box where you can select CV objects, such as DataSource, Portfolio, Aggregation, FreeFormReport, with the required lookup values. When you select the CV object as a lookup source, the CV object name is inserted in the Lookup Source field. After that, the Value Field and Description Field combo boxes appear in the respective columns. These combo boxes contain lists of the inserted Lookup Source layout fields. The following figure demonstrates how the Select Data Source dialog box looks like when you are choosing a layout field of the lookup_src Lookup Source from the Value Field combo box.
After the Lookup Source parameter is specified and the Portfolio task is executed, the lookup related to the inserted Lookup Source item is activated on the Manual Adjustments button on the Portfolio Viewer screen.
The following figure shows how the selection of a cell from the f_loans_rbi Additional column activates the respective lookup on the Portfolio Viewer screen.
Note: The Manual Adjustments screen is opened by the Manual Adjustments button (see the blue box in the following figure) on the Portfolio Viewer screen.
Value Field is used to select a required field from the specified CV object in the Lookup Source property. The value from this field can be inserted in the resulting Portfolio instance on the Manual Adjustments screen.
Note: The specified data type in the Type property should match the data type of the field selected in the Value Field property.
Description Field is used to select a required description field from the specified lookup object in the Lookup Source property. The values from the Description Field are not inserted on the Manual Adjustments screen. They serve to facilitate the search of the lookup values from the Value Field. The Value Field and Description Field lookup values are displayed in the open Select Value dialog box as shown in the following figure.
Add Lookup Source to Model is used to add other Lookup Source fields into the resulting Portfolio instance (if the checkbox is selected). The data in the columns can be adjusted on the Manual Adjustments screen by selecting a lookup value in the Select Value dialog box.
Optional Join Constraint is used to apply an SQL script, ASL expression, or UDF (predefined by AxiomSL) to stipulate additional data selection.
Relationship to Parent is used to indicate the type of database relationship that exists between the DataSource instance specified as a lookup source and the additional field in the resulting Portfolio instance. The combo box lists the following options: one->one, many->one, one or zero -> one (OUTER join), many or zero -> one (OUTER join), and FULL OUTER join.
Instance Date Selection Rule is used to indicate which method of data selection should be used when adding a DataModel instance to the resulting Portfolio instance. The following data selection methods are represented in the combo box.
- “EQUAL” (default) – The system will select the data from the same date as the added DataModel Instance.
- “LATEST” – The system will select the data from the latest load prior to the DataModel Instance date.