Advanced Settings for Custom Field import

Watch a Demonstration here : 

Advanced setting key components & detailed instructions for Standard ERP

Below is a description of necessary parameters to define simple custom fields and a description of the process by steps.

1. Find the field name

First, you need to find out the register and REST API code for the field. You can find codes for registers in the HansaWorld source File import documentation page. Then you can use the code to find code for REST API field in HansaWorld Module Technics → Reports → Import/Export format. 

You can see in the example screenshot below the register code (1.) and codes for REST API fields (2.).

2. Configure custom field settings

Register code and register REST API code

[register_code.register_rest_api_code]

Where register_code is register code and register_rest_api_code is the register REST API code. This will start the block of configuration parameters for the custom field. 

Name for the custom field

A name for the field describing the logic in the most understandable for you way e.g.

name = "Price"

Cube Name

The cube name where the custom field will be available. You can find the full cube names in Analyze section in your flex.bi or if you would like to create a new cube you can use define the name here. 

cube_name = "HansaWorld Invoices"

Dimension name in flex.bi for the register

Name of the dimension that should be created.

dimension_name = "Invoice"

Dimension level name

The name of the level within the specified dimension.

level_name = "Invoice Item"

Dimension item level names for registers that do not have dimensions: "Transaction Item", "Simulation Item".

Data type of this field

One of the supported data types - integer, value, string e.g.

data_type = "string"

By default, the string maximum length is 255 characters. If there is a need to import longer string values, then use the additional limit setting to specify the maximum length.
Other available types are text, integer, decimal, date, datetime.

If you use the decimal data type, then by default the precision (maximum number of digits) is 15 and the scale (digits after the decimal point) is 2. You can change these defaults with the additional precision and scale settings.

When defining fields for key_field, name_field, bind_field flex.bi will recognise that they will be strings and this parameter can be skipped.

Additional options

If you would like to import custom field as additional dimension then add

dimension = true

If you would like to import custom field as a measure then add

measure = true

If you would like to do operation with the incoming data before fields are imported you can use custom javascript code

javascript_code ='''
  if (doc.rows) {
    doc.rows.forEach(function(row){
    row.negative_rowGP = String(Number(row.rowGP) * -1);
  })
}
'''

If you would like to share the added measure to another cube then specify the cube name with the cubes_for_measure_sharing parameter:

cubes_for_measure_sharing = ["HansaWorld Invoices"]

If you are importing HansaWorld's data with files then the number of the field also needs to be defined. You can find what number is each field in your HansaWorld's "Technics → Reports → Export/import format" by specifying the register's code that you are importing. 

file_field = 4

If an REST API field contains a full stop characters (“.“) or the same REST API should be used twice you can use rest_api_field to define the REST API field name instead. If rest_api_field is used then in order to change the returned value for the whole custom field using Javascript the name in rest_api_field needs to be referenced instead as well. 

rest_api_field = 'T.NAME'

Example of custom dimension definition

[ORVc.LangCode]
name = "Language code"
cube_name = "HansaWorld Sales Orders"
data_type = "string"
dimension_name = "Sales Order"
level_name = "Sales Order"
dimension = true

Example of custom measure definition with javascript that imports the row gross profit but turns the numbers negative:

[QTVc.negative_rowGP]
name = "Quotation negative GP"
cube_name = "HansaWorld Quotations"
data_type = "decimal"
rest_api_item_field = "rows"
measure = true
javascript_code ='''
if (doc.rows) {
  doc.rows.forEach(function(row){
    row.negative_rowGP = String(Number(row.rowGP) * -1);
  })
}
'''

If the data need to be linked to another date field then use custom_date_measure_group to define the name for the measure group and also the prefix for the duplicated measures.

custom_date_measure_group = 'Planned'

Example for both record and item level below.

[SHVc.Planned_Send_date]
rest_api_field = 'PlanSendDate'
name = 'Planned Send Date'
dimension_name = 'Delivery'
cube_name = 'HansaWorld Deliveries'
custom_date_measure_group = 'Planned'

[SHVc.Plan_Send_DateRow]
name = 'Item Planned send Date'
dimension_name = 'Delivery'
level_name = 'Delivery Item'
cube_name = 'HansaWorld Deliveries'
custom_date_measure_group = 'Planned'
javascript_code ='''
  if (doc.rows && doc.PlanSendDate) {
    doc.rows.forEach(function(row){
      row.Plan_Send_DateRow = doc.PlanSendDate;
    })
  }
'''

