SPLIT(data, integer[size] | character[split on character]);

Used with Scatter to split 1 field into several records.

Some databases has a big textfield for comments in a Customer record, whereas e.g. Navision has a separate table with several comment records for each Customer.

To split the field Comment on a Customer record into a separate Comment table you need to setup a Scatter transfer on the Customer transfer.

On the Scatter transfer you then use the split formula in the fieldlist.
##SPLIT("Comment",'; ')

If a Comment field in the source record has a syntax where each comment is separated with ; the split formula will make a record for each comment.

Example1

Example of a Scatter on a Customer transfer:

Source Record:
No.: 10008
Comment: '20082003 Credit Limit exceeded.; 23082003 Notification Sent.; 30082003 Account OK

Destination Record:
No.: 1008
Line No.: 1
Comment Line: 20082003 Credit Limit exceeded

No.: 1008
Line No.: 2
Comment Line: 23082003 Notification Sent.

 

No.: 1008
Line No.: 3
Comment Line: 30082003 Account OK

 

NOTICE THAT

The Field "Line No." is filled using the Auto Generate Key functionality on the Scatter transfer.

To do this you simply mark the checkbox on the Scatter, and map the destination field in the Scatter link list. In the source field syntax must be ##'-1':

 

Scatter Setup:
Link List:
Source Field: No.

Dest. field: No.

Source Field: ##'-1

Dest. field: Line No.

 

Field List:
Source Field: No.

Dest. field: No.

Source Field: ##SPLIT("Comment",'; ')

Dest. field: Comment Line

Example2

##SPLIT("Location"+IF(EQUALS("location2",''),'',':'+"Location2"),':')

This formula will split Location 1 and 2 into two records if both fields have a value. In case the Location2 field is empty only one record would be created.

If Location2 ='' then insert nothing after Value in Location. ('Location Value'+''

If Location2 is not empty then insert (Location Value+':''Location2 value')

 

If there is a value in the location2 field a record is created where the values in location and location2 are separated by :.

Split will then work on that record and split it into 2.