ERP Customer/Account Sync To/From Sage100

Overview

This article reviews the data synced between Sage100 and the CIMcloud platform for Sage Customers to Website Accounts. Customers in Sage are the equivalent to accounts in CIMcloud. For more detailed information on Accounts and related information in CIMcloud review this article Customers Overview.

At the beginning of a standard integration all of the Customers in Sage will by brought over to create Accounts in CIMcloud. The unique identifier for the account will be the Ref ID which is the standard Sage combination of the division code, a dash,  and the customer number. In addition accounts can be created directly in CIMcloud by several means depending on the add-ons purchased and feature set-up. Account creation in CIMcloud is possible through:

1. By Workers in the CIMcloud worker portal. Ability to do this is controlled in Workspace settings and individual Worker Rights.
2. By front end user request through the account request process. This is base functionality that can be configured to use this option or not.  This request allows users to create an account but the account is not typically allowed access to the site until reviewed at which point site front access with this account can be turn on or not. This base functionality does not sync the account into Sage independently of an order.
3. By front end user request with the additional add-on that allows account requests to be approved in the CIMcloud worker portal and if approved sync directly to Sage outside of an order.
4. For sites with add-ons for retail sites, users will be able to create accounts during the checkout process.

Accounts created in the CIMcloud platform will have a CIMcloud generated value (by default the letter W followed by 6 digits). The 6 digits are based on an accounts record counter. This will not appear in Sage as sequential numbers as users can create accounts that may not ever sync to Sage that will increment the counter and new accounts created from new Sage customers will also increment this counter. The division code used when created on the website will be based on a default value.

Outside of the Accounts Approval add-on that will create new accounts in Sage, accounts only sync to Sage to create or update Sage customers when they are used on an order. Customer updates in Sage will sync over to CIMcloud based on the standard integration which typically sees data updated within 10 minutes of the change.

Standard Sage Fields Mapped:

1. and 2. Customer Number: This is actually the combination of two fields, the division code and the customerno fields, in Sage. These two together map as the Ref ID (accounts.ref_id) on the website account record. It also maps to the two separate fields in CIMcloud division code (accounts.divison) and account number (accounts.num). The Ref ID in CIMcloud is the unique identifier for this record. For accounts that have originated in sage this will be the value synced from Sage.