Add additional custom field definitions as needed in advanced settings text area field and then click Save.

3. Import the custom field

Finally, Edit the HansaWorld source application again and in "Cube properties" section select your new defined custom field for import.

Save the application settings and start the import. After the import is finished start using your property, measure or dimension in your reports.

In case of any questions please contact support@flex.bi for more information. 

Defining dimension in a separate table

If you would like to have a dimension definition that has a hierarchy, has a key and name separately or you would like to add additional properties to the dimension then you can use a more advanced definition. The definition consists of 3 parts:

• A field that contains the keys for dimension elements
• A field that contains the name for dimension elements (optional)
• A field that binds the dimension to measures

Key field

Using the custom field definition and an additional parameter key_field you can define that this field will be used as a key for the new dimension.

[INVc.item_code]
name = 'Item Code'
dimension_name = 'Item2'
cube_name = "HansaWorld Invoices"
rest_api_field = "Code"
key_field = true

Name field

Using the custom field definition and an additional parameter name_field you can define that this field will be used as a name for the new dimension.

[INVc.item_name]
name = 'Item Name'
dimension_name = 'Item2'
cube_name = "HansaWorld Invoices"
name_field = true
rest_api_field = "Name"

Bind field

While the dimension members will be imported with the key_field and name_field definitions a bind field also needs to be defined so that flex.bi can know what field from the register where measures is imported will be linked to this new dimension. For this 2 parameters have to be used: bind_field_name and for_custom_dimension.

bind_field_name is the field's rest api code that we defined previously (the "item_code" from this "[INVc.item_code]") and the for_custom_dimension is the name of the dimension we want to link the measures import to.

If the bind field from the measures import register is available only on the item/row level rest_api_item_field parameter can be used to specify this.

[IVVc.item_code_bind]
name = 'Item Code bind'
cube_name = "HansaWorld Invoices"
for_custom_dimension = 'Item2'
rest_api_item_field = "rows"
bind_field_name = 'item_code'
rest_api_field = 'ArtCode'

Available parameters for Advanced Settings

Below you can find a list of all the available custom field parameters.

Skipping and deleting records

If it's necessary to skip or delete records during import, define a custom field "delete_and_skip". All the records that will have "true" value for this custom field will be skipped and also deleted if they have have been already imported. Example below.

[TdmGramatSL.delete_and_skip]
default_custom_field = true
name = 'Delete and Skip'
dimension_name = 'Transaction'
cube_name = 'Horizon Financials'
skip_request = true
property = true
javascript_code = '''
  switch(doc.STATUSS) {
    case "1":
      break;
    default:
      doc.delete_and_skip = 'true';
    }
'''

Advanced Settings examples

In the example below, a combination of Price List, Price and Customer registers are used to create 3 level hierarchy.

[PLVc.F_PriceList]
name = 'Price List Code'
dimension_name = 'Price'
cube_name = 'Hansaworld Invoices'
level_name = 'Price list'
rest_api_field = "PLCode"
key_field = true

[PLVc.F_PriceListName]
name = 'Price List Name'
dimension_name = 'Price'
cube_name = 'Hansaworld Invoices'
level_name = 'Price list'
rest_api_field = "PLCode"
name_field = true

[PLVc.F_PriceItemCode]
name = 'Price Item Code'
dimension_name = 'Price'
cube_name = 'Hansaworld Invoices'
level_name = 'Item'
rest_api_field = "ArtCode"
key_field = true

[PLVc.F_PriceItemName]
name = 'Price Item Name'
dimension_name = 'Price'
cube_name = 'Hansaworld Invoices'
level_name = 'Item'
rest_api_field = "Comment"
name_field = true

[PLVc.F_PriceCustomerCode]
name = 'Price Customer Code'
dimension_name = 'Price'
cube_name = 'Hansaworld Invoices'
level_name = 'Customer'
rest_api_field = "CustCode"
key_field = true

[PLVc.F_PriceCustomerName]
name = 'Price Customer Name'
dimension_name = 'Price'
cube_name = 'Hansaworld Invoices'
level_name = 'Customer'
rest_api_field = "CustCode"
name_field = true

