5.2 The transformation

A central component of a synchronisation of individual systems is the adaptation of data of the particular requirements of the end system.

This performs the transformation in the SIS process. It is subdivided in individual operations which will be processed in a determined sequence. Thereby, each operation can adapt or generate data for the following operation.

The input mask of the transformation is subdivided in two areas: The left side includes the feature buttons and a list of all operations in the transformation. Underneath the list, arrow buttons for postponing the currently openend operation are available. By double clicking in the list, an operation can be opened.

By the button "New", an operation of the transformation can be added.

The button "Save" manifests your input in the current operation in the process. Your changes will get lost without this button, even when you are closing the process dialogue by "OK".

The button "Delete" removes the current operation from the transformation. The "test drive" offers the possibility to implement your transformation with desired data.

Each individual operation disposes a name which can be provided in each mask. In case, already configurated columns are marked in red in their operations, these can currently not be determined anymore. That can occur by a modified source scheme or a changed transformation.

A description of the available operation types is following:

Display value

With this feature, you are defining an image regulation from value couples which can be applied on various columns for postponing values. The search for an appropriate image of the source value does thereby not consider upper and lower cases.

Under input, you find the source columns as well as all generated columns from the precedented operations. Underneath, you find a list with all already selected columns and you can edit these next to the list by the buttons.

At the bottom area, you define the image regulation from comparison value "From" to target value "To". You can process the replacements with the available buttons.

You can export and import the image regulation by "Loading" and "Saving". This happens by XML and enables therefore an easy transfer from foreign sources. The required format is an element with two adjuncts:

The star symbol has a special position. It serves as a final replacement, in case no other regulation is appropriate for the current value. This entry is not required, however, gives you the opportunity to filtrate the source data. Without a star entry, all source data which will not be considered in the regulation, will not be changed.

Unite

Over here, you can assembly several columns with a determined separator sequence to a new column. .

Select the source columns by "Input" and add them to the list. Under "Separator", you can determine the separator sequence which will be inserted between each column. Under "New input", you determine the column name of the result of this transformation. This new column will then be available in the following transformations or the panel allocation.

Change coding

This transformation serves for transferring the values between 2 different codings.

Add the required columns using "Input" and then select the source and target coding. The selected codings will be applied on all selected columns.

Convert Excel date

This feature converts a figure in a date according to Excel display regulations.

• 1 = 01.01.1900 00:00:00
• 2,5 = 02.01.1900 12:00:00

Generate parameter

With this transformation, you can make constant values of other transformations or panel allocations available.

Determine a designation under "New column" and provide a value.

Upper and lower cases

Over here, it is possible to format a value only in upper cases, only in smaller cases or only an upper case for the first letter of the value. 

Select therefore as normal the source column using "Input" and determine the kind of formatting. Afterwards, add the selected settings to the listing.

Trimming

Depending on the type set, this transformation removes all spaces or a desired character from the start, end or both sides of the value.

Using "Input", you can select and add the columns for processing.

Separate phone numbers

This transformation separates a phone number in the three components: country code, area code and phone number in order to correspond to the standard of the CRM.

The separation is thereby based on a code data base. If a separation is not possible, the input value will be returned to the output column "Number".

The value standard country code should improve the quality of the separation of phone numbers without country code. In case, a phone number does not have a country code what will be determined based on "00" and "+", only area codes of the standard country codes will be verified for the separation of the input.

Type formatting

This operation can format data for a certain data type with a certain format. Using "Input", you select the desired source column and then determine the target data type. The formatting is optional and can establish for instance a certain date and figure format. The "New input" defines the name of the new generated column for the result of the formatting.

You can define many different and various formattings in one step.

This transformation will also be used by the SIS administrator if you are dissolving a type conflict in your allocations.

Extract text from Rtf

