Source ERP · 20 of 27

CRMPro → Zoho CRM — Sage 300: what the shipped templates set up

Read it in the terminal with "dlake skills show dlake-crmpro-zohocrm/erps/sage-300". It extends the dlake-crmpro-zohocrm skill, which holds what is true whatever the source ERP.

CRMPro → Zoho CRM — Sage 300: what the shipped templates set up

Keep this skill current. dlake ships updates often and this text is embedded in the CLI you have installed. Before relying on it, compare dlake --version with npm view @commercient/dlake version; if they differ, run npm install -g @commercient/dlake@latest and then dlake skills install, which overwrites the installed skill files with the current text.

dlake-crmpro is the parent skill and the authority for everything general: the crmpro_* tools, CRM_Configuration and CRM_FieldList, TimeStampRepository, the three kinds of source view, the NULL-cursor rule and the silent zero-record run. Read it first; this page does not repeat it. dlake-crmpro-zohocrm is the destination skill this page is a child of, and the authority for the Zoho CRM conventions that hold across every ERP: read it first, then come back here for what this source’s own templates set. This page is dlake-crmpro-zohocrm/erps/sage-300.md, and that skill’s ERP table is what points at it. What follows is only what the shipped Sage 300 → Zoho CRM templates themselves set, read from their Insert_Query, CreateViewQuery and MappingJson. This page grows as the catalogue does.

1. What the templates deliver

GroupBusiness outcomeObjectsSource tables and views
SAGE300 SalesPersonThe templates push commercientsage300__Sage300_SalesPerson to Zoho CRM.commercientsage300__Sage300_SalesPersonSF_ERP_Salesforce_Clone_ARSAP
Parent AccountThe templates push Accounts to Zoho CRM.AccountsSF_ERP_Salesforce_Clone_ARCUS, SF_ERP_Salesforce_Clone_ARCSP
Child AccountThe templates push Accounts to Zoho CRM.AccountsSF_ERP_Salesforce_Clone_ARCSP, SF_ERP_Salesforce_Clone_ARCUS
SAGE300 CustomerThe templates push commercientsage300__Sage300_Customer to Zoho CRM.commercientsage300__Sage300_CustomerSF_ERP_Salesforce_Clone_ARCUS
ContactsThe templates push Contacts to Zoho CRM.ContactsSF_ERP_Salesforce_Clone_ARCUS
SAGE300 ShipToThe templates push commercientsage300__Sage300_ShipToAddress to Zoho CRM.commercientsage300__Sage300_ShipToAddressSF_ERP_Salesforce_Clone_ARCSP
ProductsThe templates push Products to Zoho CRM.ProductsSF_ERP_Salesforce_Clone_ICITEM
SAGE300 ItemThe templates push commercientsage300__Sage300_Item to Zoho CRM.commercientsage300__Sage300_ItemSF_ERP_Salesforce_Clone_ICITEM
SAGE300 SalesOrderHeaderThe templates push commercientsage300__Sage300_SalesOrderHeader to Zoho CRM.commercientsage300__Sage300_SalesOrderHeaderSF_ERP_Salesforce_Clone_OEORDH
SAGE300 SalesOrderDetailThe templates push commercientsage300__Sage300_SalesOrderDetail to Zoho CRM.commercientsage300__Sage300_SalesOrderDetailSF_ERP_Salesforce_Clone_OEORDD
SAGE300 Invoice HeaderThe templates push commercientsage300__Sage300_InvoiceHeader to Zoho CRM.commercientsage300__Sage300_InvoiceHeaderSF_ERP_Salesforce_Clone_OEINVH
SAGE300 Invoice DetailThe templates push commercientsage300__Sage300_InvoiceDetail to Zoho CRM.commercientsage300__Sage300_InvoiceDetailSF_ERP_Salesforce_Clone_OEINVD
PricebookThe templates push Price_Books to Zoho CRM.Price_BooksSF_ERP_Salesforce_Clone_ICPRICP
ProductPricebookThe templates push Products:Price_Books to Zoho CRM.Products:Price_BooksSF_ERP_Salesforce_Clone_ICPRICP
Sales OrdersThe templates push Sales_Orders to Zoho CRM.Sales_OrdersSF_ERP_Salesforce_Clone_OEORDD, SF_ERP_Salesforce_Clone_ICITEM, SF_ERP_Salesforce_Clone_OEORDH, SF_ERP_Salesforce_Clone_ARCUS, SF_ERP_Salesforce_Clone_ARCSP, x
InvoicesThe templates push Invoices to Zoho CRM.InvoicesSF_ERP_Salesforce_Clone_OEINVD, SF_ERP_Salesforce_Clone_ICITEM, SF_ERP_Salesforce_Clone_OEINVH, SF_ERP_Salesforce_Clone_ARCUS, SF_ERP_Salesforce_Clone_OEORDH, SF_ERP_Salesforce_Clone_ARCSP
CRM OwnershipThe templates push users to Zoho CRM.users

