Data Management > Tab Mappings > Options > Functions & External Lookup

In the relevant Tab you will find a feature to let you define if you want to perform an (or multiple) FUNCTION-OPERATION in a specified column

Functions can be "linked" together or not

dmc_option_operation_main_s

The PARENT Functions display in RED and the CHILD ones in BLEU

The CONDITION ID the Function is linked to is displayed in the "L" column

dmc_option_operation

You can also UPPERCASE or LOWERCASE a String or even CONCATENATE a STRING entered in the 'Value to Use' entry field BEFORE or AFTER the value of the selected Source Column value

If you want a space concat separator then type in 'space' (this is the only time when it is needed to 'write' space)

You can also use only the X LEFT or RIGHT characters from the source column's value

You can also extract a SUBSTRING starting at X position and of Y length of the source column's value

You can also add delete multiply or divide by a PERCENTAGE the value of a column.

You can use a FORMAT or a DEFORMAT function on your data and see onscreen the format selected

dmc_option_datetime

You can also use a DEFAULT value to be used (like to append a '.' at the end of a column value).

You can also CAPITALIZE a column to clean it out of all uppercased entries by users

Here are the symbols used to 'separate' words and capitalize the 1st of each separated group

' : quote

. : period

- : hyphen

_ : underscore

/ : division bar

Parse (n) : will extract from a STRING the N th.WORD

If the value of (n) entered is bigger than the number of words in the column at runtime, then the LAST word will be extracted.

Example :

If you have a column in destination for Price with VAT and in the source Table only a column with Price Vat less - in this case you check the OPERATION box and define to perform a MULTIPLICATION on the source column which you chose in the listbox and o use an operand of say 0.83612 to apply on that columns value at transfer time

DMC will take the source column value and multiply it by the operand and fill the destination selected column with the result

IF you define several Functions on the SAME source column (non linked ones) :

DMC will consider them in SEQUENCE and depending on the ORDER they are defined will apply the 1st then take the result and apply the 2nd on this result etc ....

Example : You can start by using a SUBSTRING to extract a portion of the source column
Then use a LEFT or RIGHT to again use only a portion of the portion extracted earlier
Then use a DEFORMAT to convert from a STRING to a LONG a DATE stored in a String source
And finally apply a FORMAT to define the extracted LONG (date) into a display readable format

EXTERNAL TABLE LOOKUP (during a Data Transfer to an SQL table ONLY)

You can perform (in each or any columns of the destination Table) ONE external Table lookup to retrieve a value contained in ANOTHER SQL table from the SAME DB

1. Select External Lookup

2. From the tables drop down select the Table to query

3. From the Columns drop down select the one to retrieve the value to use in the current destination Column

4. You now have to "link" TWO columns from the current table and the External Table to act as IDENTIFIERS (to build a proper WHERE clause in sql language - DMC will do it for you)

(ie Destination table contains a Columns called ClientName and the External Table has the SAME Column (or a different Name) but ALSO containing the SAME VALUE so you need to "link" them together

5. Your query can be built like this on up to 4 different columns to create uniqueness.

select SearchColumn from ExternalTable where LookupColumn = 'LookupValue' [ and where LookupColumn2 = 'LookupValue2' ] etc ...

dmc_external_lookup

If your lookup can return with an empty value of the selected WHERE clause, you can add a second lookup using a different WHERE clause

(ie : you are not sure all records contains a ZIPCODE value (as in this example) but then some others can contain a STATECODE one)

The you define several External Lookups on the same column (lookup columnA where ZIPCODE = ZIPCODE and a second one where STATECODE = STATECODE) etc :

You can use the Stop processing at First return option so as to avoid processing the other lookups

Filter on Error : Define this function to act as a FILTER (no return from lookup) and NOT transfer the RECORD