Invoice Posted to CRM Integration¶

Integration Overview¶

This integration synchronizes posted invoice data from Microsoft Dynamics GP to Microsoft Dynamics CRM 2011 after invoices have been completed in the ERP system. It creates or updates invoice records in CRM with complete header information and line item details, providing visibility into billing history and customer purchasing patterns.

Purpose¶

Synchronize posted invoices from GP to CRM for sales tracking
Create invoice headers with billing and shipping addresses
Populate invoice line items with product, pricing, and quantity details
Link invoices to corresponding sales orders when applicable
Maintain security permissions for invoice access

Data Flow Direction¶

GP (ERP) → CRM

flowchart TD
    Start([GP Posted Invoice]) --> 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 Invoice]
    XRef -->|Not Exists| Insert[Step 2: Insert Invoice]
    Update --> DeleteDetails[Step 3: Delete Old InvoiceDetail Records]
    Insert --> DeleteDetails
    DeleteDetails --> InsertDetails[Step 4: Insert New InvoiceDetail Records<br/>Skip If Repeated]
    InsertDetails --> Success([Success<br/>Invoice 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
Invoice Header Update/Insert: Check cross-reference for existing invoice
If invoice exists in CRM: Update header with latest GP data
If invoice doesn't exist: Insert new invoice record
Populate billing and shipping addresses, totals, dates, and payment terms
Delete Old Invoice Details: Remove all existing InvoiceDetail records for this invoice
Ensures line items are refreshed completely
Prevents orphaned detail records from previous versions
Insert New Invoice Details: Add all current invoice line items
Uses "Skip If Repeated" pattern to handle multiple detail rows
Populates product, quantity, pricing, UoM, and shipping information
Links to salesperson via name concatenation lookup
Sets security permissions based on account owner

User Variables¶

Variable Name	Formula	Purpose
`LookupInvoiceID`	`DBLOOKUP( S34, "Microsoft CRM 2011 Adapter", "Invoice", "Name", "InvoiceID" )`	Looks up existing invoice in CRM by invoice number (SOPNUMBE from GP) to determine if this is an update or insert operation
`AreaCode`	`"603"`	Default area code for phone number formatting when area code not present in GP phone fields
`LookupUoMSchedID`	`DBLOOKUPCached( S208, "Microsoft CRM 2011 Adapter", "UoMSchedule", "Name", "UoMScheduleID" )`	Finds UoM Schedule ID for the product's unit of measure schedule; uses cached lookup for performance across multiple detail lines
`NonInvProdDesc`	`IF( S212 > 0, S211, "###Product Lookup Error###" )`	Returns product description for non-inventoried items (when S212 NONINVEN flag > 0), or error message if product lookup fails
`LookupProductID`	`DBLOOKUP( S210, "Microsoft CRM 2011 Adapter", "Product", "ProductNumber", "ProductID" )`	Finds CRM Product ID using GP item number to link invoice detail lines to product master
`LookupUoMID`	`DBLOOKUP2( S213, 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
`SalespersonName`	`TRIM( DBLOOKUP( S125, "GP Adapter", "SalesPerson", "SLPRSNID", "SLPRSNFN" ) & " " & DBLOOKUP( S125, "GP Adapter", "SalesPerson", "SLPRSNID", "SLPRSNLN" ) )`	Concatenates salesperson first and last name from GP SalesPerson table, trims whitespace, used to lookup CRM systemuser
`LookupOrderId`	`DBLOOKUP( S163, "Microsoft CRM 2011 Adapter", "SalesOrder", "Name", "SalesOrderID" )`	Looks up related CRM sales order ID using GP order number (from ORIGNUMB field) to link invoice to originating order

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 invoice
`AccountOwnerType`	Step 1: Account seek Field: `owneridtype`	Captures the type of owner (8=systemuser, 9=team) for proper security permission assignment

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 <SalesOrder> (processes one invoice 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: Invoice Header Update/Insert¶

Target Field	Source Field(s)	Formula	Description
`name`	S34 (SOPNUMBE)	`S34`	Invoice number from GP (e.g., "INV12345")
`description`	S35 (CUSTNAME)	`S35`	Customer name from GP for invoice description
`customerid`	AccountID from Step 1	`#LASTID!`	Links invoice to the account found in Step 1
`datedelivered`	S50 (INVODATE)	`S50`	Invoice date from GP (when invoice was posted)
`duedate`	S51 (DUEDATE)	`S51`	Payment due date from GP
`billto_line1`	S192 (bill_ADDRESS1)	`S192`	Billing address line 1
`billto_line2`	S193 (bill_ADDRESS2)	`S193`	Billing address line 2
`billto_line3`	S194 (bill_ADDRESS3)	`S194`	Billing address line 3
`billto_city`	S195 (bill_CITY)	`S195`	Billing city
`billto_stateorprovince`	S196 (bill_STATE)	`S196`	Billing state
`billto_postalcode`	S197 (bill_ZIPCODE)	`S197`	Billing postal code
`billto_country`	S198 (bill_COUNTRY)	`S198`	Billing country
`billto_fax`	S202 (bill_FAX) AreaCode	`PHONEFROMGP( S202, AreaCode, "###-###-####", "x####" )`	Formats billing fax number from GP format to CRM standard
`billto_name`	S199 (bill_ADRSCODE)	`S199`	Billing address code from GP
`billto_telephone`	S201 (bill_PHONE1) AreaCode	`PHONEFROMGP( S201, AreaCode, "###-###-####", "x####" )`	Formats billing phone number from GP format to CRM standard
`shipto_line1`	S73 (ADDRESS1)	`S73`	Shipping address line 1
`shipto_line2`	S74 (ADDRESS2)	`S74`	Shipping address line 2
`shipto_line3`	S75 (ADDRESS3)	`S75`	Shipping address line 3
`shipto_city`	S76 (CITY)	`S76`	Shipping city
`shipto_stateorprovince`	S77 (STATE)	`S77`	Shipping state
`shipto_postalcode`	S78 (ZIPCODE)	`S78`	Shipping postal code
`shipto_country`	S80 (COUNTRY)	`S80`	Shipping country
`shipto_telephone`	S81 (PHONE1) AreaCode	`PHONEFROMGP( S81, AreaCode, "###-###-####", "x####" )`	Formats shipping phone number from GP format to CRM standard
`shipto_fax`	S84 (FAX) AreaCode	`PHONEFROMGP( S84, AreaCode, "###-###-####", "x####" )`	Formats shipping fax number from GP format to CRM standard
`shipto_name`	S70 (SHIPMTHD)	`S70`	Shipping method from GP
`discountamount`	S93 (DISTKNAM)	`S93`	Discount amount applied to invoice
`freightamount`	S102 (FRTAMNT)	`S102`	Freight/shipping charges for invoice
`pricelevelid`	S47 (PRCLEVEL)	`DBLOOKUP( S47, "Microsoft CRM 2011 Adapter", "PriceLevel", "Name", "PriceLevelID" )`	Looks up CRM price level using GP price level name
`paymenttermscode`	S46 (PYMTRMID)	`FILELOOKUP( S46, "XRef.ini", "Payment_Terms" )`	Translates GP payment terms code to CRM option value via XRef.ini
`shippingmethodcode`	S88 (SHIPMTHD)	`FILELOOKUP( S88, "XRef.ini", "Shipping_Method" )`	Translates GP shipping method to CRM option value via XRef.ini
`totalamount`	S98 (DOCAMNT)	`S98`	Total invoice amount including tax and freight
`totaltax`	S99 (TAXAMNT)	`S99`	Total tax amount for invoice
`totallineitemamount`	S100 (SUBTOTAL)	`S100`	Subtotal of all line items before tax and freight
`salesorderid`	S14 (SOPTYPE) LookupOrderId	`IF( S14 = 2, LookupOrderId, #NULL! )`	Links invoice to originating sales order if SOPTYPE=2 (invoice from order), otherwise null
`statecode`	S147 (VOIDSTTS)	`IF( S147 = 1, 3, 0 )`	Sets invoice state: 3=Canceled if voided in GP, 0=Active otherwise
`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: Invoice name (S34 SOPNUMBE) to determine update vs insert - Break Group: SOPNUMBE <SalesOrder> (processes one invoice 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 Invoice matches found!", rollback transaction

Step 3: Delete Old Invoice Details¶

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

Step Configuration: - Operation: Delete (D) - Break Group: SOPNUMBE <SalesOrder> (deletes all details for current invoice) - Purpose: Removes all old invoice 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 Invoice Details¶

Target Field	Source Field(s)	Formula	Description
`invoiceid`	Invoice ID from Step 2	`#LASTID!`	Links detail line to invoice header
`lineitemnumber`	S214 (dtlLNITMSEQ)	`S214`	Line item sequence number from GP
`productid`	S212 (dtlNONINVEN) LookupProductID	`IF( S212 > 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`	S216 (dtlQUANTITY)	`S216`	Quantity ordered
`priceperunit`	S217 (dtlUNITPRCE)	`S217`	Unit price from GP
`baseamount`	S218 (dtlXTNDPRCE)	`S218`	Extended price (quantity × unit price)
`manualdiscountamount`	S219 (dtlMRKDNAMT)	`S219`	Manual discount amount for this line
`tax`	S220 (dtlTAXAMNT)	`S220`	Tax amount for this line item
`shipto_line1`	S228 (dtlADDRESS1)	`S228`	Line-specific shipping address line 1
`shipto_line2`	S229 (dtlADDRESS2)	`S229`	Line-specific shipping address line 2
`shipto_line3`	S230 (dtlADDRESS3)	`S230`	Line-specific shipping address line 3
`shipto_city`	S231 (dtlCITY)	`S231`	Line-specific shipping city
`shipto_stateorprovince`	S232 (dtlSTATE)	`S232`	Line-specific shipping state
`shipto_postalcode`	S233 (dtlZIPCODE)	`S233`	Line-specific shipping postal code
`shipto_country`	S234 (dtlCOUNTRY)	`S234`	Line-specific shipping country
`shipto_telephone`	S237 (dtlPHONE1) AreaCode	`PHONEFROMGP( S237, AreaCode, "###-###-####", "x####" )`	Line-specific shipping phone (formatted)
`shipto_fax`	S238 (dtlFAX) AreaCode	`PHONEFROMGP( S238, AreaCode, "###-###-####", "x####" )`	Line-specific shipping fax (formatted)
`shipto_name`	S227 (dtlSHIPMTHD)	`S227`	Line-specific shipping method
`uomid`	S212 (dtlNONINVEN) LookupUoMID	`IF( S212 > 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 invoices)
`salesrepid`	SalespersonName	`DBLOOKUPCached( SalespersonName, "Microsoft CRM 2011 Adapter", "systemuser", "fullname", "systemuserid" )`	Looks up CRM user ID using concatenated salesperson name; uses cached lookup for performance
`isproductoverridden`	S212 (dtlNONINVEN)	`S212`	Flag indicating if product is overridden (set when non-inventoried item)

Step Configuration: - Operation: Insert (I) - Repeat Handling: "Skip If Repeated" - handles multiple detail rows for same invoice - 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", "GP 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", "Invoice", "Name", "InvoiceID" )

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( S213, LookupUoMSchedID, "Microsoft CRM 2011 Adapter", "UoM", "Name", "UoMScheduleID", "UoMID" )
Use Case: Prevents ambiguity when same UoM name exists in multiple schedules (e.g., "Each" in different product groups)

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( S208, "Microsoft CRM 2011 Adapter", "UoMSchedule", "Name", "UoMScheduleID" )
Best Practice: Use for frequently repeated lookups (e.g., UoM schedules, price levels, salesperson names) 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") - "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( S201, "603", "###-###-####", "x####" )

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( S14 = 2, LookupOrderId, #NULL! ) returns order ID if SOPTYPE=2 (invoice from order), else null

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" )

TRIM¶

TRIM( String )

Purpose: Removes leading and trailing whitespace from a string.
Parameters: String - Text value to trim
Return Value: Trimmed string
Example: TRIM( DBLOOKUP(...) & " " & DBLOOKUP(...) ) trims concatenated salesperson name

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, dtl_ReqShipDate_UTC ) adds 1 day to requested ship date

Special Literals¶

#LASTID!: References the ID of the record created/updated in the previous step (e.g., Invoice 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: - SalesOrder (header data with billing/shipping addresses) - Detail rows with dtl_ prefix fields (invoice line items)

Break Groups: - Header Steps (1-3): Break on SOPNUMBE <SalesOrder> (one invoice 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) - Invoice: Invoice header records - InvoiceDetail: Invoice 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¶

Invoice Processing Pattern¶

Account Dependency: Invoice cannot be created without existing account
If account not found: Job fails, message goes to retry queue
This ensures data integrity and allows retry when customer is later synced
Update vs Insert Logic: Uses cross-reference on invoice name (SOPNUMBE)
If invoice already exists in CRM: Update header and refresh details
If invoice is new: Insert header and details
Cross-reference persists after first successful sync
Delete-Then-Insert for Details: Invoice line items are completely refreshed
Step 3 deletes ALL existing InvoiceDetail records for the invoice
Step 4 inserts ALL current detail lines from GP
This pattern 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

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)
isproductoverridden flag set to true
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"
This ensures data integrity for inventoried products

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 Name Concatenation¶

GP stores salesperson as separate SLPRSNFN and SLPRSNLN fields
Integration concatenates: FirstName + " " + LastName
TRIM removes extra whitespace
Result used to lookup CRM systemuser by fullname
Uses DBLOOKUPCached for performance across multiple detail lines

Sales Order Linkage¶

Invoice can be linked to originating sales order
Only links when SOPTYPE = 2 (invoice created from order)
Uses ORIGNUMB field from GP to lookup CRM SalesOrderID
If SOPTYPE ≠ 2: salesorderid field set to #NULL!

Invoice State Mapping¶

GP VOIDSTTS field determines CRM state:
VOIDSTTS = 1: Invoice is voided → CRM statecode = 3 (Canceled)
Otherwise: CRM statecode = 0 (Active)
Allows CRM to reflect voided invoices from GP

Security Permissions¶

Invoice 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 invoices for their accounts

Price Override Flag¶

All invoice detail lines have ispriceoverridden = 1
Indicates prices come from GP, not CRM price lists
Prevents CRM from recalculating prices based on CRM price levels
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 SOP30200, SOP30300, RM00101, IV00101)

Pre-Map Tasks:
1. Clear InvoiceDetail records for invoices being processed
2. Cache UoM Schedules dictionary
3. Cache Salesperson Names dictionary

Account Lookup Task (Before Map)¶

-- Pre-map validation: Check if account exists
SELECT accountid, ownerid, owneridtype
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: InvoiceDetail entity (Maintenance with Delete existing + Add new)

// Configure relationship:
// Parent: Invoice (invoiceid)
// Child: InvoiceDetail (invoiceid foreign key)

Salesperson Name Concatenation Task¶

-- Pre-map calculated field
SELECT
    SLPRSNID,
    RTRIM(SLPRSNFN) + ' ' + RTRIM(SLPRSNLN) AS FullName
FROM SalesPerson

// Or in map script:
public string GetSalespersonFullName(string slprsnId, string firstName, string lastName)
{
    if (string.IsNullOrEmpty(slprsnId))
        return null;

    return $"{firstName?.Trim()} {lastName?.Trim()}".Trim();
}

// Then lookup systemuser:
public Guid? LookupSystemUser(string fullName)
{
    if (string.IsNullOrEmpty(fullName))
        return null;

    var user = crmContext.SystemUserSet
        .Where(u => u.FullName == fullName)
        .Select(u => u.SystemUserId)
        .FirstOrDefault();

    return user;
}

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;
}

Sales Order Linkage¶

public Guid? GetRelatedSalesOrder(int sopType, string origNumber)
{
    if (sopType != 2 || string.IsNullOrEmpty(origNumber))
        return null; // Only link for invoices created from orders

    var order = crmContext.SalesOrderSet
        .Where(o => o.Name == origNumber)
        .Select(o => o.SalesOrderId)
        .FirstOrDefault();

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

Invoice State Mapping¶

public int GetInvoiceState(int voidStatus)
{
    return (voidStatus == 1) ? 3 : 0; // 3=Canceled, 0=Active
}

Security Permission Assignment¶

// In map post-processing or as calculated field:
public void AssignInvoiceSecurity(Guid invoiceId, Guid accountOwnerId, int accountOwnerType)
{
    // vfGrantAction: 1 (Grant)
    // vfGranteeId: accountOwnerId
    // vfGranteeType: accountOwnerType (8=systemuser, 9=team)
    // vfGrantRights: 1

    // These fields typically set directly in field mapping
    // SmartConnect handles CRM security assignment
}

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>();
private Dictionary<string, Guid> _salespersonCache = 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 invoice can be created
Invoice links to account via customerid field
Job fails if account not found (retry queue)
ProductToCRM: Products must exist in CRM for inventoried items
Invoice detail lines link to products via productid
Job fails if inventoried product not found
UofMToCRM: UoM schedules and units must exist in CRM
Invoice detail lines require UoM for inventoried items
Job fails if UoM not found

Downstream Dependencies (Run After)¶

None - This is a terminal integration for posted invoice data

SalesOrderToCRM: Creates sales orders that may be referenced by invoices
Invoice can link to originating order via salesorderid field
Link only created when invoice generated from order (SOPTYPE=2)

Invoice Posted to CRM Integration¶

Integration Overview¶

Purpose¶

Data Flow Direction¶

Key Processing Steps¶

User Variables¶

Target Variables¶

Field Mappings¶

Step 1: Account Lookup¶

Step 2: Invoice Header Update/Insert¶

Step 3: Delete Old Invoice Details¶

Step 4: Insert New Invoice Details¶

Formula Reference¶

DBLOOKUP¶

DBLOOKUP2¶

DBLOOKUPCached¶

FILELOOKUP¶

PHONEFROMGP¶

IF¶

OR¶

ISERROR¶

ENDJOBFAILEDMSG¶

TRIM¶

DATEADD¶

Special Literals¶

Source Connection Details¶

Target Connection Details¶

Business Rules¶

Invoice Processing Pattern¶

Non-Inventoried Items¶

UoM Two-Key Lookup¶

Salesperson Name Concatenation¶

Sales Order Linkage¶

Invoice State Mapping¶

Security Permissions¶

Price Override Flag¶

SmartConnect Re-implementation Notes¶

Equivalent SmartConnect Map Structure¶

Account Lookup Task (Before Map)¶

Header-Detail Relationship Handling¶

Salesperson Name Concatenation Task¶

Non-Inventoried Item Handling¶

UoM Two-Key Lookup¶

Sales Order Linkage¶

Invoice State Mapping¶

Security Permission Assignment¶

Error Handling¶

Performance Optimization¶

Integration Dependencies¶

Upstream Dependencies (Must Run First)¶

Downstream Dependencies (Run After)¶

Related Integrations¶