Scribe Integration Deep Dive: Customer to CRM¶

This document provides a detailed analysis of the CustomerToCRM.dts integration, designed to synchronize customer data from Dynamics GP (ERP) to Microsoft Dynamics CRM 2011. The goal is to provide enough detail for a developer to re-implement this logic in a different integration platform like SmartConnect.

Plain Language Summary¶

Change Detection: Dynamics GP writes each customer change into a helper table called SCRIBESHADOW. The MSGP Publisher checks that table every second. Rows marked New (N) or Modified (M) create a FromERP_Customer message for this DTS. When the DTS finishes successfully the publisher flips the status to Synced (S), so the row drops off the queue.
Linking Table (Cross-Reference): Scribe maintains a linking table called KEYCROSSREFERENCETWOWAY that tracks which GP customers have been synced to which CRM accounts. Think of it like a phone book: GP Customer ID → CRM Account ID. This prevents creating duplicate accounts every time a customer is updated.
Smart Routing: The DTS checks the linking table first. If it finds an entry, it simply updates the existing CRM account. If not found, it searches CRM by name and ZIP code to see if the account already exists there (maybe someone created it manually). Only if nothing is found does it create a brand-new CRM account.
Two-Way Handshake: When we match an existing CRM account by name/ZIP (soft-key match), Step 5 deliberately updates the CRM address using a different CRM user so CRM's own plug-ins fire. That update then triggers the CRM ➜ GP address job, which finalizes the handshake between the two systems and ensures all related addresses are synced.

1. Integration Overview¶

Name: CustomerToCRM.dts
Purpose: To create and update account records in CRM based on customer data from GP. This is a bidirectional sync integration that implements sophisticated soft-key matching logic to link existing CRM accounts with GP customers when they were created independently in each system.
How we detect changes: The MSGP Publisher checks GP's SCRIBESHADOW change log every second. Customer rows flagged as N (new) or M (modified) generate a FromERP_Customer message. After this DTS succeeds, the publisher stamps the same row as S (synced) so it does not re-queue.
Source: The DTS reads queued FromERP_Customer XML payloads defined by the publisher (FromERP_Customer.xsd).
Target: Microsoft Dynamics CRM 2011, specifically the account and customeraddress entities.

Trigger and Message Source¶

Think of SCRIBESHADOW as GP's change log: the triggers defined in DocsAndScripts/CRMGP_TemplateConfig.sql add a row with status N when a customer is inserted and switch the flag to M when that customer is updated. The MSGP Publisher (see publishers-bridges-connections.spkg) polls that log once per second (frequency = 1). For each customer row showing N or M, it pulls the enriched data defined in the package and publishes a FromERP_Customer message. When this DTS finishes successfully, the publisher runs its SourceUpdate command, updating SYNCSTATUS1 to S. If the DTS errors, the status stays at N or M, so the record is retried on the next polling cycle.

Data Flow and Logic¶

The integration implements a complex multi-step workflow that handles three distinct scenarios: (1) updating existing shared customers via cross-reference lookup, (2) soft-key matching to find and link accounts that exist in both systems but haven't been synchronized yet, and (3) creating new accounts in CRM for GP customers that don't exist in either the cross-reference table or through soft-key matching.

graph TD
    A[GP Customer Change Detected] --> B{Has this customer<br/>been synced before?};
    B -->|Yes - Found in<br/>linking table| C[Step 6: Update CRM<br/>Account with GP changes];
    B -->|No - Not found<br/>in linking table| D{Does GP customer<br/>have a ZIP code?};
    D -->|Yes| E[Step 1: Search CRM by<br/>Name + ZIP];
    D -->|No ZIP| F[Step 3: Search CRM by<br/>Name only];
    E --> G{How many CRM<br/>accounts match?};
    F --> H{How many CRM<br/>accounts match?};
    G -->|0 matches| I[Step 7: Create New<br/>CRM Account];
    G -->|1 match| J[Step 2: Check if match<br/>is a Customer];
    G -->|2+ matches| J;
    H -->|0 matches| I;
    H -->|1 match| K[Step 4: Check if match<br/>is a Customer];
    H -->|2+ matches| K;
    J -->|Not a Customer| L[Fail: Match found but<br/>wrong account type];
    J -->|Is a Customer| M[Step 5: Link & Update<br/>matched CRM Account];
    K -->|Not a Customer| L;
    K -->|Is a Customer| M;
    M --> N[Trigger address sync<br/>back to GP];
    C --> O[Sync Complete];
    I --> O;
    N --> O;
    L --> P[Manual Review Required];

Key Processing Steps:¶

