Sales Order to CRM Integration¶

Integration Overview¶

This integration synchronizes non-transferred sales order data from Microsoft Dynamics GP to Microsoft Dynamics CRM 2011. It creates or updates sales order records in CRM with complete header information and line item details, providing sales visibility for orders that have not yet been transferred to fulfillment.

Purpose¶

Synchronize active (non-transferred) sales orders from GP to CRM
Create sales order headers with billing and shipping addresses
Populate sales order line items with product, pricing, quantity, and delivery details
Support sales rep assignment and territory management
Maintain security permissions for sales order access
Exclude transferred orders (handled by separate SalesOrderTransferredToCRM integration)

Data Flow Direction¶

GP (ERP) → CRM

flowchart TD
    Start([GP Sales Order<br/>Not Transferred]) --> Lookup[Step 1: Lookup Account]
    Lookup -->|Not Found| Error1[End Job - Account Not Found<br/>Retry Queue]
    Lookup -->|Found| XRef{Cross-Reference<br/>Check}
    XRef -->|Exists| Update[Step 2: Update SalesOrder]
    XRef -->|Not Exists| Insert[Step 2: Insert SalesOrder]
    Update --> DeleteDetails[Step 3: Delete Old SalesOrderDetail Records]
    Insert --> DeleteDetails
    DeleteDetails --> InsertDetails[Step 4: Insert New SalesOrderDetail Records<br/>Skip If Repeated]
    InsertDetails --> Success([Success<br/>Order Synchronized])
    Error1 --> End([End])
    Success --> End

Key Processing Steps¶

Account Lookup: Seeks the CRM account using GP customer number via cross-reference
If account not found: Job ends with error, message goes to retry queue
If account found: Continue to Step 2
Sales Order Header Update/Insert: Check cross-reference for existing order
If order exists in CRM: Update header with latest GP data
If order doesn't exist: Insert new order record
Populate billing and shipping addresses, totals, dates, requested delivery dates, and payment terms
Delete Old Sales Order Details: Remove all existing SalesOrderDetail records for this order
Ensures line items are refreshed completely
Prevents orphaned detail records from previous versions
Insert New Sales Order Details: Add all current order line items
Uses "Skip If Repeated" pattern to handle multiple detail rows
Populates product, quantity, pricing, UoM, requested delivery dates, and shipping information
Links to salesperson via GP salesperson ID
Sets security permissions based on account owner

User Variables¶

Variable Name	Formula	Purpose
`LookupOrderID`	`DBLOOKUP( S34, "Microsoft CRM 2011 Adapter", "SalesOrder", "Name", "SalesOrderID" )`	Looks up existing sales order in CRM by order number (SOPNUMBE from GP) to determine if this is an update or insert operation
`ReqShipDate_UTC`	`IF( YEAR( S44 ) = 1900, #NULL!, LOCAL2UTC( CONVERTDATETIME( S44, "12:00 AM" ) ) )`	Converts GP requested ship date to UTC timezone; if YEAR=1900 (null date in GP), returns null
`dtl_ReqShipDate_UTC`	`IF( YEAR( S207 ) = 1900, #NULL!, LOCAL2UTC( CONVERTDATETIME( S207, "12:00 AM" ) ) )`	Converts detail line requested ship date to UTC; handles 1900 null dates
`AreaCode`	`"000"`	Default area code for phone number formatting (placeholder value, likely overridden at runtime)
`LookupUoMSchedID`	`DBLOOKUPCached( S192, "Microsoft CRM 2011 Adapter", "UoMSchedule", "Name", "UoMScheduleID" )`	Finds UoM Schedule ID for the product's unit of measure schedule; uses cached lookup for performance
`NonInvProdDesc`	`IF( S196 > 0, S195, "###Product Lookup Error###" )`	Returns product description for non-inventoried items (when S196 NONINVEN flag > 0), or error message if product lookup fails
`LookupProductID`	`DBLOOKUP( S194, "Microsoft CRM 2011 Adapter", "Product", "ProductNumber", "ProductID" )`	Finds CRM Product ID using GP item number to link order detail lines to product master
`LookupUoMID`	`DBLOOKUP2( S198, LookupUoMSchedID, "Microsoft CRM 2011 Adapter", "UoM", "Name", "UoMScheduleID", "UoMID" )`	Finds specific UoM ID within the schedule using two-key lookup (UoM name + schedule ID) to prevent ambiguity
`LookupOrderStatus`	`FILELOOKUP( S103, "XRef.ini", "Order_Status" )`	Translates GP order status code to CRM status code option value via XRef.ini

