Each integrator can define Functions that can be called in a dynamic formula. These are built and maintained by the integrator and are documented in the subscription documentation.

This document provides a comprehensive reference for functions available in mapping formulas in iPaaS.com.

The most current list of functions can always be obtained from GET /integrators/v1/DynamicFormulas. That endpoint returns every function registered for your integration version, with its exact signature, parameter names and descriptions, and return type. Every signature, parameter name, and return type on this page is taken from that response. Because functions are registered per integration version, two integrations may not expose the same set - check the endpoint for the version you are working in rather than assuming a function is available.

Usage Notes

Async Functions

Many functions run asynchronously. These return a Task and must be awaited, or the formula receives a Task object instead of a value.

await GetSpaceportId_StringAsync("ADM-TL2", "Product Inventory", SpaceportSystemId)

The Async suffix is not a reliable indicator of whether a function is asynchronous. A number of functions return Task<...> without an Async suffix in the name, including ProductIdFromSku, VariantIdFromSku, ParentIdFromSku, ParentIdFromVariantId, SkuFromAlternateId, UnitFromAlternateId, GetProductType, CustomFieldValue, and the whole AlternateIdFrom... family. Check the return type rather than the name.

Throughout this page, any return type shown as Task<...> must be awaited. Where both a synchronous and an Async version of the same function exist, prefer the async version for better performance under high concurrency or with large datasets; both return the same value.

Error Handling

Functions with a Required suffix, such as CountryCodeRequired, throw an error if no results are found. The standard versions return null or a default value instead. The WithDefault variants let you supply your own fallback.

Deprecated Functions

Avoid using functions marked as deprecated. They remain callable but may be removed in future versions. See Deprecated functions for the current list and its replacements.

Performance Considerations

Use async variants when available for better performance
Prefer specific lookup functions over generic ones when possible
Cache results of expensive operations when feasible

The SpaceportSystemId variable

Functions that take a systemId parameter identify which external system to look up against. SpaceportSystemId is a built-in variable that resolves to your current external system, so you can pass it directly rather than hardcoding an ID.

The related tableName parameter is the name of the data type being looked up, such as Product, Customer, Transaction, or Product Inventory.

Condition strings

Functions that accept a condition parameter, such as FirstMatch and FieldFromFirstMatch, use == for equality. Inner quotes must be escaped.

System Classes

The following standard system classes and their methods are available by default:

System.Math - Mathematical operations and constants
System.String - String manipulation methods
System.Collections.ICollection - Collection operations
System.DateTime - Date and time operations

These come from the runtime rather than the function registry, so they do not appear in the DynamicFormulas response.

Working with null and missing values

Function	Signature	Returns	Notes
`Coalesce`	`(Object[] list)`	`object`	First non-null element. `Coalesce(ADDL_DESCR_2, ADDL_DESCR_1)`
`CoalesceToDateTime`	`(Object[] list)`	`DateTime`	First non-null element, converted to DateTime. `CoalesceToDateTime(PROF_DAT_1, PROF_DAT_2)`
`CoalesceToDecimal`	`(Object[] list)`	`Decimal`	First non-null element, converted to decimal. `CoalesceToDecimal(QuantityOnHand, 0)`
`CoalesceToInt`	`(Object[] list)`	`int`	First non-null element, converted to integer. `CoalesceToInt(QuantitySold, 0)`
`IsNull`	`(Object value)`	`bool`	Whether the object is null. `IsNull(CustomFields)`
`IsEmpty`	`(Object input)`	`bool`	Broader than `IsNull`. Null and DBNull return true, and so do edge default values that indicate emptiness: `Guid.Empty`, `DateTime.MinValue`, `DateTimeOffset.MinValue`, and the empty string. `IsEmpty(DESCR)`

Converting types

Function	Signature	Returns	Example
`ConvertToDecimal`	`(Object obj)`	`Decimal`	`ConvertToDecimal("7.5")`
`ConvertToDouble`	`(Object obj)`	`Double`	`ConvertToDouble("7.5")`
`ConvertToInt`	`(Object obj)`	`int`	`ConvertToInt("7")`
`ConvertToLong`	`(Object obj)`	`Int64`	`ConvertToLong(DOC_ID)`