Step Pre-Op (Step 1 Pre-Formula): The integration starts by evaluating the LookupAccountID user variable to determine which processing path to follow:

If LookupAccountID contains a valid GUID (found in cross-reference table), jump directly to Step 6 for a standard update.
If LookupAccountID is #NOTFOUND! and ZIP code (S20) is valid, proceed to Step 1 for soft-key matching with ZIP.
If LookupAccountID is #NOTFOUND! and ZIP code is null/error, jump to Step 3 for soft-key matching without ZIP.

Step 1: Soft-Key Seek with ZIP (Account) - Seeks CRM accounts by name (exact match) and address1_postalcode (ZIP):

Success with 0 records: No match found → Jump to Step 7 (Insert new account)
Success with 1 record: Potential match → Continue to Step 2 for customer type validation
Success with 2+ records: Multiple matches → Continue to Step 2 for customer type filtering

Step 2: Filter by Customer Type (Account(2)) - Narrows Step 1 results to only customertypecode = 3 (Customer):

Success with 0 records: No customers in the match set → Fail with "Multiple potential matches - None are Customers"
Success with 1 record: Unique customer match → Jump to Step 5 (Update with Non-INTEGRATION user)
Success with 2+ records: Multiple customers → Fail with "Multiple potential matches - Multiple are Customers"

Step 3: Soft-Key Seek without ZIP (Account(3)) - Same as Step 1 but seeks only by name and address1_postalcode IS NULL:

Same success routing as Step 1 but proceeds to Step 4 instead of Step 2

Step 4: Filter by Customer Type (Account(4)) - Narrows Step 3 results to only customertypecode = 3:

Same logic as Step 2

Step 5: Update Matched Account (Non-INTEGRATION CRM.customeraddress) - When a soft-key match is found, this step performs a special update:

Uses the Non-INTEGRATION connection (critical for triggering address cascade)
Updates the account's address fields via the customeraddress entity
This update causes CRM to cascade the changes, which triggers the AddressToERP integration to sync back to GP
This two-way handshake ensures the matched account and GP customer are properly synchronized

Step 6: Update Existing Account (Account(5)) - Standard update path for customers already in the cross-reference:

Updates the CRM account using the GUID from LookupAccountID
Success with 0 records: Account was deleted → Fail with "Account not found! Must have been deleted from CRM."
Success with 1 record: Normal update → Complete successfully
Success with 2+ records: Should never happen → Report error but continue

Step 7: Insert New Account (Account(6)) - Creates a new CRM account when no match exists in cross-reference or soft-key seeks:

Inserts all customer data fields
Validates that customer is not marked as INACTIVE in GP (fails job if inactive)
Creates cross-reference entry linking GP customer to new CRM account

2. User Variables¶

These variables are used to store values for lookups and transformations during the integration.

Variable Name	Formula	Purpose
`LookupAccountID`	`DBLOOKUP( S114, "Microsoft CRM 2011 Adapter", "account", "accountnumber", "accountid" )`	Calls `DBLOOKUP` with parameters: 1. `S114` – GP Integration ID, likely `CUSTNMBR` or similar unique identifier. 2. CRM connection name. 3. Target entity `account`. 4. Key field `accountnumber` that stores the GP ID in CRM. 5. Return field `accountid`. The function returns the CRM account GUID when a match is found in the cross-reference; otherwise it returns `#NOTFOUND!`. This value determines which processing path the integration follows: update (Step 6) vs. soft-key matching (Steps 1-5) vs. insert (Step 7).
`AreaCode`	`"000"`	Constant string passed as the second parameter to `PHONEFROMGP`. The function expects a fallback area code it can prepend when the raw GP number is fewer than 10 digits. The value `"000"` acts as a placeholder indicating no specific area code default.

Important Note: The ExistingAccountID is listed as a TargetVariable (not a UserVariable), which means it stores the result of seek operations during Steps 1-4 and is used to link the parent account when soft-key matches are updated in Step 5.

3. Field Mappings and Transformations¶

This section details the mapping from the source XML fields to the target CRM entity fields. Due to the complexity of this integration with 7 steps, fields are organized by the step they are used in.

Target: `account` (Steps 1 & 3 - Seek Operations)¶

These steps perform lookup operations using minimal fields to find potential matches:

Target Field	Source Field(s)	Transformation Formula	Explanation
`name`	`S4 (CUSTNAME)`	`S4`	Customer name from GP used as the primary soft-key match criterion.
`address1_postalcode`	`S20 (ZIP)`	`S20` (Step 1) `IS NULL` (Step 3)	Step 1 seeks accounts where name matches AND ZIP matches. Step 3 seeks accounts where name matches AND ZIP IS NULL.
`accountid`	(return field)	-	This is the return field stored in `ExistingAccountID` target variable.