[IVVc.PriceListProperty]
name = "Price list Property"
cube_name = 'Hansaworld Invoices'
data_type = "string"
dimension_name = 'Invoice'
level_name = 'Invoice'
rest_api_field = 'PriceList'

[IVVc.RowPriceListCode]
name = 'Invoice Price List Code'
cube_name = 'Hansaworld Invoices'
for_custom_dimension = 'Price'
for_custom_dimension_level = 'Price list'
bind_field_name = 'F_PriceList'
rest_api_item_field = "rows"
javascript_code = '''
if (doc.rows) {
  doc.rows.forEach(function(row){
    if (doc.PriceList){
      row.RowPriceListCode = doc.PriceList;
    }
  })
}
'''

[IVVc.ArtCode]
name = 'Invoice Item Code'
cube_name = 'Hansaworld Invoices'
for_custom_dimension = 'Price'
for_custom_dimension_level = 'Item'
bind_field_name = 'F_PriceItemCode'
rest_api_item_field = "rows"

[IVVc.RowCustCode]
name = 'Invoice Customer Code'
cube_name = 'Hansaworld Invoices'
for_custom_dimension = 'Price'
for_custom_dimension_level = 'Customer'
bind_field_name = 'F_PriceCustomerCode'
rest_api_item_field = "rows"
javascript_code = '''
if (doc.rows) {
  doc.rows.forEach(function(row){
    if (doc.CustCode){
      row.RowCustCode = doc.CustCode;
    }
  })
}
'''

In the example below, account_code value will be populated from the credit_account_code for the credit transaction and from debit_account_code for debit transaction. 

[FinancialDocLine.account_code]
skip_request = true
default_custom_field = true
name = 'Transaction Account Code'
dimension_name = 'Transaction'
cube_name = 'Jumis Financials'
for_custom_dimension = 'Account'
bind_field_name = 'account_code'
credit_debit_dimension_column = true

[FinancialDocLine.credit_account_code]
default_custom_field = true
name = 'Credit Account Code'
dimension_name = 'Transaction'
cube_name = 'Jumis Financials'
rest_api_field = 'CreditAccount.AccountCode'

[FinancialDocLine.debit_account_code]
default_custom_field = true
name = 'Debit Account Code'
dimension_name = 'Transaction'
cube_name = 'Jumis Financials'
rest_api_field = 'DebetAccount.AccountCode'

In the example below, a Customer dimension with two hierarchy levels Customer type and Customer is defined. This dimension contains both key and name fields for both levels and is linked  to the measures (Transaction dimension) using customer_code key field. In this example, customer_name is defined both for Customer and Transaction dimension import. This allows for Customer dimension member creation from the Transaction record in case a matching customer code is not found in the Customer dimension.

[TdmGramatSL.customer_code]
default_custom_field = true
cube_name = 'Horizon Financials'
name = 'Transaction Customer Code'
rest_api_field = 'D.K.KODS'
for_custom_dimension = 'Customer'
bind_field_name = 'customer_code'

[TDdmKlBaseSar.customer_type_code]
default_custom_field = true
cube_name = 'Horizon Financials'
dimension_name = 'Customer'
level_name = 'Customer Type'
name = 'Customer Type Code'
rest_api_field = 'K.KTIPS'
key_field = true

[TDdmKlBaseSar.customer_type_name]
default_custom_field = true
cube_name = 'Horizon Financials'
dimension_name = 'Customer'
level_name = 'Customer Type'
name = 'Customer Type Name'
skip_request = true
name_field = true
javascript_code = '''
cust_type = doc.K_KTIPS;
switch(cust_type) {
  case "0":
    doc.customer_type_name =  "Active";
    break;
  case "1":
    doc.customer_type_name =  "Potential";
    break; }
'''

[TDdmKlBaseSar.customer_code]
default_custom_field = true
cube_name = 'Horizon Financials'
dimension_name = 'Customer'
name = 'Customer Code'
rest_api_field = "K.KODS"
key_field = true

[TDdmKlBaseSar.customer_name]
default_custom_field = true
cube_name = 'Horizon Financials'
dimension_name = 'Customer'
name = 'Customer Name'
rest_api_field = "K.NOSAUK"
name_field = true

[TDdmKlBaseSar.customer_pvn_reg_no]
default_custom_field = true
cube_name = 'Horizon Financials'
dimension_name = 'Customer'
name = 'VAT Registration No'
rest_api_field = "K.PVN_REGNR"
property = true