2. The process rows the templates create

Each template’s Insert_Query writes one CRM_Configuration row. These are the values it sets; a column the inserts never set is not listed.

Display nameCRM_Object_API_NameCRM_PK_API_NameSync_OrderTimeStamp_PrefixSQL_Query targetfield prefix / postfix
UsersusersCommercient_ExternalKey1vw_Users:SELECT 1'' / ''
SAGE300 SalesPersoncommercientsage300__Sage300_SalesPersonCommercient_ExternalKey2vw_SAGE300_SalesPerson:vw_SAGE300_SalesPersoncommercientsage300__ / __c
Parent AccountAccountsCommercient_ArCustomer_Code3vw_SAGE300_Account:vw_SAGE300_Account'' / ''
Child AccountAccountsCommercient_ArCustomer_Code4vw_SAGE300_Account:vw_SAGE300_ChildAccount'' / ''
SAGE300 Customercommercientsage300__Sage300_CustomerCommercient_ExternalKey5vw_SAGE300_Customer:vw_SAGE300_Customercommercientsage300__ / __c
ContactsContactsCommercient_ExternalKey6vw_SAGE300_Contacts:vw_SAGE300_Contacts'' / ''
SAGE300 ShipTocommercientsage300__Sage300_ShipToAddressCommercient_ExternalKey7vw_SAGE300_ShipToAddress:vw_SAGE300_ShipToAddresscommercientsage300__ / __c
ProductsProductsCommercient_ExternalKey8vw_SAGE300_Product:vw_SAGE300_Product'' / ''
SAGE300 Itemcommercientsage300__Sage300_ItemCommercient_ExternalKey9vw_SAGE300_Item:vw_SAGE300_Itemcommercientsage300__ / __c
SAGE300 SalesOrderHeadercommercientsage300__Sage300_SalesOrderHeaderCommercient_ExternalKey10vw_SAGE300_SalesOrderHeader:vw_SAGE300_SalesOrderHeadercommercientsage300__ / __c
SAGE300 SalesOrderDetailcommercientsage300__Sage300_SalesOrderDetailCommercient_ExternalKey11vw_SAGE300_SalesOrderDetail:vw_SAGE300_SalesOrderDetailcommercientsage300__ / __c
SAGE300 Invoice Headercommercientsage300__Sage300_InvoiceHeaderCommercient_ExternalKey12vw_SAGE300_InvoiceHeader:vw_SAGE300_InvoiceHeadercommercientsage300__ / __c
SAGE300 Invoice Detailcommercientsage300__Sage300_InvoiceDetailCommercient_ExternalKey13vw_SAGE300_InvoiceDetail:vw_SAGE300_InvoiceDetailcommercientsage300__ / __c
PricebookPrice_BooksCommercient_ExternalKey14vw_SAGE300_Pricebooks:vw_SAGE300_Pricebooks'' / ''
ProductPricebookProducts:Price_BooksCommercient_ExternalKey15vw_SAGE300_ProductPricebook:vw_SAGE300_ProductPricebook'' / ''
Sales OrdersSales_OrdersCommercient_ExternalKey16vw_SAGE300_DSalesOrder:vw_SAGE300_DSalesOrder'' / ''
InvoicesInvoicesCommercient_ExternalKey17vw_SAGE300_DInvoice:vw_SAGE300_DInvoice'' / ''