Target: `account` (Steps 2 & 4 - Customer Type Filter)¶

These steps narrow down the seek results to only Customer-type accounts:

Target Field	Source Field(s)	Transformation Formula	Explanation
`name`	`S4 (CUSTNAME)`	`S4`	Same name match as previous seek steps.
`address1_postalcode`	`S20 (ZIP)`	`S20` (Step 2) `IS NULL` (Step 4)	Maintains the same ZIP matching logic from the preceding step.
`customertypecode`	(constant)	`3`	Hard-coded filter value `3` represents "Customer" in CRM's option set. This narrows results to only customer accounts, excluding leads, prospects, etc.

Target: `customeraddress` (Step 5 - Soft-Key Match Update)¶

This step uses the Non-INTEGRATION connection to update the matched account's address, triggering the address cascade back to GP:

Target Field	Source Field(s)	Transformation Formula	Explanation
`name`	`S10 (ADRSCODE)`	`S10`	GP Address Code becomes the name identifier for the address record.
`parentid`	(from Step 1/3)	`ExistingAccountID`	Links the address to the matched account GUID found in the soft-key seek steps.
`objecttypecode`	(constant)	-	System field indicating the parent object type.
`addressnumber`	(constant)	`1`	Typically set to 1 for the primary address.
`createdbyname`	(constant)	`"INTEGRATION"`	Marks the record as created by the integration process.

Target: `account` (Step 6 - Update Existing)¶

Standard update of accounts that are already in the cross-reference:

Target Field	Source Field(s)	Transformation Formula	Explanation
`accountnumber`	`S3 (CUSTNMBR)`	`S3`	GP Customer Number serves as the natural key stored in CRM.
`name`	`S4 (CUSTNAME)`	`S4`	Customer name from GP.
`telephone1`	`S21 (PHONE1)`	`PHONEFROMGP( S21, AreaCode, "###-###-####", "x####" )`	`PHONEFROMGP` parameters: 1. Raw GP phone value (`S21`). 2. Default area code (`AreaCode`, set to `"000"`). 3. Output mask `"###-###-####"` for the primary number. 4. Extension mask `"x####"`. The function strips non-numeric characters, prepends the area code when fewer than 10 digits are supplied, and returns a formatted string adhering to the masks. When no digits are found it returns an empty string.
`telephone2`	`S22 (PHONE2)`	`PHONEFROMGP( S22, AreaCode, "###-###-####", "x####" )`	Same `PHONEFROMGP` pattern for second phone number.
`telephone3`	`S23 (PHONE3)`	`PHONEFROMGP( S23, AreaCode, "###-###-####", "x####" )`	Same `PHONEFROMGP` pattern for third phone number.
`fax`	`S24 (FAX)`	`PHONEFROMGP( S24, AreaCode, "###-###-####", "x####" )`	Same `PHONEFROMGP` pattern for fax number.
`accountcategorycode`	`S5 (CUSTCLAS)`	`FILELOOKUP( S5, "XRef.ini", "Account_Category")`	`FILELOOKUP` parameters: 1. GP customer class code (`S5`). 2. Lookup file `XRef.ini`. 3. Section name `Account_Category`. Returns the mapped CRM account category option value. If no match exists, returns the original GP code.
`description`	`S47 + S48 (COMMENT1 + COMMENT2)`	`S47 & " " & S48`	Concatenates the two GP comment fields with a space separator. The `&` operator performs string concatenation.
`paymenttermscode`	`S30 (PYMTRMID)`	`FILELOOKUP( S30, "XRef.ini", "Payment_Terms")`	Uses `FILELOOKUP` with: 1. GP payment terms ID (`S30`). 2. Lookup file `XRef.ini`. 3. Section `Payment_Terms`. Returns the mapped CRM payment terms option value.
`creditlimit`	`S32 (CRLMTAMT)`	`S32`	Credit limit amount from GP, direct mapping.
`creditonhold`	`S72 (HOLD)`	`S72`	Boolean flag indicating if credit is on hold, direct mapping.
`emailaddress1`	`S106`	`S106`	Primary email address from GP custom field or extension, direct mapping.
`websiteurl`	`S107`	`S107`	Website URL from GP custom field or extension, direct mapping.
`customertypecode`	(constant)	`3`	Hard-coded to `3` which represents "Customer" in CRM. This ensures the account is properly classified.
`address1_name`	`S10 (ADRSCODE)`	`S10`	GP Address Code becomes the address name in CRM.
`address1_addresstypecode`	`S10 (ADRSCODE)`	`FILELOOKUP( S10, "XRef.ini", "Address_Code")`	Uses `FILELOOKUP` to translate GP address code to CRM address type option value from the `Address_Code` section of `XRef.ini`.
`address1_primarycontactname`	`S7 (CPRCSTNM)`	`S7`	Contact person name from GP, direct mapping.
`address1_line1`	`S14 (ADDRESS1)`	`S14`	First address line from GP.
`address1_line2`	`S15 (ADDRESS2)`	`S15`	Second address line from GP.
`address1_line3`	`S16 (ADDRESS3)`	`S16`	Third address line from GP.
`address1_city`	`S18 (CITY)`	`S18`	City from GP.
`address1_stateorprovince`	`S19 (STATE)`	`S19`	State or province from GP.
`address1_postalcode`	`S20 (ZIP)`	`IF( ISERROR( S20 ), #NULL!, S20 )`	Nested functions: - `ISERROR(S20)` evaluates to `TRUE` when the ZIP field contains an error or is invalid. - `IF(condition, valueIfTrue, valueIfFalse)` returns `#NULL!` (CRM null value) when the condition is true to avoid populating CRM with invalid data; otherwise returns the ZIP code string.
`address1_country`	`S17 (COUNTRY)`	`S17`	Country from GP.
`address1_telephone1`	`S21 (PHONE1)`	`PHONEFROMGP( S21, AreaCode, "###-###-####", "x####" )`	Same `PHONEFROMGP` transformation as the main account phone fields.
`address1_telephone2`	`S22 (PHONE2)`	`PHONEFROMGP( S22, AreaCode, "###-###-####", "x####" )`	Same `PHONEFROMGP` transformation for phone 2.
`address1_telephone3`	`S23 (PHONE3)`	`PHONEFROMGP( S23, AreaCode, "###-###-####", "x####" )`	Same `PHONEFROMGP` transformation for phone 3.
`address1_fax`	`S24 (FAX)`	`PHONEFROMGP( S24, AreaCode, "###-###-####", "x####" )`	Same `PHONEFROMGP` transformation for fax.
`address1_shippingmethodcode`	`S12 (SHIPMTHD)`	`FILELOOKUP( S12, "XRef.ini", "Shipping_Method")`	Uses `FILELOOKUP` to translate GP shipping method to CRM shipping method option value from the `Shipping_Method` section of `XRef.ini`.
`address1_upszone`	`S11 (UPSZONE)`	`S11`	UPS shipping zone from GP, direct mapping.
`defaultpricelevelid`	`S38 (PRCLEVEL)`	`DBLOOKUPCached( S38, "Microsoft CRM 2011 Adapter", "pricelevel", "name", "pricelevelid" )`	`DBLOOKUPCached` parameters (cached version of DBLOOKUP for performance): 1. GP price level name (`S38`). 2. CRM connection name. 3. Target entity `pricelevel`. 4. Match field `name`. 5. Return field `pricelevelid`. Returns the CRM price level GUID when a match is found; otherwise returns `#NOTFOUND!`, leaving the field blank.
`ownerid`	`S28 (SLPRSNID)`	`DBLOOKUP( S28, "Microsoft CRM 2011 Adapter", "systemuser", "new_gpsalespersonid", "systemuserid" )`	`DBLOOKUP` parameters: 1. GP salesperson ID (`S28`). 2. CRM connection name. 3. Target entity `systemuser`. 4. Match field `new_gpsalespersonid` (custom field linking GP salesperson to CRM user). 5. Return field `systemuserid`. Returns the CRM user GUID when a match is found; otherwise `#NOTFOUND!`. This assigns ownership of the account to the appropriate sales rep.

Target: `account` (Step 7 - Insert New)¶

Insert operation for new customer accounts that don't exist in CRM:

