Import and Export

Optional Features

Setting Up Import and Export

About Important Records

Certain information is important to the return process and must be set up for the import and export process to proceed.

Role

The functions you assign to roles are used to either grant access to enhanced import/export functions or to remove access entirely.

CUSTIMPORTEXPORTMAP—specify this function to enable the userto use a custom import/export map
EXPORTCURRENTRESULTS—specify this function and disable it ifyou want to prevent export of current search results
EXPORTSCREENONLY—specify this function to enable the userto export only and disallow import
IMPORTEXPORTANY—specify this function to enable the user to import or export any table on the FSM database
IMPORTEXPORTDELETE—specify this function to restrict the user from deleting data
IMPORTEXPORTMAPLOOKUP—specify this function to enable theuser to use the import/export map lookup
IMPORTEXPORTSCREENONLY—specify this function to enable the user to import and export only to tables that correspond to screens the user can access, by role
IMPORTEXPORTTTABLE—specify this function and select Disable to disable the user from using the T‐table import/export feature
IMPORTEXPORTWIZARD—specify this function and select Disableto disable the user from using the import/export wizard

About Application Parameters

EXPORT_ALLOWED. If you want to allow export, set this value to Y (yes).

EXPORT_BATCH_SIZE. Set this value to the number of records to export in one batch when exporting a large number of records.

EXPORT_COLUMNS_IN_DB_ORDER. If you want to export columns in database order rather than by name alphabetically,

EXPORT_LIMIT. Set this value to the maximum number of records you want to export at one time.

EXPORT_MAP. If you want to allow export using export maps, set this value to Y (yes).

IMPORT_ALLOWED. If you want to allow import, set this value to Y (yes).

IMPORT_BATCH_SIZE. Set this value to the number of records to import in one batch when importing a large number of records.

IMPORT_LIMIT. Set this value to the maximum number of records you want to import at one time.

IMPORT_MAP. If you want to allow import using import maps, set this value to Y (yes).

IMPORT_MPM. If you want to allow execution of perform messages using import, set this value to Y (yes).

IMPORT_T_TABLE. If you want to allow import using T‐tables, including the use of automated T‐table processing, set this value to Y (yes).

T_TABLE_BULK_INSERT. If you want to use bulk insert, which avoidsnon‐ essential policy checks and improves performance by directly creatingpolicy objects, set this value to Y (yes).

T_TABLE_DELETE_ON_SUCCESS. If you want to delete T‐table rows when they are successfully imported, set this value to Y (yes).

T_TABLE_FORCE_SINGLE. If you want to force import of multiple tables to execute one at a time, set this value to Y (yes).

T_TABLE_INITIAL_UPDATE. Set this value to the number of rows at the beginning of the T‐table to include on the Run Log. Value is any positive integer.

T_TABLE_INTERFACE_LOOP_MINUTES. Set this value to the number of minutes until the next processing of T‐table interface records.

T_TABLE_INTERFACE_PERSON_ID. Set this value to the person ID to associate with the processing of T‐table records.

T_TABLE_THREAD_LIMIT. Set this value to the maximum number of threads to allow on the application server when executing T‐table imports for a single table. Value is any positive integer. A value of 1 limits to a single thread. We recommend a value of 10. Do not use a value larger than twice the number of processor cores available on the application server.

T_TABLE_UPDATE_INTERVAL. Set this value to the number of rows in the T‐table to skip logging until writing the next Run Log record. Value is any positive integer.

About the Import/Export Map Screen

This screen is used to set up import/export maps. You can map columns in your file to columns in FSM and you can specify relationships that enable you to import multiple records for each row in your file.

Splitting and Merging Columns

You can split or merge information when importing or exporting. Multiple columns can be merged or a single column can be split. For strings youcan also trim the unwanted text.

To perform these operations, the Import/Export Map screen uses a Function field for each map entry. For each piece of information a separate map entry is created and a function is specified that describes how to treat the information in that entry.

Merge. The merge() function combines two or more columns. A merge() function with no argument concatenates the values. You can specify a string enclosed with quotes if you want to insert text between the values. For example, merge(", ") concatenates the previous value with the current value and inserts a comma followed by a space between the values.

The merge() function is not specified on the first map entry but on second and subsequent map entries.

Split. The split() function separates one column into multiple columns. The split() function has one required argument and one optional argument. The first argument specifies the separator you want to use to identify where the split occurs. The second argument specifies which value is returned. For example, split(",",2) splits comma‐separated information and returns the second value. When multiple split() functions are specified, they are always from the perspective of the original column value.