Every one of these inserts Is_Active as 0, so an imported process is inactive until an operator activates it. Across the set they insert Is_Create_Entity 0, Is_Create_Fields 0, Is_Active_Get_Records 0, Is_Active_Delete_Records 0. The insert column lists do not include Sync_Operation_Type, Sync_Batch_Size, IsAccountMatching, Document_Source_Path, so those columns take their CRM_Configuration defaults. Text columns a template does not use are inserted as '' rather than left NULL. Get_SOQL_Query carries a query on Parent Account, Contacts — those rows read records back from Zoho CRM as well as pushing.

3. The views

Each template creates its view with CreateViewQuery, in the gateway dbo schema. “Change detection” names which of the three kinds dlake-crmpro defines the view’s own WHERE makes it; where a view’s shape is not one of those three the column is left empty and the view’s own WHERE is the authority.

ViewChange detectionRepository keyReads other prefixesIdentity columnSource tables
vw_SAGE300_SalesPersoninsert-onlyvw_SAGE300_SalesPerson:Commercient_ExternalKey__c, Commercient_ExternalKeySF_ERP_Salesforce_Clone_ARSAP
vw_SAGE300_Accountinsert + updatevw_Users:, vw_SAGE300_SalesPerson:, vw_SAGE300_Account:Commercient_ArCustomer_CodeSF_ERP_Salesforce_Clone_ARCUS, SF_ERP_Salesforce_Clone_ARCSP
vw_SAGE300_ChildAccountinsert + updatevw_SAGE300_Account:Commercient_ArCustomer_CodeSF_ERP_Salesforce_Clone_ARCSP, SF_ERP_Salesforce_Clone_ARCUS
vw_SAGE300_Customerinsert-onlyvw_SAGE300_Account:, vw_SAGE300_Customer:Commercient_ExternalKey__c, Commercient_ExternalKeySF_ERP_Salesforce_Clone_ARCUS
vw_SAGE300_Contactsinsert + updatevw_SAGE300_Account:, vw_SAGE300_Customer:, vw_SAGE300_Contacts:ExternalKey, Commercient_ExternalKeySF_ERP_Salesforce_Clone_ARCUS
vw_SAGE300_ShipToAddressinsert-onlyvw_SAGE300_Account:, vw_SAGE300_Customer:, vw_SAGE300_SalesPerson:, vw_SAGE300_ShipToAddress:Commercient_ExternalKey__c, Commercient_ExternalKeySF_ERP_Salesforce_Clone_ARCSP
vw_SAGE300_Productinsert-onlyvw_SAGE300_Product:Commercient_ExternalKey__c, Commercient_ExternalKeySF_ERP_Salesforce_Clone_ICITEM
vw_SAGE300_Iteminsert-onlyvw_SAGE300_Product:, vw_SAGE300_Item:Commercient_ExternalKey__c, Commercient_ExternalKeySF_ERP_Salesforce_Clone_ICITEM
vw_SAGE300_SalesOrderHeaderinsert-onlyvw_SAGE300_Account:, vw_SAGE300_Customer:, vw_SAGE300_SalesOrderHeader:Commercient_ExternalKey__c, Commercient_ExternalKeySF_ERP_Salesforce_Clone_OEORDH
vw_SAGE300_SalesOrderDetailinsert-onlyvw_SAGE300_SalesOrderHeader:, vw_SAGE300_Item:, vw_SAGE300_Product:, vw_SAGE300_SalesOrderDetail:Commercient_ExternalKey__c, Commercient_ExternalKeySF_ERP_Salesforce_Clone_OEORDD
vw_SAGE300_InvoiceHeaderinsert-onlyvw_SAGE300_Account:, vw_SAGE300_Customer:, vw_SAGE300_InvoiceHeader:Commercient_ExternalKey__c, Commercient_ExternalKeySF_ERP_Salesforce_Clone_OEINVH
vw_SAGE300_InvoiceDetailinsert-onlyvw_SAGE300_InvoiceHeader:, vw_SAGE300_Item:, vw_SAGE300_InvoiceDetail:Commercient_ExternalKey__c, Commercient_ExternalKeySF_ERP_Salesforce_Clone_OEINVD
vw_SAGE300_Pricebooksinsert + updatevw_SAGE300_Pricebooks:Commercient_ExternalKey__c, Commercient_ExternalKeySF_ERP_Salesforce_Clone_ICPRICP
vw_SAGE300_ProductPricebookinsert-onlyvw_SAGE300_Product:, vw_SAGE300_Pricebooks:, vw_SAGE300_ProductPricebook:Commercient_ExternalKeySF_ERP_Salesforce_Clone_ICPRICP
vw_SAGE300_DSalesOrderinsert-onlyvw_SAGE300_Product:, vw_SAGE300_Account:, vw_SAGE300_DSalesOrder:ExternalKey, Commercient_ExternalKey__c, Commercient_ExternalKeySF_ERP_Salesforce_Clone_OEORDD, SF_ERP_Salesforce_Clone_ICITEM, SF_ERP_Salesforce_Clone_OEORDH, SF_ERP_Salesforce_Clone_ARCUS, SF_ERP_Salesforce_Clone_ARCSP
vw_SAGE300_DInvoiceinsert-onlyvw_SAGE300_Product:, vw_SAGE300_Account:, vw_SAGE300_DSalesOrder:, vw_SAGE300_DInvoice:ExternalKey, Commercient_ExternalKey__c, Commercient_ExternalKeySF_ERP_Salesforce_Clone_OEINVD, SF_ERP_Salesforce_Clone_ICITEM, SF_ERP_Salesforce_Clone_OEINVH, SF_ERP_Salesforce_Clone_ARCUS, SF_ERP_Salesforce_Clone_OEORDH