Target Field	Source Field(s)	Transformation Formula	Explanation
`accountnumber`	`S3 (CUSTNMBR)`	`S3`	GP Customer Number serves as the natural key.
`name`	`S4 (CUSTNAME)`	`S4`	Customer name from GP.
`telephone1`	`S21 (PHONE1)`	`PHONEFROMGP( S21, AreaCode, "###-###-####", "x####" )`	Same phone formatting as Step 6.
`telephone2`	`S22 (PHONE2)`	`PHONEFROMGP( S22, AreaCode, "###-###-####", "x####" )`	Same phone formatting as Step 6.
`telephone3`	`S23 (PHONE3)`	`PHONEFROMGP( S23, AreaCode, "###-###-####", "x####" )`	Same phone formatting as Step 6.
`fax`	`S24 (FAX)`	`PHONEFROMGP( S24, AreaCode, "###-###-####", "x####" )`	Same phone formatting as Step 6.
`customertypecode`	(constant)	`3`	Hard-coded to `3` for "Customer".
`accountcategorycode`	`S5 (CUSTCLAS)`	`FILELOOKUP( S5, "XRef.ini", "Account_Category")`	Same account category lookup as Step 6.
`description`	`S47 + S48 (COMMENT1 + COMMENT2)`	`S47 & " " & S48`	Same comment concatenation as Step 6.
`paymenttermscode`	`S30 (PYMTRMID)`	`FILELOOKUP( S30, "XRef.ini", "Payment_Terms")`	Same payment terms lookup as Step 6.
`creditlimit`	`S32 (CRLMTAMT)`	`S32`	Same credit limit as Step 6.
`creditonhold`	`S72 (HOLD)`	`S72`	Same credit hold flag as Step 6.
`emailaddress1`	`S106`	`S106`	Same email as Step 6.
`websiteurl`	`S107`	`S107`	Same website URL as Step 6.
`statecode`	`S71 (INACTIVE)`	`IF( S71, ENDJOBFAILEDMSG( "This customer was not shared with CRM because it is marked as INACTIVE in Dynamics GP." ), #NULL! )`	Nested functions: - `IF(condition, valueIfTrue, valueIfFalse)` evaluates the `S71` (INACTIVE flag) value. - When `S71` is `TRUE` (customer is inactive), calls `ENDJOBFAILEDMSG(message)` which immediately fails the entire job with the specified error message, preventing any further processing. - When `S71` is `FALSE` (customer is active), returns `#NULL!` so the field is not set, allowing CRM to use its default active state. This validation ensures inactive GP customers are never created in CRM.
`address1_name`	`S10 (ADRSCODE)`	`S10`	Same address fields as Step 6.
`address1_addresstypecode`	`S10 (ADRSCODE)`	`FILELOOKUP( S10, "XRef.ini", "Address_Code")`	Same address type lookup as Step 6.
`address1_primarycontactname`	`S7 (CPRCSTNM)`	`S7`	Same contact name as Step 6.
`address1_line1`	`S14 (ADDRESS1)`	`S14`	Same address line 1 as Step 6.
`address1_line2`	`S15 (ADDRESS2)`	`S15`	Same address line 2 as Step 6.
`address1_line3`	`S16 (ADDRESS3)`	`S16`	Same address line 3 as Step 6.
`address1_city`	`S18 (CITY)`	`S18`	Same city as Step 6.
`address1_stateorprovince`	`S19 (STATE)`	`S19`	Same state as Step 6.
`address1_postalcode`	`S20 (ZIP)`	`IF( ISERROR( S20 ), #NULL!, S20 )`	Same ZIP validation as Step 6.
`address1_country`	`S17 (COUNTRY)`	`S17`	Same country as Step 6.
`address1_telephone1`	`S21 (PHONE1)`	`PHONEFROMGP( S21, AreaCode, "###-###-####", "x####" )`	Same phone transformations as Step 6.
`address1_telephone2`	`S22 (PHONE2)`	`PHONEFROMGP( S22, AreaCode, "###-###-####", "x####" )`	Same phone 2 as Step 6.
`address1_telephone3`	`S23 (PHONE3)`	`PHONEFROMGP( S23, AreaCode, "###-###-####", "x####" )`	Same phone 3 as Step 6.
`address1_fax`	`S24 (FAX)`	`PHONEFROMGP( S24, AreaCode, "###-###-####", "x####" )`	Same fax as Step 6.
`address1_shippingmethodcode`	`S12 (SHIPMTHD)`	`FILELOOKUP( S12, "XRef.ini", "Shipping_Method")`	Same shipping method lookup as Step 6.
`address1_upszone`	`S11 (UPSZONE)`	`S11`	Same UPS zone as Step 6.
`defaultpricelevelid`	`S38 (PRCLEVEL)`	`DBLOOKUPCached( S38, "Microsoft CRM 2011 Adapter", "pricelevel", "name", "pricelevelid" )`	Same price level lookup as Step 6.
`ownerid`	`S28 (SLPRSNID)`	`DBLOOKUP( S28, "Microsoft CRM 2011 Adapter", "systemuser", "new_gpsalespersonid", "systemuserid" )`	Same owner assignment as Step 6.

4. Formula Reference¶

