Skip to content

Scribe Integration Deep Dive: Address to ERP

Plain Language Summary

  • Change Detection: The MSCRM Publisher polls CRM every second and detects when someone (except the INTEGRATION user) creates or updates a customer address. It packages the address data into a message for this integration.

  • Composite Key Linking: Addresses use a special composite key in the linking table: "Customer Number|Address Code" (e.g., "CUST001|MAIN"). This tracks the relationship between CRM addresses and GP addresses since both pieces are needed to identify a unique address in GP.

  • Parent Dependency: Like Address to CRM, this integration checks if the parent customer exists in GP before creating a new address. If the customer hasn't been synced yet, it skips the address and retries on the next polling cycle.

  • Update vs Insert: If the composite key is found in the linking table, it updates the existing GP address. If not found, it verifies the parent customer exists, then creates a new GP address with an auto-generated address code.

Integration Overview

  • Name: Address to ERP (AddressToERP.dts)
  • Purpose: Sends new/updated address data from Microsoft CRM 2011 to Dynamics GP, synchronizing customer address records in the reverse direction
  • Trigger: MSCRM Publisher raises the FromCRM_Address message when CRM's real-time adapter detects customeraddress changes made by non-INTEGRATION users.
  • Source System: Microsoft CRM 2011 (customeraddress entity payload delivered through the publisher queue)
  • Target System: Microsoft Dynamics GP (Customer Address table)
  • Direction: CRM → GP (reverse sync)

Trigger and Message Source

The MSCRM Publisher polls Dynamics CRM every second using the Active Directory connection stored in publishers-bridges-connections.spkg. When a customeraddress record is created or updated by someone other than the integration user, the publisher packages the entity (including parent account context) into a FromCRM_Address queue message. AddressToERP.dts subscribes to that queue, processes each message, and the publisher moves the item to the processed or rejected queue depending on the DTS outcome.

