ERP Custom Field Mappings

Overview

This article provides details on the process for adding custom field mappings to the queries used to sync data from the ERP to the website.

Most ERP integrations with the CIMcloud platform use SQL style queries to sync data from the ERP database to CIMcloud.  The base query syncs critical information needed for the website function properly and cannot be edited, but additional field mappings can be added.  If additional customization is needed, please contact CIMcloud support.

Creating Custom Field Mappings

To Website Configurations

ERP-to-Website custom field mappings can be added from the Worker Portal for sites integrated with Sage 100 or NetSuite.  For Acumatica, custom field mappings can be added to the Generic Inquiries that are configured in Acumatica.

1. In the CIMcloud Workspace, select the Settings Workspace from the left rail, then navigate to System-Wide Settings > To Web Configs
2. Click Edit on the to-web config you wish to update with custom mappings.
3. Scroll to the SQL Settings section and verify the CIMcloud Table Name displays the table you want to sync the new fields to.
4. Review the SQL Query to determine which ERP tables are available, and which table alias needs to be used when adding new fields.
5. Toggle the Enable Custom Field Mappings option to “True”
6. Enter the new mappings under Custom Field Mappings 
7. Click Validate Query to test the full query with the custom mappings using the CIMcloud Query Builder utility
   1. This button opens a new window that displays
      1. The generated query (read only)
      2. The results when this query is run against the ERP
   2. Confirm the query returns the expected results
8. Enter a comment with details about the change
9. Submit the page

From Website Configurations

The CIMcloud platform allows customization to support passing additional fields to the ERP during order import to Sage 100, NetSuite, and Acumatica.  Custom fields can be added to:

• Order Headers
• Order Detail Lines
• Accounts
• Shipping Addresses

A SQL query is run against the website database to build a payload containing the order data, which is then transformed by the sync tool into a format that is compatible with the ERP.  Since the data transformation is performed after the SQL query is run, the field names in this query are abstracted more than the to-website configurations, and a different format must be used when creating custom mappings.  In general, each custom field must contain an aliased column for a minimum of 2 values:

• Name – the field name in the ERP
  • Note: for Sage 100, UDFs containing strings must include a “$” at the end (e.g. UDF_FLD_1$)
• Value – the value to be posted
• Cast (optional) – Can be used to specify the data type used when sending the field to the ERP (e.g. string, date, decimal, int).  The default type is string
• Post (optional) – Can be used to conditionally add the value to the order payload.  This can be used to prevent overwriting existing data or posting blank values.
  • Set to “1” to post the value
  • Set to “0” to omit the field from the post

Examples of the format used for custom mappings on each table are below.  Note the UDF1 portion of these examples must be incremented when adding multiple mappings to a single table (e.g. UDF1, UDF2, UDF3…)

Sales Order Header

• UDFHeader_UDF1_Name
• UDFHeader_UDF1_Value
• Optional: UDFHeader_UDF1_Cast
• Optional: UDFHeader_UDF1_PostUDFMapping

Example:

'UDF_USERNAME$' as UDFHeader_UDF1_Name,
c.username as UDFHeader_UDF1_Value,
case when left(c.username,14) = 'zz_newaccount_' then 0 else 1 end as UDFHeader_UDF1_PostUDFMapping -- don't populate UDF for guest checkout

Sales Order Detail

• UDFDetail_UDF1_Name
• UDFDetail_UDF1_Value
• Optional: UDFDetail_UDF1_Cast
• Optional: UDFDetail_UDF1_PostUDFMapping

Example:

'UDF_CS_ORDERED' as UDFDetail_UDF1_Name,
p.searchfield3 as UDFDetail_UDF1_Value,
'decimal' as UDFDetail_UDF2_FieldCast

Accounts (Customer)

• UDFCustomer_UDF1_Name
• UDFCustomer_UDF1_Value
• Optional: UDFCustomer_UDF1_Cast
• Optional: UDFCustomer_UDF1_PostUDFMapping

Example:

'UDF_FIRSTNAME$' AS UDFCustomer_UDF1_Name,
left(c.c_f_nm, 30) AS UDFCustomer_UDF1_Value

Shipping Addresses

• UDFShipTo_UDF1_Name
• UDFShipTo_UDF1_Value
• Optional: UDFShipTo_UDF1_Cast
• Optional: UDFShipTo_UDF1_PostUDFMapping

Example:

'UDF_DELIVERY_INSTR$' AS UDFShipTo_UDF1_Name,
left(sa.opt1, 30) AS UDFShipTo_UDF1_Value

Additional Examples

Multiple UDFs on a single table (note the use of UDF1, UDF2)

'UDF_FREIGHT_TERMS$' as UDFHeader_UDF1_Name,
'P' as UDFHeader_UDF1_Value,

'UDF_ORDER_TYPE_DESC$' as UDFHeader_UDF2_Name,
(CASE when o.opt1 = 'sample' THEN 'SAMPLE' ELSE 'REGULAR' END) as UDFHeader_UDF2_Value,

'UDF_SALES_TYPE$' as UDFHeader_UDF3_Name,
'Residential' as UDFHeader_UDF3_Value

Limitations

Only SQL expression fragments are allowed in Custom Field Mappings.  Subselects, Unions, Inserts, Updates, etc are not allowed.