In Sage CRM, texts will normally be saved and provided as pure text (e. g. with a multiline text panel). If you are saving these panels Rich Text (e. g. the customer memo of the SNC), the tax signs will be provided in the CRM. In order to avoid this, you can extract the pure text using this transformation. Just add the desired panels to the list by button.

Search or separation with regular impression

This operation can search or separate data using a regular impression. As the amount of the results can thereby be different depending on the data, and the SIS process requires absolute columns for the allocation, you need to determine a maximum amount of result columns.

You can define many regular and various impressions for different columns in one transformation step. By using "Input", you select the desired source column and determine with "Type" if it should be searched in your data or if the data should be separated.

The SIS administrator uses this operation for dissolving panel length conflicts. Thereby, the source data will be trimmed if applicable on the maximum length of the target column.

Replace

Similarly to the transformation "Display value", this one is defining a regulation for replacing characters and character sequences in your data. In the upper area, you select the columns their contents should be changed. Underneath, you define the particular search and replacing value.

Separate by template

This feature separates the content of a column by the fixed template in new columns (e. g. separation of the name of a person in first name and surname). In the template, you define the result columns by the placeholder. The template requires separation characters in between.

A new column will be generated and filled per placeholder.

Copy columns

With this feature, columns can be duplicated in order to use them then in e. g. further steps. If you would like to use an information for different targets (e. g. a representative for area and account manager), it may occur that the data need to be transformed (e. g. by displaying values). In this case, you need an unmodified copy of the data.

Decision for first and not empty panel

With this feature, you can select the first and not empty value from a sorted panel quantity and copy in a new column. Therefore, you can define easy decisions in your transformation. For complex requirements, the script transformation is available.

Separate complete name

With this feature, an assembled name if applicable with form of address can be separated in its components.

The lacking of a first name or of the form of address will be verified according to the setting under "Name scheme", but the feature can only be guaranteed in case of a standardised scheme.

The automatic name scheme selects the appropriate format for each value by the search for certain characters. 

Optionally, the usage of a second first name will also be supported.

Demand data

This operation can reload various data from source or target within the particular available scheme objects.

• Connection defines the usage of the source or the target of the currenct process.
• Table selects the scheme object of the particular connection.
• Filtration is the search enquiry which needs to be formulated in the syntax of the particular connection.
• By using "Input" and the additional button, you can insert source data as serial letter panels in the filtration.
• The result column selects the column of the scheme object which needs to be overtaken.
• Um Namenskonflikte zu vermeiden, müssen Sie einen Präfix für die neu angelegten Spalten festlegen.
• In order to avoid name conflicts, you need to determine a prefix for the new generated columns.

The filtration will be generated for each source record. In case, it has already been used in the current transformation, the buffered data will be used. Otherwise, the demand will be implemented to the particular system. Therefore, only as many demands as required will be implemented.

CRM translation code

In many standard scenarios, values will be displayed on CRM selection panels and the actual values will be saved as marking code. The country of the address is an example for this. Many systems save here for instance an ISO value for the country. This will also be saved in the CRM, however, will then be modified by the translations for the output.

In case, you are reading data from the CRM by webservice, what is the standard configuration, selection panels will always be returned translated in the standard language of the CRM. Instead of "DE", the system delivers "Deutschland" what then again needs to be adapted on the requirements of the target system.

In order to maintain again the code for the translated value, you can use this transformation. Thereby, it will always be searched by the German translation value.

Multiple selections will also be supported by this feature.

In the panel language, you need to provide the initial for the standard language of the CRM.

C# Code-Script

If the other transformation types or their combination do not meet your requirements, a programming gateway is available with this one. If you are generating this transformation, the text area includes a template which needs at least to fulfill your script so that an implementation is possible. In addition, all features of .Net Frameworks  are available in order to formulate your script in C#. 

The gateway to your source data will be established by a dictionary. The key corresponds to the column name and the value corresponds to the content. New columns cannot be generated by this transformation. For this you use for instance the paramenter transformation.