Scribe Integration Deep Dive: Customer to ERP¶

Plain Language Summary¶

Change Detection: CRM triggers this integration whenever someone (except the INTEGRATION user) changes an account that's marked as a Customer (type code = 3). The MSCRM Publisher polls CRM every second and creates a message for each changed account.
Linking Table: Just like Customer to CRM, this integration uses Scribe's KEYCROSSREFERENCETWOWAY linking table to track which CRM accounts map to which GP customers. The LookupCustomerID variable checks this table first.
Smart Routing: If the linking table shows this account has been synced before, we update the existing GP customer. If not found, we search GP by name and ZIP code to see if the customer already exists (maybe created manually). Only as a last resort do we create a brand-new GP customer.
Bidirectional Handshake: After syncing to GP, Steps 5 and 7 write the GP customer number back to CRM fields so both systems know about each other. This completes the two-way link and prevents future duplicate checks.

Integration Overview¶

Name: Customer to ERP (CustomerToERP.dts)
Purpose: Sends new/updated customer data from Microsoft CRM 2011 to Dynamics GP, synchronizing customer master records and addresses in the reverse direction
Trigger: MSCRM Publisher raises the FromCRM_Customer message when CRM accounts with customertypecode = 3 change outside of the integration user.
Source System: Microsoft CRM 2011 (account entity payload delivered through the publisher queue)
Target System: Microsoft Dynamics GP (Customer, Internet Information tables)
Direction: CRM → GP (reverse sync)

Trigger and Message Source¶

MSCRM Publisher polls Dynamics CRM each second using the connection stored in publishers-bridges-connections.spkg. It filters to account rows where customertypecode = 3 and the modifying user is not INTEGRATION. When a qualifying change occurs, the publisher emits a FromCRM_Customer message that contains the full account entity for enrichment. CustomerToERP.dts listens to that queue, executes the DTS workflow, and—on success—the publisher moves the message to the processed queue. Failures are diverted to the rejected queue for operator review.

graph TD
    Start[CRM Account Change Detected] --> CheckXRef{Has this account<br/>been synced before?}

    CheckXRef -->|Yes - Found in<br/>linking table| Step2[Step 2: Update<br/>GP Customer Record]
    CheckXRef -->|No - Not found<br/>in linking table| Step1[Step 1: Search GP by<br/>Name + ZIP]

    Step1 --> SeekResult{How many GP<br/>customers match?}

    SeekResult -->|0 matches| Step4[Step 4: Create New<br/>GP Customer]
    SeekResult -->|1 match| Step2
    SeekResult -->|2+ matches| ErrorMultiple[Fail: Multiple potential<br/>matches - manual review]

    Step2 --> UpdateResult{Update<br/>successful?}
    UpdateResult -->|Yes| Step3[Step 3: Save website/email<br/>to GP Internet table]
    UpdateResult -->|Customer deleted| ErrorNotFound[Fail: Customer<br/>no longer exists in GP]

    Step3 --> Step5[Step 5: Send GP number<br/>back to CRM]

    Step4 --> InsertResult{Insert<br/>successful?}
    InsertResult -->|Yes| Step6[Step 6: Save website/email<br/>to GP Internet table]

    Step5 --> Step7[Step 7: Update CRM<br/>address with GP ID]
    Step6 --> Step7

    Step7 --> Success[Sync Complete]

    ErrorMultiple --> End[Manual Review Required]
    ErrorNotFound --> End

Key Processing Steps¶

This integration uses a 6-step workflow with cross-reference and soft-key matching logic:

Step 1 - Dynamics GP.Customer (Seek): Pre-operation formula checks if customer is already shared via cross-reference (LookupCustomerID). If cross-reference exists, skip to Step 2. If not, seek customer by soft key (name + ZIP code).
Success (0 matches): Customer not found → proceed to Step 4 (insert)
Success (1 match): Customer found → proceed to Step 2 (update)
Success (2+ matches): Multiple potential matches → error and rollback
Step 2 - Dynamics GP.Customer(2) (Update): Update existing GP customer record with CRM data using cross-reference ID or matched ID from Step 1.
Success (0 matches): Customer not found error
Success (1 match): Update successful → continue to Step 3
Step 3 - Dynamics GP.Internet Information (Update/Insert): Update or insert email and website into GP Internet Information table for the customer.
Step 4 - Dynamics GP.Customer(3) (Insert): Insert new customer into GP when no cross-reference or soft-key match exists. Generates new GP customer ID using GPNEWCUSTOMERID function.
Step 5 - Microsoft CRM 2011 Adapter.account (Update): Write GP customer number back to CRM account record for bidirectional tracking.
Step 6 - Dynamics GP.Internet Information(2) (Insert): Insert email and website for newly created customer.
Step 7 - Dynamics CRM.customeraddress (Update): Update CRM customeraddress record with GP Address ID for address linking.