To turn a flat string into a list, see Working with lists and collections. To convert between objects and JSON or XML, see Working with JSON, XML, and dictionaries.

Working with dates and times

Function	Signature	Returns	Notes
`CurrentDateTime`	`()`	`DateTime`	Current DateTime in the local time zone.
`CurrentDateTimeOffset`	`()`	`DateTimeOffset`	Current DateTimeOffset in the local time zone.
`DateOnly`	`(Object input)`	`Nullable<DateTime>`	Converts the input to a DateTime or DateTimeOffset, then returns only the date portion. `DateOnly(ExpirationDate)`
`DateTimeOffsetFromLocalDateTime`	`(Object input)`	`DateTimeOffset`	Local DateTime to DateTimeOffset. `DateTimeOffsetFromLocalDateTime(LST_MAINT_DT)`
`LocalDateTimeFromDateTimeOffset`	`(Object input)`	`Nullable<DateTime>`	DateTimeOffset to local DateTime. `LocalDateTimeFromDateTimeOffset(ImportedDateTimeOffset)`
`MaximumDateTime`	`()`	`DateTime`	`DateTime.MaxValue`. Useful as a sentinel for "no expiry".
`MinimumDateTime`	`()`	`DateTime`	`DateTime.MinValue`. Note that `IsEmpty` treats this value as empty.

Working with text

Matching

Function	Signature	Returns	Notes
`RegExMatch`	`(Object input, Object pattern)`	`bool`	Whether the input matches the regex pattern. `RegExMatch("The dog is running", "^The")` returns true.
`WildcardMatch`	`(Object input, Object pattern)`	`bool`	SQL-style likeness comparison. The SQL wildcard `%` is converted to the regex wildcard `.*` and passed to the regex engine, so input that is not regex-friendly is unsupported. `WildcardMatch("test1234", "%est1%")` returns true.

Transforming

Function	Signature	Returns	Notes
`RegExReplace`	`(Object input, Object pattern, Object replacement)`	`string`	Regex find and replace. Pass an empty string as the replacement to strip every match. `RegExReplace("The dog is running", "^The", "That")`
`RemovePrefix`	`(Object prefix, Object value)`	`string`	Removes a prefix if present, otherwise leaves the value unchanged. The prefix is the first argument. `RemovePrefix("BC", "BC123")` returns `"123"`.
`SubstringAfterLastMatch`	`(Object haystack, Object needle)`	`string`	The portion of a string after the last occurrence of a character. Useful for parsing compound IDs. `SubstringAfterLastMatch("DIR-L78-JWT-6GQ\|1", "\|")` returns `"1"`.
`Truncate`	`(Object input, Object maxLength)`	`string`	Truncates to the specified maximum length. Use this to fit a destination field limit. `Truncate("This is a really long string", 10)` returns `"This is a "`. Note the trailing space, which is the tenth character.
`RemoveNonASCIICharacters`	`(Object input)`	`string`	Removes all non-ASCII characters except tabs and carriage returns.
`RemoveNonprintableCharacters`	`(Object input)`	`string`	Removes non-printable characters.
`MD5Hash`	`(Object input)`	`string`	MD5 hash of the input. Non-string input is converted to a string first.

Doing math

The formula engine does not handle nullable numeric types cleanly, so these functions exist to make arithmetic safe. Prefer them over raw operators whenever a value may be null.

Function	Signature	Returns	Notes
`Larger`	`(Object a, Object b)`	`Decimal`	The larger of two numeric values. `Larger(QtyOnHnd, 0)` sends quantity on hand only when non-negative.
`TypesafeDivision`	`(Object numerator, Object denomonator)`	`Double`	Null-safe and type-safe division. The denominator cannot be null. Note the parameter spelling, which is how it is registered. `TypesafeDivision(EXT_PRC, QTY_SOLD)`
`TypesafeMultiplication`	`(Object a, Object b)`	`Double`	Null-safe and type-safe multiplication. `TypesafeMultiplication(PRC_1, QTY_SOLD)`

To sum a field across a collection, see SumFieldFromCollection in Working with lists and collections.