3. Name: This is the account name (worker portal label) or billing company name (site front label) in CIMcloud. This maps to the CIMcloud field accounts.nm
4. Address Line 1: The first address line for the customer billing address. This corresponds to the field accounts.address on CIMCloud.
5. Address Line 2:  This is the second address line on the customer main billing address. This corresponds to accounts.address2 on CIMcloud.
6. Address Line 3: This is the third address line on the customer main billing address. This corresponds to accounts.address3 on CIMcloud. The website by default does not display or use this field. It can be enabled in settings.
7. Zip Code: This is the zip code associated with the main customer billing address. This corresponds to field accounts.zip in CIMcloud. In Sage zip codes are stored in a separate table and when new addresses are created in sage the zip code must exist in this table. CIMcloud does not make changes to this table. If a record with a zip code that does not exist in Sage is synced, the record creation will err. When this happens you will be notified of the issue and can add the missing zip code and re-export the record.
8. City: This is the city associated with the main customer billing address. This corresponds to field accounts.city in CIMcloud.
9. State: This is the state associated with the main customer billing address. This corresponds to field accounts.state in CIMcloud. This is the standard 2 digit state code for US addresses. For non-US addresses the website uses a standard states table that has most commonly used state/provinces populated. You will have access in the CIMcloud worker portal to make changes to this table.
10. Country: This is the country associated with the main customer billing address. This corresponds to field accounts.country in CIMcloud. The website uses ISO3 country codes by default.
11. Salesperson: This is the default sales rep associated with a customer. This corresponds to accounts.rep_un1 in CIMcloud. In Cimcloud this field includes the division number since in sage the same salesperson code can be used in different divisions and signify different salespersons. So this is stored in CIMcloud as division + salesperson code. This field is required when creating a customer record in Sage. The ERP Questionnaire allows you to give us the default value for new accounts. You will also be able to update this in the settings section of the Worker Portal.
12. Tax Schedule: For existing customer the tax schedule should stay the tax schedule synced from Sage. If a new customer the system will populate the Sage record with the tax schedule populated from the order. If no ta schedule is used during order processing then the system will use the default value provided from the ERP Questionnaire which can be updated in the Site settings. The CIMcloud value passed to Sage comes from a linked table and is from field tx_schedules.sacct_code. For more detail on CIMcloud and taxes check out – CIMcloud and Taxes the Basics
13. Terms Code: This will be the value brought over from Sage for existing accounts. For new accounts created in CIMcloud this will be the default value assigned provided with the ERP Questionnaire and that can updated in in the settings. The value stored on the account is the two digit code and not the terms description. It populates as 30 versus Net 30 for example. This corresponds to CIMcloud field accounts.payment_terms.
14. Telephone: The telephone number for the customer billing address. This corresponds to the field accounts.phone1 on CIMCloud.
15. Fax: The fax number for the customer billing address. This corresponds to the field accounts.fax on CIMCloud.
16. Email Address: The email address for the customer. For existing accounts this is the value synced from Sage. If this is blank or a new account on CIMcloud syncing to sage for the first time it will be the email address of the contact placing the first order. This corresponds to the field accounts.email_address on CIMCloud.
17. URL Address: This is the customer website URL. This corresponds to the field accounts.website in CIMcloud.
18. Credit Hold: This is the flag that indicates if the customer is on credit hold. Base CIMcloud functionality does not use this but there are add-ons that would allow this flag to be used for messaging or to prevent/limit ordering on CIMcloud. Base website functionality does allow for this status to display on the users Account Statement page. For existing accounts this will be the value synced from Sage for new Sage customers it will not be set. This corresponds to the field accounts.credit_hold in CIMcloud.
19. Customer Type: This is a Sage way of grouping customers. This is not used outside of custom workflows on the CIMcloud platform at this time and is not populated when we update/create a customer in Sage. This corresponds to the field accounts.type in CIMcloud.
20. Ship Code: This is the default ship via for the customer. This is synced to CIMcloud but is not currently used. It is not populated when creating new customers from CIMcloud in Sage. This corresponds to filed accounts.
21. Tax Exemption: This is currently synced to the website and is part of the order export but this field is no longer used and will not populate a value on new accounts. All tax exemptions are now handled through exemptions against the specific tax codes.
22. Price Level: Price Level is a customer grouping for pricing where a single pricing record can apply across multiple customers. This is synced to CIMcloud as the account price group and is used by the CIMcloud pricing engine in the same way. For existing Sage customers the value syncs to CIMcloud and is what is used for users on that account. Sage does allow for the value to be blank and for pricing to be applied based on this being blank. CIMcloud will populate the value “Blank” in this case, so if the value is not populate din Sage the Value in CIMcloud will be the word “Blank”. For accounts created on the site front users will get the default website pricing and when the records sync to Sage the ERP Questionnaire allows you to give us the default value for new accounts. You will also be able to update this is the settings section of the Worker Portal.
23. Discount Rate: This is a discount percentage that applies to all products where Allow Trade Discount is checked for this customer. This is used in CIMcloud for adjust price as well for the marked products. The default is this is applied to the price the user sees for each product. This is not displayed separately in the cart. This is synced to CIMcloud as field accounts.trade_adjustment
24. Ext: This is the billing address phone extension. By default this sync to CIMcloud for information only and is not handled as a separate field in the application. Since this is not a standard field it is not populated on customer creation. This is field accounts.phone_ext in CIMcloud.
25. Primary Ship To: This is the address designated as the primary shipping address on the customer record. This is available as information to workers in CIMcloud but is not used by the application. This is populated in CIMcloud as a mapping to the specific shipping address record synced to the CIMcloud. CImcloud does not populate this when creating or updating customer records in Sage.
26. Credit Limit: This is the credit limit of the customer. Base CIMcloud functionality does not use this beyond display but there are add-ons that would allow this field to be used for messaging or to prevent/limit ordering on CIMcloud. Base website functionality does allow for this to display on the users Account Statement page. For existing accounts this will be the value synced from Sage for new Sage customers it will not be set. This corresponds to the field accounts.credit_limit in CIMcloud.
27. Current Balance: (not shown in Figure 1). This value is populated on the account record when synced from Sage. It is not populated when creating/updating customer records in Sage. This is used for accuracy reporting where this value is compared the value of the invoices synced from sage.
28. Open Order Amount: (not shown in Figure 1). This value is populated on the account record when synced from Sage. It is not populated when creating/updating customer records in Sage. This is used for accuracy reporting where this value is compared to the value on the website based on sales orders synced from sage.
29. Aging Category Balances (4): (not shown in Figure 1). This value is populated on the account record when synced from Sage. It is not populated when creating/updating customer records in Sage. This is used for accuracy reporting where this value is compared the value of the invoices synced from sage.

Standard Field Mappings Spreadsheet

A spreadsheet with standard field mappings between Sage100 and CIMcloud can be found here:

Sage to CIMCloud Customer Fields