The Scribe Insight formulas referenced above expect the following inputs and produce the noted outputs:

DBLOOKUP( value, connection, table, keyField, returnField )
- Inputs: a lookup value (text/number), a named connection, the target entity/table name, the field to match against, and the field to return.
- Returns: the value of returnField when a single matching row exists; otherwise returns #NOTFOUND!, which Scribe treats as empty.
DBLOOKUPCached( value, connection, table, keyField, returnField )
- Inputs: identical to DBLOOKUP.
- Returns: same as DBLOOKUP but caches the lookup results in memory for improved performance when the same lookup is performed multiple times within a job.
FILELOOKUP( value, fileName, sectionName )
- Inputs: the value to translate, the INI-style file that holds the mapping, and the section name inside that file.
- Returns: the mapped string when found; otherwise it returns the original input value, so missing mappings pass through unchanged.
PHONEFROMGP( phoneValue, defaultAreaCode, mainMask, extensionMask )
- Inputs: the raw GP phone number string, a default area code to prepend if needed, a format mask for the main number portion, and a format mask for any extension.
- Returns: a formatted phone string where # represents digit placeholders. The function strips all non-numeric characters, prepends the area code if the cleaned number has fewer than 10 digits, then applies the masks to format the output. Returns an empty string if no digits are found.
ISERROR( value )
- Inputs: any value or expression.
- Returns: TRUE if the value is an error state like #NOTFOUND!, #NULL!, or any other error; otherwise returns FALSE.
IF( condition, valueIfTrue, valueIfFalse )
- Inputs: a boolean condition, a value to return when the condition is true, and a value to return when the condition is false.
- Returns: either the true or false value based on the condition evaluation.
GOTOSTEP( stepNumber )
- Inputs: the step number (integer) to jump to.
- Returns: causes the integration to skip remaining processing and jump directly to the specified step. Used extensively in Pre-Op formulas to control workflow routing.
ENDJOBFAILEDMSG( message )
- Inputs: an error message string.
- Returns: immediately terminates the job with a failure status, displaying the provided message. Prevents any further processing of the current row and subsequent rows.
#NULL!
- A special literal value (not a function) that represents database NULL in Scribe.
- Used to explicitly set CRM fields to NULL/empty rather than passing through default or existing values.

5. Source and Target Connection Details¶

Source Connection¶

Type: XML File Source
Schema: FromERP_Customer.xsd
Source Query: ResultsSet (root element containing customer records)
Key Fields:
ScribeObject (metadata field)
CUSTNMBR (GP Customer Number)

Target Connections¶

Microsoft CRM 2011 Adapter (Primary Connection)

Type: CRM Adapter
Purpose: Standard integration connection for all normal operations (Steps 1-4, 6-7)
Authentication: Uses INTEGRATION user credentials

Non-INTEGRATION CRM (Special Connection)

Type: CRM Adapter
Purpose: Special connection used only in Step 5 for soft-key match updates
Authentication: Uses a different user account (Non-INTEGRATION)
Critical Behavior: When this connection updates an account's address, it triggers CRM's address cascade plugin, which creates/updates corresponding address records. This cascade is detected by the AddressToERP integration, causing the matched account's addresses to sync back to GP. This two-way handshake is essential for properly linking soft-key matched accounts.

Cross-Reference Configuration¶

The integration uses Scribe's built-in cross-reference table to track which GP customers have been synchronized to which CRM accounts:

Source Key: ScribeObjectKey (a composite key from the source data)
Target Key: accountid (the CRM account GUID)
Target Step: Account
Variable: LookupAccountID
Source Name: CUSTNAME (used for reporting/debugging)
Target Name: name (used for reporting/debugging)
Target Database: Microsoft CRM 2011 Adapter

6. Business Rules and Data Quality Considerations¶

Soft-Key Matching Logic¶

The integration implements sophisticated soft-key matching to handle scenarios where accounts were created independently in both GP and CRM:

Primary Match Criteria (Steps 1-2): Matches on exact customer name + ZIP code
More restrictive and preferred for better match accuracy
Only used when ZIP code is present and valid
Secondary Match Criteria (Steps 3-4): Matches on exact customer name + NULL ZIP
Less restrictive fallback when ZIP is not available
Helps match older records where ZIP may not have been captured
Customer Type Filtering (Steps 2 & 4): After initial name/ZIP matching, the integration filters to only customertypecode = 3 (Customer)
Prevents matching against leads, prospects, or other account types
Error handling for ambiguous matches:
- If multiple non-customer accounts match: Fails with "Multiple potential matches - None are Customers"
- If multiple customer accounts match: Fails with "Multiple potential matches - Multiple are Customers"

