Overview
When a transaction is created in iPaaS.com that meets all qualifying conditions — including at least one line item, a non-cancelled/non-refunded status, and a non-negative total — it is transferred to NetSuite as a Sales Order. This mapping collection handles the creation of Sales Orders including header-level details, address information, customer deposits, and line items.
NetSuite calculates its own tax amounts based on the customer, item, and shipping method configuration. Tax amounts cannot be set directly from iPaaS using the API. Template mappings include the iPaaS tax amount in the order memo, and the tax amount may also be uploaded to a transaction custom field. Consult with your NetSuite implementation team to implement overrides of the NetSuite-calculated tax amount after the sales order has been created.
Before You Begin
Ensure the following prerequisites are in place before configuring this integration.
iPaaS Requirements
Transaction must have at least one line item.
Transaction status must not be "Cancelled", "Refunded", or "Partially Refunded".
Transaction type must not be "Validated Return" or "Invoice".
Transaction number must not end with "-D" or "-GC".
Transaction total must be greater than or equal to 0.
Gift-card-only orders are excluded.
If a Payments collection is present, all payments must have a non-empty Description containing "House Account".
Line item SKUs should be linked to existing NetSuite items.
NetSuite Requirements
Customer records must exist in NetSuite (matched by External ID or email address).
Items referenced in line mappings must exist in NetSuite.
Order status values must be configured in the lookup translation table.
If using tax overrides, the required custom fields must be created (see Custom Fields Support).
If using fulfillment, warehouse locations must be configured with valid inventory.
Shipping methods must be configured to match iPaaS shipping method names.
Discount items must exist if discount mappings are used.
ID Format
When manually transferring a Transaction from iPaaS, enter the valid iPaaS Transaction (internal ID) into the input field on the iPaaS manual sync page.
External ID Format: The NetSuite Sales Order internal ID will be saved as the external ID in iPaaS.
Mapping Collection Status
Status: Enabled
Trigger Events: Create (Add)
Supported Child Collections
This flow uses three child collections to create the complete Sales Order record in NetSuite:
Data Requirements
The following fields are required for successful Sales Order creation.
Mandatory Header Fields
Entity_Id (NetSuite Customer ID)
OrderStatus_RefName (via lookup translation)
Recommended Header Fields
Memo
Email
Total
Subtotal
OtherRefNum (Transaction Number)
ShipMethod_RefName
ShippingCos/AltShippingCost
Mandatory Line Fields
Item_Id (NetSuite Item ID, resolved by SKU)
Quantity
Amount
System Caveats
iPaaS Caveats
Transaction Qualification: The mapping filter applies multiple conditions to determine whether a transaction qualifies for transfer. Transactions that are gift-card-only orders, cancelled, refunded, partially refunded, validated returns, or invoices are excluded. The transaction total must be non-negative, and if payments exist, all payment descriptions must contain "House Account."
Customer Resolution: The Entity_Id mapping uses a multi-step fallback to resolve the NetSuite Customer ID. It first attempts to look up the external ID using the CompanyId, then falls back to the CustomerId, and finally attempts a lookup by email address. If none of these resolve, the transfer will fail with an API exception.
NetSuite Caveats
Tax Calculation: NetSuite calculates tax amounts independently based on customer, item, and shipping configuration. iPaaS tax amounts are included in the order memo for reference and may be uploaded to custom fields, but they do not override NetSuite's calculated tax. Consult your NetSuite implementation team if tax overrides are required.
Discount Handling: The template uses a static NetSuite discount item ID ("241") as a catch-all for website discounts. Update this ID to match your specific NetSuite discount item. Discount mappings only fire when DiscountAmount is greater than 0.
Fulfillment Control: The CreateItemFulfillment and LocationId_Fulfillment fields are control fields. By default, the template sets CreateItemFulfillment to
falseand LocationId_Fulfillment to 0, meaning no fulfillment records are generated. See Configuration Examples for enabling fulfillment with inventory validation.
Integration Caveats
SKU Validation on Lines: Line item filters validate that each SKU exists in NetSuite before including the line. If a SKU is not linked to a NetSuite item, the line is flagged. The gift card line filter similarly validates gift certificate SKUs.
Commented-Out Code: Several dynamic formulas in this template contain commented-out example code for tax code mapping and fulfillment location logic. These are provided as starting points and must be updated to match your specific NetSuite configuration before uncommenting.
Custom Fields Support
This integration references several NetSuite custom fields for tax override functionality. These fields must be created in NetSuite before they can be populated.
Order-Level Tax Custom Fields
If using order-level tax collection in NetSuite, create the following transaction body custom fields:
custbody_otg_ipaas_tax_code: NetSuite Sales Order Header Custom Tax Code
custbody_otg_ipaas_tax_rate: NetSuite Sales Order Header Custom Tax Percent
Line-Level Tax Custom Fields
If using line-item-level tax collection in NetSuite, create the following transaction column custom fields:
custcol_otg_ipaas_tax_code: NetSuite Sales Order Line Custom Tax Code
custcol_otg_ipaas_tax_rate: NetSuite Sales Order Line Custom Tax Percent
Other Custom Fields
custbody_es_bt_auth_by_external_pnref: External payment authorization reference flag.
custbodycustbody_otg_ipaas_original_or: Stores the original iPaaS order total.
For the transfer of custom fields from iPaaS.com, create an iPaaS.com subscription custom field having the same name as the NetSuite custom field ID.
You may then create a mapping directly to the custom field:
Important! Free-Form text is the only custom field data type currently supported. |
Transaction custom field Ids may be found in the following NetSuite locations:
Sales Order Custom Fields
Customization > Lists, Records & Fields > Transaction Body Field
Sales Order Line Items Custom Fields
Customization > Lists, Records & Fields > Transaction Line Fields
After creating custom fields, you must enable them for Sale transactions.
On the Applies To subtab, select the transaction types where the field should be available. All custom transaction fields are automatically available in form customization regardless of what you select here.
Click Save.
Tip: If you ever need to remove the field, it's better to inactivate it than delete it — inactivating preserves the underlying data.
Authentication and Security
NetSuite uses token-based authentication (TBA) or OAuth 2.0 to authorize API requests during transfer operations. Ensure credentials are stored securely within the iPaaS credential manager.
Integration Flow
The integration processes iPaaS Transaction records as follows:
A transaction is created in iPaaS, and the mapping filter evaluates qualifying conditions (status, type, line count, total, payment descriptions).
iPaaS authenticates with NetSuite using configured credentials.
The NetSuite Customer ID is resolved via CompanyId external ID, CustomerId external ID, or email address lookup.
Order status is mapped via a lookup translation table.
Header fields are populated: memo (with payment summary and tax), email, totals, shipping method, and shipping cost.
Discount fields are conditionally populated if a discount amount exists.
Tax override custom fields are populated (header-level tax code and rate).
Address child collection maps, billing, and shipping addresses.
Customer deposit child collection maps payment amounts with account lookup.
Line item child collection maps each qualifying line (SKU validation, amount calculation, tax fields, fulfillment control fields).
Sales Order is created in NetSuite via API.
NetSuite Sales Order ID is saved as the External ID in iPaaS.
Transfer status and any errors are logged in iPaaS.
Mappings
Parent: NetSuite Add Sales Order From iPaaS
Purpose and Function
This mapping collection creates NetSuite Sales Orders from qualifying iPaaS Transactions. It maps header-level information including customer identification, order status, memo details, shipping, discounts, and tax override fields. Child collections handle addresses, payments, and line items.
Mapping Filter
if (
(
Payments == null ||
Payments.All(x =>
!string.IsNullOrWhiteSpace(x.Description) &&
x.Description.Contains("House Account")
)
) &&
Lines.Count > 0 &&
!IsGiftCardSaleOnlyOrder(Type, Lines, Payments) &&
!TransactionNumber.EndsWith("-D") &&
!TransactionNumber.EndsWith("-GC") &&
Type != "Validated Return" &&
Type != "Invoice" &&
Status != "Cancelled" &&
Status != "Refunded" &&
Status != "Partially Refunded" &&
Total >= 0
)
{
return true;
}
return false;
Description
This filter ensures that only qualifying transactions are transferred to NetSuite as Sales Orders. It validates that the transaction has at least one line item, is not a gift-card-only order, does not have a transaction number ending with "-D" or "-GC", is not a Validated Return or Invoice, is not Cancelled/Refunded/Partially Refunded, and has a non-negative total. If a Payments collection exists, all payments must have a non-empty description containing "House Account."
Mapping Type | Source (iPaaS) | Destination (NetSuite) | Description |
Static | true | custbody_es_bt_auth_by_external_pnref (custom field) | Sets the external payment authorization reference flag. Note: static values are treated as strings; use a dynamic formula ( |
Dynamic Formula |
| custbody_otg_ipaas_tax_rate (custom field) | Calculates the tax percentage from order totals, rounded to 2 decimal places. |
Dynamic Formula | // This is an example only and must be updated to your configuration! var country = GetCustomFieldValue(CustomFields, "NetSuite Tax Code Country"); var region = GetCustomFieldValue(CustomFields, "NetSuite Tax Code Region"); // Depending on your order source, normalize the country here country = CountryCodeRequired(country); // Map the country and region to a NetSuite tax code ID if (country == "US") { // This function will abbreviate US or Canada regions region = StateAbbreviation(region); switch (region) { case "NJ": return "-11"; // NJ in NetSuite break; case "NY": throw new Exception("Unmapped tax code"); // Throws an error break; default: // Default when no region match is found return "5"; break; } } // Add else if for each country // else if () ... else { // Default when no country match is found return "-7"; // Not taxable in NetSuite } | custbody_otg_ipaas_tax_code | Maps the header-level tax code. Default returns a static value; see Configuration Examples for region-based tax code mapping. |
Dynamic Formula | decimal total = 0; string paymentMethod = ""; string tax = "$0"; if (TaxAmount != null) tax = "$" + TaxAmount.ToString(); if (Payments != null) { foreach (var payment in Payments) { total += payment.Amount; payment.Method += " " + payment.Method + " "; } } if (total == 0) { return "Web Order " + TransactionNumber + " (" + paymentMethod + ") Tax: " + tax; } else { return "Web Order " + TransactionNumber + " $" + total + " (" + paymentMethod + ") Tax: " + tax; } | Memo | Generates a custom memo summarizing the transaction number, payment method(s), total payment amount, and tax. If no payments are recorded, the total is omitted. |
Field | TransactionNumber | OtherRefNum | Maps the iPaaS transaction number as a reference. |
Field | EmailAddress | Maps the customer email address. | |
Static | true | ShippingCostOverridden | Ensures the shipping cost from iPaaS overrides any NetSuite-calculated shipping. |
Field | Subtotal | Subtotal | Maps the transaction subtotal. |
Field | Total | Total | Maps the transaction total. |
Dynamic Formula | var companyId = await GetExternalIdAsync(CompanyId, "Company", SpaceportSystemId); if (companyId != null || companyId != "") return companyId; var customerId = await GetExternalIdAsync(CustomerId, "Customer", SpaceportSystemId); if (customerId == null || customerId == "") return await GetCustomerIdByEmail(EmailAddress); else return customerId; | Entity_Id | (Required) Resolves the NetSuite Customer ID. Falls back through CompanyId external ID → CustomerId external ID → email address lookup. Transfer fails if no match is found. |
Lookup Translation | OrderStatus_RefName | Maps iPaaS order status to NetSuite order status using the "NetSuite Order Status From iPaaS" translation table (ID: 22270). | |
Field | ShippingAmount | ShippingCost | Maps the shipping amount. |
Dynamic Formula | FieldFromFirstMatch(Addresses, "IsPrimaryShipping == true", "ShippingMethod") | ShipMethod_RefName | Retrieves the shipping method name from the primary shipping address using the FieldFromFirstMatch function. |
Dynamic Formula |
| DiscountAmount | Passes through the discount amount when present; returns null otherwise. |
Field | ShippingAmount | AltShippingCost | Maps the shipping amount. |
Dynamic Formula |
| DiscountRate | Returns the discount amount as a negative value for NetSuite's discount rate field. |
Dynamic Formula | if (DiscountAmount != null && DiscountAmount > 0) { return "241"; // NetSuite catch-all website discount item } else return null; | DiscountItem_Id | Returns a static NetSuite discount item ID ("241") when a discount exists. Update this ID to match your NetSuite configuration. |
Dynamic Formula |
| DiscountItem_Rate | Passes through the discount amount as the item rate when present. |
Static | More | ShipMethod_ShippingCarrier | Sets a static shipping carrier value. |
Lookup Translation Table
Source (iPaaS.com | Destination (NetSuite) |
Cancelled | Pending Approval |
Refunded | Pending Approval |
Complete | Pending Fulfillment |
Partially Refunded | Pending Approval |
Pending | Pending Fulfillment |
Child 1: NetSuite Add Sales Order Address From iPaaS
Purpose and Function
This mapping collection maps iPaaS address records to NetSuite Sales Order addresses. It handles both billing and shipping addresses, using the IsPrimaryBilling and IsPrimaryShipping flags to identify address roles.
Mapping Type | Source (iPaaS) | Destination (NetSuite) | Description |
Field | Address1 | Addr1 | Maps the first address line. |
Field | Address2 | Addr2 | Maps the second address line. |
Dynamic Formula | if (FirstName != null && FirstName != "" && LastName != null && LastName != "") { return FirstName + " " + LastName; } else { return Company; } | Addressee | Concatenates FirstName and LastName if both are present; defaults to Company name otherwise. |
Field | City | City | Maps the city. |
Field | Region | State | Maps the state or region. |
Field | PostalCode | Zip | Maps the postal code. |
Field | IsPrimaryBilling | IsPrimaryBilling | Identifies the primary billing address. |
Field | IsPrimaryShipping | IsPrimaryShipping | Identifies the primary shipping address. |
Field | Address3 | Addr3 | Maps the third address line. |
Child 2: NetSuite Add Sales Order Customer Deposit From iPaaS
Purpose and Function
This mapping collection maps iPaaS payment records to NetSuite customer deposit records associated with the Sales Order. The account is resolved via lookup translation.
NOTE: Update filters as needed to transfer only the desired payments. |
Mapping Type | Source (iPaaS) | Destination (NetSuite) | Description |
Field | Amount | Payment | Maps the payment amount. |
Lookup Translation | Account_Id | Maps the payment account using the "NetSuite Sales Order Payment Account From iPaaS" translation table (ID: 22272). |
Lookup Translation Table
Source (iPaaS.com | Destination (NetSuite |
Cash | 4 |
Check | 5 |
American Express | 4 |
Discover | 4 |
Master Charge | 4 |
Visa | 4 |
Cash on delivery | 4 |
Child 3: NetSuite Add Sales Order Line From iPaaS
Mapping Filter
var netSuiteProductId = await GetNetSuiteProductIdBySku(Sku);
if(netSuiteProductId != null){
return true;
}
return true;
throw new Exception("Sku "+Sku+" is not linked to a NetSuite item id");
}
var netSuiteProductId = await GetNetSuiteProductIdBySku(Sku);
if(netSuiteProductId != null){
return true;
}
else{
return true;
throw new Exception("Sku "+Sku+" is not linked to a NetSuite item id");
}
Description
This filter checks whether the Sku of the iPaaS transaction line exists in NetSuite by retrieving the corresponding NetSuite product ID. If the Sku is found, the transfer process continues. If the Sku is not linked to a NetSuite item ID, the process attempts to throw an exception. The exception is intended to prevent the iPaaS transaction line from being created as a Sales Order line in NetSuite when the Sku does not exist
Mapping Type | Source (iPaaS) | Destination (NetSuite) | Description |
Dynamic Formula | // Tax percent is provided if (TaxPercent != null && TaxPercent != 0) { return Math.Round(TaxPercent, 2); } // Estimated tax amount is provided — calculate percentage if (EstimatedTaxAmount != null && EstimatedTaxAmount != 0) { var result1 = (EstimatedTaxAmount / ExtendedPrice) * 100; return Math.Round(result1, 2); } // No line tax information — use order header amounts if (TaxPercent == null || TaxPercent == 0 || EstimatedTaxAmount == null || EstimatedTaxAmount == 0) { var result2 = (Parent.TaxAmount / Parent.Subtotal) * 100; return Math.Round(result2, 2); } | custcol_otg_ipaas_tax_code | Line-level tax code. Default returns null; see Configuration Examples for region-based mapping. |
Dynamic Formula | //Some integrations may or may not provide line tax information. //In situations where it is not, the order headers are used. //Adjust the header total filters as needed. //Tax percent is provided if (TaxPercent != null && TaxPercent != 0) { return Math.Round(TaxPercent, 2); } // Estimated tax amount is provided — calculate percentage if (EstimatedTaxAmount != null && EstimatedTaxAmount != 0) { var result1 = (EstimatedTaxAmount / ExtendedPrice) * 100; return Math.Round(result1, 2); } // No line tax information — use order header amounts if (TaxPercent == null || TaxPercent == 0 || EstimatedTaxAmount == null || EstimatedTaxAmount == 0) { var result2 = (Parent.TaxAmount / Parent.Subtotal) * 100; return Math.Round(result2, 2); } | custcol_otg_ipaas_tax_rate | Calculates line-level tax rate using TaxPercent, EstimatedTaxAmount, or order header totals as fallbacks. |
Dynamic Formula |
| CreateItemFullfilment | Control field: set to |
Dynamic Formula |
| LocationId_Fullfilment | Control field for fulfillment location. Default returns 0 (no fulfillment). See Configuration Examples for inventory-validated location lookup. |
Dynamic Formula |
| ItemRecieved | Marks the item as received. |
Field | Qty | Quantity | Maps the line item quantity. |
Field | Description | Description | Maps the line item description. |
Dynamic Formula | // ExtendedPrice //Some iPaaS transactions include discounted amounts in the //line item extended price. When passing a header discount only to //NetSuite, a non-discounted item price should often be used. //Adjust according to your source line item discount handling! return Qty * OriginalUnitPrice; | Amount | Calculates the line amount using non-discounted unit price. Adjust if your source includes discounted extended prices. |
Dynamic Formula |
| Item_Id | (Required) Retrieves the NetSuite item ID by SKU. |
Configuration Examples
The following code examples are provided as templates in the integration mapping. They are commented out by default and must be customized to match your NetSuite configuration before use.
Region-Based Tax Code Mapping (Header or Line Level)
This example maps iPaaS tax information to a NetSuite tax code ID based on the customer's country and region. It applies to both custbody_otg_ipaas_tax_code (header) and custcol_otg_ipaas_tax_code (line) fields.
// Depending on your order source, normalize the country here
country = CountryCodeRequired(country);
// Map the country and region to a NetSuite tax code ID
if (country == "US") {
// This function will abbreviate US or Canada regions
region = StateAbbreviation(region);
switch (region) {
case "NJ":
return "-11"; // NJ in NetSuite
break;
case "NY":
throw new Exception("Unmapped tax code"); // Throws an error
break;
default: // Default when no region match is found
return "5";
break;
}
}
// Add else if for each country
// else if () ...
else { // Default when no country match is found
return "-7"; // Not taxable in NetSuite
}
Customization notes
Add
caseentries for each state or region requiring a specific tax code IDAdd
else ifblocks for additional countriesEnsure "NetSuite Tax Code Country" and "NetSuite Tax Code Region" custom fields exist in iPaaS
For line-level usage, reference
Parent.CustomFieldsinstead ofCustomFields
Fulfillment Location with Inventory Validation
This example validates that a product exists at a specific warehouse location with non-zero inventory before returning the location ID for fulfillment.
if (Sku != null && !string.IsNullOrEmpty(Sku)) {
var LocationId = await CheckLocationQuantityByLocationNameAndProductBySku(
"US Warehouse", Sku.ToString()
);
if (LocationId != null && LocationId > 0) {
return LocationId;
}
}
Customization notes
Replace
"US Warehouse"with your NetSuite warehouse location nameThe function checks product existence and inventory quantity at the specified location
If inventory quantity is zero, an exception is thrown to prevent fulfillment at an empty location
The line item SKU must have its external ID set, and the product must exist in NetSuite
Set
CreateItemFulfillmenttotruein the same collection to enable fulfillment generation
Error Handling
Customer Not Found
Entity_Id is null or empty — API exception on Sales Order creation.
Description: None of the fallback methods (CompanyId, CustomerId, email) resolved a NetSuite Customer ID.
Resolution: Ensure the customer exists in NetSuite and that the iPaaS CompanyId or CustomerId has a valid external ID mapped. Verify the email address matches the NetSuite customer record.
SKU Not Linked to NetSuite Item
Sku [SKU] is not linked to a NetSuite item id.
Description: The line item SKU could not be resolved to a NetSuite item ID.
Resolution: Ensure the product exists in NetSuite and that the iPaaS product's external ID is set to the NetSuite item internal ID. Run a product sync if needed.
Invalid Discount Item ID
Discount item not found or invalid.
Description: The static discount item ID ("241") does not match a valid NetSuite item.
Resolution: Update the DiscountItem_Id mapping formula to use your NetSuite discount item's internal ID.
Unmapped Tax Code
Unmapped tax code (thrown by example formula).
Description: The region-based tax code mapping encountered a region without a configured tax code.
Resolution: Add the missing region to the tax code switch statement in the dynamic formula.
Invalid Price List or Tax Configuration
Transfer fails with NetSuite validation errors.
Description: NetSuite rejected the Sales Order due to missing or invalid field values.
Resolution: Review NetSuite's error response for specific field validation failures. Common causes include missing required custom fields, invalid tax codes, or misconfigured shipping methods.
Validation and Testing
Validation Rules
Customer Resolution The Entity_Id field uses a multi-step fallback (CompanyId → CustomerId → email). Ensure at least one resolution path is valid for each transaction.
SKU Validation Line items are validated against NetSuite before inclusion. The line filter checks that each SKU is linked to a NetSuite item.
Tax Field Dependencies Tax override custom fields must exist in NetSuite before they can be populated. The tax rate calculation falls back through line-level TaxPercent → EstimatedTaxAmount → header-level totals.
Fulfillment Dependencies Fulfillment is only generated when CreateItemFulfillment is
trueand LocationId_Fulfillment returns a valid location ID with non-zero inventory.
Validation Checklist
Entity_Id resolves to a valid NetSuite Customer ID for test transactions.
The order status lookup translation table is configured with all expected iPaaS statuses.
All line item SKUs are linked to NetSuite items (external IDs set).
Discount item ID matches a valid NetSuite item (update from default "241").
Tax override custom fields exist in NetSuite if tax mappings are active.
Shipping methods in iPaaS match configured NetSuite shipping methods.
The payment account lookup translation table is configured.
Gift certificate SKUs are linked to NetSuite gift certificates (if gift card lines are used).
Fulfillment control fields are configured appropriately (default: disabled).
Test Scenarios
Scenario 1: Standard Sales Order Creation
Sync a qualifying transaction from iPaaS with standard line items. Sales Order should be created in NetSuite with correct header details, addresses, line items, and customer deposit. Verify the memo contains payment summary and tax information.
Scenario 2: Order with Discount
Sync a transaction with a non-zero DiscountAmount. Verify the discount item, rate, and amount are applied correctly on the Sales Order.
Scenario 3: Customer Fallback Resolution
Sync a transaction where the CompanyId external ID does not exist. Verify the system falls back to CustomerId and then to email address lookup.
Scenario 4: SKU Not Found (Failure Case)
Sync a transaction with a line item SKU that does not exist in NetSuite. Verify the line is handled according to the filter behavior and an appropriate error is logged.
Scenario 5: Tax Override Fields
Sync a transaction and verify that custbody_otg_ipaas_tax_rate is calculated correctly from TaxAmount and Subtotal. If line-level tax fields are active, verify the fallback logic (TaxPercent → EstimatedTaxAmount → header totals).
Additional Notes
The discount item ID ("241") is a template default. Update it to match your NetSuite configuration.
The
custbody_es_bt_auth_by_external_pnreffield is set as a static string true. If a boolean is required, use a dynamic formula:return true;.The line filter for standard lines currently returns
truebefore the exception can execute when a SKU is not found. Carefully review and test this behavior for your implementation.Ensure dynamic formulas return valid output; null or invalid returns may cause transfer failures.
The ShipMethod_ShippingCarrier is statically set to More. Update this if your configuration uses a different carrier.
Payment filters should be reviewed and updated to transfer only the desired payment types.