Working with lists and collections

Reading from a collection

Function	Signature	Returns	Notes
`First`	`(IEnumerable inputList)`	`object`	The first object in a collection. `First(Barcodes)`
`FirstMatch`	`(IEnumerable inputList, String condition)`	`object`	The first object matching the condition. Returns the entire object. `FirstMatch(Barcodes, "BARCOD_TYP == \"UPC\"")`
`FieldFromFirstMatch`	`(IEnumerable inputList, String condition, String fieldName)`	`object`	A single field from the first matching object. Use this rather than `FirstMatch` when you only need one value. `FieldFromFirstMatch(Barcodes, "BARCOD_ID == \"ITEM\"", "BARCOD")`
`GetValue`	`(Object Source, String FieldName)`	`object`	A field value by name from any object.
`SumFieldFromCollection`	`(ICollection iList, String fieldName)`	`Decimal`	Sums one field across every entry in a collection.

Building a collection

Function	Signature	Returns	Notes
`IntListFromString`	`(Object input)`	`List<int>`	Turns a string into a list of integers. Use for destination fields that take an array of ints when the source is a flat string or a static mapping. `IntListFromString("1,2,3")`
`StringListFromString`	`(Object input)`	`List<string>`	Same, for arrays of strings. `StringListFromString("[keyword1, keyword2]")`
`ConvertJsonStringToObjectList`	`(Type destinationType, Dictionary sourceDestinationDictionary, String json)`	`List<object>`	Parses a JSON string into a list of the destination type, mapping source fields to destination fields via the supplied dictionary.

Working with JSON, XML, and dictionaries

Function	Signature	Returns	Notes
`JSONToDictionary`	`(Object input)`	`Dictionary<string, string>`	Parses a JSON string into a string/string dictionary. `JSONToDictionary("{\"GiftCardId\":\"12345\"}")`
`ReadFromStringDictionary`	`(Object dictionary, Object key)`	`string`	The value for a key, or null if the key does not exist. `ReadFromStringDictionary(LineInfo, "GiftCardId")`
`ConvertJsonStringToDynamicObject`	`(String jsonString)`	`object`	Converts a JSON string to a dynamic ExpandoObject using Newtonsoft. Use when the shape is not known ahead of time.
`ConvertObjectToJsonString`	`(Object anyObject)`	`string`	Serializes any object to a JSON string using `System.Text`.
`BuildJsonFromList`	`(Object inputList, List propertyNames)`	`string`	Builds a JSON string from a list, including only the named properties.
`GetXMLFromObject`	`(Object o)`	`string`	Serializes an object to XML and returns it as a string.

Inspecting and validating data

Function	Signature	Returns	Notes
`ValidateDataType`	`(Object candidate, Type dataType, String methodName, String parameterName, Boolean throwError)`	`bool`	Whether the object belongs to the specified type. Recommended usage is to pass the method and parameter names and leave `throwError` as true, so failures surface clearly. When testing whether data matches one of several types, omit the name fields and handle the boolean instead.
`GetProductType`	`(Int64 id)`	`Task<string>`	The tracking method for an ID. Returns `"Product"` or `"Variant"`. Must be awaited.

See also IsNull and IsEmpty in Working with null and missing values.

Looking up products, SKUs, and alternate IDs

Every function in this section returns a Task and must be awaited, even though none of them carry an Async suffix.

Resolving IDs from a SKU

Function	Signature	Returns	Notes
`ProductIdFromSku`	`(String sku)`	`Task<string>`	The iPaaS.com product ID for a SKU.
`VariantIdFromSku`	`(String sku)`	`Task<string>`	The variant ID for a SKU.
`ParentIdFromSku`	`(String sku)`	`Task<string>`	The parent product ID for a SKU or alternate ID.
`ParentIdFromVariantId`	`(Int64 id)`	`Task<Nullable<Int64>>`	A variant's parent ID, from the variant ID.

Alternate IDs and units

The ...FromStockingUnit variants resolve against the item's stocking unit. The ...FromUnitName variants let you name a specific unit instead.

