Skip to content

Step 4 – Map Transform

The Map transform can:

  • Set each field to a static value or a formula.
    • IMan formulas use a variant of the VBScript/VBA language. See VBScript Function in the IMan User Guide for more detail.
  • Add fields to each of the transaction types.

The Map transform cannot delete fields

The Map transform cannot:

  • Delete fields

In this step you use the Map transform to:

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

Design > Transform Setup

  1. Press the Transform Setup tab.
  2. Open the Transforms group in the palette.
  3. Drag a Map transform onto the design surface.

  4. Drag a Connector from the same group and join the Hierarchise transform to it.

    The design surface with the Excel Reader, Hierarchise and Map transforms connected in a chain

Transform > Setup

  1. Double-click the Map transform to open it.

    • The Transform Id identifies the transform in the audit report. A new Map transform is already named Map. For training, leave it.

    The Map transform's Setup tab, showing the Transform Id

Transform > Field Mapping

Here you create fields that hold customer details, with a few simple formulas and some more complex ones.

  1. Press the Field Mapping tab.
  2. Check that Orders is selected in the transaction drop-down above the grid.

  3. Press Add to create a new field.

    The Field Mapping dialog: New Field Name, Type, Evaluate and the Evaluate String editor with a formula in it

    1. New Field Name
      • The name of the field being added.
    2. Type
      • The data type of the result. For training, leave as: Text
    3. Evaluate
      • Ticked, IMan runs the Evaluate String as a formula. Unticked, IMan uses it as a static value.
    4. Evaluate String
      • The formula, or the static value. Field names are prefixed with %.
      • The editor checks the formula as you type and reports No Errors Found beneath it.

  4. Press the tick to save the field, then repeat for each of the following:

    New Field Name Evaluate Evaluate String
    CustomerName ✔ %CustomerTitle & " " & %CustomerFirstName & " " & %CustomerLastName
    CustomerActualName ✔ IIf(%CompanyName <> "", %CompanyName, %CustomerName)
    CustomerContact ✔ IIf(%CompanyName <> "", %CustomerName, "")
    CustomerGrp ✘ WHL
    • CustomerGrp has Evaluate unticked, so every order gets the literal value WHL.

    The field grid showing the four new fields at the end of the list

  5. Press Refresh and scroll the preview to the right to see the new fields.

    • The first order has a company name, so CustomerActualName takes the company and CustomerContact takes the person. The other two have no company, so CustomerActualName takes the person's name and CustomerContact is empty.

    The preview scrolled right, showing CustomerName, CustomerActualName, CustomerContact and CustomerGrp

Lookups and counters

Example

Problems:

  1. The input file does not have the customer id. You need to search the customer table by email address to see whether the customer record exists.
  2. Sage 300 cannot generate new customer ids automatically.

Solutions:

  1. Use the IMan lookup function for checking.
  2. Use the IMan counter function to generate new sequential ids.

A lookup queries a database through a database connection. You define the connection once under Setup and then choose it by name in any lookup. Define the connection first.

Setup > Database Connections

  1. Press Setup, then Database Connections in the left-hand menu.

    The Database Connections list

  2. Press Add, or select the connection and press Edit to see an existing one.

    1. Id
      • Identifies the connection.
      • For training, enter: SAGE300
    2. Description
      • A recognisable name. You pick from these when you set up a lookup.
      • For training, enter: Sage300
    3. Connection String
      • An ODBC connection string for the Sage 300 database.
      • For training: Driver={ODBC Driver 17 for SQL Server};Server=<server>;Database=<DBID>;Trusted_Connection=yes;

    The connection editor showing the Id, Description and ODBC connection string

    <server> is your SQL Server and instance, and <DBID> is the id of the Sage 300 database. Trusted_Connection=yes connects as the IMan service account. To connect as a named SQL user, replace it with Uid=<user>;Pwd=<password>;.

Use ODBC Driver 17 or 18, not SQLNCLI

Use the ODBC Driver 17 for SQL Server or ODBC Driver 18 for SQL Server. Some sample connections still carry older Provider=SQLNCLI… strings. Do not use them for new work.

  1. Press TEST.

    • A green tick on the button confirms that IMan opened the connection. If it fails, a red cross appears on the button and the database driver's error appears to the right of it. Fix the string before you go any further.

    The connection editor after pressing TEST, showing a tick on the button

  2. Press SAVE.

