Scatters (one record to many records)

Overview

• If you need to distribute the value of one field in the source record to several records in the destination database (in a separate table), you can use Scatter.
• A typical use of this feature, is when you in the source have a comment field (a big text field) with several lines of text, and in the destination you need to spread these text lines across several records in a sub-table (related table). For example MS Dynamics NAV stores comments like that - in a related table called comments, and only one lines of text in each record.
• Another example of a Scatter Transfers, is to add Dimensions to Sales Orders in MS Dynamics NAV (when creating a Sales Order in MS Dynamics NAV).

 

Setup Scatter

1. Select the Transfer where you want to apply Scatter.
2. From the Transfer you select "Scatters". If a Scatter Transfer has already been created you can choose it here or you can create a new.
3. In the "Scatter Transfer" window you setup the scatter transfer much like a normal transfer, with the exception that you only specify the destination side; the source side is implicitly given as the fields are taken from the main record read from the source table.
4. On the Scatter transfer, you can use the "Destination Link Filter" to specify the relation between the related table and the main destination table.
5. The Code of the Scatter transfer should start with the same as the main transfer; and then have ending indicating what the Scatter transfer is doing. For example a main transfer called "CUST01_TO_NAV_ADD" and a scatter transfer called "CUST01_TO_NAV_ADD_COMMENTS". This will ensure that copying the main transfer will work and correctly create a new transfer including any scatter transfer(s).
6. In the Field List of a scatter transfer you could typically use the formula SPLIT to split out a comment field into separate records.
7. There is a special formula that you can use on a ScatterTransfer only. It's called ScatterCounter - you use it on the left side of the Field List like this  ##?'ScatterCounter'     - on the right side you would map this into a Line Number field (for example in MS Dynamics NAV Comments). The ScatterCounter will generate a number starting from 1 and with increment of 1 for each line. You would typically use it in combination with "Delete All" so that all lines for the e.g. Comment are first deleted in the destination and then all the lines are again transferred from the source.