Inactive Customer Validation¶

The integration prevents inactive GP customers from being shared to CRM:

Uses the INACTIVE flag (S71) from GP
In Step 7 (Insert), if S71 = TRUE, the job fails with message: "This customer was not shared with CRM because it is marked as INACTIVE in Dynamics GP."
This validation only applies to new inserts; existing shared customers can still be updated even if marked inactive

Address Cascade Trigger (Step 5)¶

When a soft-key match is found, Step 5 performs a critical operation:

Updates the matched account's address using the Non-INTEGRATION connection
This special connection user is configured in CRM to trigger the address cascade plugin
The cascade creates/updates corresponding customeraddress records
These address updates are detected by the AddressToERP integration
This creates a two-way sync that properly links the GP customer and CRM account

This architecture ensures that soft-key matched accounts become fully synchronized, not just linked in the cross-reference table.

Cross-Reference Management¶

The integration relies on Scribe's cross-reference table to track synchronized records:

Successful Update (Step 6): Uses existing cross-reference entry
Successful Insert (Step 7): Creates new cross-reference entry
Successful Soft-Key Match (Steps 1-5): Creates cross-reference entry after update
Deleted Accounts: If an account is deleted from CRM, Step 6 will fail with "Account not found!" and the cross-reference entry should be manually removed

7. SmartConnect Re-implementation Notes¶

When re-implementing this integration in SmartConnect, consider the following architectural differences and requirements:

Multi-Step Workflow Implementation¶

SmartConnect does not have Scribe's built-in multi-step workflow with conditional branching. Recommended approaches:

Option A: Multiple SmartConnect Maps
Create separate maps for Update (Step 6) and Insert (Step 7) scenarios
Use pre-map SQL scripts to determine which records go to which map
Implement soft-key matching logic in T-SQL stored procedures
Option B: C# Script for Workflow Control
Use SmartConnect's scripting capability to implement the Pre-Op formula logic
Store the workflow state (which step to execute) in a global variable
Call appropriate map functions based on workflow state
Option C: Hybrid Approach
Use SQL stored procedures for complex soft-key seek logic
Pass results to SmartConnect maps for CRM operations
Implement cross-reference table separately in SQL

Soft-Key Matching Implementation¶

The soft-key matching logic (Steps 1-4) is the most complex part to replicate:

-- Example T-SQL for Step 1 logic (seek by name + ZIP)
DECLARE @CustomerName VARCHAR(100) = ?  -- From GP source
DECLARE @ZIP VARCHAR(20) = ?             -- From GP source

SELECT accountid, COUNT(*) as MatchCount
FROM FilteredAccount
WHERE name = @CustomerName
  AND address1_postalcode = @ZIP
  AND customertypecode = 3  -- Customer type
GROUP BY accountid
HAVING COUNT(*) = 1  -- Only proceed if exactly 1 match

Connection and User Context¶

SmartConnect doesn't have Scribe's concept of multiple named connections to the same system:

Solution: Use SmartConnect's "Run as" feature or CRM Web API with different authentication tokens
Critical for Step 5: Ensure the update operation uses credentials that trigger the address cascade plugin
Document which CRM user accounts are used and their plugin trigger configurations

Cross-Reference Table¶

Scribe's built-in cross-reference table must be replicated:

-- Example cross-reference table structure
CREATE TABLE GPtoCRM_CrossReference (
    SourceSystem VARCHAR(50),
    SourceEntity VARCHAR(50),
    SourceKey VARCHAR(100),
    SourceName VARCHAR(200),
    TargetSystem VARCHAR(50),
    TargetEntity VARCHAR(50),
    TargetKey UNIQUEIDENTIFIER,
    TargetName VARCHAR(200),
    CreatedDate DATETIME,
    ModifiedDate DATETIME,
    CONSTRAINT PK_CrossRef PRIMARY KEY (SourceSystem, SourceEntity, SourceKey)
);

-- Index for quick lookups
CREATE INDEX IX_CrossRef_Source ON GPtoCRM_CrossReference (SourceKey);
CREATE INDEX IX_CrossRef_Target ON GPtoCRM_CrossReference (TargetKey);

XRef.ini File Translations¶

The XRef.ini file contains multiple cross-reference sections used by FILELOOKUP:

Account_Category - Maps GP customer class to CRM account category option values
Payment_Terms - Maps GP payment terms to CRM payment terms option values
Address_Code - Maps GP address codes to CRM address type option values
Shipping_Method - Maps GP shipping methods to CRM shipping method option values