User Variables¶

Variable Name	Formula	Purpose
LookupCustomerID	`S7`	Cross-reference lookup variable populated by key cross-reference feature. Contains GP Customer ID if account is already shared, or `#NOTFOUND!` if not. Used in Step 1 PreOp formula to determine routing: - If `ISERROR(LookupCustomerID)` = TRUE → run seek logic - If `ISERROR(LookupCustomerID)` = FALSE → `GOTOSTEP(2)` skip to update
AreaCode	`"000"`	Default area code string used when converting phone numbers from CRM format to GP format. Passed as parameter to `PHONEINTOGP()` function for 7-digit phone numbers.

Target Variables¶

Variable Name	Data Object	Data Field	Purpose
ExistingCustomerID	Customer	Customer ID	Captures GP Customer ID found via soft-key seek (Step 1). Used in Step 2 update to target the matched customer.
PrimaryAddressID	Customer(2)	Address ID	Retrieves the current "primary" Address ID from GP customer record. Used in Step 7 to update CRM customeraddress with GP address ID.
GPCustomerNumber	Customer(3)	Customer ID	Captures newly generated GP Customer ID after insert (Step 4). Used in Steps 5, 6, 7 for post-insert operations.

Field Mappings and Transformations¶

Step 1: Seek Customer by Name + ZIP (Soft-Key Match)¶

Target Field	Source Field(s)	Transformation Formula	Explanation
Customer ID	S126 (name)	`LEFT(S126, INSTR(S126, " ") - 1)`	Extracts first word of account name for soft-key matching
ZIP Code	S25 (address1_postalcode)	`LEFT(S25, 3)`	Uses first 3 digits of ZIP code for geographic matching

Step 2: Update Existing Customer¶

Target Field	Source Field(s)	Transformation Formula	Explanation
Name	S126 (name)	`LEFT(S126, 65)`	Account name truncated to GP 65-character limit
Statement Name	S126 (name)	`LEFT(S126, 65)`	Same as Name for billing statements
Address 1	S20 (address1_line1)	`LEFT(S20, 31)`	Primary address line truncated to 31 characters
Address 2	S21 (address1_line2)	`LEFT(S21, 31)`	Secondary address line truncated to 31 characters
Address 3	S22 (address1_line3)	`LEFT(S22, 31)`	Tertiary address line truncated to 31 characters
City	S13 (address1_city)	`LEFT(S13, 31)`	City name truncated to 31 characters
State	S30 (address1_stateorprovince)	`LEFT(S30, 29)`	State/province truncated to 29 characters
ZIP Code	S25 (address1_postalcode)	`LEFT(S25, 11)`	Full postal code truncated to 11 characters
Country	S14 (address1_country)	`LEFT(S14, 21)`	Country name truncated to 21 characters
UPS Zone	S34 (address1_upszone)	`S34`	UPS shipping zone code passed through
Shipping Method	S28 (address1_shippingmethodcode)	`FILELOOKUP(S28, "XRef.ini", "Shipping_Method_ToGP")`	Translates CRM shipping method option value to GP shipping method code via XRef.ini
Phone 1	S173 (telephone1)	`PHONEINTOGP(S173, AreaCode)`	Formats CRM phone to GP format (removes formatting, applies area code if needed)
Phone 2	S174 (telephone2)	`PHONEINTOGP(S174, AreaCode)`	Formats secondary phone number
Phone 3	S175 (telephone3)	`PHONEINTOGP(S175, AreaCode)`	Formats tertiary phone number
Fax	S106 (fax)	`PHONEINTOGP(S106, AreaCode)`	Formats fax number to GP format
Comment 1	S87 (description)	`TRIM(LEFT(S87, 31))`	Account description trimmed and truncated to 31 characters