Function	Signature	Returns
`AlternateIdFromStockingUnit`	`(Object alternateIds, Object units, String alternateIdType)`	`Task<string>`
`AlternateIdFromUnitName`	`(Object alternateIds, String alternateIdType, String unitName)`	`Task<string>`
`ProductAlternateIdFromStockingUnit`	`(Int64 productId, String alternateIdType)`	`Task<string>`
`ProductAlternateIdFromUnitName`	`(Int64 productId, String alternateIdType, String unitName)`	`Task<string>`
`VariantAlternateIdFromStockingUnit`	`(Int64 productId, Int64 variantId, String alternateIdType)`	`Task<string>`
`VariantAlternateIdFromUnitName`	`(Int64 variantId, String alternateIdType, String unitName)`	`Task<string>`
`SkuFromAlternateId`	`(String alternateId)`	`Task<string>`
`UnitFromAlternateId`	`(String alternateId)`	`Task<string>`

Summing inventory

By product or variant

Choose between these on two axes: product vs variant, and whether negative inventory counts. The Full prefix means negative values are included.

Function	Negative values	Signatures
`SumInventoryForProduct`	Excluded	`(Int64 productId)` `(Int64 productId, Int64[] locationIds)` `(Int64 productId, String[] locations)`
`SumFullInventoryForProduct`	Included	`(Int64 productId)` `(Int64 productId, Int64[] locationIds)` `(Int64 productId, String[] locations)`
`SumInventoryForVariant`	Excluded	`(Int64 variantId)` `(Int64 variantId, Int64[] locations)` `(Int64 variantId, String[] locations)`
`SumFullInventoryForVariant`	Included	`(Int64 variantId)` `(Int64 variantId, Int64[] locations)` `(Int64 variantId, String[] locations)`

All four return Decimal. Omit the location argument to sum across every location, or pass location IDs or location names to scope the sum.

Each has an Async variant - SumInventoryForProductAsync, SumFullInventoryForProductAsync, SumInventoryForVariantAsync, SumFullInventoryForVariantAsync - accepting the same three signatures and returning Task<Decimal>.

From a supplied inventory collection

Function	Signature	Returns
`QuantityFromLocation`	`(List inventory, String[] locations)` `(List inventory, Int64[] locations)`	`Decimal`
`QuantityFromLocationAsync`	`(List inventory, String[] locations)`	`Task<Decimal>`

By location group, with safety stock

These sum by location group name rather than individual locations, and support safety stock. All return Task<Decimal> and must be awaited.

Function	Signature	Use when
`SumInventoryQuantityByLocationGroupNameAsync`	`(String type, String groupName, List inventory, Boolean allowNegative, String safetyType, Int32 safetyLevel)`	You already have an inventory collection.
`SumProductInventoryQuantityByLocationGroupNameAsync`	`(String type, String groupName, Int64 productId, Boolean allowNegative, String safetyType, Int32 safetyLevel)`	You have a product ID.
`SumVariantInventoryQuantityByLocationGroupNameAsync`	`(String type, String groupName, Int64 variantId, Boolean allowNegative, String safetyType, Int32 safetyLevel)`	You have a variant ID.

allowNegative plays the same role as the Full prefix above. safetyType and safetyLevel reserve a buffer quantity, and type selects which quantity is summed.

Kit parents

Function	Signature	Returns
`SumKitParentQuantityAsync`	`(String type, Int64 productId, String locationGroupName)`	`Task<Decimal>`
`SumKitParentQuantityForSingleLocationAsync`	`(String type, Int64 productId, Int64 locationId)`	`Task<Decimal>`

Kit parent availability is derived from its components, so use these rather than the plain product sums when the product is a kit.

Gift cards

Detecting gift cards on a transaction

Function	Signature	Returns	Notes
`HasGiftCardSaleLines`	`(List lines)`	`bool`	Whether the transaction lines include a gift card sale.
`IsGiftCardSaleOnlyOrder`	`(String type, List lines, List payments)`	`bool`	Whether the order consists only of gift card sales.
`IsGiftCardTicketWithPendingOrVoidLines` `IsGiftCardTicketWithPendingOrVoidLinesAsync`	`(String type, List lines)`	`bool` `Task<bool>`	Whether the transaction has gift card sales where the gift card is pending or void.
`IsGiftCardTicketWithUncapturedPayments`	`(String type, List lines, List payments)`	`bool`	Identifies gift card sale tickets by payment capture state.
`GiftCardLineTotal`	`(List lines)`	`Decimal`	Total price of all gift cards sold on the transaction.

