Don't get lost: Training » Sage 200 Training Manual » Step 4 – Map Transform

4 – Map Transform

The Map transform can:

  • Apply expressions or formulas to fields, which may be static values or formulas to each of the fields.
    • IMan uses a derivation of the VBScript/VBA language for formulas. Please see VBScript Function in the IMan User Guide for a more in-depth discussion.
  • Add fields to each of the transaction types.

The Map transform cannot:

  • Delete fields

In this step we will use the map transform to:

  1. Create functions to derive the customer name and customer contact fields.
  2. Lookup the customer number for existing records or generate a one for new records.

Add a Map Transform

  1. Drag a Map transform onto the palette.
  2. Drag a connector onto the palette and connect it to the previous hierarchy transform.
  3. Double click the Map transform to open its setup.
  4. Enter a friendly name (e.g. Map) into the Transform Id and press the Apply button to save.

Transform > Field Mapping

In this step, we will create fields to capture some customer details. We will do this using some simple formulas and some more complex, to demonstrate what the Map transform is capable of.

  1. Click on the Field Mapping tab.
  2. Ensure ‘Orders’ is selected from the ‘Current Transaction’ drop down.
  3. Double click the top row of the table to create a new field.

  1. Enter the following:
  1. Field Name
    • CustomerName
  1. Enable Script Evaluation
    • Checked
  2. Formula
    • %CustomerTitle & " " & %CustomerFirstName & " " & %CustomerLastName
  1. Press green tick below the form to save the field.
  2. Repeat steps 7 and 8 to create the following fields:
  1. Field Name
    • CustomerActualName
  2. Enable Script Evaluation
    • Checked
  3. Formula
    • IIf(%CompanyName <> "", %CompanyName, %CustomerName)
  1. Field Name
    • CustomerContact
  2. Enable Script Evaluation
    • Checked
  3. Formula
    • IIf(%CompanyName <> "", %CustomerName, "")
  1. Field Name
    • Sage200OrderNo
  2. Enable Script Evaluation
    • Unchecked
  3. Formula
    • <Leave Empty>
  1. Field Name
    • Ph1TelNo
  2. Enable Script Evaluation
    • Checked
  3. Formula
    • Right(Replace(Replace(%Phone, " ", ""), "+", ""),7)
  1. The following formulas parse the telephone fields into their constituent Subscriber, Area and Country parts.
    1. Simply copy and paste the formulas, if you’re struggling to understand, and come back at a later point to comprehend more fully.
  1. Field Name
    • Ph1Area
  2. Enable Script Evaluation
    • Checked
  3. Formula:

    • Dim Result

      Dim LenPh

      Dim Phone

      Dim CntryPfxLen

       

      If Left(%Phone,1) = "+" Then

      CntryPfxLen = 2

      End If

       

      Phone = Replace(Replace(%Phone, " ", ""), "+", "")

       

      LenPh = Len(Phone)

      'If the length of the phone is > 7 chars, try to parse the area code.

      If LenPh > 7 Then

      'If the length is less than 11 just take the X number of digits

      'preceding the last 11, otherwise the the middle four characters.

      If LenPh <= 11 Then

      Result = Mid(Phone, 1 + CntryPfxLen, LenPh - 7 - CntryPfxLen)

      Else

      Result = Mid(Phone, LenPh - 11 + 1 + CntryPfxLen, 4 - CntryPfxLen)

      End If

      End If

       

      Result

  1. Field Name
    • Ph1Country
  2. Enable Script Evaluation
    • Checked
  3. Formula

    • Dim Phone

      Dim Result

      Dim HasCountryPfx

       

      'If the length of the phone is > 11, parse the country code.

       

      HasCountryPfx = Left(%Phone,1) = "+"

       

      Phone = Replace(Replace(%Phone, " ", ""), "+", "")

       

      If HasCountryPfx Then

       

      Result = Left(Phone, 2)

      ElseIf Len(Phone) > 11 Then

      Result = Mid(Phone, 1, Len(Phone) - 11)

      Else

      Result = ""

      End If

       

      Result

  1. Press Preview to view the results.

  1. Press the Apply button and close the Map transform setup window.
  2. Save the integration by pressing the Save button at the bottom right of the Design Palette on the Transform Setup tab or the bottom right on the Options tab.

Lookup Setup

Lookups are a key feature of IMan and allow you to check for the existence of a record or translate values by querying any database.

Example

Problems:

  1. The input data file does not have the customer Id, so we would like to check the customer table using the email address supplied to see if the customer record exists.
  2. Sage200 does not have any facility to auto generate new customer Ids.

Solutions:

  1. Use the IMan lookup function for checking.
  2. Use the IMan counter function to assist in generating new sequential Ids.