Target Variables¶

Variable Name	Captured From	Purpose
`AccountOwnerID`	Step 1: Account seek Field: `ownerid`	Captures the owner (user or team) ID of the account to set security permissions on the sales order
`AccountOwnerType`	Step 1: Account seek Field: `owneridtype`	Captures the type of owner (8=systemuser, 9=team) for proper security permission assignment
`Address1_AddressID`	Step 1: Account seek Field: `address1_addressid`	Captures the primary address ID from the account for potential address linking

Field Mappings¶

Step 1: Account Lookup¶

Target Field	Source Field(s)	Formula	Description
`accountnumber`	S3 (CUSTNMBR)	`S3`	GP customer number used to seek the account; if not found, job ends with error

Step Configuration: - Operation: Seek (S) - Break Group: SOPNUMBE <ResultsSet.Row> (processes one order at a time) - Success 0 (Not Found): End job with error "Account not found (retry next time)", rollback transaction, report error - Success 1 (Found): Continue to next step - Success 2 (Multiple Matches): End job with error "Multiple Account matches found! (this should never happen)", rollback transaction

Step 2: Sales Order Header Update/Insert¶

Target Field	Source Field(s)	Formula	Description
`name`	S34 (SOPNUMBE)	`S34`	Sales order number from GP (e.g., "SO12345")
`description`	S35 (CUSTNAME)	`S35`	Customer name from GP for order description
`customerid`	AccountID from Step 1	`#LASTID!`	Links sales order to the account found in Step 1
`ordernumber`	S34 (SOPNUMBE)	`S34`	Order number (same as name field)
`datedelivered`	S43 (DOCDATE)	`S43`	Document date from GP (order entry date)
`datefulfilled`	S50 (INVODATE)	`S50`	Invoice date from GP (when order fulfilled)
`requestdeliveryby`	ReqShipDate_UTC	`DATEADD( "d", 1, ReqShipDate_UTC )`	Requested delivery date: adds 1 day to requested ship date, converted to UTC
`billto_line1`	S226 (bill_ADDRESS1)	`S226`	Billing address line 1
`billto_line2`	S227 (bill_ADDRESS2)	`S227`	Billing address line 2
`billto_line3`	S228 (bill_ADDRESS3)	`S228`	Billing address line 3
`billto_city`	S229 (bill_CITY)	`S229`	Billing city
`billto_stateorprovince`	S230 (bill_STATE)	`S230`	Billing state
`billto_postalcode`	S231 (bill_ZIPCODE)	`S231`	Billing postal code
`billto_country`	S231 (bill_COUNTRY)	`S231`	Billing country
`billto_fax`	S235 (bill_FAX) AreaCode	`PHONEFROMGP( S235, AreaCode, "###-###-####", "x####" )`	Formats billing fax number from GP format to CRM standard
`billto_name`	S232 (bill_ADRSCODE)	`S232`	Billing address code from GP
`billto_telephone`	S234 (bill_PHONE1) AreaCode	`PHONEFROMGP( S234, AreaCode, "###-###-####", "x####" )`	Formats billing phone number from GP format to CRM standard
`shipto_line1`	S64 (ADDRESS1)	`S64`	Shipping address line 1
`shipto_line2`	S65 (ADDRESS2)	`S65`	Shipping address line 2
`shipto_line3`	S66 (ADDRESS3)	`S66`	Shipping address line 3
`shipto_city`	S67 (CITY)	`S67`	Shipping city
`shipto_stateorprovince`	S68 (STATE)	`S68`	Shipping state
`shipto_postalcode`	S69 (ZIPCODE)	`S69`	Shipping postal code
`shipto_country`	S71 (COUNTRY)	`S71`	Shipping country
`shipto_telephone`	S72 (PHONE1) AreaCode	`PHONEFROMGP( S72, AreaCode, "###-###-####", "x####" )`	Formats shipping phone number from GP format to CRM standard
`shipto_fax`	S75 (FAX) AreaCode	`PHONEFROMGP( S75, AreaCode, "###-###-####", "x####" )`	Formats shipping fax number from GP format to CRM standard
`shipto_name`	S61 (SHIPMTHD)	`S61`	Shipping method from GP
`discountamount`	S84 (DISTKNAM)	`S84`	Discount amount applied to order
`freightamount`	S93 (FRTAMNT)	`S93`	Freight/shipping charges for order
`pricelevelid`	S47 (PRCLEVEL)	`DBLOOKUP( S47, "Microsoft CRM 2011 Adapter", "PriceLevel", "Name", "PriceLevelID" )`	Looks up CRM price level using GP price level name
`shippingmethodcode`	S83 (SHIPMTHD)	`FILELOOKUP( S83, "XRef.ini", "Shipping_Method" )`	Translates GP shipping method to CRM option value via XRef.ini
`paymenttermscode`	S46 (PYMTRMID)	`FILELOOKUP( S46, "XRef.ini", "Payment_Terms" )`	Translates GP payment terms code to CRM option value via XRef.ini
`totalamount`	S89 (DOCAMNT)	`S89`	Total order amount including tax and freight
`totaltax`	S90 (TAXAMNT)	`S90`	Total tax amount for order
`totallineitemamount`	S91 (SUBTOTAL)	`S91`	Subtotal of all line items before tax and freight
`statuscode`	LookupOrderStatus	`LookupOrderStatus`	Order status translated from GP via XRef.ini (e.g., New, Pending, In Progress)
`vfGrantAction`	(constant)	`1`	Security: Grant permission action (1=Grant)
`vfGranteeId`	AccountOwnerID	`AccountOwnerID`	Security: ID of user/team to grant access to
`vfGranteeType`	AccountOwnerType	`AccountOwnerType`	Security: Type of grantee (8=systemuser, 9=team)
`vfGrantRights`	(constant)	`1`	Security: Rights level to grant

