Skip to content

Scribe Integration Deep Dive: Address to CRM

This document provides a detailed analysis of the AddressToCRM.dts integration, designed to synchronize address 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 every address insert or update into the SCRIBESHADOW change log. The MSGP Publisher checks that log every second. Entries marked New (N) or Modified (M) create a FromERP_Address message for this DTS. Once the DTS completes successfully, the same publisher marks the log row as Synced (S).

  • Linking Table: Uses KEYCROSSREFERENCETWOWAY to track which GP addresses (customer number + address code combination) have been synced to which CRM address IDs. This prevents creating duplicate addresses every time something changes.

  • Parent Dependency: Before creating a new address in CRM, the integration checks if the parent customer account exists. If not, it skips the address and retries later—addresses can't exist without their parent customer.

  • Smart Contact Creation: When creating a new address, the integration also creates a contact person if the address has a contact name. However, it skips generic names like "Accounting" or "Service Dept" (configured in XRef.ini skip list) to avoid cluttering CRM with non-person contacts.

1. Integration Overview

  • Name: AddressToCRM.dts
  • Purpose: To create and update address records in CRM based on data from GP. It also handles the creation of associated contacts if they don't already exist.
  • How we detect changes: The MSGP Publisher watches GP's SCRIBESHADOW table every second. Address rows flagged N (new) or M (modified) produce a FromERP_Address message. After the DTS succeeds, that same publisher updates SYNCSTATUS1 to S so the change is not sent again.
  • Source: The DTS subscribes to the FromERP_Address queue and consumes XML payloads shaped by the publisher, conforming to the FromERP_Address.xsd schema.
  • Target: Microsoft Dynamics CRM 2011, specifically the customeraddress (Address), account, and contact entities.

Trigger and Message Source

SCRIBESHADOW acts as the change log for Dynamics GP. The triggers defined in DocsAndScripts/CRMGP_TemplateConfig.sql insert entries with status N whenever a customer address is created and switch the flag to M on updates. The MSGP Publisher (see publishers-bridges-connections.spkg) polls this log once per second (frequency = 1) and publishes a FromERP_Address message for each row that still shows N or M. After this DTS successfully processes the message, the publisher runs its SourceUpdate statement to change SYNCSTATUS1 to S. Failed rows remain N/M, which keeps them eligible for the next polling cycle.

Data Flow and Logic

The integration follows a multi-step process with conditional logic to handle both new and existing records gracefully.

graph TD
    A[GP Address Change Detected] --> B{Has this address<br/>been synced before?};
    B -->|Yes - Found in<br/>linking table| C[Step 1: Update<br/>CRM Address];
    B -->|No - Not found<br/>in linking table| D{Does parent customer<br/>exist in CRM?};
    D -->|No| E[Skip: Wait for customer<br/>to sync first];
    D -->|Yes| F[Step 3: Create<br/>CRM Address];
    F --> G{Is contact name<br/>in skip list?};
    G -->|Yes - Generic name<br/>like 'Accounting'| H[Sync Complete];
    G -->|No - Real person| I{Does contact<br/>already exist?};
    I -->|Yes| H;
    I -->|No| J[Step 5: Create<br/>CRM Contact];
    J --> H;
    C --> H;

Key Processing Steps:

  1. Update Existing Address (Step 1): The integration first checks if the address has been migrated before by looking up its cross-reference ID.

    • If a cross-reference is found, it updates the existing customeraddress record in CRM.
    • If no cross-reference is found, it proceeds to the next step to treat it as a new record.

  2. Seek Parent Account (Step 2): For a new address, the integration looks for the parent account in CRM using the GP Customer Number (CUSTNMBR).

    • If the account is not found, the entire source row is skipped and will be retried in a future run. This prevents orphaned address records.
    • If the account is found, the process continues.

  3. Insert New Address (Step 3): A new customeraddress record is created and associated with the parent account found in the previous step.

  4. Seek/Insert Contact (Steps 4 & 5): The integration then attempts to create a contact from the address record.

    • It first checks if the contact person's name (CNTCPRSN) is listed in the [SKIP_CONTACTS] section of XRef.ini. If so, it skips contact creation for this row.
    • It then checks if a contact with the same name already exists for that account.
    • If no existing contact is found, a new contact record is created.