Step 3: Update/Insert Internet Information (Existing Customer)¶

Target Field	Source Field(s)	Transformation Formula	Explanation
E-mail	S103 (emailaddress1)	`S103`	Primary email address
Home Page	S176 (websiteurl)	`S176`	Website URL
Master Type	Constant	`"CUS"`	Master record type identifier for customer
Address ID	Variable	`PrimaryAddressID`	GP Address ID retrieved from customer record

Step 4: Insert New Customer¶

Target Field	Source Field(s)	Transformation Formula	Explanation
Customer ID	S126 (name)	`GPNEWCUSTOMERID("Microsoft GP Adapter", LEFT(UPPER(STRIP(STRIPCOMPANY(S126), "ANO")) & "000000", 8), 4)`	Generates unique GP customer ID: 1. `STRIPCOMPANY(S126)` removes common company suffixes (Inc, LLC, Corp) 2. `STRIP(..., "ANO")` keeps only alphanumeric characters 3. `UPPER()` converts to uppercase 4. `LEFT(... & "000000", 8)` pads to 8 characters 5. `GPNEWCUSTOMERID()` ensures uniqueness by adding suffix
Name	S126 (name)	`LEFT(S126, 65)`	Account name truncated to 65 characters
Short Name	S126 (name)	`LEFT(S126, 15)`	Abbreviated name truncated to 15 characters
Statement Name	S126 (name)	`LEFT(S126, 65)`	Same as Name for billing statements
Address ID	Constant	`"PRIMARY"`	Standard primary address identifier for new customers
Address 1	S20 (address1_line1)	`LEFT(S20, 31)`	Primary address line
Address 2	S21 (address1_line2)	`LEFT(S21, 31)`	Secondary address line
Address 3	S22 (address1_line3)	`LEFT(S22, 31)`	Tertiary address line
City	S13 (address1_city)	`LEFT(S13, 31)`	City name
State	S30 (address1_stateorprovince)	`LEFT(S30, 29)`	State/province
ZIP Code	S25 (address1_postalcode)	`LEFT(S25, 11)`	Postal code
Country	S14 (address1_country)	`LEFT(S14, 21)`	Country name
UPS Zone	S34 (address1_upszone)	`S34`	UPS zone code
Shipping Method	S28 (address1_shippingmethodcode)	`FILELOOKUP(S28, "XRef.ini", "Shipping_Method_ToGP")`	Shipping method translation
Phone 1	S173 (telephone1)	`PHONEINTOGP(S173, AreaCode)`	Formatted primary phone
Phone 2	S174 (telephone2)	`PHONEINTOGP(S174, AreaCode)`	Formatted secondary phone
Phone 3	S175 (telephone3)	`PHONEINTOGP(S175, AreaCode)`	Formatted tertiary phone
Fax	S106 (fax)	`PHONEINTOGP(S106, AreaCode)`	Formatted fax number
Class ID	S2 (accountcategorycode)	`FILELOOKUP(S2, "XRef.ini", "Account_Category_ToGP")`	Customer class mapping from CRM category
Comment 1	S87 (description)	`TRIM(LEFT(S87, 31))`	Account description
Comment 2	S5 (accountclassificationcodename)	`LEFT(S5, 31)`	Account classification truncated to 31 characters
Bill To Address ID	Constant	`"PRIMARY"`	Billing address defaults to primary
Use Customer Class	Constant	`1`	Flag to apply customer class defaults
Payment Terms ID	S129 (paymenttermscode)	`FILELOOKUP(S129, "XRef.ini", "Payment_Terms_ToGP")`	Payment terms translation
Salesperson ID	S127 (ownerid)	`DBLOOKUP("Microsoft CRM 2011 Adapter", "systemuser", "systemuserid", S127, "scribe_gpsalespersonid")`	Looks up GP salesperson ID from CRM owner mapping

Step 5: Update CRM Account with GP Customer Number¶

Target Field	Source Field(s)	Transformation Formula	Explanation
parentid	S6 (accountid)	`S6`	CRM account GUID for targeting the update
accountnumber	Variable	`GPCustomerNumber`	Writes newly generated GP Customer ID back to CRM account number field

