Scribe Integration Deep Dive: Address to ERP¶

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
Source System: Microsoft CRM 2011 (customeraddress entity)
Target System: Microsoft Dynamics GP (Customer Address table)
Direction: CRM → GP (reverse sync)

graph TD
    Start[CRM CustomerAddress Data] --> BuildKey[Build Composite Key<br/>Customer ID + Address ID]

    BuildKey --> CheckXRef{Cross-Reference<br/>Exists?}

    CheckXRef -->|Yes - Already Shared| Step1[Step 1: Update<br/>Customer Address]
    CheckXRef -->|No - Not Shared| Step2[Step 2: Find GP<br/>Customer by ID]

    Step1 --> UpdateResult{Update<br/>Result?}
    UpdateResult -->|Success| Success[Sync Complete]
    UpdateResult -->|Not Found| ErrorNotFound[Error: Address<br/>Not Found]

    Step2 --> CustomerResult{Customer<br/>Found?}
    CustomerResult -->|Not Found| SkipAddress[Skip Address<br/>Customer Not Shared]
    CustomerResult -->|One Match| Step3[Step 3: Insert<br/>New Address]
    CustomerResult -->|Multiple Matches| ErrorMultiple[Error: Multiple<br/>Customers Found]

    Step3 --> Success
    ErrorNotFound --> End[Job Failed]
    ErrorMultiple --> End
    SkipAddress --> End

Key Processing Steps¶

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

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.
Pre-Op Formula: IF(ISERROR(LookupAddressKey), GOTOSTEP(2), TRUE) - if no cross-reference, jump to Step 2
Success (1 match): Address updated successfully
Success (0 matches): Address not found error (should never happen if cross-reference exists)
Step 2 - Customer (Seek): If address is not shared yet, seek GP customer by parsed customer ID to verify customer exists before inserting address.
Success (0 matches): Customer not found → skip address (customer not shared between systems)
Success (1 match): Customer found → proceed to Step 3 (insert address)
Success (2+ matches): Multiple customers found → error and rollback
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:

GP → CRM Direction (CustomerToCRM.dts):
Step 5 uses Non-INTEGRATION connection to update customeraddress
This triggers CRM plugin to create customeraddress records
CRM → GP Direction (AddressToERP.dts - this integration):
Detects newly created customeraddress records in CRM
Syncs them back to GP Customer Address table
Establishes cross-reference for future bidirectional updates

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