ERP Custom Field Mappings

A preview of this functionality is available as of version 4.16. If you are interested in getting access let us know.

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 within 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.

Custom mappings are available starting in release 2026.06 (version 4.16.0) and requires the “ERP Custom Field Mappings” bundle.

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.  Steps for updating Acumatica Generic Inquries can be found in the Editing CIMcloud Generic Inquiries in Acumatica article

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. Save the changes

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 that contain string values must include a “$” at the end of this name (e.g. UDF_FLD_1$).  If the UDF stores a numerical value in Sage this is not needed.
• 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…)

In general the field alias template for each From Web UDF mappings is:

UDF<TablePrefix>_UDF<index>_<FieldPart>

• TablePrefix values:
  • Header
  • Detail
  • Customer
  • ShipTo
• Index starts at “1” and must be incremented for each additional mapping
• FieldPart values:
  • Name
  • Value
  • Cast
  • PostUDFMapping

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.