Step Configuration: - Operation: Update or Insert (UI) - Cross-Reference: Order name (S34 SOPNUMBE) to determine update vs insert - Break Group: SOPNUMBE <ResultsSet.Row> (processes one order at a time) - Success 0 (Update): Continue to Step 3 - Success 1 (Insert): Continue to Step 3 - Success 2 (Multiple Matches): End job with error "Multiple SalesOrder matches found!", rollback transaction

Step 3: Delete Old Sales Order Details¶

Target Field	Source Field(s)	Formula	Description
`salesorderid`	SalesOrder ID from Step 2	`#LASTID!`	Links to order header to delete all existing detail records for this order

Step Configuration: - Operation: Delete (D) - Break Group: SOPNUMBE <ResultsSet.Row> (deletes all details for current order) - Purpose: Removes all old sales order detail records before inserting fresh data from GP - Success: All success paths (0, 1, 2) continue to Step 4 with commit

Step 4: Insert New Sales Order Details¶

Target Field	Source Field(s)	Formula	Description
`salesorderid`	SalesOrder ID from Step 2	`#LASTID!`	Links detail line to order header
`lineitemnumber`	S192 (dtlLNITMSEQ)	`S192`	Line item sequence number from GP
`productid`	S196 (dtlNONINVEN) LookupProductID	`IF( S196 > 0, #NULL!, IF( OR( ISERROR( LookupProductID ), ISERROR( LookupUoMID ) ), ENDJOBFAILEDMSG( "The ProductID and/or the UoMID were not found in CRM" ), LookupProductID ) )`	If non-inventoried item: null; otherwise lookup product or fail job if not found
`productdescription`	NonInvProdDesc	`NonInvProdDesc`	Product description for non-inventoried items or error message
`quantity`	S199 (dtlQUANTITY)	`S199`	Quantity ordered
`priceperunit`	S199 (dtlUNITPRCE)	`S199`	Unit price from GP
`baseamount`	S200 (dtlXTNDPRCE)	`S200`	Extended price (quantity × unit price)
`manualdiscountamount`	S201 (dtlMRKDNAMT)	`S201`	Manual discount amount for this line
`tax`	S202 (dtlTAXAMNT)	`S202`	Tax amount for this line item
`shipto_line1`	S214 (dtlADDRESS1)	`S214`	Line-specific shipping address line 1
`shipto_line2`	S215 (dtlADDRESS2)	`S215`	Line-specific shipping address line 2
`shipto_line3`	S216 (dtlADDRESS3)	`S216`	Line-specific shipping address line 3
`shipto_city`	S217 (dtlCITY)	`S217`	Line-specific shipping city
`shipto_stateorprovince`	S218 (dtlSTATE)	`S218`	Line-specific shipping state
`shipto_postalcode`	S221 (dtlZIPCODE)	`S221`	Line-specific shipping postal code
`shipto_country`	S222 (dtlCOUNTRY)	`S222`	Line-specific shipping country
`shipto_telephone`	S223 (dtlPHONE1) AreaCode	`PHONEFROMGP( S223, AreaCode, "###-###-####", "x####" )`	Line-specific shipping phone (formatted)
`shipto_fax`	S224 (dtlFAX) AreaCode	`PHONEFROMGP( S224, AreaCode, "###-###-####", "x####" )`	Line-specific shipping fax (formatted)
`shipto_name`	S213 (dtlSHIPMTHD)	`S213`	Line-specific shipping method
`uomid`	S196 (dtlNONINVEN) LookupUoMID	`IF( S196 > 0, #NULL!, IF( OR( ISERROR( LookupProductID ), ISERROR( LookupUoMID ) ), ENDJOBFAILEDMSG( "The ProductID and/or the UoMID were not found in CRM" ), LookupUoMID ) )`	If non-inventoried item: null; otherwise lookup UoM or fail job if not found
`ispriceoverridden`	(constant)	`1`	Flag indicating price is overridden (always true for GP orders)
`requestdeliveryby`	dtl_ReqShipDate_UTC	`DATEADD( "d", 1, dtl_ReqShipDate_UTC )`	Requested delivery date for this line: adds 1 day to detail requested ship date, converted to UTC
`salesrepid`	S134 (SALSTERR)	`DBLOOKUP( S134, "Microsoft CRM 2011 Adapter", "systemuser", "new_gpsalespersonid", "systemuserid" )`	Looks up CRM user ID using GP salesperson territory ID via custom field mapping