Step 6: Insert Internet Information (New Customer)¶

Target Field	Source Field(s)	Transformation Formula	Explanation
Master Type	Constant	`"CUS"`	Master record type identifier
Address ID	Constant	`"PRIMARY"`	Links to primary address
E-mail	S103 (emailaddress1)	`S103`	Primary email address
Home Page	S176 (websiteurl)	`S176`	Website URL

Step 7: Update CRM customeraddress with GP Address ID¶

Target Field	Source Field(s)	Transformation Formula	Explanation
parentid	S6 (accountid)	`S6`	CRM account GUID
objecttypecode	Constant	`1`	Entity type code for account
addressnumber	Constant	`1`	Primary address number
createdbyname	Variable	`PrimaryAddressID` or `"PRIMARY"`	GP Address ID stored in CRM for bidirectional linking
accountid	S6 (accountid)	`S6`	Account GUID for record identification

Formula Reference¶

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 the original input value unchanged.

Usage: FILELOOKUP(S28, "XRef.ini", "Shipping_Method_ToGP") translates CRM shipping method code "1" to GP code "UPS GROUND"

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" or "555-1234")
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).

Logic:

Strips all non-numeric characters from input
If resulting digits = 7, prepends the default area code
If resulting digits = 10+, uses as-is
Returns formatted numeric string

Usage: PHONEINTOGP(S173, AreaCode) converts "(555) 123-4567" to "5551234567"

GPNEWCUSTOMERID¶

Description: Generates a unique GP customer ID by creating a base ID from the customer name and ensuring uniqueness.

Parameters:

Param 1: GP Adapter connection name (e.g., "Microsoft GP Adapter")
Param 2: Proposed base customer ID (e.g., "ACME0000")
Param 3: Number of numeric characters to use for uniqueness suffix (e.g., 4)

Returns: Unique customer ID string (e.g., "ACME0001" if "ACME0000" exists).

Logic:

Checks if proposed ID exists in GP
If unique, returns proposed ID
If exists, appends numeric suffix and increments until unique
Suffix uses the number of digits specified in Param 3

Usage: GPNEWCUSTOMERID("Microsoft GP Adapter", "ACME0000", 4) might return "ACME0001"

STRIPCOMPANY¶

Description: Removes common company suffixes from a company name string.

Parameters:

Param 1: Company name string (e.g., "Acme Corporation Inc.")

Returns: Company name with suffixes removed (e.g., "Acme").

Removed Suffixes: Inc, LLC, Corp, Corporation, Ltd, Limited, Co, Company (case-insensitive)

Usage: STRIPCOMPANY("Acme Inc") returns "Acme"

STRIP¶

Description: Removes specified character types from a string.

Parameters:

Param 1: Input string
Param 2: Character type code
"ANO" = Keep only alphanumeric characters (remove all others)
Other codes may exist for different filtering

Returns: Filtered string with specified characters removed.

Usage: STRIP("Acme & Co!", "ANO") returns "AcmeCo"

UPPER¶

Description: Converts all characters in a string to uppercase.

Parameters:

Param 1: Input string

Returns: Uppercase string.

Usage: UPPER("acme") returns "ACME"

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"

TRIM¶

Description: Removes leading and trailing whitespace from a string.

Parameters:

Param 1: Input string

Returns: String with whitespace removed from both ends.

Usage: TRIM(" Hello ") returns "Hello"

DBLOOKUP¶

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

Parameters:

Param 1: Connection name (e.g., "Microsoft CRM 2011 Adapter")
Param 2: Entity/table name (e.g., "systemuser")
Param 3: Key field name to search (e.g., "systemuserid")
Param 4: Key value to find (e.g., GUID)
Param 5: Field name to return (e.g., "scribe_gpsalespersonid")

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

Usage: DBLOOKUP("Microsoft CRM 2011 Adapter", "systemuser", "systemuserid", S127, "scribe_gpsalespersonid") looks up GP salesperson ID from CRM user record

ISERROR¶

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

Parameters:

Param 1: Expression to evaluate (e.g., variable, formula result)

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

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

GOTOSTEP¶

