Scribe Integration Deep Dive: Product to CRM

Integration Overview

• Name: Product to CRM (ProductToCRM.dts)
• Purpose: Sends new/updated product (item/inventory) data from Dynamics GP to Microsoft CRM 2011, synchronizing product master records and price levels
• Source System: Microsoft Dynamics GP (Item Master via XML ResultsSet)
• Target System: Microsoft CRM 2011 (product, productpricelevel entities)
• Direction: GP → CRM

graph TD
    Start[GP Item Data] --> CheckOp{Operation<br/>Type?}

    CheckOp -->|Delete| Step1[Step 1: Delete<br/>Product]
    CheckOp -->|Insert/Update| CheckXRef{Cross-Reference<br/>Exists?}

    Step1 --> StopDelete[Stop Processing]

    CheckXRef -->|Yes - Already Shared| Step2[Step 2: Update<br/>Existing Product]
    CheckXRef -->|No - Not Shared| Step3[Step 3: Insert<br/>New Product]

    Step2 --> UpdateResult{Update<br/>Result?}
    UpdateResult -->|Success| Step4[Step 4: Update/Insert<br/>ProductPriceLevel]
    UpdateResult -->|Not Found| Step3Insert[Fallback to<br/>Step 3 Insert]

    Step3 --> InsertResult{Insert<br/>Success?}
    Step3Insert --> InsertResult
    InsertResult -->|Success| Step4

    Step4 --> Success[Sync Complete]
    StopDelete --> End[Job Complete]

Key Processing Steps

This integration uses a 4-step workflow with delete handling and cross-reference logic:

1. Step 1 - product(3) (Delete): Pre-operation checks if operation is "Delete". If so, delete product from CRM and stop processing.

2. Pre-Op Formula: IF(S95 = "3", TRUE, GOTOSTEP(2)) - if delete operation, process; else skip to Step 2

3. Step 2 - Product (Update): Pre-operation checks cross-reference. If product already shared, update it. If not found during update, processing falls back to Step 3 insert.

4. Pre-Op Formula: IF(ISERROR(LookupProductID), GOTOSTEP(3), TRUE) - if no cross-reference, skip to Step 3
5. Success (1 match): Product updated → continue to Step 4

6. Success (0 matches): Product not found → goto Step 4 (which includes insert logic)

7. Step 3 - Product(2) (Insert): Insert new product into CRM when no cross-reference exists or update failed.

8. Step 4 - ProductPriceLevel (Update/Insert): Update or insert product price level record. The first price level automatically becomes the default list price.

User Variables

Variable Name	Formula	Purpose
LookupPriceLevelID	DBLOOKUPCached(S91, "Microsoft CRM 2011 Adapter", "pricelevel", "name", "pricelevelid")	Looks up CRM Price Level GUID by name using cached database lookup. Used in Step 4 to link product to correct price level. Cached version improves performance for repeated lookups.
LookupUoMSchedID	DBLOOKUPCached(S40, "Microsoft CRM 2011 Adapter", "uomschedule", "name", "uomscheduleid")	Looks up Unit of Measure Schedule GUID by name using cached lookup. Required for linking product to correct UoM schedule in CRM.
LookupUoMID	DBLOOKUP2(S92, LookupUoMSchedID, "Microsoft CRM 2011 Adapter", "uom", "name", "uomscheduleid", "uomid")	Performs two-key lookup to find Unit of Measure GUID. Requires both UoM name (S92) and UoM Schedule ID (from LookupUoMSchedID). Ensures correct UoM is selected from potentially multiple with same name.
LookupProductID	DBLOOKUP(S3, "Microsoft CRM 2011 Adapter", "product", "productnumber", "productid")	Cross-reference lookup variable maintained by key cross-reference feature. Looks up CRM product GUID by GP item number. Returns #NOTFOUND! if product not yet shared.

Target Variables

Variable Name	Data Object	Data Field	Purpose
ProductID	Product	productid	Captures product GUID from successful update (Step 2) or insert (Step 3). Used in Step 4 to link ProductPriceLevel record to correct product.

Field Mappings and Transformations

Step 1: Delete Product

Target Field	Source Field(s)	Transformation Formula	Explanation
productid	Variable	LookupProductID	Product GUID from cross-reference used to target deletion

Step 2: Update Existing Product

