Data Sheets
Apart from Start sheets, data processing documents also consist of data sheets. Below is an example of a data sheet that enters bond transactions in Transaction Board
Data sheets are the Excel worksheets that define what data to create, fetch, and/or update. These data are structured and processed row by row. The four main components in data sheets are: control column, services and options, keywords and arguments, and the output columns.
Control Column
The control column of a data sheet is always located in column C. It is used to control how each row is treated. The allowable characters in a control column are:
- #: declares a comment row which will not be read when executing the task.
- H or h: declares a header row containing keywords, arguments, and field names
- D or d: declares a data row containing the actual data to be processed.
Note that data processing tasks stop once an empty cell or any other characters besides those specified above are found in the control column.
Output Columns
Columns A and B serve as output columns in data processing. These two columns output the status of the task, indicating “OK” or “Error” in column A, while giving more detailed error messages in column B.
Argument Values
The input value should be set in the data row and must match the column type.
NULL Values
A value can be set to null by setting {NULL} as the value. For string values it is also possible to set “ ” (space) to set and empty string.
Services and Options
All data processing tasks require a service header that instructs OmniFi what system or service it should connect to. To configure this, the first header of a data sheet always contains the keyword *Service followed by which service will be used in the data processing. It is possible to use different services in one document, allowing users to process different types of data in a single Excel.
When defining which service to use, the keyword *Service can take one of the following arguments:
- Transaction
- StaticData
- EntityBoard
- WebServices
- Database
- IONBus
Within *Service is another keyword *Option that is used to configure the standard factory settings of the service chosen.
Different service types as well as the available options for each are shown in the table below.
| Service Type | Option Name | Default Option Value | Comments |
|---|---|---|---|
| Transaction | "mode" | ADMIN | |
| "view" | No Default Value | To use several views, separate the values using a comma, e.g., "repo,matching". This is only available for WSS v7 and later. | |
| Static Data | "entity-type" | No default value | This option is mandatory |
| "default_sdm_access" | 3 | Set to 1 when deleting entities | |
| "view_hidden" | TRUE | ||
| "mode" | |||
| EntityBoard | "mode_id" | ADMIN | |
| "view" | No default value | This option is mandatory. Different from view for Transaction. Only one view can be set here | |
| WebServices | No options available | ||
| Database | "DsnAlias" | No default value | The DSN alias defined in OmniFi Admin |
| "Module" | No default value | The name of the SQL module | |
| "Statement" | No default value | The name of the statement in the module | |
| IONBus | No options available | ||
| Extension | "module" | No default value | The Id of the extension. The Id is the extensions relative path to the extension folder, e.g. “DataProcessing\Import.pyplugin” |
| "view" | No default value | All option values will be sent to the extension. Then it is up to the extension to handle the value. |
To configure a service correctly, users must adhere to the following guidelines:
*Servicemust be the first keyword in the header.- A
*Serviceblock cannot be followed by any other blocks in the same header, i.e., all functions after this block will be ignored. *Optionsmust be used inside*Service. It is the only one permitted be used within this block.
Example of a service configuration to use the Transaction service. The option mode is set to ADMIN, while the view are repo and matching.
Additional Options
In addition to the options in the table above, the follow additional options are also available for the services:
- BatchSize defines the maximum number of processed data rows before a new factory is created. This default value is 500.
- RetryOnError defines the number of times that data processing will attempt a new connection to the comKIT service whenever a data row gets an error. Upon successful connection, the data row will be re-executed. If you do not want to retry, set this value to 0 or exclude it from the options.
- ContinueOnError is a TRUE or FALSE option that determines whether data processing should proceed to the next row when an error is encountered. When set to FALSE, the task will stop. The default value is TRUE.
- ContinueOnWarning is a TRUE or FALSE option that determines whether data processing should proceed to the next column when a warning is encountered. When set to TRUE, the execution will continue as if no error has occurred. The warning will be written to the result column as information only. The default value is FALSE.
Example of a service configuration using additional options
Updated over 2 years ago