Step Configuration: - Operation: Insert (I) - Repeat Handling: "Skip If Repeated" - handles multiple detail rows for same order - Break Group: None (processes all detail lines) - Success: Continue processing all detail lines

Formula Reference¶

DBLOOKUP¶

DBLOOKUP( LookupValue, "Adapter", "Table", "KeyField", "ReturnField" )

Purpose: Looks up a single value in a database table using a key field.
Parameters: - LookupValue: Value to search for - "Adapter": Data connection name (e.g., "Microsoft CRM 2011 Adapter") - "Table": Target table name - "KeyField": Field to search in - "ReturnField": Field value to return

Return Value: String, GUID, or numeric value from ReturnField; #NOTFOUND! if no match; #ERROR! if multiple matches
Example: DBLOOKUP( S34, "Microsoft CRM 2011 Adapter", "SalesOrder", "Name", "SalesOrderID" )

DBLOOKUP2¶

DBLOOKUP2( LookupValue1, LookupValue2, "Adapter", "Table", "KeyField1", "KeyField2", "ReturnField" )

Purpose: Looks up a value using TWO key fields (composite key lookup).
Parameters: - LookupValue1: First key value to search for - LookupValue2: Second key value to search for - "Adapter": Data connection name - "Table": Target table name - "KeyField1": First field to search in - "KeyField2": Second field to search in - "ReturnField": Field value to return

Return Value: Value from ReturnField where both key conditions match; #NOTFOUND! if no match; #ERROR! if multiple matches
Example: DBLOOKUP2( S198, LookupUoMSchedID, "Microsoft CRM 2011 Adapter", "UoM", "Name", "UoMScheduleID", "UoMID" )
Use Case: Prevents ambiguity when same UoM name exists in multiple schedules

DBLOOKUPCached¶

DBLOOKUPCached( LookupValue, "Adapter", "Table", "KeyField", "ReturnField" )