Target Field	Source Field(s)	Transformation Formula	Explanation
productid	Variable	LookupProductID	Product GUID from cross-reference used to target update
productnumber	S3 (ITEMNMBR)	S3	GP item number (SKU)
name	S4 (ITEMDESC)	LEFT(S4, 100)	Product description truncated to 100 characters
description	S5 (ITMSHNAM)	S5	Short item name for additional description
productstructure	Constant	1	Product structure type: 1 = Product (vs BOM/Kit)
defaultuomscheduleid	Variable	LookupUoMSchedID	Unit of Measure Schedule GUID
defaultuomid	Variable	LookupUoMID	Default Unit of Measure GUID
quantitydecimal	S6 (DECPLQTY)	S6	Decimal precision for quantity
stockvolume	S7 (ITEMSHWT)	S7	Shipping weight
stockweight	S7 (ITEMSHWT)	S7	Same as stockvolume for weight tracking
pricelevelid	Variable	LookupPriceLevelID	Price level GUID for product pricing

Step 3: Insert New Product

Target Field	Source Field(s)	Transformation Formula	Explanation
productnumber	S3 (ITEMNMBR)	S3	GP item number
name	S4 (ITEMDESC)	LEFT(S4, 100)	Product description
description	S5 (ITMSHNAM)	S5	Short name
productstructure	Constant	1	Product structure type
defaultuomscheduleid	Variable	LookupUoMSchedID	UoM Schedule GUID
defaultuomid	Variable	LookupUoMID	Default UoM GUID
quantitydecimal	S6 (DECPLQTY)	S6	Quantity decimal precision
stockvolume	S7 (ITEMSHWT)	S7	Shipping weight
stockweight	S7 (ITEMSHWT)	S7	Weight tracking
pricelevelid	Variable	LookupPriceLevelID	Price level GUID

Step 4: Update/Insert ProductPriceLevel

Target Field	Source Field(s)	Transformation Formula	Explanation
productid	Variable	ProductID	Product GUID from Step 2/3 operation
pricelevelid	Variable	LookupPriceLevelID	Price level GUID
uomid	Variable	LookupUoMID	Unit of Measure GUID
amount	S8 (LISTPRCE)	S8	List price amount from GP

Formula Reference

DBLOOKUPCached

Description: Performs a single-key database lookup with in-memory caching for improved performance on repeated lookups.

Parameters: - Param 1: Key value to search for - Param 2: Connection name (e.g., "Microsoft CRM 2011 Adapter") - Param 3: Entity/table name (e.g., "pricelevel") - Param 4: Key field name (e.g., "name") - Param 5: Return field name (e.g., "pricelevelid")

Returns: Value of return field if found, otherwise #NOTFOUND!. Caches results in memory for subsequent calls with same key value.

Usage: DBLOOKUPCached(S91, "Microsoft CRM 2011 Adapter", "pricelevel", "name", "pricelevelid") looks up price level with caching

DBLOOKUP2

Description: Performs a two-key database lookup requiring both keys to match.

Parameters: - Param 1: First key value (e.g., UoM name) - Param 2: Second key value (e.g., UoM Schedule ID) - Param 3: Connection name - Param 4: Entity/table name (e.g., "uom") - Param 5: First key field name (e.g., "name") - Param 6: Second key field name (e.g., "uomscheduleid") - Param 7: Return field name (e.g., "uomid")

Returns: Value of return field if exactly one record matches both keys, otherwise #NOTFOUND!.

Usage: DBLOOKUP2(S92, LookupUoMSchedID, "Microsoft CRM 2011 Adapter", "uom", "name", "uomscheduleid", "uomid") finds specific UoM within schedule

DBLOOKUP

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

Parameters: - Param 1: Key value to search for - Param 2: Connection name - Param 3: Entity/table name - Param 4: Key field name - Param 5: Return field name

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

Usage: DBLOOKUP(S3, "Microsoft CRM 2011 Adapter", "product", "productnumber", "productid") looks up product GUID

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(S4, 100) truncates product description to 100 characters

ISERROR

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

Parameters: - Param 1: Expression to evaluate

Returns: TRUE if expression is error state, otherwise FALSE.

Usage: ISERROR(LookupProductID) returns TRUE if product not in cross-reference

GOTOSTEP

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

Parameters: - Param 1: Step number to jump to

Returns: Special control value like #GOTOSTEP2! or #GOTOSTEP3!.

Usage: IF(S95 = "3", TRUE, GOTOSTEP(2)) routes delete operations to Step 1, others to Step 2

IF

Description: Conditional evaluation.