The split() function is specified on each map entry.

Substring. The substring function() collects the desired number of characters from a column and discards the rest. The substring() function has one required argument and one optional argument. The first argument specifies the character position, starting at one. The second argument specifies the maximum number of characters to return; if no number is specified, all remaining available characters are returned. For example, substring(10,4) returns a maximum of four characters beginning with the tenth character. When multiple substring() functions are specified, they are always from the perspective of the original column value.

If you want to perform multiple functions on one column, you can concatenate functions using a period (.) between them and subsequent functions are performed on the results of previous functions. For example, split(" ",2).substring(2,2) splits a column at the first space, returns the second value, and of that second value, returns a maximum of two characters beginning with the second character.

Examples

The following table shows an example of an import map:

FSM Table Name	FSM Column Name	Column Name	Function	Description
request	cust_prob_descr	prob1		Because this spreadsheet column is being merged, the Function field is blank
request	cust_prob_descr	prob2	merge(“–”)	The spreadsheet columns prob1 and prob2 are merged into the FSM column cust_prob_descr with a dash between them.
request	req_category	type	split(ʺ,ʺ,3)
address	city	address4		Because this spreadsheet column is being merged, the Function field is blank
address	state	address4	merge(ʺ, ʺ)	The FSM columns city and state are merged into the spreadsheet column address4 with a comma followed by a space between them
address	zippost	address4	merge(ʺ, ʺ)	The FSM column zippost is merged onto the end of spreadsheet column address4 with a comma and a space before it.

Header

Buttons

New—used to create a new map record
Delete—used to delete a map record
Save—used to save changes to a map record
Select—used to select an existing map record to use as a template for a custom map record

Significant Fields

Field Label	Description
Name	Identifies the import/export map.
Allow Import	Indicates whether this map can be used forimport.
Allow Export	Indicates whether this map can be used for export

Table Map

Buttons

New—this button adds a table to the map
Delete—this button deletes a table from the map

Significant Fields

Field Label	Description
Sequence	Identifies in what order the tables are loaded into memory. For example, if you will be accessing child tables on export, you must first specify the related parent table.
FSM Table Name	Identifies a table that can be referenced in the column map below.

Column Map

The column map area is used to do two things:

Identify the columns to import or export and the headings to use in the file; these rows contain a value in the Column Name field
Identify the relationship between child tables and their parent tables; these rows do not contain a value in the Column Name field

In general, the processing order (sequence) is not significant. However, we recommend grouping the row that describes the column to export together with the rows that describe the relationship between child and parent columns.

Buttons

New—this button adds a table and column combination to the map record
Delete—this button deletes a table and column combination from the amp record

Significant Fields

Field Label	Description
Sequence	Identifies the order in which the row is evaluated.
FSM Table Name	Identifies the FSM table to be mapped.
FSM Column Name	Identifies the column to be mapped.
Column Name	Identifies the column as specified in the spreadsheet or other file to be imported or exported. These identifiers must be the first row.
Function	Identifies how fields are split or merged, as described above.
Default Value	Identifies the value used as a constraint duringthe import or export process. During import, the field value is replaced with this specified value. During export, only records with this value are exported.
Parent Table	For columns on a specified child table, identifies the related parent table.
Parent Column	For columns on a specified child table,identifies the related parent column.
Group Name	For table columns that are exported more than once, identifies the column that contains the value used to group them together.
Parent Group Name	For child table columns that are exported more than once, identifies the column on the parent table that contains the value used to group them together.

About the Setup Wizard Global Codes Screens

These screens are used to set up the items that appear on the setup wizard. You can remove entries that were supplied by us and add your own entries. The three screens have the same fields. The following global codes are available:

SETUPWIZARDADD—this table defines the items that can be added
SETUPWIZARDEXPORT—this table defines the items that can be exported
SETUPWIZARDUPDATE—this table defines the items that can be updated

Significant Fields

Field Label	Description
Code Value	Identifies the import/export map that can be used.
Sequence	Identifies the order in which entries appear on the setup wizard.
Description	Identifies the entry text that appears on the setup wizard. Overridden by a message when a message ID is specified.
Message ID	Identifies the message used to specify the entry text that appears on the setup wizard. This message overrides the text specified in the description
Active	Indicates whether this entry appears on the setup wizard.
Access Group	Identifies who can view this entry.