Functions

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

Many functions have async variants (ending with "Async"). Use these for better performance in scenarios with high concurrency or when dealing with large datasets.

Functions with "Required" suffix (e.g., CountryCodeRequired) will throw errors if no results are found, while standard versions return null or default values.

Avoid using functions marked as deprecated. They may be removed in future versions.

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

Syntax: FieldFromFirstMatch(IEnumerable inputList, String condition, String fieldName)
​Returns: Field value from first matching item
​Description: Returns a specific field from the first item in a list that matches the given condition.
​Example: FieldFromFirstMatch(Barcodes, "BARCOD_ID = \"ITEM\"", "BARCOD")

Syntax: FirstMatch(IEnumerable inputList, String condition)
​Returns: Complete matching object
​Description: Returns the entire first object that matches the condition (use FieldFromFirstMatch to get a specific field).

Example: FirstMatch(Barcodes, "BARCOD_TYP == \"UPC\""

Syntax: GetValue(Object Source, String FieldName)
​Returns: Field value
​Description: Retrieves a field value by name from the specified object.

Syntax: SumFieldFromCollection(ICollection iList, String fieldName)
​Returns: Sum of field values
​Description: Calculates the sum of a specific field across all entries in a collection.

The following functions are deprecated and should not be used:

Syntax: Coalesce(Object[] list)
​Returns: object
​Description: Returns the first non-null element from the array.
​Example: Coalesce(ADDL_DESCR_2, ADDL_DESCR_1)

Syntax: CoalesceToDateTime(Object[] list)
​Returns: DateTime
​Description: Returns the first non-null element, converted to DateTime.
​Example: CoalesceToDateTime(PROF_DAT_1, PROF_DAT_2)

Syntax: CoalesceToDecimal(Object[] list)
​Returns: decimal
​Description: Returns the first non-null element, converted to decimal.
​Example: CoalesceToDecimal(QuantityOnHand, 0)

Syntax: CoalesceToInt(Object[] list)
​Returns: int
​Description: Returns the first non-null element, converted to integer.
​Example: CoalesceToInt(QuantitySold, 0)

Syntax: ConvertToDecimal(Object obj)
​Returns: decimal
​Description: Converts an object to a decimal value.
​Example: ConvertToDecimal("7.5")

Syntax: ConvertToDouble(Object obj)
​Returns: double
​Description: Converts an object to a double value.
​Example: ConvertToDouble("7.5")

Syntax: ConvertToInt(Object obj)
​Returns: int
​Description: Converts an object to an integer value.
​Example: ConvertToInt("7")

Syntax: ConvertToLong(Object obj)
​Returns: long
​Description: Converts an object to a long (int64) value.
​Example: ConvertToLong(DOC_ID)

Syntax: CurrentDateTime()
​Returns: DateTime
​Description: Returns current DateTime in local time zone.
​Example: CurrentDateTime()

Syntax: CurrentDateTimeOffset()
​Returns: DateTimeOffset
​Description: Returns current DateTimeOffset in local time zone.
​Example: CurrentDateTimeOffset()

Syntax: DateOnly(Object input)
​Returns: DateTime (nullable)
​Description: Extracts only the date portion from DateTime or DateTimeOffset.
​Example: DateOnly(ExpirationDate)

Syntax: DateTimeOffsetFromLocalDateTime(Object input)
​Returns: DateTimeOffset
​Description: Converts local DateTime to DateTimeOffset.
​Example: DateTimeOffsetFromLocalDateTime(LST_MAINT_DT)

Syntax: LocalDateTimeFromDateTimeOffset(Object input)
​Returns: DateTime (nullable)
​Description: Converts DateTimeOffset to local DateTime.
​Example: LocalDateTimeFromDateTimeOffset(ImportedDateTimeOffset)

Syntax: MaximumDateTime()
​Returns: DateTime
​Description: Returns DateTime.MaxValue.
​Example: MaximumDateTime()

Syntax: MinimumDateTime()
​Returns: DateTime
​Description: Returns DateTime.MinValue.
​Example: MinimumDateTime()

Syntax: First(IEnumerable inputList)
​Returns: object
​Description: Returns the first object in a collection.
​Example: First(Barcodes)

Syntax: IntListFromString(Object input)
​Returns: List<int>
​Description: Converts string to list of integers. Useful for array fields from string sources.
​Example: IntListFromString("1,2,3")

Syntax: StringListFromString(Object input)
​Returns: List<string>
​Description: Converts string to list of strings for array fields.
​Example: StringListFromString("[keyword1, keyword2]")

Syntax: IsEmpty(Object input)
​Returns :bool
​Description: Checks if input represents an empty value (null, DBNull, empty string, Guid.Empty, DateTime.MinValue, etc.).
​Example: IsEmpty(DESCR)

Syntax: IsNull(Object value)
​Returns: bool
​Description: Determines if the object is null.
​Example: IsNull(CustomFields)

Syntax: RegExMatch(Object input, Object pattern)
​Returns: bool
​Description: Tests if input string matches the regex pattern.
​Example: RegExMatch("The dog is running", "^The") returns 1 (true) because it matches the beginning of the string.

Syntax: RemovePrefix(Object prefix, Object value)
​Returns: string
​Description: Safely removes prefix from string if present.
​Example: RemovePrefix("BC", "BC123") returns "123"

Syntax: SubstringAfterLastMatch(Object haystack, Object needle)
​Returns: string
​Description: Returns portion of string after the last occurrence of specified character.
​Example: SubstringAfterLastMatch("DIR-L78-JWT-6GQ|1", "|") returns "1"

Syntax: Truncate(Object input, Object maxLength)
​Returns: string
​Description: Truncates input string to specified maximum length.
​Example: Truncate("This is a really long string", 10) returns “This is a “. Note the space at the end, which is the 10th character.

Syntax: WildcardMatch(Object input, Object pattern)
​Returns: bool
​Description: SQL-style wildcard matching (% becomes .* in regex).
​Example: WildcardMatch("test1234", "%est1%") returns true.

Syntax: Larger(Object a, Object b)
​Returns: decimal
​Description: Returns the larger of two numeric values.
​Example: Larger(QtyOnHnd, 0) ensures non-negative value.

Syntax: TypesafeDivision(Object numerator, Object denominator)
​Returns: double
​Description: Performs null-safe and type-safe division.
​Example: TypesafeDivision(EXT_PRC, QTY_SOLD)

Syntax: TypesafeMultiplication(Object a, Object b)
​Returns: double
​Description: Performs null-safe and type-safe multiplication.
​Example: TypesafeMultiplication(PRC_1, QTY_SOLD)

Syntax: JSONToDictionary(Object input)
​Returns: Dictionary<string, string>
​Description: Parses JSON string into string/string dictionary.
​Example: JSONToDictionary("{\"GiftCardId\":\"12345\"}")

Syntax: ReadFromStringDictionary(Object dictionary, Object key)
​Returns: string
​Description: Returns value for specified key from dictionary (null if key doesn't exist).
​Example: ReadFromStringDictionary(LineInfo, "GiftCardId")

Syntax: ProductIdFromSku(String sku)
​Returns: string
​Description: Returns the iPaaS.com product ID for a given SKU.
​Example: ProductIdFromSku("PROD123")

Syntax: VariantIdFromSku(String sku)
​Returns: string
​Description: Returns the iPaaS.com variant ID for a given SKU.
​Example: VariantIdFromSku("VAR123")

Syntax: ParentIdFromSku(String sku)
​Returns: string
​Description: Returns iPaaS.com parent product ID for a given SKU or alternate ID.
​Example: ParentIdFromSku("PARENT123")

Syntax: ParentIdFromVariantId(Int64 id)
​Returns: Product parent ID
​Description: Looks up a variant's parent ID from the variant ID.
​Example :ParentIdFromVariantId(12345)

Syntax: SumInventoryForProduct(Int64 productId)
​Returns: decimal
​Description: Returns sum of positive inventory for a product (negative values excluded).
​Example: SumInventoryForProduct(12345)

Syntax: SumFullInventoryForProduct(Int64 productId)
​Returns: decimal
​Description: Returns sum of all inventory for a product (including negative values).
​Example: SumFullInventoryForProduct(12345)

Syntax: SumInventoryForVariant(Int64 variantId)
​Returns: decimal
​Description: Returns sum of positive inventory for a variant (negative values excluded).
​Example: SumInventoryForVariant(67890)

Syntax: SumFullInventoryForVariant(Int64 variantId)
​Returns: decimal
​Description: Returns sum of all inventory for a variant (including negative values).
​Example: SumFullInventoryForVariant(67890)

Syntax: QuantityFromLocation(List inventory, String[] locations)
​Returns: decimal
​Description: Determines quantity of an item from specified location names.
​Example: QuantityFromLocation(inventory, ["Warehouse", "Store"])

Syntax: AlternateIdFromStockingUnit(Object alternateIds, Object units, String alternateIdType)
​Returns: string
​Description: Returns an iPaaS.com alternate ID of specified type for the stocking unit from existing product data.

Syntax: ProductAlternateIdFromStockingUnit(Int64 productId, String alternateIdType)
​Returns: string
​Description: Returns the iPaaS.com alternate ID of specified type for the stocking unit for a specified product ID.

Syntax: VariantAlternateIdFromStockingUnit(Int64 productId, Int64 variantId, String alternateIdType)
​Returns: string
​Description: Returns the iPaaS.com alternate ID of specified type for the stocking unit for specified product and variant IDs.

Syntax: SkuFromAlternateId(String alternateId)
​Returns:s tring
​Description: Returns the iPaaS.com SKU based on the alternate ID provided.

Syntax: UnitFromAlternateId(String alternateId)
​Returns: string
​Description: Returns the iPaaS.com unit name based on the alternate ID provided.

Syntax: GiftCardLineTotal(List lines)
​Returns: decimal
​Description: Returns total price of all gift cards sold on the transaction.
​Example: GiftCardLineTotal(TransactionLines)

Syntax: HasGiftCardSaleLines(List lines)
​Returns :bool
​Description: Determines if the transaction lines include a sale of a gift card.
​Example: HasGiftCardSaleLines(TransactionLines)

Syntax: GetGiftCardRedemptionNumber(Dictionary methodInfo)
​Returns: string
​Description: Returns the gift card number for redeemed gift cards with null checking and validation.

Syntax: GiftCardTypeFromMethodInfo(Object methodInfo)
​Returns: string
​Description: Determines gift card type (e.g., Gift Card or Store Credit) based on method info values.

Syntax: GetPaymentTypeFromMethodName(String paymentMethodName)
​Returns: string
​Description: Determines the payment type based on the method name.
​Example: GetPaymentTypeFromMethodName("Credit Card")

Syntax: PaymentMethodNameSearchAsync(String name)
​Returns:string?
​Description: Returns the first iPaaS.com payment method name found using case-insensitive search.

Syntax: HasAnyCapturedPaymentsAsync(Object transactionId)
​Returns: bool
​Description: Determines if the iPaaS.com transaction has any "Authorized" payments.

Syntax: IsAuthOnlyDeposit(String transactionNumber, String type, List payments)
​Returns :bool
​Description: Determines if this is an authorization-only deposit ticket.

Syntax: ContainsHeaderDiscounts(List discounts)
​Returns: bool
​Description: Returns whether a transaction contains header discounts.
​Example: ContainsHeaderDiscounts(TransactionDiscounts)

Syntax: ContainsLineDiscounts(List lines)
​Returns: bool
​Description: Returns whether a transaction contains line discounts.

Syntax: SumHeaderDiscounts(List discounts)
​Returns: decimal
​Description: Returns sum of header discount amounts.
​Example: SumHeaderDiscounts(TransactionDiscounts)

Syntax: SumLineDiscounts(List lines)
​Returns: decimal
​Description: Returns sum of line discount amounts.

Syntax:SumHeaderDiscountPercentage(List discounts, Decimal subtotal)
​Returns: decimal
​Description: Returns percentage of header discount amounts compared to subtotal (rounded to 3 decimal places).

Syntax: SumLineDiscountPercentageOfHeader(List lines, Decimal subtotal)
​Returns: decimal
​Description: Returns percentage of line discount amounts compared to order subtotal (rounded to 3 decimal places).

Syntax: LocationIdFromName(String location)
​Returns: long
​Description: Determines the location ID based on the location's name.
​Example: LocationIdFromName("Main Store")

Syntax: LocationIdInLocationGroupNameAsync(Int64 locationId, String groupName)
​Returns :bool
​Description: Returns true if the location ID exists in the specified location group.

Syntax: CountryCode(String countryName)
​Returns: string
​Description: Returns the two-character ISO country code.
​Example: CountryCode("United States") returns "US"

Syntax: CountryCodeRequired(String countryName)
​Returns: string
​Description: Returns country code but throws error if no results found.

Example: CountryCodeRequired("Canada") returns "CA".

Syntax: CountryCodeWithDefault(String countryName, String defaultCode)
​Returns: string
​Description: Returns country code or uses default if no results found.

Example: CountryCodeWithDefault("Japan", "N/A") returns "JP".

Syntax: StateAbbreviation(String state)
​Returns: string
​Description: Converts input to state abbreviation (supports US and Canadian states/territories).
​Example: StateAbbreviation("Georgia") returns "GA"

Syntax: StateName(String state)
​Returns: string
​Description: Converts input to full state name.
​Example: StateName("GA") returns "Georgia".

Syntax: iPaaSCustomerFromEmail(String emailAddress)
​Returns: string
​Description: Determines the iPaaS.com customer ID based on email address.
​Example: iPaaSCustomerFromEmail("customer@example.com") returns the ID, for example, "CUST-1357".

Syntax: iPaaSCustomerFromEmailAsync(String emailAddress)
​Returns: string
​Description: Determines the iPaaS.com customer ID based on email address (async version).

Example: iPaaSCustomerFromEmailAsync("customer@example.com") returns the ID, for example, "CUST-2468".

Syntax: iPaaSEmployeeFromEmailAsync(String emailAddress)
​Returns: string
​Description: Determines the iPaaS.com employee ID based on email address (async).
​Example: iPaaSEmployeeFromEmailAsync("employee@company.com") might return the employee "ID-EFT-1011".

Syntax: iPaaSTransactionFromNumberAsync(String transactionNumber)
​Returns: Transaction ID
​Description: Determines the iPaaS.com transaction ID based on transaction number.

Example:iPaaSTransactionFromNumberAsync("INV-20250722-001") might return the transaction ID "TID-88774455".

Syntax: GetExternalIdAsync(Object id, Object tableName, Object systemId)
​Returns: External system ID
​Description: Async version of GetExternalId.

Syntax: GetExternalIdForGiftCardTicket(Nullable orderId, Int64 systemId)
​Returns: External ID
​Description: Determines external ID of gift card ticket associated with an order.

Syntax: GetSpaceportId(Object externalId, Object tableName, Object systemId)
​Returns: long (nullable)
​Description: DEPRECATED - Converts an external ID to an internal iPaaS.com ID.
​Example: GetSpaceportId("EXT123", "products", 1)

Syntax: GetSpaceportId_String(Object externalId, Object tableName, Object systemId)
​Returns: String version of an iPaaS.com ID
​Description: String variant of GetSpaceportId function.

Syntax: ExternalIdFromStructure(List externalIdResponses, Int64 systemId)
​Returns: External ID
​Description: Returns ID for specified system from an iPaaS.com external ID collection.

Syntax: GetCustomFieldValue(List customFields, String customFieldName)
​Returns: Custom field value
​Description: Gets the value of a custom field (returns null if field doesn't exist).
​Example: GetCustomFieldValue(CustomFields, "Special Instructions")

Syntax: CustomFieldValue(String collectionType, String fieldName, String id)
​Returns: string
​Description: Returns the iPaaS.com custom field value based on collection type, field name, and parent ID.

Syntax: Setting(String SettingName)
​Returns: string
​Description: Gets the value for a given system setting.
​Example: Setting("DefaultTaxRate")

Syntax: ConvertJsonStringToDynamicObject(String jsonString)
​Returns: Dynamic ExpandoObject
​Description: Converts JSON string to dynamic ExpandoObject using Newtonsoft.
​Example: ConvertJsonStringToDynamicObject("{\"name\":\"John\"}")

Syntax: ConvertObjectToJsonString(Object anyObject)
​Returns: JSON string
​Description: Converts object to JSON string using System.Text.

Syntax: RegExReplace(Object input, Object pattern, Object replacement)
​Returns: string
​Description: Performs regex find and replace operation.
​Example: RegExReplace("The dog is running", "^The", "That")

Syntax: RemoveNonASCIICharacters(Object input)
​Returns: String with non-ASCII removed
​Description: Removes all non-ASCII characters except tabs and carriage returns.

Example: RemoveNonASCIICharacters("Temperature: 25°C") returns "Temperature: 25C".

Syntax: RemoveNonprintableCharacters(Object input)
​Returns: String with non-printable characters removed
​Description: Removes non-printable characters from input.

Example: RemoveNonprintableCharacters("User\x0CInput") returns "User Input".

Syntax: GetProductType(Int64 id)
​Returns: string?
​Description: Returns the iPaaS.com tracking method when provided its ID - returns "Product" or "Variant".

Syntax: OrderHasOpenDepositTicket(Object id, Object spaceportSystemId)
​Returns: bool
​Description: Determines if there is an open deposit ticket associated with a given order.