Parameters: - Param 1: Boolean condition - Param 2: Value if TRUE - Param 3: Value if FALSE

Returns: Value based on condition.

Usage: IF(ISERROR(LookupProductID), GOTOSTEP(3), TRUE) controls update vs insert routing

Source and Target Connection Details

Source Connection

• Type: XML File Source
• Entity: ResultsSet
• Schema: FromERP_Product.xml/xsd
• Key Fields: ScribeObject, ITEMNMBR
• Name Fields: ITEMDESC
• Query: ResultsSet query extracting item data from GP

Target Connection

• Microsoft CRM 2011 Adapter:
• product entity (Steps 1, 2, 3)
• productpricelevel entity (Step 4)

Cross-Reference Configuration

• Source Key: ScribeObjectKey (ResultsSet identifier)
• Target Key: productid (CRM product GUID)
• Lookup Variable: LookupProductID
• Source Name: ITEMDESC
• Target Name: productnumber, name
• Purpose: Tracks which GP items are synchronized to CRM products

Business Rules and Data Quality Considerations

Delete Operation Handling

• Operation Field: S95 contains operation code
• Value "3": Delete operation
• Other values: Insert/Update operation
• Delete Logic: Step 1 processes deletes, then stops (doesn't continue to other steps)
• Delete Requirement: Product must exist in cross-reference to be deleted

Update vs Insert Routing

• Primary Path (Update): If LookupProductID has value, product exists → Step 2 update
• Fallback Path (Insert): If LookupProductID is #NOTFOUND!, or update finds 0 matches → Step 3 insert
• Automatic Fallback: Step 2 success with 0 matches automatically proceeds to Step 4, which will handle as insert

Unit of Measure Complexity

CRM requires two-level UoM structure: 1. UoM Schedule: Group of related units (e.g., "Weight", "Volume", "Count") 2. UoM: Specific unit within schedule (e.g., "Pound", "Ounce" in "Weight" schedule)

Lookup Sequence: 1. LookupUoMSchedID finds schedule by name 2. LookupUoMID finds specific UoM using both name AND schedule ID 3. This prevents confusion when multiple schedules have same unit name (e.g., "Each")

Price Level Management

• Default Price Level: First ProductPriceLevel record becomes default
• Step 4 Operation: Update/Insert (UI) allows idempotent pricing updates
• Price Level Lookup: Uses cached lookup for performance (same price level used by many products)

Field Truncation

• Product name: 100 characters (CRM limit)
• Other text fields validated against GP and CRM limits

SmartConnect Re-implementation Notes

Multi-Step Workflow Implementation

Option 1: Separate Maps by Operation - Map 1: Product Delete (conditional on S95 = "3") - Map 2: Product Update/Insert with pricing (conditional on S95 != "3")

Option 2: Single Map with C# Scripting

// Pre-map script for operation routing
string operation = GetSourceFieldValue("S95");

if (operation == "3")
{
    // Delete operation
    DeleteProduct(itemNumber);
    SkipRemainingSteps();
    return;
}

// Update/Insert logic
Guid? productId = LookupProductInCrossReference(itemNumber);

if (productId.HasValue)
{
    // Product exists - update
    UpdateProduct(productId.Value, productData);
}
else
{
    // New product - insert
    productId = InsertProduct(productData);
}

// Always update/insert price level
UpsertProductPriceLevel(productId.Value, priceData);

Unit of Measure Lookup Implementation

Two-Step UoM Resolution:

public Guid? LookupUnitOfMeasure(string uomName, string uomScheduleName)
{
    // Step 1: Find UoM Schedule
    Guid? scheduleId = CachedLookup("uomschedule", "name", uomScheduleName, "uomscheduleid");

    if (!scheduleId.HasValue)
    {
        LogError($"UoM Schedule '{uomScheduleName}' not found");
        return null;
    }

    // Step 2: Find UoM within schedule
    string sql = @"
        SELECT uomid
        FROM UoM
        WHERE name = @UoMName
          AND uomscheduleid = @ScheduleId";

    Guid? uomId = ExecuteScalar<Guid>(sql,
        new { UoMName = uomName, ScheduleId = scheduleId.Value });

    if (!uomId.HasValue)
    {
        LogError($"UoM '{uomName}' not found in schedule '{uomScheduleName}'");
    }

    return uomId;
}

Cached Lookup Implementation

C# In-Memory Cache:

private static Dictionary<string, Guid> priceLevelCache = new Dictionary<string, Guid>();

public Guid? LookupPriceLevelCached(string priceLevelName)
{
    // Check cache first
    if (priceLevelCache.ContainsKey(priceLevelName))
    {
        return priceLevelCache[priceLevelName];
    }

    // Not in cache - query CRM
    string sql = "SELECT pricelevelid FROM PriceLevel WHERE name = @Name";
    Guid? pricelevelId = ExecuteScalar<Guid>(sql, new { Name = priceLevelName });

    // Store in cache
    if (pricelevelId.HasValue)
    {
        priceLevelCache[priceLevelName] = pricelevelId.Value;
    }

    return pricelevelId;
}

Price Level Update/Insert

SmartConnect Approach:

public void UpsertProductPriceLevel(Guid productId, Guid priceLevelId, Guid uomId, decimal amount)
{
    // Check if price level record exists
    string checkSql = @"
        SELECT productpricelevelid
        FROM ProductPriceLevel
        WHERE productid = @ProductId
          AND pricelevelid = @PriceLevelId
          AND uomid = @UoMId";

    Guid? existing = ExecuteScalar<Guid>(checkSql,
        new { ProductId = productId, PriceLevelId = priceLevelId, UoMId = uomId });

    if (existing.HasValue)
    {
        // Update existing price
        UpdateRecord("productpricelevel", existing.Value, new { amount = amount });
    }
    else
    {
        // Insert new price level
        InsertRecord("productpricelevel", new
        {
            productid = productId,
            pricelevelid = priceLevelId,
            uomid = uomId,
            amount = amount
        });
    }
}

Error Handling

SmartConnect Error Scenarios:

// Scenario 1: UoM Schedule not found
if (!uomScheduleId.HasValue)
{
    LogError("UoM Schedule not found", itemNumber, uomScheduleName);
    SetRecordStatus("FAILED");
    return;
}

// Scenario 2: UoM not found in schedule
if (!uomId.HasValue)
{
    LogError("UoM not found in schedule", itemNumber, $"{uomName} in {uomScheduleName}");
    SetRecordStatus("FAILED");
    return;
}

// Scenario 3: Price Level not found
if (!priceLevelId.HasValue)
{
    LogWarning("Price Level not found - using default", itemNumber, priceLevelName);
    priceLevelId = GetDefaultPriceLevelId();
}

Testing Strategy

Test Scenario 1: New Product Insert 1. Create GP item not in CRM (no cross-reference) 2. Run integration 3. Verify: Step 2 pre-op jumps to Step 3, product inserted, ProductPriceLevel created, cross-reference created

Test Scenario 2: Existing Product Update 1. Modify GP item already in CRM (cross-reference exists) 2. Run integration 3. Verify: Step 2 updates product, Step 4 updates ProductPriceLevel

Test Scenario 3: Delete Operation 1. Set operation field S95 = "3" for item 2. Run integration 3. Verify: Step 1 deletes product from CRM, processing stops, cross-reference removed

Test Scenario 4: UoM Resolution 1. Create product with UoM "Each" in schedule "Count" 2. Verify both schedule and UoM GUIDs resolved correctly 3. Confirm product created with correct default UoM

Test Scenario 5: Price Level Update 1. Update list price for existing product in GP 2. Run integration 3. Verify: ProductPriceLevel record updated with new amount

Performance Considerations

• Cached Lookups: Use DBLOOKUPCached pattern for price levels and UoM schedules (high reuse)
• Standard Lookups: Use DBLOOKUP for product-specific lookups (low reuse)
• Batch Processing: Process products in batches of 100-500
• Price Level Optimization: Cache all price level GUIDs at map start
• UoM Schedule Caching: Pre-load all UoM schedules and UoMs into memory dictionary

Integration Dependencies

Upstream Dependencies

• UofMToCRM.dts: Should run first to create UoM schedules and units in CRM

Downstream Dependencies

• SalesOrderToCRM.dts: Requires products to exist before order lines can reference them
• InvoicePostedToCRM.dts: Requires products for invoice line items

CRM Prerequisites

• Price Level records must exist (e.g., "Default Price Level", "Retail", "Wholesale")
• UoM Schedule and UoM records must exist
• Product entity must be enabled

GP Customizations

• Source query must extract: ITEMNMBR, ITEMDESC, ITMSHNAM, DECPLQTY, ITEMSHWT, LISTPRCE
• Operation field (S95) must indicate insert/update/delete