Description: Flow control function that causes immediate jump to a specified step, skipping intermediate steps.

Parameters:

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

Returns: Special control value #GOTOSTEP2! (or equivalent for target step number).

Usage: IF(ISERROR(LookupCustomerID), TRUE, GOTOSTEP(2)) skips seek step if cross-reference exists

IF¶

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

Parameters:

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

Returns: Value from Param 2 or Param 3 depending on condition evaluation.

Usage: IF(ISERROR(LookupCustomerID), TRUE, GOTOSTEP(2)) evaluates cross-reference and determines routing

INSTR¶

Description: Returns the position of the first occurrence of a substring within a string.

Parameters:

Param 1: String to search in
Param 2: Substring to find

Returns: 1-based position of substring if found, otherwise 0.

Usage: INSTR(S126, " ") finds the position of the first space in the name

#NOTFOUND!¶

Description: Special literal value indicating a database lookup or cross-reference returned no results.

Usage: Returned by DBLOOKUP, LookupCustomerID, and other lookup operations when no match exists.

Testing: Use with ISERROR() function: ISERROR(#NOTFOUND!) returns TRUE

Source and Target Connection Details¶

Source Connection¶

Type: Microsoft CRM 2011 Adapter
Entity: account
Key Fields: ScribeObjectKey (accountid GUID)
Name Fields: name (account name)
Query: Standard account entity query

Target Connections¶

Microsoft GP Adapter (Primary Target):
Customer entity (Steps 1, 2, 4)
Internet Information entity (Steps 3, 6)
Read/Write operations for customer master and contact info
Microsoft CRM 2011 Adapter (Write-Back Target):
account entity (Step 5) - Write GP customer number back to CRM
customeraddress entity (Step 7) - Update with GP Address ID

Cross-Reference Configuration¶

Source Key: ScribeObjectKey (accountid GUID)
Target Key: Customer ID (GP customer number)
Lookup Variable: LookupCustomerID
Source Name: name (account)
Target Name: Name (customer)
Purpose: Tracks which CRM accounts are already synchronized to GP customers

Business Rules and Data Quality Considerations¶

Soft-Key Matching Logic¶

The integration uses name + ZIP code matching when cross-reference doesn't exist:

Primary Criteria: First word of account name + first 3 digits of ZIP
Match Evaluation:
0 matches: Account not in GP → insert new customer (Step 4)
1 match: Account found in GP → update customer (Step 2) and create cross-reference
2+ matches: Ambiguous match → error and rollback with message "Multiple potential matches on Customer"

Customer ID Generation Rules¶

For new customers (Step 4), GP Customer ID is generated using:

Company name sanitization: Remove suffixes (Inc, LLC, Corp), strip special characters
Uppercase conversion for consistency
Padding to 8 characters with zeros
Uniqueness check via GPNEWCUSTOMERID() which adds numeric suffix if needed

Examples:

"Acme Corporation" → "ACME0000" (or "ACME0001" if exists)
"Smith & Sons, Inc." → "SMITHSON0" (or "SMITHSO01" if exists)

Field Truncation¶

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

Name: 65 characters
Address lines: 31 characters
City: 31 characters
State: 29 characters
ZIP: 11 characters
Country: 21 characters
Comment fields: 31 characters

Phone Number Formatting¶

PHONEINTOGP() function normalizes phone formats:

Input: "(555) 123-4567 x890"
Process: Strip to "5551234567890"
Output: "5551234567890" (or with area code prepended if 7 digits)

Bidirectional Synchronization¶

This integration maintains bidirectional links:

Step 5: Writes GP Customer ID back to CRM account.accountnumber field
Step 7: Writes GP Address ID back to CRM customeraddress.createdbyname field
Cross-reference table tracks accountid ↔ Customer ID mapping

These write-backs enable:

CRM users to see GP customer numbers
Downstream integrations to use GP identifiers
Address synchronization in both directions

SmartConnect Re-implementation Notes¶

Multi-Step Workflow Implementation¶

SmartConnect can replicate this 6-step process using:

Option 1: Multiple Maps (Recommended for Clarity)

Map 1: Customer Seek and Update (Steps 1-3)
Map 2: Customer Insert (Steps 4, 6)
Map 3: CRM Write-Back (Steps 5, 7)

Option 2: Single Map with C# Scripting

// Pre-map script to determine routing
string gpCustomerID = GetCrossReference(accountId);

if (!string.IsNullOrEmpty(gpCustomerID))
{
    // Cross-reference exists - route to update
    SetGlobalValue("RouteMode", "UPDATE");
    SetGlobalValue("GPCustomerID", gpCustomerID);
}
else
{
    // No cross-reference - seek by soft key
    string softKeyMatch = SeekCustomerBySoftKey(accountName, zipCode);

    if (softKeyMatch == null)
    {
        // No match - route to insert
        SetGlobalValue("RouteMode", "INSERT");
    }
    else if (softKeyMatch.Contains(","))
    {
        // Multiple matches - error
        throw new Exception("Multiple potential matches on Customer");
    }
    else
    {
        // One match - route to update and create xref
        SetGlobalValue("RouteMode", "UPDATE");
        SetGlobalValue("GPCustomerID", softKeyMatch);
        SetGlobalValue("CreateXRef", "true");
    }
}

Soft-Key Matching Implementation¶

T-SQL for SmartConnect SQL Script:

DECLARE @AccountName NVARCHAR(100) = ?  -- Input parameter
DECLARE @ZipCode NVARCHAR(11) = ?       -- Input parameter
DECLARE @FirstWord NVARCHAR(50)
DECLARE @ZipPrefix NVARCHAR(3)

-- Extract first word of name
SET @FirstWord = LEFT(@AccountName, CHARINDEX(' ', @AccountName + ' ') - 1)

-- Extract first 3 digits of ZIP
SET @ZipPrefix = LEFT(@ZipCode, 3)

-- Seek customers matching criteria
SELECT CUSTNMBR
FROM RM00101  -- GP Customer Master
WHERE
    CUSTNAME LIKE @FirstWord + '%'
    AND LEFT(ZIP, 3) = @ZipPrefix

-- Return result:
-- 0 rows = insert
-- 1 row = update with returned CUSTNMBR
-- 2+ rows = error

Customer ID Generation¶

C# Implementation of GPNEWCUSTOMERID:

public string GenerateUniqueCustomerID(string baseName, int suffixLength)
{
    // Step 1: Clean the company name
    string cleaned = StripCompanySuffixes(baseName);
    cleaned = StripNonAlphanumeric(cleaned);
    cleaned = cleaned.ToUpper();

    // Step 2: Create base ID (8 characters)
    string baseID = (cleaned + "000000").Substring(0, 8);

    // Step 3: Check uniqueness and add suffix if needed
    string proposedID = baseID;
    int suffix = 0;

    while (CustomerIDExists(proposedID))
    {
        suffix++;
        string suffixStr = suffix.ToString().PadLeft(suffixLength, '0');
        proposedID = baseID.Substring(0, 8 - suffixLength) + suffixStr;
    }

    return proposedID;
}

private string StripCompanySuffixes(string name)
{
    string[] suffixes = { " Inc", " LLC", " Corp", " Corporation",
                          " Ltd", " Limited", " Co", " Company" };

    foreach (string suffix in suffixes)
    {
        if (name.EndsWith(suffix, StringComparison.OrdinalIgnoreCase))
        {
            name = name.Substring(0, name.Length - suffix.Length);
        }
    }

    return name.Trim();
}

private string StripNonAlphanumeric(string input)
{
    return Regex.Replace(input, "[^a-zA-Z0-9]", "");
}

private bool CustomerIDExists(string customerID)
{
    // Query GP database
    string sql = "SELECT COUNT(*) FROM RM00101 WHERE CUSTNMBR = @CustomerID";
    // Execute and return true if count > 0
}

Phone Number Formatting¶

C# Implementation of PHONEINTOGP:

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 (add area code)
    if (digitsOnly.Length == 7)
    {
        digitsOnly = defaultAreaCode + digitsOnly;
    }

    // Return digits only (GP stores unformatted)
    return digitsOnly;
}

