This article focuses on data transformation, explaining how to create mappings and use custom functions to manipulate data.
Mappings
Mapping Collections should be named in the following format:
Verb - Noun - Direction - Noun. For instance: Update MS NAV Company FROM iPaaS.com.
All data transformation should be handled in mapping collections as much as possible and should not be hardcoded. There are some exceptions to this that may include:
Fields that can be calculated. E.g., two fields: has_email and email_adddress, can map to
email_addressand calculatehas_email.
There may be cases where an entire mapping collection can be replaced by a single mapping on the parent. For example, product category assignment in an external system may just be a list of integers representing the category ids. In that case, it doesn’t make sense to create a mapping collection for this type. A simple conversion function can be created and used as a dynamic formula mapping on the product. Be cautious with this approach. Some systems may include additional data even on types as simple as category assignments, such as start/end dates or custom fields. If there may be more data that would need a full mapping collection, then this must be defined in your integration.
When defining your mapping filter, value clarity of code. Users will need to look at your filter and quickly see what the expected function is. E.g., rather than something like this, which fits all logical operators on a single line:
return !(isVariantParent || isKitParent);
Instead, write something slightly more verbose, but which makes the operation clear to people who might not be professional programmers:
if(isVariantParent || isKitParent)
return false;
return true;
Error Filters and Lookup Translations
Use error filters when possible to short-circuit errors early. For example, if you know that customers in your external system require an email address, you can catch this with an error filter before any mappings or API calls are made.
When possible, use lookup translations to map one set of values to another. Lookup translations are much easier for non-technical end users than even the simplest code. Anytime you have more than 2 values, a lookup translation is preferred. If the lookup key is calculated or there is more than one key/condition, then you may need to use a dynamic formula.
For a default or fallback value on a lookup translation, set the last translation as a dynamic formula with the source value as “true”. The "sort order" is used here so any defaults should have a high order like "99" so they are processed for matches last.
Use a FieldWithDefault if you need to supply a value when the source is null. Anything more complex will require a Dynamic Formula or Lookup Translation.
Do not rely entirely on code comments in dynamic formulas. Ensure notes are added to a mapping when:
Using an integration custom function.
When using placeholder data.
The logic may not be clear to a non-technical user.
When using a control field.
Alternative formulas/options are available.
It is possible to build complex objects in a dynamic formula. If the result of a formula is a complex object, it will be serialized and deserialized when assigned to the destination. This means that any complex type with matching field names can be used to assign to any other complex type. Because of that, often the simplest and most universal way to create a complex object is to create an ExpandoObject and dynamically add your fields as needed. For example:
var retVal = new List<dynamic>();
foreach(var attributeValue in attributeValues)
{
dynamic attribute = new ExpandoObject();
attribute.Name = attributeValue.Name.English;
attribute.NameFrench = attributeValue.Name.French;
attribute.NameGerman = attributeValue.Name.German;
retVal.Add(attribute);
}
return retVal;
If possible, keep Add and Update mappings together in a single mapping. They may be broken up if necessary. For example, if you are sending products to an e-commerce platform, you may want to leave most data in place for existing products, but you will need to add it for new products.
For Add/Update mappings, you can utilize the DestinationValue field to determine if the current transfer is an add or an update.
Use Delete Triggered Updates to perform data updates on objects deleted in external systems. For example, you may have a field AvailableForEcommerce in your product management system. If a product is deleted in your ecommerce system, you likely would not want to delete the product in your product management system, but you may want an update the AvailableForEcommerce flag to false.
By convention, data stored in iPaaS.com custom fields that represent a list of values should be a comma-separated list (e.g., Category1, Category2, Category3). Data that represents multiple elements of the same data can be stored in pipe-delimited format (e.g., Case|12 for a field that stores the purchasing unit and unit conversion). Data that is more complex can be stored in a JSON string.
Be cautious of this format if your data may contain commas or other commonly used characters, such as
|.
Dates may be stored in UTC, DateTimeOffset, or localized DateTime, depending on what you are using and how a user might see it. By default, dates displayed in iPaaS.com will be localized to the user’s timezone. If this is the desired functionality, use a DateTimeOffset or a UTC date. If the field is displaying a localized field (such as the operating hours of a store, which should be localized to the store), then use the localized value.
Some integration APIs/ filters only support local time, which may be different than the time on iPaaS.com servers (UTC in production, EST/EDT in staging). These discrepancies have led to unexpected polling results without accounting for the difference. In some cases, we add a preset to allow users to convert the times used for polling to align the time zones with iPaaS.com.
As C# and iPaaS.com values are case sensitive by default, if an incoming piece of data could come with different cases (i.e., COLOR, color, or Color), consider using ToUpper()/ToLower () to ensure consistency.
Functions
Functions should be named in TitleCase. Use underscores to denote groups of functions or to highlight distinctions between them. E.g., ConvertToDateTimeOffset_UTC(), ConverToDateTimeOffset_Localized(). Function parameters should be named in pascalCase.
Other function standards:
Append
Asyncon any async functions (e.g.,GetLocationIdFromNameAsync(string name))As much as possible, define your function parameters as objects rather than specified types. The C# interpreter used by dynamic formulas is VERY particular about data types and will fail to find a matching function if there is any ambiguity in the type. Using objects as your parameter type will help avoid this.
Any parameter that is an object should be type-checked and converted inside your function before use. For complex objects, you may consider serializing and then deserializing the value.
Define your conversion functions using the standard C# comment template, with the addition of an <example> tag. All data in this comment will be scraped into a database to be used as Intellisense in the dynamic formula editor, with the exception of the <remarks> tag. An example can be found at the end of this document in the XML Function Template.
Use conversion functions to handle challenges that users can’t or won’t be able to handle in a dynamic formula:
Any logic that requires an external API call must be made in a conversion function
Any logic that is repetitive or that will be used in multiple places. For example, if a system has a specific date format that all dates must use, a single function to handle this will save the end user a lot of effort.
Any reference to a non-standard assembly must be added as a conversion function.
Asynchronous methods should be used anywhere additional API calls are being made. If the conversion function only processes data passed into it as parameters without requiring additional api requests, then it is typically not necessary to make it asynchronous.
XML Function Template
XML Function Template
For each function you want to document, copy the Template Structure below and fill in the details for each section. Refer to the "Example Documentation" for a practical demonstration.
Template Structure
/// <summary>
/// [FunctionName]
/// Provides a brief description of what the function does, including its purpose, inputs,
/// outputs, and the context in which it should be used.
/// </summary>
///
/// <example>
/// Example Usage:
/// [Parameter1Name] =\> "[ExampleValue1]"
/// [Parameter2Name] =\> "[ExampleValue2]"
///
/// await [FunctionName]([Parameter1Name], [Parameter2Name]);
///
/// Explanation:
/// [Explain what the function does internally and what it returns in this example.]
///
/// Returns:
/// [Example return object/value]
/// </example>
///
/// <remarks>
/// Usage Notes:
/// [Explain usage in a mapping or business scenario.]
///
/// Data Types:
/// [Parameter1Name] → [Type]
/// [Parameter2Name] → [Type]
/// </remarks>
///
/// <param name="[parameter1]">
/// Description: [Description of the parameter]
/// </param>
///
/// <param name="[parameter2]">
/// Description: [Description of the parameter]
/// </param>
///
/// <returns>
/// [ReturnType]
/// </returns>
Explanation of Sections
/// <summary>
Purpose: A concise overview of the function.
Instructions:
Replace [FunctionName] with the actual name of your function.
Provide a brief, clear description of what the function does, its main purpose, what inputs it expects, what outputs it produces, and the general context in which it should be used. Think of this as the elevator pitch for your function.
Specify whether the function is asynchronous or synchronous.
/// <example>
Purpose: Demonstrates how to use the function with a specific, realistic scenario.
Instructions:
Example Usage: Show the actual values passed to each parameter ([Parameter1Name] => "[ExampleValue1]") and how the function is called (await [FunctionName]([Parameter1Name], [Parameter2Name])😉.
Explanation: Describe, in detail, what happens when this specific example is run. Explain the internal logic, any transformations, and the step-by-step process of what the function accomplishes in this case.
Returns: Show the exact or representative return value for this specific example. If it's an object, provide its structure and sample data.
/// <remarks>
Purpose: Provides important usage notes, context, and data type information.
Instructions:
Usage Notes: Explain the function's use in larger scenarios (e.g., data mapping, integration workflows, business logic). Include any crucial prerequisites, dependencies, or conditions that must be met for the function to work correctly.
Data Types: Clearly list each parameter name and its corresponding data type (e.g., String, Number, Boolean, Array<Object>, Object).
/// <param name="[parameterName]"
Purpose: Describes each individual parameter.
Instructions:
Replace [parameter1] with the actual name of the parameter.
Description: Provide a detailed explanation of the parameter. What kind of data does it expect? What is its role in the function? Are there any specific formats, ranges, or constraints it must adhere to?
/// <returns>
Purpose: Describes the function's return value.