Reading gift card details

Function	Signature	Returns	Notes
`GetGiftCardRedemptionNumber` `GetGiftCardRedemptionNumberAsync`	`(Dictionary methodInfo)`	`string` `Task<string>`	The gift card number for a redeemed gift card. Null checks the ID on the order, validates it exists, then converts it to the number.
`GiftCardTypeFromMethodInfo` `GiftCardTypeFromMethodInfoAsync`	`(Object methodInfo)`	`string` `Task<string>`	Looks up the gift card by the method info's `GiftCardId` and returns its type, such as Gift Card or Store Credit.
`GetExternalIdForGiftCardTicket` `GetExternalIdForGiftCardTicketAsync`	`(Nullable orderId, Int64 systemId)`	`string` `Task<string>`	Whether the order has an associated gift card ticket, and if so its external ID in the specified system.

Payments and deposits

Function	Signature	Returns	Notes
`GetPaymentTypeFromMethodName` `GetPaymentTypeFromMethodNameAsync`	`(String paymentMethodName)`	`string` `Task<string>`	The payment type for a method name.
`PaymentMethodNameSearchAsync`	`(String name)`	`Task<string>`	The first iPaaS.com payment method name found by case-insensitive search. Use when the external system's naming does not match exactly.
`HasAnyCapturedPaymentsAsync`	`(Object transactionId)`	`Task<bool>`	Whether the transaction has any Authorized payments.
`HasAnyCapturedPaymentsDepositAsync`	`(String transactionNumber, String type, List payments)`	`Task<bool>`	Whether a supplied payment list has any captured payments or gift cards. Only evaluated for tickets where `Type = Ticket`.
`IsAuthOnlyDeposit` `IsAuthOnlyDepositAsync`	`(String transactionNumber, String type, List payments)`	`bool` `Task<bool>`	Whether this is an authorization-only deposit ticket, so it can be filtered in the header.
`OrderHasOpenDepositTicket` `OrderHasOpenDepositTicketAsync`	`(Object id, Object spaceportSystemId)`	`bool` `Task<bool>`	Whether an open deposit ticket is associated with the order. Also confirms the child exists in the external system.

Discounts

Discounts apply at two levels: the transaction header and individual lines. Pick the function that matches the level you are mapping.

Detecting discounts

Function	Signature	Returns	Level
`ContainsHeaderDiscounts`	`(List discounts)`	`bool`	Header, from the transaction discount collection
`ContainsLineDiscounts`	`(List lines)`	`bool`	Any line, from the transaction lines
`ContainsLineDiscountsForSingleLine`	`(List discounts)`	`bool`	One line, from that line's discount collection

Summing discounts

Function	Signature	Returns	Level
`SumHeaderDiscounts`	`(List discounts)`	`Decimal`	Header, as an amount
`SumLineDiscounts`	`(List lines)`	`Decimal`	All lines, as an amount
`SumLineDiscountsForSingleLine`	`(List discounts)`	`Decimal`	One line, as an amount
`SumHeaderDiscountPercentage`	`(List discounts, Decimal subtotal)`	`Decimal`	Header, as a percentage of subtotal, rounded to 3 decimal places
`SumLineDiscountPercentageOfHeader`	`(List lines, Decimal subtotal)`	`Decimal`	All lines, as a percentage of order subtotal, rounded to 3 decimal places

Use the percentage variants when the destination system expects a discount rate rather than an amount.

Locations, states, and countries

Locations

Function	Signature	Returns	Notes
`LocationIdFromName` `LocationIdFromNameAsync`	`(String location)`	`Nullable<Int64>` `Task<Nullable<Int64>>`	The location ID for a location name. `LocationIdFromName("Main Store")`
`LocationNameFromId`	`(Nullable locationId)`	`string`	The reverse lookup: location name for a location ID.
`LocationIdInLocationGroupNameAsync`	`(Int64 locationId, String groupName)`	`Task<bool>`	Whether a location belongs to a named location group. Useful for routing rules.