2. User Variables

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

Variable Name Formula Purpose
LookupAddressID DBLOOKUP2( LookupAccountId, S4, "Microsoft CRM 2011 Adapter", "customeraddress", "parentid", "name", "customeraddressid" ) Calls DBLOOKUP2 with parameters:
1. LookupAccountId – CRM account GUID found earlier in the job.
2. S4 – GP Address Code (ADRSCODE).
3. Connection name pointing at CRM.
4. Target entity/table customeraddress.
5. Match field parentid (CRM account relationship).
6. Match field name (CRM address name).
7. Return field customeraddressid.
The function returns the matching address GUID when both key fields match; otherwise it returns #NOTFOUND!, which keeps the variable empty and signals a new insert is required.
AreaCode "603" 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. No transformation occurs here; the downstream PHONEFROMGP call returns a formatted phone string for each field.
LookupAccountId DBLOOKUP(S3, "Microsoft CRM 2011 Adapter", "account", "accountnumber", "accountid" ) Calls DBLOOKUP with parameters:
1. S3 – GP Customer Number (CUSTNMBR).
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; otherwise it returns #NOTFOUND!, leaving the variable empty and causing the job to skip the source row.

3. Field Mappings and Transformations

This section details the mapping from the source XML fields to the target CRM entity fields.

Target: customeraddress (Address Entity)

Target Field Source Field(s) Transformation Formula Explanation
name S4 (ADRSCODE) S4 The Address Code from GP is directly mapped to the name of the address record in CRM.
addresstypecode S4 (ADRSCODE) FILELOOKUP( S4, "XRef.ini", "Address_Code" ) FILELOOKUP parameters:
1. Value to translate – the GP Address Code (ADRSCODE).
2. Lookup file – XRef.ini.
3. Section name – Address_Code.
The function returns the mapped CRM option label (e.g., Ship To). If no match exists it returns the original input, so keep the section up to date with every GP address code.
primarycontactname S9 (CNTCPRSN) S9 The Contact Person from GP is directly mapped.
line1, line2, line3 S10-S12 Direct map Address lines are mapped directly.
city, stateorprovince, postalcode, country S13-S16 Direct map Location fields are mapped directly.
telephone1, telephone2, telephone3, fax S17-S20 PHONEFROMGP( source, AreaCode, "###-###-####", "x####" ) PHONEFROMGP parameters:
1. Raw GP phone value (S17–S20).
2. Default area code (AreaCode, set to "603").
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.
shippingmethodcode S7 (SHIPMTHD) FILELOOKUP( S7, "XRef.ini", "Shipping_Method" ) Uses FILELOOKUP with:
1. GP shipping method code (S7).
2. Lookup file XRef.ini.
3. Section Shipping_Method.
Returns the mapped CRM shipping method value; if no entry exists the original GP code is passed through, so keep the cross-reference synchronized with GP.
upszone S6 (UPSZONE) S6 Mapped directly.

Target: contact (Contact Entity)

If a contact is created, its fields are populated as follows:

Target Field Source Field(s) Transformation Formula Explanation
firstname S9 (CNTCPRSN) PARSENAME( S9, "F" ) PARSENAME parameters:
1. Full contact name (S9).
2. Part selector "F" meaning first name.
The function splits the name on spaces and punctuation, returning the first element. If the input lacks separators, the entire value is returned.
lastname S9 (CNTCPRSN) IF( ISERROR( PARSENAME( S9, "L" ) ), #NULL!, PARSENAME( S9, "L" ) ) Nested functions:
- PARSENAME(S9, "L") attempts to return the last name component.
- ISERROR(...) evaluates to TRUE when PARSENAME cannot find a last name (for example, a single-word name).
- IF(condition, valueIfTrue, valueIfFalse) returns #NULL! when the condition is true to avoid populating CRM with bad data; otherwise it returns the parsed last name string.
middlename S9 (CNTCPRSN) PARSENAME( S9, "M" ) Uses the same PARSENAME function with part selector "M", which returns the middle portion of the name when present. If no middle value exists the function returns #NOTFOUND!, and Scribe leaves the CRM field blank.
telephone1, fax, etc. S17-S20 PHONEFROMGP(...) Same PHONEFROMGP call pattern as the address entity: raw contact phone value, default area code "603", main mask "###-###-####", extension mask "x####". Returns a formatted string or blank when no digits are supplied, ensuring parity between address and contact phone formatting.
parentcustomerid (from Step 2) AccountID The new contact is linked to the account that was found in Step 2.
vfPrimary (none) IF( ISERROR( PrimaryContactID ), "Y", #NULL! ) ISERROR(PrimaryContactID) evaluates whether the PrimaryContactID target variable contains a valid GUID from the earlier account lookup. IF then returns "Y" (flagging the new contact as primary) when the variable is in an error/empty state; otherwise it returns #NULL! so CRM leaves the field unset.

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.
  • DBLOOKUP2( value1, value2, connection, table, keyField1, keyField2, returnField )
    • Inputs: two lookup values that must simultaneously match the two specified key fields, followed by the same connection, table, and return field parameters as DBLOOKUP.
    • Returns: the requested field when both keys match a row; otherwise #NOTFOUND!.
  • 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 string, a fallback area code, an output mask for the primary number, and an optional extension mask.
    • Returns: a formatted phone string that adheres to the supplied masks, or an empty string if no digits are provided.
  • PARSENAME( fullName, partSelector )
    • Inputs: the full name string and a selector ("F", "M", "L", etc.) indicating which component to extract.
    • Returns: the requested name component when present; otherwise #NOTFOUND!.
  • ISERROR( expression )
    • Inputs: any expression or function call.
    • Returns: boolean TRUE when the expression results in an error sentinel (e.g., #NOTFOUND!), allowing conditional handling.
  • IF( condition, trueValue, falseValue )
    • Inputs: a logical condition, the value to return when it evaluates to TRUE, and the value to return when it evaluates to FALSE.
    • Returns: one of the two supplied values without additional conversion.
  • #NULL! literal
    • Used inside formulas to signal Scribe to write a database NULL instead of a literal string.

4. Source and Target Details

Source Data Provider

  • Type: XML Adapter
  • File: FromERP_Address.xml (dynamic)
  • Schema: FromERP_Address.xsd
  • Root Element: The integration processes rows found under the ResultsSet/Row path in the source XML.

Target Data Provider

  • Type: Microsoft CRM 2011 Adapter
  • Server URL: http://crm15/
  • Organization: BioCat
  • Authentication: Active Directory, running as the INTEGRATION user.

5. Notes for Re-implementation in SmartConnect

  • Cross-Reference Lookups: The use of DBLOOKUP and DBLOOKUP2 for finding existing records is fundamental. SmartConnect's lookup capabilities should be used to replicate this. The logic is:
    1. Find CRM Account using GP CUSTNMBR.
    2. Find CRM Address using the found Account ID and GP ADRSCODE.
  • File Lookups: The FILELOOKUP function relies on the XRef.ini file. This can be replaced in SmartConnect by using translation tables or lookup columns to map values like Address_Code and Shipping_Method.
  • Custom Functions:
    • PHONEFROMGP: This logic will need to be recreated. It appears to be a standard phone number formatting function that applies a default area code if needed. This can likely be replicated with standard string manipulation and conditional logic in SmartConnect.
    • PARSENAME: This function for splitting a full name into parts (first, last, middle) is common in integration tools and should have an equivalent in SmartConnect.
  • Conditional Logic: The IF statements and step control functions (GOTOSTEP, SKIPSTEP, NEXTROW) are critical. The entire workflow in SmartConnect should be built around this conditional processing to ensure data integrity and prevent errors.
  • Contact Skip Logic: The check against the [Skip_Contacts] list in XRef.ini is a business rule to prevent creating contacts for generic department names (e.g., "Accounting Dept"). This should be implemented as a condition before the contact creation step in SmartConnect.