Skip to content

Step 5 – Sage 300 Customer Import

In this step you import the customers into Sage 300. There are two parts:

  1. A System Connector, which holds the credentials and company IMan uses to talk to a Sage 300 installation.
  2. A Sage 300 connector on the design surface, which takes the transformed orders and writes a customer for each one.

The connector is the first transform in this training that changes data outside IMan. Everything before it has been a preview.

Setup > System Connector

You define a System Connector once and then choose it by name in any Sage 300 connector. The database connection in step 4 works the same way.

  1. Save the integration, then press Setup at the top of the main screen.

  2. Press System Connector in the left-hand menu, then press Add.

    The Add New Record dialog for a system connector, with the Sage 300 details filled in and an empty System Connector Results panel beside it

    1. ID
      • Must be populated.
      • For training, enter: SAMS300LTD
    2. Description
      • Always enter a recognisable description. The connector transform lists System Connectors by description, not by id. A vague description is hard to find later.
    3. System Type
      • Must be selected.
      • For training, choose: Sage 300
    4. Version
      • Used only by connector types that have more than one release. Leave it empty for Sage 300.
    5. Connection String
      • What goes here depends on the connector. See System Connectors.
      • For Sage 300, leave blank.
    6. Username
      • The Sage 300 user IMan signs in as.
    7. Password
      • The user's password. The field hides it as you type.
    8. Company ID
      • The Sage 300 company id as it appears in the Sage 300 database setup utility. This is not the database id.
      • For training, enter: ASML69

  3. Press TEST.

    • A tick on the button and a second one in the System Connector Results panel confirm that IMan signed in to Sage 300 with those details. If they do not appear, fix the details before you go further.

    The same dialog after pressing TEST, with a green tick on the TEST button and in the System Connector Results panel

  4. Press the green tick at the bottom right to save the record.

Design > Transform Setup

  1. Return to Design, load the integration and press the Transform Setup tab.
  2. Open the Connectors group in the palette and drag a Sage 300 connector onto the design surface.

  3. Drag a Connector from the Transforms group and join the Map transform to it.

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

Connector > Setup

  1. Double-click the Sage 300 connector to open it.

    The connector's Setup tab, showing Transform Id, Sage 300 Connector, Sage 300 Import Type and Update Operation

    1. Transform Id
      • Identifies the transform in the audit report. A new connector is already named Connector. For training, leave it.
    2. Sage 300 Connector
      • The System Connector defined above, listed by its description.
      • For training, choose: Sage 300 SAMLTD training company
      • The refresh button beside the list reloads it. Use it if you have just added a System Connector in another tab.
    3. Sage 300 Import Type
      • What you are importing. Sage 300 has 92 import types. The list includes types for modules that are not activated on this company.
      • For training, choose: A/R Customer
    4. Update Operation
      • Whether to Insert new records, Update existing ones, or Insert/Update either as required.
      • For training, leave as: Insert

Connector > Field Mapping

  1. Press the Field Mapping tab.
  2. Check that Orders is selected in Current Transaction Id.

  3. Choose Customers (AR0024) from Sage 300 Transaction Type.

    • Transaction Level Mapping then shows which incoming transaction feeds which Sage 300 view. Orders maps to AR0024. OrderDetails stays unmapped, because this step imports customers, not orders.

    The Field Mapping tab with Orders selected, the transaction type set to Customers (AR0024), and the Transaction Level Mapping panel showing Orders mapped to AR0024

  4. Press Edit above the grid, then set the Sage 300 Field on each of these rows:

    Incoming Field Sage 300 Field
    AddressLine1 Address Line 1 (TEXTSTRE1)
    AddressLine2 Address Line 2 (TEXTSTRE2)
    City City (NAMECITY)
    County State/Prov. (CODESTTE)
    Country Country (CODECTRY)
    Postcode Zip/Postal Code (CODEPSTL)
    Phone Phone Number (TEXTPHON1)
    Fax Fax Number (TEXTPHON2)
    Email E-mail (EMAIL2)
    ContactEmail Contact's E-mail (EMAIL1)
    CustomerActualName Customer Name (NAMECUST)
    CustomerContact Contact Name (NAMECTAC)
    CustomerGrp Group Code (IDGRP)
    CustomerNo Customer Number (IDCUST)
    • Each entry shows the Sage 300 view field it writes to in brackets. The CUSTEML lookup in step 4 queries EMAIL2, so Email maps to EMAIL2 and not to Contact's E-mail.
    • You do not need to tick Import. Choosing a Sage 300 field ticks the row for you.
    • The grid is one scrolling list. Every incoming field is in it, including the four the Map transform created.

  5. Press Save above the grid.

    The field grid after saving, with Import ticked and a Sage 300 field set on the mapped rows and (not mapped) on the rest

The tax group error

  1. Press Refresh at the bottom of the pane.

  2. The import fails.

    An error dialog reading Accpac API Error 1140 - Tax group cannot be blank

    • This is a Sage 300 error, not an IMan one. Sage 300 will not accept a customer without a tax group, and nothing in the incoming file supplies one.
    • Behind the dialog the preview shows Progress - Error. The TRACE tab records the Sage 300 calls the run made, up to the one that failed. The message itself appears only in the dialog.

Fix it in the Map transform. You have to supply the tax group, so add it as a field like any other.

Map > Field Mapping — the tax group

  1. Press Close at the bottom of the connector, then open the Map transform and press its Field Mapping tab. Press Add:

    1. New Field Name
      • TaxGroup
    2. Type
      • Text
    3. Evaluate
      • Unticked. USDTAX is a literal, not a formula.
    4. Evaluate String
      • USDTAX

    The Field Mapping dialog for TaxGroup, with Evaluate unticked and USDTAX in the Evaluate String editor

  2. Press the tick to save the field, then press Refresh and scroll the preview to the right.

    The Map preview scrolled right, showing TaxGroup set to USDTAX on all three orders

Connector > Field Mapping — mapping the tax group

  1. Press Close on the Map transform, save the integration and re-open the Sage 300 connector.
  2. On Field Mapping, press Edit, set TaxGroup to Tax Group (CODETAXGRP), and press Save.
    • TaxGroup is already in the grid. The connector picks up a field added upstream by itself. Use Refresh MetaData on the Setup tab only when Sage 300 itself has changed.

  3. Press Refresh.

    • The run reaches Progress - Completed and the customers are in Sage 300. The preview now shows the values as Sage 300 received them, under the Sage 300 field names.

    The connector preview reading Progress - Completed, with the imported NAMECUST, IDGRP, IDCUST and CODETAXGRP values

Proving the customers were created

  1. Re-open the Map transform and press Refresh twice.

    • CustomerNo no longer changes between refreshes. Before the import, the lookup found nothing and the counter allocated a new number every time. Now the lookup finds the customer the import created and returns its id, so the formula does not call the counter.

  2. To check this directly, go to Setup > Lookups, select CUSTEML and press Edit. Enter one of the imported email addresses as the parameter and press TEST.

    • The lookup returns the customer number the import allocated. The record exists in Sage 300.

    The CUSTEML lookup editor with an imported email address as the parameter, and a Lookup Result panel returning IDCUST WEB015156

Your generated numbers will not match the screenshots

The generated numbers will not match the ones in these screenshots. The CUSTNO counter advances on every preview refresh, including runs that do not reach Sage 300. By the time you get here, the sequence has moved on.

Step 6: Create Shipment charges >