Purpose: Performs database lookup with in-memory caching for performance.
Parameters: Same as DBLOOKUP
Return Value: Same as DBLOOKUP
Performance: First lookup queries database; subsequent lookups retrieve from cache
Example: DBLOOKUPCached( S192, "Microsoft CRM 2011 Adapter", "UoMSchedule", "Name", "UoMScheduleID" )
Best Practice: Use for frequently repeated lookups within same integration run

FILELOOKUP¶

FILELOOKUP( LookupValue, "Filename.ini", "Section" )

Purpose: Looks up a translation value in an INI file cross-reference table.
Parameters: - LookupValue: Value to translate (e.g., GP code) - "Filename.ini": INI file name (typically "XRef.ini") - "Section": Section within INI file

Return Value: Translated value (typically CRM option value); #NOTFOUND! if no match
Example: FILELOOKUP( S46, "XRef.ini", "Payment_Terms" ) translates GP "NET 30 DAYS" → CRM option value 1

PHONEFROMGP¶

PHONEFROMGP( PhoneNumber, DefaultAreaCode, "MainFormat", "ExtensionFormat" )

Purpose: Converts GP phone number format to CRM standard format.
Parameters: - PhoneNumber: GP phone field (may include extension after "EXT") - DefaultAreaCode: Area code to use if not present (e.g., "603", "000") - "MainFormat": Format mask for main number (e.g., "###-###-####") - "ExtensionFormat": Format mask for extension (e.g., "x####")

Return Value: Formatted phone string (e.g., "603-555-1234 x567")
Example: PHONEFROMGP( S234, AreaCode, "###-###-####", "x####" )

LOCAL2UTC¶

LOCAL2UTC( LocalDateTime )

Purpose: Converts local date/time to UTC timezone.
Parameters: LocalDateTime - Date/time value in local timezone
Return Value: Date/time value converted to UTC
Example: LOCAL2UTC( CONVERTDATETIME( S44, "12:00 AM" ) ) converts GP date + midnight time to UTC
Business Context: CRM stores dates in UTC; GP stores in local timezone

CONVERTDATETIME¶

CONVERTDATETIME( DateValue, "TimeString" )

Purpose: Combines a date value with a specific time.
Parameters: - DateValue: Date field from source - "TimeString": Time to apply (e.g., "12:00 AM", "11:59 PM")

Return Value: Combined date/time value
Example: CONVERTDATETIME( S44, "12:00 AM" ) sets time to midnight for date-only field
Use Case: GP date fields have no time component; this adds consistent time for CRM datetime fields

YEAR¶

YEAR( DateValue )

Purpose: Extracts the year component from a date.
Parameters: DateValue - Date field
Return Value: Integer year (e.g., 2024, 1900)
Example: YEAR( S44 ) returns year from requested ship date
Business Context: GP uses 1900-01-01 as null date indicator; checking YEAR=1900 detects these nulls

IF¶

IF( Condition, TrueValue, FalseValue )

Purpose: Returns one of two values based on a condition.
Parameters: - Condition: Expression that evaluates to true/false - TrueValue: Value returned if condition is true - FalseValue: Value returned if condition is false