4. Order of work

The templates set Sync_Order from 1 to 17. A run processes active rows in ascending Sync_Order, which is the order the templates put them in:

  • 1 — Users
  • 2 — SAGE300 SalesPerson
  • 3 — Parent Account
  • 4 — Child Account
  • 5 — SAGE300 Customer
  • 6 — Contacts
  • 7 — SAGE300 ShipTo
  • 8 — Products
  • 9 — SAGE300 Item
  • 10 — SAGE300 SalesOrderHeader
  • 11 — SAGE300 SalesOrderDetail
  • 12 — SAGE300 Invoice Header
  • 13 — SAGE300 Invoice Detail
  • 14 — Pricebook
  • 15 — ProductPricebook
  • 16 — Sales Orders
  • 17 — Invoices

These views read another process’s repository prefix, which is what makes the order a dependency order: the row appears in the view only once the process that writes that prefix has run, so a parent flows on one run and its children on the next.

  • vw_SAGE300_SalesPerson reads vw_SAGE300_SalesPerson:; no template in this set writes vw_SAGE300_SalesPerson:
  • vw_SAGE300_Account reads vw_Users:, vw_SAGE300_SalesPerson:, vw_SAGE300_Account:; no template in this set writes vw_Users:, vw_SAGE300_SalesPerson:, vw_SAGE300_Account:
  • vw_SAGE300_ChildAccount reads vw_SAGE300_Account:; no template in this set writes vw_SAGE300_Account:
  • vw_SAGE300_Customer reads vw_SAGE300_Account:, vw_SAGE300_Customer:; no template in this set writes vw_SAGE300_Account:, vw_SAGE300_Customer:
  • vw_SAGE300_Contacts reads vw_SAGE300_Account:, vw_SAGE300_Customer:, vw_SAGE300_Contacts:; no template in this set writes vw_SAGE300_Account:, vw_SAGE300_Customer:, vw_SAGE300_Contacts:
  • vw_SAGE300_ShipToAddress reads vw_SAGE300_Account:, vw_SAGE300_Customer:, vw_SAGE300_SalesPerson:, vw_SAGE300_ShipToAddress:; no template in this set writes vw_SAGE300_Account:, vw_SAGE300_Customer:, vw_SAGE300_SalesPerson:, vw_SAGE300_ShipToAddress:
  • vw_SAGE300_Product reads vw_SAGE300_Product:; no template in this set writes vw_SAGE300_Product:
  • vw_SAGE300_Item reads vw_SAGE300_Product:, vw_SAGE300_Item:; no template in this set writes vw_SAGE300_Product:, vw_SAGE300_Item:
  • vw_SAGE300_SalesOrderHeader reads vw_SAGE300_Account:, vw_SAGE300_Customer:, vw_SAGE300_SalesOrderHeader:; no template in this set writes vw_SAGE300_Account:, vw_SAGE300_Customer:, vw_SAGE300_SalesOrderHeader:
  • vw_SAGE300_SalesOrderDetail reads vw_SAGE300_SalesOrderHeader:, vw_SAGE300_Item:, vw_SAGE300_Product:, vw_SAGE300_SalesOrderDetail:; no template in this set writes vw_SAGE300_SalesOrderHeader:, vw_SAGE300_Item:, vw_SAGE300_Product:, vw_SAGE300_SalesOrderDetail:
  • vw_SAGE300_InvoiceHeader reads vw_SAGE300_Account:, vw_SAGE300_Customer:, vw_SAGE300_InvoiceHeader:; no template in this set writes vw_SAGE300_Account:, vw_SAGE300_Customer:, vw_SAGE300_InvoiceHeader:
  • vw_SAGE300_InvoiceDetail reads vw_SAGE300_InvoiceHeader:, vw_SAGE300_Item:, vw_SAGE300_InvoiceDetail:; no template in this set writes vw_SAGE300_InvoiceHeader:, vw_SAGE300_Item:, vw_SAGE300_InvoiceDetail:
  • vw_SAGE300_Pricebooks reads vw_SAGE300_Pricebooks:; no template in this set writes vw_SAGE300_Pricebooks:
  • vw_SAGE300_ProductPricebook reads vw_SAGE300_Product:, vw_SAGE300_Pricebooks:, vw_SAGE300_ProductPricebook:; no template in this set writes vw_SAGE300_Product:, vw_SAGE300_Pricebooks:, vw_SAGE300_ProductPricebook:
  • vw_SAGE300_DSalesOrder reads vw_SAGE300_Product:, vw_SAGE300_Account:, vw_SAGE300_DSalesOrder:; no template in this set writes vw_SAGE300_Product:, vw_SAGE300_Account:, vw_SAGE300_DSalesOrder:
  • vw_SAGE300_DInvoice reads vw_SAGE300_Product:, vw_SAGE300_Account:, vw_SAGE300_DSalesOrder:, vw_SAGE300_DInvoice:; no template in this set writes vw_SAGE300_Product:, vw_SAGE300_Account:, vw_SAGE300_DSalesOrder:, vw_SAGE300_DInvoice:

5. Field mapping

CRM_FieldList carries one row per pushed column, with Object_Name equal to the CRM_Object_API_Name value — as dlake-crmpro puts it, an object with no CRM_FieldList rows pushes nothing and records no error. Each template carries its intended mapping in MappingJson. Read that with dlake admin crmpro_templates, then check crmpro_field_mapping on the created process before activating it.

TemplateObjectMapped fieldsFirst ERP → Zoho CRM pairs
Usersusers1Commercient_ExternalKeyCommercient_ExternalKey
SAGE300 SalesPersoncommercientsage300__Sage300_SalesPerson26CODESLSPCommercient_ExternalKey__c, NAMEEMPLName, CODESLSPSalesperson, AUDTDATEAudit_Date, AUDTTIMEAudit_Time
Parent AccountAccounts18IDCUSTCommercient_ArCustomer_Code, NAMECUSTAccount_Name, TEXTSTRE1, TEXTSTRE2, TEXTSTRE3Billing_Street, NAMECITYBilling_City, CODESTTEBilling_State
Child AccountAccounts11IDCUST, IDCUSTSHPTCommercient_ArCustomer_Code, NAMECUST, IDCUSTSHPTAccount_Name, TEXTSTRE1, TEXTSTRE2, TEXTSTRE3, TEXTSTRE4Shipping_Street, NAMECITYShipping_City, CODESTTEShipping_State
SAGE300 Customercommercientsage300__Sage300_Customer157IDCUSTCommercient_ExternalKey__c, NAMECUSTName, IDCUSTCustomer_Number, AUDTDATEAudit_Date, AUDTTIMEAudit_time
ContactsContacts11SFDCIDAccount_Name, SFDCIDSage300_Customer, IDCUSTExternalKey, IDCUSTCommercient_ExternalKey, NAMECTACLast_Name
SAGE300 ShipTocommercientsage300__Sage300_ShipToAddress56IDCUST, IDCUSTSHPTCommercient_ExternalKey__c, NAMELOCN, IDCUSTSHPTName, IDCUSTCustomer_Number, IDCUSTSHPTShip_To_Location, AUDTDATEAudit_Date
ProductsProducts8ITEMNOProduct_Name, ITEMNOProduct_Code, ITEMNOCommercient_ExternalKey__c, DESCDescription, INACTIVEProduct_Active
SAGE300 Itemcommercientsage300__Sage300_Item69ITEMNOCommercient_ExternalKey__c, ITEMNOName, ITEMNOItem_Number, AUDTDATEAudit_Date, AUDTTIMEAudit_Time
SAGE300 SalesOrderHeadercommercientsage300__Sage300_SalesOrderHeader163CUSTOMERAccount, ORDUNIQCommercient_ExternalKey__c, ORDUNIQName, ORDUNIQOrder_Uniquifier, AUDTDATEAudit_Date
SAGE300 SalesOrderDetailcommercientsage300__Sage300_SalesOrderDetail154ORDUNIQ, LINENUMCommercient_ExternalKey__c, ORDUNIQ, LINENUMName, ORDUNIQOrder_Uniquifier, LINENUMLine_Number, AUDTDATEAudit_Date
SAGE300 Invoice Headercommercientsage300__Sage300_InvoiceHeader164INVUNIQCommercient_ExternalKey__c, INVUNIQName, INVUNIQInvoice_Uniquifier, AUDTDATEAudit_Date, AUDTTIMEAudit_Time
SAGE300 Invoice Detailcommercientsage300__Sage300_InvoiceDetail122INVUNIQ, LINENUMCommercient_ExternalKey__c, INVUNIQ, LINENUMName, INVUNIQInvoice_Uniquifier, LINENUMLine_Uniquifier, AUDTDATEAudit_Date
PricebookPrice_Books4PRICELIST, DPRICETYPECommercient_ExternalKey__c, PRICELIST, DPRICETYPEPrice_Book_Name, PRICELIST, DPRICETYPEDescription
ProductPricebookProducts:Price_Books4SFDCIDPRODUCT_ID, SFDCIDZOHO_PriceBookID, UNITPRICElist_price, PRICELIST, DPRICETYPE, ITEMNOCommercient_ExternalKey
Sales OrdersSales_Orders18ORDNUMBERCommercient_ExternalKey__c, SFDCIDAccount_Name, ORDNUMBERName, ORDNUMBERSO_Number, ORDDATEERP_Created_Date__c
InvoicesInvoices19INVUNIQCommercient_ExternalKey__c, SFDCIDAccount_Name, ORDUNIQSales_Order, INVUNIQName, INVUNIQInvoice_Number

