Making data load work with Foundation data customizations

Staging forms are the load forms where data is loaded and validated. Once the data is validated, the data is pushed to the target forms. This section describes how the workflow in the dataload staging forms is structured.

Staging forms have Public-Hidden permission. All fields on staging forms have Public-Write permission.

Staging form filters

You can customize the following types of filters. These types of filters run on modify and only on records that have not been promoted:

• Filters that run on validation—The filters that perform the validations are coded in the 200-299 execution order range. For fields that must be set for use in validation, the filters with those actions are coded in this range. Validation filters look at the keyword VALIDATE.
• Filters that run on promote—The calculation filters that set the fields needed for promotion, and the filters that perform the actual promotion (push to target forms) are coded in the 700-798 execution order range. Promotion filters look at keyword LOAD.

The following table lists the execution order and filter actions for the staging forms.

Staging form fields

Fields on staging forms are grouped into sections:

• The first section of the staging form contains fields that provide information about the processing of the staging form. They include fields for ID, data load status, error flag, error code, and error message. These fields are not promoted to the target form.
• The second section of the staging form contains fields that contain the data being imported. These fields correspond to the columns of data entered on the staging form. These fields are promoted to the target form.
• The third section of the staging form contains fields that are calculated from data in the second section These fields are read-only; the values are entered only through filter calculations. These fields are promoted to the target form.
• The fourth section of the staging form contains system fields.

Hidden fields that are not in the view are used for temporary calculation by workflow.

The following figure shows these sections:

Dataload staging forms dependencies

The following tables show the dependencies between the dataload staging forms.

Foundation staging forms

Process Setup staging forms

Transactional staging forms

Example of adding custom fields to target forms

When a new field is added to a target form, you can incorporate the field into the Data Management tool. To determine the staging form equivalent of the target form into which you are loading data, see How tabs map from spreadsheet to target form.

In the following procedure, a new required character field called Employee ID, with field ID 536871147, has been created in the CTM:People form. The requirement is to incorporate this new field into the Data Management tool so that the contents of this field can be loaded at the same time as the other out-of-the-box fields.

To add a custom field to CTM:People form

1. Add the Employee ID field to the CTM:LoadPeople staging form.
2. Add a new column called Employee ID in the CTM_LoadPeople tab of the People.xlsx spreadsheet. 
   This spreadsheet can be downloaded from Spreadsheet Management. You can use this column to populate and load the contents of the custom field at the same time as the other People records.
3. Update the People transformation using BMC Atrium Integrator. Go to File > Open and select the People transformation.
4. Modify the Excel input step:
   1. Double-click on the CTM_LoadPeople Excel input step. In the Files tab, highlight ${DMTATTACHPATH} in the Selected files table and click. Click next to the File or directory field and select People.xlsx in the directory in which the file is saved. Click so that the files is included in the Selected files table.
   2. Go to the Sheets tab, click Get sheetname(s) and select all the sheets except for Instructions.
   3. Go to the Fields tab, click Get fields from header row. Click Yes when asked whether to clear the field list before starting.
   4. Go to the Files tab, in the Selected files table and replace the path to People.xlsx (including the xlsx file name) with ${DMTATTACHPATH}.
   5. Click OK to save the Excel input step.
5. Modify the AR output step:
   1. Double-click on the CTM:LoadPeople AR output step and click New... in the AR Output window.
   2. Enter valid connection information and click Test to verify it.
   3. Click OK.
   4. Go to the Edit Field Mappings tab and click Edit Mapping.
   5. Select the Employee ID from the left column and map it to the Employee ID in the right column.
   6. Go back to the General tab and re-select the connection to BMC Remedy AR Server.
   7. Click OK and select File > Save to save the transformation.

This mapping is required for importing the modified spreadsheet into the staging form.

After this procedure, you can load new People records that contain the customized Employee ID column. For some forms, however, you must first modify the filter on the staging form before you can promote the data.

To modify workflow to promote custom fields

For most forms, you do not need to modify the workflow for creating data on the target forms. However, you will have to modify the workflow that updates the data from the staging forms to the target forms. These filters have defined mappings.

1. Copy the new field from the target form to the staging form.
2. View the filters on the staging form.
3. Open the filter that pushes to the target form. 
   This filter is in the 700-799 range. For example, on the FIN:LoadCostCenterUDAAssoc form, the filter is called FIN:DCU:CreatCostCenterUDA_750_FIN'!
4. On the If Action tab, in the fields area, select the Name and select Value > Field. 
   The Name indicates the field name on the target form. Select Value > Field to select the field name on the staging form. These two fields might be the same field name.
5. Save your changes.
   In addition to the above steps, you may have to add validation steps to the transformation and validation workflows in the staging or load forms per your requirement and customization needs. The customization needs may differ for each customer.