SmartConnect Implementation Options:

Load XRef.ini into SQL lookup tables during initialization
Use SmartConnect's built-in lookup/translation tables feature
Implement as C# dictionary objects in script

Phone Number Formatting¶

The PHONEFROMGP function has specific formatting logic that must be replicated:

// Example C# implementation of PHONEFROMGP
public string FormatPhoneFromGP(string rawPhone, string defaultAreaCode,
                                string mainMask, string extensionMask)
{
    if (string.IsNullOrWhiteSpace(rawPhone))
        return string.Empty;

    // Strip all non-numeric characters
    string digits = new string(rawPhone.Where(char.IsDigit).ToArray());

    if (digits.Length == 0)
        return string.Empty;

    // Prepend area code if less than 10 digits
    if (digits.Length < 10)
    {
        digits = defaultAreaCode + digits;
    }

    // Apply formatting masks
    // mainMask: "###-###-####"
    // extensionMask: "x####"

    string formatted = FormatWithMask(digits, mainMask, extensionMask);
    return formatted;
}

Error Handling and Logging¶

SmartConnect's error handling differs from Scribe:

ENDJOBFAILEDMSG: Map to SmartConnect's exception throwing with custom error messages
Row-level failures: Configure SmartConnect's error handling to write failures to error tables
Step success actions: Implement using SmartConnect's map execution order and conditional logic
Logging: Enable SmartConnect's detailed logging for all CRM operations to match Scribe's audit trail

Testing Strategy¶

Due to the complexity of this integration, implement thorough testing:

Test Cross-Reference Lookup Path (Step 6)
Create test customers with existing cross-references
Verify update operations succeed
Confirm all fields are updated correctly
Test Soft-Key Matching Paths (Steps 1-5)
Create CRM accounts that match GP customers by name + ZIP
Verify single match scenario updates and creates cross-reference
Test multiple match scenarios fail with appropriate errors
Verify NULL ZIP fallback logic works correctly
Test Insert Path (Step 7)
Create new GP customers with no CRM counterpart
Verify account creation with all fields
Confirm cross-reference creation
Test inactive customer validation fails the insert
Test Address Cascade (Step 5)
Verify soft-key match update triggers address cascade
Confirm cascade creates/updates customeraddress records
Verify AddressToERP integration picks up the changes
Confirm bidirectional sync completes successfully

Performance Considerations¶

This integration processes potentially large customer lists:

Batch Size: Configure SmartConnect batch sizes based on CRM performance
Cached Lookups: The DBLOOKUPCached function suggests performance optimization for repeated lookups
Implement lookup caching in SmartConnect using in-memory dictionaries
Cache price level and system user lookups that repeat across multiple customers
Soft-Key Seeks: Steps 1-4 perform CRM queries on every row without a cross-reference
Consider pre-loading CRM accounts into staging table for faster matching
Index the staging table on name and postal code fields
Parallel Processing: If SmartConnect supports it, consider parallel map execution for independent customer records

Required XRef.ini Sections¶

Ensure the following sections exist in XRef.ini with appropriate mappings:

[Account_Category]
; Maps GP CUSTCLAS to CRM accountcategorycode option values
AAAA-DEFAULT=1
; Add all GP customer classes here

[Payment_Terms]
; Maps GP PYMTRMID to CRM paymenttermscode option values
NET 30 DAYS=1
; Add all GP payment terms here

[Address_Code]
; Maps GP ADRSCODE to CRM address1_addresstypecode option values
WAREHOUSE=3
PRIMARY=1
BILLING=2
SHIPPING=4
; Add all GP address codes here

[Shipping_Method]
; Maps GP SHIPMTHD to CRM address1_shippingmethodcode option values
UPS GROUND=1
; Add all GP shipping methods here

8. Integration Dependencies¶

This integration has dependencies on other integrations in the GP ↔ CRM synchronization suite:

Upstream Dependencies¶

None - This integration is typically triggered directly from GP or on a schedule

Downstream Dependencies¶

AddressToERP - When Step 5 updates a matched account using the Non-INTEGRATION connection, the address cascade triggers this integration to sync addresses back to GP
AddressToCRM - After customer creation/update, separate address records may be synchronized via this integration

CRM Plugin Configuration¶

The Non-INTEGRATION user must be configured in CRM to trigger the address cascade plugin
Verify that the address cascade plugin is enabled for the account entity
Document the plugin's registered steps and execution order

GP Integration Extensions¶

The source data includes fields like S106 (email) and S107 (website) which may come from GP custom fields or third-party extensions
Document any GP customizations that provide these extended fields