6. Verifying

# per-prefix counts; every synced record carries its destination id
dlake tool query --profile <tenant> --sql "SELECT LEFT([Key], CHARINDEX(':',[Key])-1) AS prefix, COUNT(*) n, COUNT(NULLIF(SFDCID,'')) withId FROM dbo.TimeStampRepository WHERE CHARINDEX(':',[Key])>0 GROUP BY LEFT([Key], CHARINDEX(':',[Key])-1)"

withId = n for every prefix is the success condition dlake-crmpro defines. The split is on the first colon, which is the prefix boundary whether the key separator is : or ::.

The prefixes these templates set:

  • vw_SAGE300_SalesPerson:
  • vw_SAGE300_Account:
  • vw_SAGE300_Customer:
  • vw_SAGE300_Contacts:
  • vw_SAGE300_ShipToAddress:
  • vw_SAGE300_Product:
  • vw_SAGE300_Item:
  • vw_SAGE300_SalesOrderHeader:
  • vw_SAGE300_SalesOrderDetail:
  • vw_SAGE300_InvoiceHeader:
  • vw_SAGE300_InvoiceDetail:
  • vw_SAGE300_Pricebooks:
  • vw_SAGE300_ProductPricebook:
  • vw_SAGE300_DSalesOrder:
  • vw_SAGE300_DInvoice:
  • vw_Users:

7. Where this sits

dlake-crmpro is the general operating surface — the crmpro_* tools, the setup and transaction tables, field mapping, and the source-view contract that applies to every destination. This page adds what the shipped Sage 300 → Zoho CRM templates set up. dlake-crmpro-zohocrm is the destination skill this page sits under: its own text is the authority for the Zoho CRM conventions that hold across every ERP, and its ERP table lists this page alongside every sibling ERP page for this destination. For the extract leg that fills the clone tables, see dlake-normalsync; for the on-premises agent that runs it, dlake-syncagent; for the writeback leg, dlake-txdownloaderpro; for standing an integration up, dlake-integration-setup.