graph TD
    Start[CRM Address Change Detected] --> BuildKey[Build lookup key:<br/>Customer# + Address Code]

    BuildKey --> CheckXRef{Has this address<br/>been synced before?}

    CheckXRef -->|Yes - Found in<br/>linking table| Step1[Step 1: Update<br/>GP Address]
    CheckXRef -->|No - Not found<br/>in linking table| Step2[Step 2: Check if parent<br/>customer exists in GP]

    Step1 --> UpdateResult{Update<br/>successful?}
    UpdateResult -->|Yes| Success[Sync Complete]
    UpdateResult -->|Address deleted| ErrorNotFound[Fail: Address no<br/>longer exists in GP]

    Step2 --> CustomerResult{Customer<br/>exists in GP?}
    CustomerResult -->|No| SkipAddress[Skip: Customer not<br/>synced yet - retry later]
    CustomerResult -->|Yes - 1 match| Step3[Step 3: Create<br/>GP Address]
    CustomerResult -->|Multiple matches| ErrorMultiple[Fail: Duplicate customer<br/>IDs in GP - manual review]

    Step3 --> Success
    ErrorNotFound --> End[Manual Review Required]
    ErrorMultiple --> End
    SkipAddress --> End

Key Processing Steps

This integration uses a 3-step workflow with cross-reference and customer validation logic:

  1. Step 1 - Customer Address (Update): Pre-operation formula checks if address is already shared via cross-reference (LookupAddressKey). If cross-reference exists, update the address in GP. If not found during update, log error.
  2. Pre-Op Formula: IF(ISERROR(LookupAddressKey), GOTOSTEP(2), TRUE) - if no cross-reference, jump to Step 2
  3. Success (1 match): Address updated successfully

  4. Success (0 matches): Address not found error (should never happen if cross-reference exists)

  5. Step 2 - Customer (Seek): If address is not shared yet, seek GP customer by parsed customer ID to verify customer exists before inserting address.

  6. Success (0 matches): Customer not found → skip address (customer not shared between systems)
  7. Success (1 match): Customer found → proceed to Step 3 (insert address)

  8. Success (2+ matches): Multiple customers found → error and rollback

  9. Step 3 - Customer Address (Insert): Insert new address record for the validated customer and establish cross-reference link.

User Variables

Variable Name Formula Purpose
LookupAddressKey DBLOOKUP(S40, "Adapter for Dynamics CRM 2011", "account", "accountid", "accountnumber") & "\|" & UPPER(LEFT(S33, 15)) Builds composite cross-reference key in format "GPCU STOMERID|ADDRESSID".
Looks up GP Customer ID from CRM account GUID, concatenates with Address ID.
Used to determine if address is already shared and to target update/insert operations.
AreaCode "000" Default area code string used when converting phone numbers from CRM format to GP format.
Passed as parameter to PHONEINTOGP() function.
ParseCustomerID PARSE(LookupAddressKey, 1, "\|") Extracts Customer ID portion from composite key (before pipe delimiter).
Used in Steps 2 & 3 to identify which GP customer the address belongs to.
ParseAddressID PARSE(LookupAddressKey, 2, "\|") Extracts Address ID portion from composite key (after pipe delimiter).
Used in Step 3 to set the GP Address ID for newly inserted addresses.

Field Mappings and Transformations

Step 1: Update Existing Customer Address

Target Field Source Field(s) Transformation Formula Explanation
Customer ID Variable ParseCustomerID GP Customer ID extracted from composite cross-reference key
Address ID Variable ParseAddressID GP Address ID extracted from composite cross-reference key
Contact Person S32 (name) LEFT(S32, 61) Contact name truncated to GP 61-character limit
Address 1 S11 (line1) LEFT(S11, 61) Primary address line truncated to 61 characters
Address 2 S12 (line2) LEFT(S12, 61) Secondary address line truncated to 61 characters
Address 3 S13 (line3) LEFT(S13, 61) Tertiary address line truncated to 61 characters
City S9 (city) LEFT(S9, 35) City name truncated to 35 characters
State S31 (stateorprovince) LEFT(S31, 29) State/province truncated to 29 characters
ZIP Code S24 (postalcode) LEFT(S24, 11) Postal code truncated to 11 characters
Country S10 (country) LEFT(S10, 61) Country name truncated to 61 characters
Phone 1 S34 (telephone1) PHONEINTOGP(S34, AreaCode) Primary phone formatted to GP numeric format
Phone 2 S35 (telephone2) PHONEINTOGP(S35, AreaCode) Secondary phone formatted to GP numeric format
Phone 3 S36 (telephone3) PHONEINTOGP(S36, AreaCode) Tertiary phone formatted to GP numeric format
Fax S7 (fax) PHONEINTOGP(S7, AreaCode) Fax number formatted to GP numeric format
UPS Zone S37 (upszone) S37 UPS shipping zone code passed through
Shipping Method S29 (shippingmethodcode) FILELOOKUP(S29, "XRef.ini", "Shipping_Method_ToGP") Translates CRM shipping method option value to GP shipping method code

Step 2: Seek Customer by ID

Target Field Source Field(s) Transformation Formula Explanation
Customer ID Variable ParseCustomerID GP Customer ID parsed from cross-reference key, used to verify customer exists

Step 3: Insert New Customer Address

Target Field Source Field(s) Transformation Formula Explanation
Customer ID Variable ParseCustomerID GP Customer ID parsed from cross-reference key
Address ID Variable ParseAddressID GP Address ID parsed from cross-reference key (CRM address code uppercased)
Contact Person S32 (name) LEFT(S32, 61) Contact name truncated to 61 characters
Address 1 S11 (line1) LEFT(S11, 61) Primary address line
Address 2 S12 (line2) LEFT(S12, 61) Secondary address line
Address 3 S13 (line3) LEFT(S13, 61) Tertiary address line
City S9 (city) LEFT(S9, 35) City name
State S31 (stateorprovince) LEFT(S31, 29) State/province
ZIP Code S24 (postalcode) LEFT(S24, 11) Postal code
Country S10 (country) LEFT(S10, 61) Country name
Phone 1 S34 (telephone1) PHONEINTOGP(S34, AreaCode) Primary phone formatted
Phone 2 S35 (telephone2) PHONEINTOGP(S35, AreaCode) Secondary phone formatted
Phone 3 S36 (telephone3) PHONEINTOGP(S36, AreaCode) Tertiary phone formatted
Fax S7 (fax) PHONEINTOGP(S7, AreaCode) Fax number formatted
UPS Zone S37 (upszone) S37 UPS zone code
Shipping Method S29 (shippingmethodcode) FILELOOKUP(S29, "XRef.ini", "Shipping_Method_ToGP") Shipping method translation

Formula Reference

DBLOOKUP

Description: Performs a single-key database lookup and returns a specified field value.

Parameters:

  • Param 1: Key value to search for (e.g., CRM accountid GUID)
  • Param 2: Connection name (e.g., "Adapter for Dynamics CRM 2011")
  • Param 3: Entity/table name (e.g., "account")
  • Param 4: Key field name (e.g., "accountid")
  • Param 5: Return field name (e.g., "accountnumber")

Returns: Value of return field if exactly one match found, otherwise #NOTFOUND!.

Usage: DBLOOKUP(S40, "Adapter for Dynamics CRM 2011", "account", "accountid", "accountnumber") retrieves GP Customer ID from CRM account

PARSE

Description: Splits a delimited string and returns the Nth segment.

Parameters:

  • Param 1: String to parse (e.g., "CUST001|PRIMARY")
  • Param 2: Segment number to return (1-based index, e.g., 1 for first segment)
  • Param 3: Delimiter character (e.g., "|")

Returns: The specified segment of the string.

Usage: PARSE("CUST001|PRIMARY", 1, "|") returns "CUST001"

LEFT

Description: Returns the leftmost N characters from a string.

Parameters:

  • Param 1: Input string
  • Param 2: Number of characters to return

Returns: Substring from start of string.

Usage: LEFT("Hello World", 5) returns "Hello"

UPPER

Description: Converts all characters in a string to uppercase.

Parameters:

  • Param 1: Input string

Returns: Uppercase string.

Usage: UPPER("primary") returns "PRIMARY"

PHONEINTOGP

Description: Converts CRM phone number format to GP format by removing formatting characters and handling area codes.

Parameters:

  • Param 1: CRM phone number (e.g., "(555) 123-4567")
  • Param 2: Default area code to use if phone is only 7 digits (e.g., "000")

Returns: GP-formatted phone string (digits only with area code).

Usage: PHONEINTOGP(S34, AreaCode) converts formatted phone to numeric string

FILELOOKUP

Description: Looks up a value in an INI file section and returns the mapped value.

Parameters:

  • Param 1: Input value to translate (e.g., CRM option value)
  • Param 2: INI file name (e.g., "XRef.ini")
  • Param 3: Section name (e.g., "Shipping_Method_ToGP")

Returns: Mapped value from INI file if found, otherwise returns original input unchanged.

Usage: FILELOOKUP(S29, "XRef.ini", "Shipping_Method_ToGP") translates CRM shipping method code

ISERROR

Description: Tests if an expression evaluates to an error state.

Parameters:

  • Param 1: Expression to evaluate

Returns: TRUE if expression is error state (#NOTFOUND!, #ERROR!), otherwise FALSE.

Usage: ISERROR(LookupAddressKey) returns TRUE if cross-reference lookup failed

GOTOSTEP

Description: Flow control function that causes immediate jump to a specified step.

Parameters:

  • Param 1: Step number to jump to (e.g., 2)

Returns: Special control value #GOTOSTEP2!.

Usage: IF(ISERROR(LookupAddressKey), GOTOSTEP(2), TRUE) skips update if no cross-reference

IF

Description: Conditional evaluation - returns one of two values based on a boolean condition.

Parameters:

  • Param 1: Boolean condition
  • Param 2: Value to return if TRUE
  • Param 3: Value to return if FALSE

Returns: Value from Param 2 or Param 3 based on condition.

Usage: IF(ISERROR(LookupAddressKey), GOTOSTEP(2), TRUE) controls step routing

Source and Target Connection Details

Source Connection

  • Type: Microsoft CRM 2011 Adapter (via Scribe Publisher)
  • Entity: customeraddress
  • Key Fields: ScribeObjectKey (customeraddress GUID)
  • Name Fields: addressnumber, name
  • Query: Standard customeraddress entity query

Target Connection

  • Microsoft GP Adapter:
  • Customer entity (Step 2 - seek only)
  • Customer Address entity (Steps 1, 3 - update/insert)

Cross-Reference Configuration

  • Source Key: ScribeObjectKey (customeraddress GUID)
  • Target Key: Customer ID|Address ID (composite key with pipe delimiter)
  • Lookup Variable: LookupAddressKey
  • Source Name: addressnumber, name
  • Target Name: Address 1
  • Purpose: Tracks which CRM customeraddress records are synchronized to GP

Business Rules and Data Quality Considerations

Composite Key Structure

The integration uses a composite cross-reference key combining Customer ID and Address ID:

  • Format: "CUSTID|ADDRESSID" (e.g., "AARONFIT0001|PRIMARY")
  • Components:
  • Customer ID: Looked up from CRM account → GP customer cross-reference
  • Address ID: Derived from CRM address code, uppercased, first 15 characters
  • Purpose: GP requires both Customer ID and Address ID to uniquely identify an address

Customer Dependency

Addresses require parent customer to exist in GP:

  • Step 2 Validation: Seeks GP customer before allowing address insert
  • Not Found Handling: If customer doesn't exist, address is skipped (not error)
  • Sequencing: Addresses should always sync after parent customers
  • Retry Logic: Addresses are not automatically retried if customer is missing

Field Truncation

GP has strict field length limits. All text fields are truncated using LEFT():

  • Name/Contact Person: 61 characters
  • Address lines: 61 characters
  • City: 35 characters
  • State: 29 characters
  • ZIP: 11 characters
  • Country: 61 characters

Phone Number Formatting

PHONEINTOGP() function normalizes phone formats:

  • Input: "(555) 123-4567 x890"
  • Process: Strip non-numeric characters
  • Output: "5551234567890" (GP stores unformatted)

Address ID Generation

GP Address ID is derived from CRM:

  • Source: CRM customeraddress name field (typically "PRIMARY", "SHIPPING", etc.)
  • Processing: UPPER(LEFT(S33, 15)) - uppercase, max 15 characters
  • Common Values: "PRIMARY", "BILLING", "SHIPPING", "WAREHOUSE"

SmartConnect Re-implementation Notes

Multi-Step Workflow Implementation

SmartConnect can replicate this 3-step process using:

Option 1: Single Map with Conditional Logic 

// Pre-map script to determine routing
string customerID = GetGPCustomerIDFromAccount(accountId);
string addressID = customerAddressName.ToUpper().Substring(0, Math.Min(15, customerAddressName.Length));
string compositeKey = customerID + "|" + addressID;

bool addressExists = CheckGPAddressExists(customerID, addressID);

if (addressExists)
{
    // Route to update
    SetGlobalValue("OperationMode", "UPDATE");
}
else
{
    // Verify customer exists before insert
    if (!CheckGPCustomerExists(customerID))
    {
        // Skip address - customer not shared
        throw new SkipRecordException("Customer not found in GP - address skipped");
    }

    // Route to insert
    SetGlobalValue("OperationMode", "INSERT");
}

SetGlobalValue("GPCustomerID", customerID);
SetGlobalValue("GPAddressID", addressID);

Cross-Reference Key Building

SQL Lookup for Customer ID: 

-- Get GP Customer ID from CRM Account ID
SELECT GPCustomerID
FROM ScribeCrossReference
WHERE
    CRMAccountID = @AccountID
    AND EntityType = 'account'

C# Composite Key Builder: 

public string BuildAddressKey(Guid accountId, string addressName)
{
    // Lookup GP Customer ID
    string customerID = LookupGPCustomerID(accountId);

    if (string.IsNullOrEmpty(customerID))
        return "#NOTFOUND!";  // Customer not shared

    // Generate Address ID from CRM address name
    string addressID = addressName.ToUpper().Substring(0, Math.Min(15, addressName.Length));

    // Return composite key
    return customerID + "|" + addressID;
}

Customer Existence Validation

T-SQL Validation Query: 

-- Check if GP customer exists before inserting address
SELECT COUNT(*) as CustomerCount
FROM RM00101  -- GP Customer Master
WHERE CUSTNMBR = @CustomerID

-- Return:
-- 0 = customer not found, skip address
-- 1 = customer exists, proceed with insert
-- 2+ = error, multiple customers (should never happen)

Phone Number Formatting

C# Implementation: 

public string FormatPhoneForGP(string crmPhone, string defaultAreaCode)
{
    if (string.IsNullOrEmpty(crmPhone))
        return string.Empty;

    // Strip all non-numeric characters
    string digitsOnly = Regex.Replace(crmPhone, @"[^\d]", "");

    // Handle 7-digit numbers
    if (digitsOnly.Length == 7)
        digitsOnly = defaultAreaCode + digitsOnly;

    return digitsOnly;
}

Error Handling

SmartConnect Error Scenarios: 

// Scenario 1: Address update fails (cross-reference exists but address not in GP)
catch (RecordNotFoundException ex)
{
    LogError("Address not found in GP during update", customerAddressId, ex);
    SetRecordStatus("FAILED");
}

// Scenario 2: Customer not found (skip address without error)
if (!CustomerExists(customerID))
{
    LogInfo("Customer not found - skipping address", customerID);
    SetRecordStatus("SKIPPED");
    return;  // Don't throw error, just skip
}

// Scenario 3: Multiple customers found
if (customerCount > 1)
{
    LogError("Multiple customers found for ID", customerID);
    SetRecordStatus("FAILED");
    throw new Exception("Data integrity error - duplicate customer IDs");
}

Testing Strategy

Test Scenario 1: Cross-Reference Update Path

  1. Create CRM customeraddress with existing cross-reference (address already in GP)
  2. Modify address fields in CRM
  3. Run integration
  4. Verify: Step 1 pre-op processes update directly, GP address updated

Test Scenario 2: New Address Insert Path (Customer Exists)

  1. Ensure GP customer exists (e.g., "CUST001")
  2. Create new CRM customeraddress for that account (no cross-reference)
  3. Run integration
  4. Verify: Step 1 pre-op jumps to Step 2, Step 2 finds customer, Step 3 inserts address, cross-reference created

Test Scenario 3: Skip Path (Customer Not Found)

  1. Create CRM customeraddress for account with no GP customer
  2. Run integration
  3. Verify: Step 2 seek returns 0 matches, address skipped (no error logged)

Test Scenario 4: Composite Key Validation

  1. Create address with long name: "PRIMARY SHIPPING WAREHOUSE"
  2. Verify GP Address ID becomes "PRIMARY SHIPPIN" (15 char limit, uppercased)
  3. Verify cross-reference key format: "CUST001|PRIMARY SHIPPIN"

Performance Considerations

  • Customer Lookup Caching: Cache GP Customer ID lookups by CRM account GUID to avoid repeated queries
  • Address Existence Check: Index GP Customer Address table on composite key (CUSTNMBR + ADRSCODE)
  • Batch Processing: Process addresses in batches of 100-500
  • Sequencing: Ensure CustomerToERP integration runs before AddressToERP

Required XRef.ini Sections

[Shipping_Method_ToGP]
1=UPS GROUND
2=UPS 2ND DAY AIR
3=UPS NEXT DAY AIR
4=FEDEX GROUND
5=WILL CALL

Integration Dependencies

Upstream Dependencies

  • CustomerToERP.dts: Must run first to create GP customers before addresses can be added
  • CustomerToCRM.dts: Initial customer sync from GP to CRM (Step 5 triggers address cascade)

Downstream Dependencies

  • None (terminal integration in address sync workflow)

CRM Customizations

  • account.accountnumber: Must contain GP Customer ID for cross-reference lookup
  • customeraddress.name: Used to derive GP Address ID (typically "PRIMARY", "SHIPPING", etc.)

GP Customizations

  • None required (uses standard Customer Address table)

Notes on Bidirectional Address Synchronization

This integration works in conjunction with CustomerToCRM integration:

  1. GP → CRM Direction (CustomerToCRM.dts):
  2. Step 5 uses Non-INTEGRATION connection to update customeraddress

  3. This triggers CRM plugin to create customeraddress records

  4. CRM → GP Direction (AddressToERP.dts - this integration):

  5. Detects newly created customeraddress records in CRM
  6. Syncs them back to GP Customer Address table
  7. Establishes cross-reference for future bidirectional updates

This creates a complete bidirectional address synchronization loop between the systems.