Setup > Lookups

  1. Save the integration and click on the Setup tab at the top of the main screen.

  1. Click on Lookups item in the left hand menu and double click the top row to create a new record.

  1. Lookup Id
    • Used to identify the lookup.
    • For training, enter: EMLCUST
  2. Description
    • A recognisable name.
  3. Lookup From IntMan DB
    • IMan provides an internal lookup table facility which can store 20 fields per lookup.
    • For training: Deselect. We are querying the Sage200 database.
  4. Select Clause
    • Enter one or more fields to return from the query.
    • For training, enter: CustomerAccountNumber
  5. From Clause
    • The ‘From’ clause of the SQL query. This may be a single table or a complex join involving many tables.
    • For training, enter:
      SLCustomerAccount A inner join SLCustomerContact C on A.SLCustomerAccountID = C.SLCustomerAccountID inner join SLCustomerContactValue V on C.SLCustomerContactID = V.SLCustomerContactID
  6. Where Clause
    • The ‘Where’ clause of the SQL query.
    • To parameterise the query enter %1, %2, %3, for each of the parameters. The parameter values will then be passed in when we call the lookup query.
    • For training, enter:
      ContactValue = '%1' and SYSContactTypeID = 2
  7. Connection String
    • Enter the database connection string. The connection string must be either OleDb or ODBC; DotNet Native connection strings are not supported.
    • Use www.connectionstrings.com to help create all connection strings.
    • For training, enter:
      Provider=SQLNCLI10; Server=<computer>; Database=<DBID>; Uid=<user>; Pwd=<password>      ;
Where:
<computer> Replace with your SQLServer\Instance; this assumes Sage200 is using a SQLServer database.
<DBID> Replace with the ID of the Sage200 database.
<user> Replace with the SQL User with sufficient rights to query the database.
<password> The password for the SQL User.
  1. Save the lookup by pressing the Green tick.

Counter Setup

Counters allow you to generate a sequential number, or sequence, which is persisted by calls.

Counter Setup allows you to format the generated sequence by length, prefix, suffix and length.

Setup > Counters

  1. Click on the Counters item on the left hand menu and double click the top row to create a new Counter:

  1. Counter Id
    • Used to identify the Counter.
    • For training, enter: CUSTNO
  2. Description
    • A friendly name.
  3. Counter Prefix
    • A static prefix incorporated into the returned value.
    • For training, enter: WEB
  4. Counter Suffix
    • A static suffix incorporated into the returned value.
    • For training: Leave blank
  5. Evaluate Key
    • A single counter has facility to maintain one or multiple sequences.
    • When the evaluated key is selected, the formula field is evaluated to obtain reference to an already existent counter detail record. The counter detail sequence is identified using the result of the formula field.
    • If a record is found, the counter formula obtains the next number, otherwise a new detail record is created and the return value is set to the counter’s starting value.
    • For training: Unchecked
  6. Formula
    • For training: Leave blank
  7. Step
    • How much to increment for each call to the counter.
    • For training, enter: 1
  8. Starting Number
    • The starting number of the counter (or internal sequence).
    • For training, enter: 9000
  9. Total Length
    • The total length of the counter.
    • For training, enter: 8
  1. Save the counter by pressing the Green tick.
  1. Return to the design tab.

Lookup Function

The lookup function takes four parameters:

  1. Lookup Id ("CUSTEML")
    • This is the ID of the Lookup defined in the Setup.
  2. Return Field ("IDCUST")
    • This is the field you wish to return from the Lookup.
  3. Parameters (%Email)
    • These are the values you wish to parameterise the Lookup’s where clause.

Example

'%Email' corresponds to '%1'.

  1. Must Return (False)
    • A true/false value indicating whether a matching result must be returned.
Consult Appendix A of the user guide for a full discussion on each of the parameters.

Map > Field Mapping

In this step we will apply formulas / functions to the dataset.

  1. Reload the integration by selecting it from the Job Id drop down and pressing the Edit button.

  1. Re-open the Map transform by clicking the Transform Setup tab & then double clicking the Map transform.
  2. Click the Field Mapping tab and double click the top row to create a new field with the following details:
  1. Field Name
    • CustomerNo
  2. Enable Script Evaluation
    • Checked
  3. Formula
    • Lookup("EMLCUST", "CustomerAccountNumber", %Email, False)

  1. Save the field and press refresh. If correct the first customer will have a reference.
  2. If there’s an error with the lookup (e.g. an invalid field, SQL statement or database connection string) an error box will appear:

  1. We will now edit the existing formula by generating a new sequence from the Counter when there are no matching records returned from the lookup.
  2. To generate a new sequence call GetCounterSequence and pass the user defined Id of the counter to the function:
  1. Re-open the CustomerNo field
    • Edit the formula to:
      Dim Result

      Result = Lookup("EMLCUST", "CustomerAccountNumber", %Email, False)

      If Result = "" Then
      Result = GetCounterSequence("CUSTNO")
      End If

      Result
  1. Press refresh and the customer numbers should look like the following.

Step 5: Sage 200 Customer Import >