XRef.ini File Translations¶

SmartConnect can replicate XRef.ini translations using:

Option 1: SQL Lookup Tables

CREATE TABLE XRef_ShippingMethod_ToGP (
    CRM_Value INT,
    GP_Code VARCHAR(15)
)

INSERT INTO XRef_ShippingMethod_ToGP VALUES
(1, 'UPS GROUND'),
(2, 'UPS 2ND DAY AIR'),
(3, 'UPS NEXT DAY AIR')

Option 2: SmartConnect Translation Tables (Built-in Feature)

Create translation in SmartConnect UI
Map CRM option values to GP codes
Apply in field mapping

Option 3: C# Dictionary

private Dictionary<int, string> shippingMethodMap = new Dictionary<int, string>
{
    { 1, "UPS GROUND" },
    { 2, "UPS 2ND DAY AIR" },
    { 3, "UPS NEXT DAY AIR" }
};

public string TranslateShippingMethod(int crmValue)
{
    return shippingMethodMap.ContainsKey(crmValue)
        ? shippingMethodMap[crmValue]
        : crmValue.ToString();  // Return original if not found
}

Error Handling and Logging¶

SmartConnect Error Handling:

try
{
    // Attempt customer sync
    SyncCustomerToGP(accountData);
}
catch (MultipleMatchException ex)
{
    // Log to SmartConnect error table
    LogError("Multiple potential matches on Customer", accountId, ex.Message);

    // Mark record as failed
    SetRecordStatus("FAILED");

    // Optionally send notification
    SendEmailAlert("Customer Sync Error", ex.Message);
}
catch (CustomerNotFoundException ex)
{
    LogError("Customer update didn't find record", accountId, ex.Message);
    SetRecordStatus("FAILED");
}