Setup > Lookups

  1. Press Lookups in the left-hand menu, then Add. To change an existing lookup, select it and press Edit.

    1. Id
      • Identifies the lookup.
      • For training, enter: CUSTEML
    2. Description
      • A recognisable name.
    3. Use IMan Lookup Table
      • IMan has an internal lookup table that can store 20 fields per lookup.
      • For training, leave unticked. This lookup queries the Sage 300 database.
    4. Database Connection
      • The connection defined above.
      • For training, choose: Sage300
    5. Select Clause
      • One or more fields to return from the query.
      • For training, enter: top 1 IDCUST
      • top 1 keeps the lookup to a single answer if more than one customer shares an email address.
    6. From Clause
      • The From clause of the SQL query. This can be a single table or a join across many tables.
      • For training, enter: ARCUS
    7. Where Clause
      • The Where clause of the SQL query.
      • To add parameters, enter %1, %2, %3 and so on. The formula that calls the lookup passes in their values.
      • For training, enter: EMAIL2 = '%1'

    The lookup editor showing the database connection, select, from and where clauses

  2. Enter a value for the parameter and press TEST.

    • IMan shows the result of the query beneath. This is new in IMan v6. It is the quickest way to prove a lookup before you use it in a formula.

    The lookup editor after pressing TEST, showing the returned customer Id

  3. Press SAVE.

Setup > Counters

A counter generates a sequential number, or sequence, and IMan stores it between calls. The counter's settings format the number with a prefix, suffix and length.

  1. Press Counters in the left-hand menu, then Add. To change an existing counter, select it and press Edit.

    1. Counter Id
      • Identifies the counter.
      • For training, enter: CUSTNO
    2. Description
      • A friendly name.
    3. Counter Length
      • The total length of the generated value, including the prefix.
    4. Prefix
      • A fixed prefix added to the returned value.
      • For training, enter: WEB
    5. Suffix
      • A fixed suffix added to the returned value.
      • For training, leave blank.
    6. Evaluate Key
      • One counter can keep one or several sequences. When ticked, IMan evaluates the formula field, and the result names the counter detail record, that is, the sequence to use. If the record exists, IMan takes the next number from it. If not, IMan creates a new one that begins at the counter's starting value.
      • For training, leave unticked.
    7. Step
      • How much to increment on each call.
      • For training, enter: 1
    8. Starting Value
      • Where a sequence begins the first time it is used.

    The CUSTNO counter, showing the prefix, length, step and starting value

  2. Press SAVE.

Map > Field Mapping — the customer number

The lookup function takes four parameters:

  1. Lookup Id — "CUSTEML", the id of the lookup defined above.
  2. Return Field — "IDCUST", the field to return.
  3. Parameters — %Email, the values that fill the where clause. %Email fills %1.
  4. Must Return — False, whether a matching result is required.

Where the parameters are described in full

Lookup in the VBScript function reference describes each parameter in full.

  1. Re-open the Map transform, press the Field Mapping tab and press Add:

    1. New Field Name
      • CustomerNo
    2. Evaluate
      • Ticked
    3. Evaluate String
      • Lookup("CUSTEML", "IDCUST", %Email, False)

    The Field Mapping dialog for CustomerNo, containing the Lookup formula

  2. Save the field and press Refresh.

    • The first order matches an existing customer on its email address and returns their customer id. The other two do not match, so their CustomerNo is empty. These customers need a generated number.

    The preview showing CustomerNo populated for the first order only

  3. Re-open the CustomerNo field and extend the formula. When the lookup finds nothing, the formula takes the next number from the counter:

    Dim Result
Result = Lookup("CUSTEML", "IDCUST", %Email, False)
If Result = "" Then
    Result = GetCounterSequence("CUSTNO")
End If
Result

    The Field Mapping dialog for CustomerNo, containing the full formula with the counter fallback

  4. Save the field and press Refresh.

    The preview showing every order with a customer number, the first from the lookup and the rest generated

The first record matched, so it kept its Id

The first record still returns the existing customer id, because its email address matched. The others now carry a newly generated number.

  1. Press Close at the bottom of the transform, then Save the integration.

Step 5: Sage 300 Customer Import >