Return Value: TrueValue or FalseValue depending on condition
Example: IF( YEAR( S44 ) = 1900, #NULL!, LOCAL2UTC( CONVERTDATETIME( S44, "12:00 AM" ) ) ) returns null for 1900 dates, otherwise converts to UTC

OR¶

OR( Condition1, Condition2, ... )

Purpose: Returns true if ANY condition is true.
Parameters: One or more boolean expressions
Return Value: TRUE if any condition is true, FALSE if all are false
Example: OR( ISERROR( LookupProductID ), ISERROR( LookupUoMID ) ) returns true if either lookup failed

ISERROR¶

ISERROR( Value )

Purpose: Tests if a value is an error (#ERROR!, #NOTFOUND!, etc.).
Parameters: Value - Any value or expression result
Return Value: TRUE if value is error, FALSE otherwise
Example: ISERROR( LookupProductID ) returns true if product lookup failed

ENDJOBFAILEDMSG¶

ENDJOBFAILEDMSG( "Error message" )

Purpose: Immediately terminates job execution with custom error message.
Parameters: "Error message" - Text to display in error log
Return Value: Does not return; stops execution
Example: ENDJOBFAILEDMSG( "The ProductID and/or the UoMID were not found in CRM" )

DATEADD¶

DATEADD( "interval", Number, Date )

Purpose: Adds a time interval to a date.
Parameters: - "interval": Type of interval ("d"=days, "m"=months, "y"=years, "h"=hours, etc.) - Number: Quantity to add (can be negative) - Date: Source date value

Return Value: New date/time value
Example: DATEADD( "d", 1, ReqShipDate_UTC ) adds 1 day to requested ship date
Business Context: Adjusts requested ship date to delivery date (+1 day for shipping)

Special Literals¶

#LASTID!: References the ID of the record created/updated in the previous step (e.g., SalesOrder ID from Step 2)
#NULL!: Represents a null/empty value; used to leave fields blank
#NOTFOUND!: Returned by lookup functions when no matching record is found
#ERROR!: Returned by lookup functions when multiple matches are found (ambiguous result)

Source Connection Details¶

GP Adapter (Microsoft Dynamics GP) - Connection Name: "GP Adapter" - Source Type: ResultsSet XML from GP (likely eConnect or custom stored procedure) - Source Objects: - ResultsSet.Row (header data with billing/shipping addresses) - Detail rows with dtl_ prefix fields (order line items)

Break Groups: - Header Steps (1-3): Break on SOPNUMBE <ResultsSet.Row> (one order at a time) - Detail Step (4): No break group (processes all detail lines)

Target Connection Details¶

Microsoft CRM 2011 Adapter - Connection Name: "Microsoft CRM 2011 Adapter" - Target Objects: - Account: Customer account (lookup only, not modified) - SalesOrder: Order header records - SalesOrderDetail: Order line item records - PriceLevel: Price level lookup - Product: Product master lookup - UoMSchedule: Unit of measure schedule lookup - UoM: Unit of measure lookup - systemuser: User/salesperson lookup

Business Rules¶

Sales Order Filtering¶

Excludes Transferred Orders: This integration only processes non-transferred orders
Transferred orders handled by separate SalesOrderTransferredToCRM integration
Filter likely applied at GP source query level (e.g., WHERE TRANSFERRED = 0)

Order Processing Pattern¶

Account Dependency: Order cannot be created without existing account
If account not found: Job fails, message goes to retry queue
Ensures data integrity and allows retry when customer is later synced
Update vs Insert Logic: Uses cross-reference on order name (SOPNUMBE)
If order already exists in CRM: Update header and refresh details
If order is new: Insert header and details
Cross-reference persists after first successful sync
Delete-Then-Insert for Details: Order line items are completely refreshed
Step 3 deletes ALL existing SalesOrderDetail records for the order
Step 4 inserts ALL current detail lines from GP
Handles quantity changes, line deletions, and additions
Skip If Repeated Pattern: Detail step configured to skip duplicate rows
Prevents inserting same detail line multiple times
Handles cases where detail data repeats in source XML

Date/Time Conversion¶

GP 1900 Null Date Handling: GP uses 1900-01-01 as null date
Formula checks: IF( YEAR( S44 ) = 1900, #NULL!, ... )
Prevents invalid 1900 dates from appearing in CRM
UTC Conversion: CRM stores dates in UTC timezone
GP dates are local timezone
Uses LOCAL2UTC( CONVERTDATETIME( date, "12:00 AM" ) ) pattern
Adds midnight time to date-only fields, then converts to UTC
Delivery Date Calculation: Adds 1 day to requested ship date
Formula: DATEADD( "d", 1, ReqShipDate_UTC )
Business rule: Requested delivery = ship date + 1 day for transit
Applied to both header and detail line requested delivery fields

Non-Inventoried Items¶

When dtlNONINVEN > 0: Item is not tracked in inventory
productid field set to #NULL! (no product link)
productdescription populated with description from GP
uomid field set to #NULL! (no UoM link)
When inventoried item:
Both ProductID and UoMID must exist in CRM or job fails
Error message: "The ProductID and/or the UoMID were not found in CRM"

UoM Two-Key Lookup¶

Uses DBLOOKUP2 to prevent UoM ambiguity
Requires BOTH UoM name AND UoM Schedule ID
Prevents issues when same UoM name exists in multiple schedules
Example: "Each" might exist in multiple product UoM schedules

Salesperson Assignment¶

Links to CRM systemuser via custom GP salesperson ID field
Uses DBLOOKUP( S134, ..., "systemuser", "new_gpsalespersonid", "systemuserid" )
Requires custom CRM field: new_gpsalespersonid to store GP salesperson territory ID
Maps GP territory ID to CRM user for sales credit and territory management

Order Status Translation¶

GP order status codes translated to CRM status codes via XRef.ini
Uses FILELOOKUP( S103, "XRef.ini", "Order_Status" )
Example mappings (likely):
GP "NEW" → CRM status option 1 (New)
GP "PENDING" → CRM status option 2 (Pending)
GP "IN PROGRESS" → CRM status option 3 (In Progress)

Security Permissions¶

Order inherits security from account owner
vfGrantAction = 1 (Grant permission)
vfGranteeId = Account owner ID (user or team)
vfGranteeType = Account owner type (8=systemuser, 9=team)
vfGrantRights = 1 (basic access rights)
Ensures salesperson/team can see orders for their accounts

Price Override Flag¶

All order detail lines have ispriceoverridden = 1
Indicates prices come from GP, not CRM price lists
Prevents CRM from recalculating prices
Maintains exact GP pricing in CRM

SmartConnect Re-implementation Notes¶

Equivalent SmartConnect Map Structure¶

Map Type: CRM 2011
Data Source: GP SQL View (custom view joining SOP10100, SOP10200, RM00101, IV00101)
Filter: WHERE SOPTYPE = 2 AND TRANSFERRED = 0

Pre-Map Tasks:
1. Clear SalesOrderDetail records for orders being processed
2. Cache UoM Schedules dictionary

Account Lookup Task (Before Map)¶

-- Pre-map validation: Check if account exists
SELECT accountid, ownerid, owneridtype, address1_addressid
FROM Account
WHERE accountnumber = @CUSTNMBR

-- If not found, throw error to retry queue

Header-Detail Relationship Handling¶

// In map setup, use Maintenance with Delete and Add strategy
// Header: SalesOrder entity (Maintenance with Update if exists, else Add)
// Details: SalesOrderDetail entity (Maintenance with Delete existing + Add new)

// Configure relationship:
// Parent: SalesOrder (salesorderid)
// Child: SalesOrderDetail (salesorderid foreign key)

Date/Time Conversion with 1900 Null Handling¶

public DateTime? ConvertGPDateToUTC(DateTime? gpDate)
{
    if (!gpDate.HasValue || gpDate.Value.Year == 1900)
        return null; // GP null date indicator

    // Combine with midnight time, then convert to UTC
    var localDateTime = gpDate.Value.Date.AddHours(0);
    var utcDateTime = TimeZoneInfo.ConvertTimeToUtc(localDateTime);

    return utcDateTime;
}

public DateTime? CalculateDeliveryDate(DateTime? shipDate)
{
    var shipDateUTC = ConvertGPDateToUTC(shipDate);

    if (!shipDateUTC.HasValue)
        return null;

    return shipDateUTC.Value.AddDays(1); // Add 1 day for delivery
}

Non-Inventoried Item Handling¶

public Guid? GetProductId(int nonInven, string itemNumber)
{
    if (nonInven > 0)
        return null; // Non-inventoried item, no product link

    var product = crmContext.ProductSet
        .Where(p => p.ProductNumber == itemNumber)
        .Select(p => p.ProductId)
        .FirstOrDefault();

    if (product == Guid.Empty)
        throw new Exception($"ProductID not found for item {itemNumber}");

    return product;
}

public string GetProductDescription(int nonInven, string description)
{
    if (nonInven > 0)
        return description; // Use GP description for non-inventoried items

    return null; // Let CRM populate from product master
}

UoM Two-Key Lookup¶

public Guid? GetUoMId(string uomName, Guid? scheduleId)
{
    if (scheduleId == null || string.IsNullOrEmpty(uomName))
        return null;

    var uom = crmContext.UoMSet
        .Where(u => u.Name == uomName && u.UoMScheduleId == scheduleId)
        .Select(u => u.UoMId)
        .FirstOrDefault();

    if (uom == Guid.Empty)
        throw new Exception($"UoM '{uomName}' not found in schedule {scheduleId}");

    return uom;
}

Salesperson Lookup via GP Territory ID¶

public Guid? GetSalesRepId(string gpSalespersonId)
{
    if (string.IsNullOrEmpty(gpSalespersonId))
        return null;

    var user = crmContext.SystemUserSet
        .Where(u => u.GetAttributeValue<string>("new_gpsalespersonid") == gpSalespersonId)
        .Select(u => u.SystemUserId)
        .FirstOrDefault();

    return user == Guid.Empty ? (Guid?)null : user;
}

Error Handling¶

// Account not found handling
if (accountId == Guid.Empty)
{
    throw new Exception($"Account not found for customer {customerNumber}. Retry queue.");
}

// Product/UoM validation for inventoried items
if (nonInven == 0) // Inventoried item
{
    if (productId == null || uomId == null)
    {
        throw new Exception("The ProductID and/or the UoMID were not found in CRM");
    }
}

Performance Optimization¶

// Cache frequently used lookups
private Dictionary<string, Guid> _uomScheduleCache = new Dictionary<string, Guid>();

public Guid? GetUoMScheduleIdCached(string scheduleName)
{
    if (_uomScheduleCache.ContainsKey(scheduleName))
        return _uomScheduleCache[scheduleName];

    var scheduleId = crmContext.UoMScheduleSet
        .Where(s => s.Name == scheduleName)
        .Select(s => s.UoMScheduleId)
        .FirstOrDefault();

    if (scheduleId != Guid.Empty)
        _uomScheduleCache[scheduleName] = scheduleId;

    return scheduleId == Guid.Empty ? (Guid?)null : scheduleId;
}

Integration Dependencies¶

Upstream Dependencies (Must Run First)¶

CustomerToCRM: Account must exist in CRM before order can be created
Order links to account via customerid field
Job fails if account not found (retry queue)
ProductToCRM: Products must exist in CRM for inventoried items
Order detail lines link to products via productid
Job fails if inventoried product not found
UofMToCRM: UoM schedules and units must exist in CRM
Order detail lines require UoM for inventoried items
Job fails if UoM not found

Downstream Dependencies (Run After)¶

InvoicePostedToCRM: May reference sales orders via salesorderid link
Invoices can link back to originating orders

SalesOrderTransferredToCRM: Handles transferred orders (separate DTS)
This integration excludes transferred orders
SalesOrderTransferredToCRM processes same structure but for transferred orders only
Together they provide complete sales order synchronization

Sales Order to CRM Integration¶

Integration Overview¶

Purpose¶

Data Flow Direction¶

Key Processing Steps¶

User Variables¶

Target Variables¶

Field Mappings¶

Step 1: Account Lookup¶

Step 2: Sales Order Header Update/Insert¶

Step 3: Delete Old Sales Order Details¶

Step 4: Insert New Sales Order Details¶

Formula Reference¶

DBLOOKUP¶

DBLOOKUP2¶

DBLOOKUPCached¶

FILELOOKUP¶

PHONEFROMGP¶

LOCAL2UTC¶

CONVERTDATETIME¶

YEAR¶

IF¶

OR¶

ISERROR¶

ENDJOBFAILEDMSG¶

DATEADD¶

Special Literals¶

Source Connection Details¶

Target Connection Details¶

Business Rules¶

Sales Order Filtering¶

Order Processing Pattern¶

Date/Time Conversion¶

Non-Inventoried Items¶

UoM Two-Key Lookup¶

Salesperson Assignment¶

Order Status Translation¶

Security Permissions¶

Price Override Flag¶

SmartConnect Re-implementation Notes¶

Equivalent SmartConnect Map Structure¶

Account Lookup Task (Before Map)¶

Header-Detail Relationship Handling¶

Date/Time Conversion with 1900 Null Handling¶

Non-Inventoried Item Handling¶

UoM Two-Key Lookup¶

Salesperson Lookup via GP Territory ID¶

Error Handling¶

Performance Optimization¶

Integration Dependencies¶

Upstream Dependencies (Must Run First)¶

Downstream Dependencies (Run After)¶

Related Integrations¶