Testing Strategy¶

Test Scenario 1: Cross-Reference Path (Existing Shared Customer)

Create CRM account already in cross-reference table
Modify account name, address, phone in CRM
Run integration
Verify: Step 1 pre-op jumps to Step 2, GP customer updated, Internet Information updated

Test Scenario 2: Soft-Key Match Path (Unshared Existing Customer)

Create GP customer with name "ACME" and ZIP "12345"
Create CRM account "ACME Corporation" with ZIP "12345" (not in cross-reference)
Run integration
Verify: Step 1 seek finds 1 match, Step 2 updates customer, cross-reference created

Test Scenario 3: New Customer Insert Path

Create CRM account with unique name and ZIP not matching any GP customer
Run integration
Verify: Step 1 seek returns 0, Step 4 inserts customer with generated ID, Step 5 writes GP number back to CRM, Step 6 inserts Internet Information

Test Scenario 4: Multiple Match Error

Create 2+ GP customers with name "TEST" and ZIP "12300-12399"
Create CRM account "TEST Company" with ZIP "12345"
Run integration
Verify: Step 1 seek returns 2+, error "Multiple potential matches on Customer", rollback

Test Scenario 5: Bidirectional Write-Back

Run new customer insert (Scenario 3)
Verify GP Customer ID appears in CRM account.accountnumber field (Step 5)
Verify GP Address ID appears in CRM customeraddress.createdbyname field (Step 7)

Performance Considerations¶

Cross-Reference Caching: Cache cross-reference lookups in memory to avoid repeated database queries
Batch Processing: Process accounts in batches of 100-500 for optimal throughput
Soft-Key Optimization: Index GP Customer table on Name + ZIP prefix for faster seeks
Connection Pooling: Reuse CRM and GP connections across records
Parallel Processing: Consider parallel map execution for independent account records

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

[Account_Category_ToGP]
1=USA-SALES
2=INTERNATIONAL
3=DISTRIBUTOR
4=RETAIL

[Payment_Terms_ToGP]
1=NET 30
2=NET 60
3=NET 90
4=COD
5=CREDIT CARD

Integration Dependencies¶

Upstream Dependencies¶

None (triggered directly from CRM or via schedule)

Downstream Dependencies¶

CustomerToCRM.dts: May trigger reverse sync if GP customer is modified
AddressToCRM.dts: GP Address ID written to CRM enables address sync

CRM Customizations¶

systemuser.scribe_gpsalespersonid: Custom field storing GP Salesperson ID for lookup in Step 4
account.accountnumber: Used to store GP Customer ID for bidirectional tracking
customeraddress.createdbyname: Repurposed to store GP Address ID

GP Customizations¶

None required (uses standard Customer and Internet Information tables)