Countries

Function	Signature	Returns	Behaviour when not found
`CountryCode`	`(String countryName)`	`string`	Returns null. `CountryCode("United States")` returns `"US"`.
`CountryCodeRequired`	`(String countryName)`	`string`	Throws an error. `CountryCodeRequired("Canada")` returns `"CA"`.
`CountryCodeWithDefault`	`(String countryName, String defaultCode)`	`string`	Returns your default. `CountryCodeWithDefault("Japan", "N/A")` returns `"JP"`.
`CountryNameFromCodeAsync`	`(String countryCode)`	`Task<string>`	The reverse lookup: country name from an ISO code.

States and provinces

Function	Signature	Returns	Notes
`StateAbbreviation`	`(String state)`	`string`	Supports all states and territories of the US and Canada. `StateAbbreviation("Georgia")` returns `"GA"`.
`StateName`	`(String state)`	`string`	The reverse lookup. `StateName("GA")` returns `"Georgia"`.

Looking up customers, employees, and transactions

Function	Signature	Returns	Notes
`iPaaSCustomerFromEmail` `iPaaSCustomerFromEmailAsync`	`(String emailAddress)`	`string` `Task<string>`	The iPaaS.com customer ID for an email address.
`iPaaSEmployeeFromEmailAsync`	`(String emailAddress)`	`Task<string>`	The employee ID for an email address. Async only.
`iPaaSTransactionFromNumberAsync`	`(String transactionNumber)`	`Task<string>`	The transaction ID for a transaction number. Async only.

Translating IDs between systems

These convert between iPaaS.com IDs and external system IDs. See The SpaceportSystemId variable for what to pass as systemId and tableName.

iPaaS.com ID to external ID

Function	Signature	Returns	Notes
`GetExternalId` `GetExternalIdAsync`	`(Object id, Object tableName, Object systemId)`	`string` `Task<string>`	`GetExternalId(144, "Product", SpaceportSystemId)`
`ExternalIdFromStructure`	`(List externalIdResponses, Int64 systemId)`	`string`	Pulls the ID for one system out of an external ID collection you already have. No lookup, so prefer this when the collection is available.

External ID to iPaaS.com ID

Function	Signature	Returns	Notes
`GetSpaceportId_String` `GetSpaceportId_StringAsync`	`(Object externalId, Object tableName, Object systemId)`	`string` `Task<string>`	Use these. They work for all tables, including those whose IDs are not longs. `GetSpaceportId_String("444\|555", "Product Inventory", SpaceportSystemId)`

GetSpaceportId and GetSpaceportIdAsync do the same job but only for tables with long IDs, and both are deprecated. See Deprecated functions.

Custom fields and settings

Custom fields

Function	Signature	Returns	Use when
`GetCustomFieldValue`	`(List customFields, String customFieldName)`	`string`	You already have the custom field collection. Returns null if the field does not exist. Registered for both request and response collection types. `GetCustomFieldValue(CustomFields, "Special Instructions")`
`CustomFieldValue`	`(String collectionType, String fieldName, String id)`	`Task<string>`	You only have a parent ID. This queries iPaaS.com, so it must be awaited.

Settings

Function	Signature	Returns	Notes
`Setting`	`(String SettingName)`	`string`	The value for a given setting. `Setting("DefaultTaxRate")`
`GetSubscriptionSettingValue`	`(String settingName, Boolean returnErrors)`	`string`	The value of a subscription-level setting.

Deprecated functions

These are still registered and callable, but should not be used in new mappings. Existing mappings should migrate.

Deprecated	Use instead	Why
`GetSpaceportId`	`GetSpaceportId_String`	Only works for tables whose IDs are longs.
`GetSpaceportIdAsync`	`GetSpaceportId_StringAsync`	Same limitation.
`CreateTypeFromJSON`	`ConvertJsonStringToObjectList` or `ConvertJsonStringToDynamicObject`	Replaced by the newer JSON conversion functions.
`CreateTypeFromKVP`	No direct replacement.	Contact support if you rely on this.

Functions

Table of Contents