MyRapidi
MyRapidi

Search our Wiki

SPLIT

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

 

Overview

  • 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.

 

Example 1 (Example of a Scatter on a Customer transfer):

Source Record:

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

Destination Record:

No.Line No.Comment Line
1008 1 20082003 Credit Limit exceeded.
1008 2 23082003 Notification Sent.
1008 3 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'. Refer to the setup below:

Scatter Setup:


Link List:

Source FieldDest. field
No. No.
##'-1 Line No.

Field List:

Source FieldDest. field
No. No.
##SPLIT("Comment",'; ') Comment Line

 

Example 2:

##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.