TABLE OF CONTENTS

• Introduction
• The Form for building/testing formulas
• Customizable test data (randomized)
• Why formulas?
• Some basic concepts and syntaxes for making formulas
• The DateTime class
• The String class
• The Integer and Double classes
• The Boolean class
• The Array class
  • Making a formula to test if a value is in a given list
  • Making a formula for ad hoc Value Mapping
• The Math class
• The Sys class
• Formula with conditional statement
• How to handle Null values?
• What if we want to return a particular value if the field's value is null?
• Value map formulas (auto generated)
• Tips and tricks
• The complete list of classes available in Formula builder
• Differences between sending and receiving config formulas

Introduction

Building mapping formulas is a big topic as it branches into the field of programming, and all the possibilities that are at your fingertips when writing C# code. This tutorial will try to limit the scope to what is most useful for a Setup Admin to know.

The hope is that after reading this article, you will have a sense of the playing field, and have some concrete tips and tricks to get you started into this exciting area of work. The possibilities are virtually endless for you to create the formulas you need to achieve your end goals. But as with acquiring everything else - it takes some practice and patience.

The Form for building/testing formulas

Below is a screenshot of the form:

Explanations to red numbers above:

Customizable test data (randomized)

When testing formulas, it is critical to be able to have SOME data for all the host fields - even if it is not "realistic". Therefore a very important feature in IDE is to automatically create a randomized minimalistic schedule for this purpose - i.e a schedule with "one of everything"; activity, resource, resource-assignment, etc. The contents can be edited so that you can ensure that your formulas result in expected results. Below you see the screenshot of such a random test schedule:

Screenshot above shows randomized test data used when evaluating formula results. 

Since data will be randomized each time the form is opened, you may want to save any specially prepared test data to a text file so it can be reused on later occasions.
Also note that you can delete any data here to test your formula's resilience against "Null values" (i.e avoiding data dependent errors in formula).

Why formulas?

Typically, when making a mapping in IDE, you do this at the "sending end" (sending config) where you simply select which host field should be used to populate a particular ILAP Term (called "direct mapping"). In some cases, however, you need to make some alteration to the host field’s value, and that is when you need a formula to define how the given value should be transformed into some other value; It is like a “black box” where there is a value coming in and another value going out. The incoming value for the selected host field is typically referenced by the placeholder called “[VALUE]” (if no host field is selected in sending config it will not be possible to test and save the formula). 

Let's jump straight into some examples to get a head start:

If you need to prefix the incoming value with the string “ABC_”, you could have the formula:
"ABC_" + [VALUE]
...or if you wanted to return the 2 leftmost characters of the value, you could have written:
Sys.Left([VALUE], 2)
...or you may have wanted to combine values from other fields; E.g you wanted a prefix (from first example) to depend on the scope of the activity (if > 100 then “ABC_” otherwise “XYZ_”):
(PlannedWorkHours > 100 ? "ABC_"  : "XYZ_") + [VALUE]

Some basic concepts and syntaxes for making formulas

The skillset reqiured for making formulas in IDE is a subset of the skillset required to write code in a so called "Object-Oriented Programming Language". Therefore we need to have a basic understand what these 4 terms means (since they will be used later in this document): class, object, property and method.

A Class defines a structure that combines Properties (data) and Methods (behavior). Properties hold information about an object, such as Name or Age. Methods are functions inside the class that perform actions or manipulate the data, like Drive() or Speak(). One notable difference between properties and methods is that methods can take parameters, such as Speak("Loudly") or Speak("Softly"), whereas properties cannot. 
The relationship between a class and an object is the same as the relationship between "Human" and "a baby" - the former is the abstract name of the species and the second is the real (tangible) instance in the world, inheriting all the properties of of the "Human" class.

The formulas you will write in IDE are conforming to the .NET’s C# syntax (pronounced “Dot Net’s C Sharp”). Readers familiar with programming in .NET will feel right at home, but for those new to it, let’s start with the basic syntax:

Object.Property or Object.Method()

The first of these always refer to a value of some kind, and the second always implies an action of some kind (but may still return a value).

Let’s give an “everyday” example of what this could look like:

Dog.Height or Dog.Bark()

As you see, the Property refers to an attribute value (the dog’s height), whereas the Method refers to an action that the dog does (barking).  Methods always have a set of parentheses in their syntax to allow values to be passed to them. It might for example be relevant to dictate how loud the dog should bark, using a number between 0 and 5: Dog.Bark(4)

Obviously we are not operating with barking dogs when making formulas, but with hostfields containing values.
These values can have different so called Data Types, i.e a particular type of value.  There are 5 such basic types that are central when working with formulas (and they are incidentally also classes):

1. DateTime          E.g: 2025-01-20
2. String                E.g: "My dog barks"
3. Integer              E.g: 42
4. Double               E.g: 42.5
5. Boolean              E.g: True

Let’s take a look at each of the above 5 classes, plus a few additional ones, to get more familiar with what the are useful for.

The DateTime class

One of the most useful Methods for dates is adding a number of days to a date field (number may be negative if you wish to subtract days), for example:

EarlyStart.AddDays(7)  <-- Note that you can type (or copy paste from here) this in the web page and press “Test” to see result.

The same concept can be used, even if you do not have a field, but just type the name of the Class itself.  You will then be able to access properties and methods from the DateTime class - and one of the more useful properties would be to get today’s date: 

DateTime.Today (returns the date with 00:00:00 as time of day) or

DateTime.Now (returns the date and time – down to seconds)

… and with the .NET syntax, you may again treat the above as an object and add a new method to it:

DateTime.Today.AddDays(7)

If you wanted to add another 7 days, you could technically continue this concept:

DateTime.Today.AddDays(7).AddDays(7)

… but it would be much simpler to just write: DateTime.Today.AddDays(14)

Useful technique: Note that you can type the following and press “Test” to see ALL methods and properties for DataTime in the Formula Tester (which is also true for ANY class described later):
typeof(DateTime)

For more details: https://msdn.microsoft.com/en-us/library/system.datetime(v=vs.110).aspx

The String class

For a String it would be useful to be able to pick out one or more characters from within all of its characters.
Note that you can use a “constant” instead of a named field as well by wrapping it in double quotes:

"abcdefg".Substring(1,3) would return "bcd"

Please note that .NET is  “zero-index”, i.e it uses 0 to mean the “first element” and also note that the second number (3) tells the method how many characters to return.

It is possible to concatenate (join) strings by simply using the “+” operator:

MyStringField + "ABC"

Note: You can convert any other type of value to a string by typing ".ToString()" at the end of it.

Example you can test: 1.ToString()  <- results in "1".

Note that you are advised to use the Sys class for string operations, as the String class methods may result in errors as described in this link: The Sys class

For more details: https://msdn.microsoft.com/en-us/library/system.string(v=vs.110).aspx

The Integer and Double classes

For the Integer (typically the variant called "Int32" with 32 bit precision is used) and Double Data Types, you can use the 4 regular mathematical operations ( + - * / ), together with parentheses. 

The Double class can contain numbers having decimals, whereas Int32 is a whole number between plus and minus 2 billion (or 2,147,483,647 to be more precise). 

Please note some not so obvious aspects of integers:

Type 9 / 2 in the Formula and press “Test”. You will se 4 (and not 4.5 as you may have expected)
The reason is that both are interpreted as integers, and then the result will also be an integer.

If you want to specify that a number is decimal number, then type 9.0 / 2 (which will result in 4.5)

You could also have “cast” the number to a double by placing this type in front of it in parenthesis:
(double)9 / 2

The Boolean class

There are a few operators that must be mentioned before giving an example of a Boolean Expression:

Examples:

Please note that "CAT" = "cat" would return False, since string comparisons are case sensitive.

The Array class

It is possible to specify a list of values (separated by comma) by wrapping them in curly brackets, and having "new object[]" in front of it:

new object[] { "cat", "dog", "horse" }

It is then possible to pick out a particular item in such a list by adding a pair of square brackets with an “index number” in it:
new object[] { "cat", "dog", "horse" }[2]
The result above would be “horse”   (remember that .NET is “zero-indexed”, i.e that 0 refers to the first element)

There is a useful Methods of the Array Class that is worth mentioning here called “IndexOf”. An example makes it easier to understand:
Array.IndexOf( new object[]{"A", "B", "C"} , "C") will return the index in the array where the “C” is found, i.e 2 (since 0 is used to refer to the first element).

If we had looked for “D”, which is not in the list, then -1 would be returned.

Making a formula to test if a value is in a given list

One useful case for the above example (that Array.IndexOf returns -1 if item not in list) is is to check is a value is one of a specified list. This could for example be used when making a upload filter (that uses the same syntax as making formulas). 
Let's say we want to return true if the third character of the Activity Id is "A", "B", of "C":

Array.IndexOf(new object[]{"A", "B", "C"}, Sys.Mid(Activity.Code, 2, 1)) > -1

Note that the yellow highlighted text above represents the third character, and also note that we do not need to have any "if statement" here since the ">" operator will return True or False.

Making a formula for ad hoc Value Mapping

Let’s again expand on the fact that -1 is returned by the Array.IndexOf method. The formula we will create now may look a bit complicated, but achieves 2 goals:

1. It is a possibly useful real-life case for doing value mapping, where A is mapped to X, B to Y, C to Z, and if no match - keep the original (can be extended with any number of elements in mapping lists).
2. It illustrates the power of formulas (if this hasn't already been made clear...).

Some hints for decoding this: We add 1 to the "IndexOf value" to ensure that (-1), i.e value returned if not found, becomes 0 and then will point to the first element in the outer list (which is uf_ref3) - which is in fact the value to return if not found):

new object[]{ uf_ref3, "X", "Y", "Z" }[Array.IndexOf( new object[]{ "A", "B", "C" } , uf_ref3 ) + 1] 

If you prefer to return a default value (e.g “Q”) in case the value is not found, this would be the formula:

new object[]{ "Q", "X", "Y", "Z" }[Array.IndexOf( new object[]{ "A", "B", "C" } , uf_ref3 ) + 1] 

The Math class

If you need an extended set of mathematical functions, you can use the Math Class to access some useful Methods. 

Examples:

Math.Round(uf_dec1, 2) <-- return the rounded result of field value to 2 decimals.

Math.Max(uf_dec5, 100.0)  <-- return the max value of the two given values.

Math.Log10(uf_dec5) <-- return the logarithm (base 10) of a given field value.

Math.Abs(uf_dec3) <-- return the absolute value of a given field value.

Math.Pow(2, 5) <-- returns 2 to the power of 5, i.e 32.

For a full list of methods type this formula and press “Test”:

typeof(Math) 

The Sys class

The main purpose of the Sys class is to provide a "safe" way to extract elements from a string, and perform some other string manipulations. Let's demonstrate what we mean by "safe" here:

As seen in the String class, we can use "abc".Substring(1,2) as a formula and it would result in "bc".

However, if you were to test "abc".Substring(5,2) you would get the error: startIndex cannot be larger than length of string. (Parameter 'startIndex') 

The preferred way is therefore to use the Methods found in the Sys class for these purposes. In the above example the following would work better:

Sys.Mid("abc", 1, 2) would return "bc" and Sys.Mid("abc", 5, 2) would return "" (i.e no error!)

Below is the list of Methods seen when testing the formula: typeof(Sys)

Examples:

Sys.Left("Abcde", 2) will return the 2 leftmost characters: i.e "Ab".

Sys.Right("Abcde", 2) will return the 2 rightmost characters: i.e "de".

Sys.Mid("Abcde", 1, 2) will return the 2 middle characters starting from positon 2: i.e "bc".

Sys.GetPart("Abc.de", ".", "R") will return the "Right part" of the string split by ".": i.e "de".

Sys.GetPart("Abc.de", ".", "L") will return the "Left part" of the string split by ".": i.e "Abc".

Sys.GetPartIndex("Abc.de.f.ghi", ".", 2) will return the third element when string is split on ".": i.e "f".

Sys.Truncate("Abcdefg", 4) will truncate the string to first 4 elements: i.e "Abcd".

Sys.PreserveChars("ab1c2", "1234567890") will preserve the characters in the string based on the second list of characters (and remove all others): i.e "12".

Sys.RemoveChars("ab1c2", "1234567890") will remove the characters in the string based on the second list of characters (and preserve all others): i.e "abc".

Sys.Obfuscate("AbCdefg1234") will return an obfuscated version of the text: e.g: "HhJhjij1399" (it will change each time you press "Test" so is randomized to some degree, but preserves casing and numbers.

Formula with conditional statement

If you wish to make a conditional formula (if a then b else c), the syntax for this is:

Condition ? ReturnThisIfTrue : ReturnThisIfFalse

The condition must always evaluate to a boolean (True/False) value.

ReturnThisIfTrue is the statemenat to evaluate if Condition is true.

ReturnThisIfFalse is the statemenat to evaluate if Condition is false.

Note: ReturnThisIfTrue and ReturnThisIfFalse must have the same data types. The following will NOT work (since one is integer and the other string):
PlannedWorkHours > 100 ? 1 : "X"

Example 1: 

(PlannedWorkHours > 100 ? "Large" : "Small") + " Value"

Example 2 (...and yes a formula can contain line breaks, and is encouraged for readability):

PlannedWorkHours < 5 ? "Really Small" :

PlannedWorkHours < 10 ? "Quite Small" :

PlannedWorkHours < 15 ? "Rather Small" :

PlannedWorkHours < 20 ? "Small" :

PlannedWorkHours < 25 ? "Not so small" :

PlannedWorkHours < 30 ? "Below 30" : "Above 30"

How to handle Null values?

When writing formulas you need to be aware of some edge cases where the value may be missing (or being "Null" as it is technically called), and therefore there are no properties to retrieve from it - resulting in an error when evaluating the formula. Let's take this example:

As seen above, the Description value is "abc", so we should be able to modify the formula to get the "Length" property of the field: Description.Length

The result is (as expected): 3

But what happens if we delete the row with "Description": "abc" from the test data?

The result is an error as seen above, and we should try to avoid these cases, since they represent a performance hit (time-wise) and results in an "uncontrolled" value resulting from the mapping.

Instead we should preemptively take into account that the value could be null by signaling "If the value is null then return null instead of throwing an error". This is done by adding a "?" before the ".":

Description?.Length

The result will then say: [null] (...and no error is thrown)

What if we want to return a particular value if the field's value is null?

Answer: Then use the "??" syntax, meaning: "return the following if the value is null".
In our example: Description??"The value is null"

The result (please test it) will be: "The value is null".

Value map formulas (auto generated)

When you define a list of valid values for an ILAP Term (for template) in a sending config, it may look like this:

The moment you press "Save", a formula will automatically be produced looking something like this:

It starts with "VM" which is the internal class for ValueMaps, and then (after the ".") the name of the value map; created from the name of the ILAP Term prefixed with "Map_" and suffixed by "_U" (as in "Upload" - for Receiving configs "D", as in "Download", will be used as suffixs in "Download"). Example for ILAP Term "ContractorDisciplineCode":

VM.Map_ContractorDisciplineCode_U.Map([VALUE])

If you want to return a default value if no matching value found, you may do so by manually adding a second parameter with this value - in the case "X":

VM.Map_ContractorDisciplineCode_U.Map([VALUE], "X")

Note: If you wish to see which methods are avalable for a value map, just select the part up to (and including) "_U" and press "Test" (you first need to temporarily deselect any host field, or the "Test" will make you aware of missing placeholder for [VALUE]):

Tips and tricks

Here are some tips and tricks to make you more efficient in working with formulas:

1. Setting relevant test data:
   If you have a text hostfield selected (such as uf_text1 for Safran), you may set the formula to [VALUE] and press “Test”. The value you see as a result will be a long random string. Copy this value and click in the “Test schedule data” tab and press Ctrl-F (find). Then you can change the sample value to whatever you want, e.g. “Abc.de”.
   Then you can type the formula Sys.GetPart(uf_text1, "." , "R") and can note that “de” is shown as the result.
   
    
2. Testing part of a formula using highlight:
   If you wish to test parts of your formula, you can highlight it and press “Test”
   
    
3. Using the same type of utility for testing the entire config mapping:
   For a config, you can press
   

   
   

   Here, as for the Formula Tester, you may edit the test data. Below is an example of the Formula result preview:  
   
   
4. Use the typeof(SomeClassName) to get useful info on which properties and methods they have.
   In fact, you can type “Activity” and press “Test” to see which properties it has, and there you will find “ResourceAssignments”, so you can type “Activity.ResourceAssignments” and press “Test”… in order to see a particular resourceassignment’s properties you need to select one: “Activity.ResourceAssignments[0]”:
    
   
   
5. If you need to do aggregations of e.g ResourceAssignment data, look at the example provided in the Example picker and take inspiration from it to cover you own needs:
   Activity?.ResourceAssignments.Sum(x => x.PlannedHours)
   
   This is called a "LINQ" (short for "Language Integrated Query") type formula, and more info can be found online.
   
   
6. If you copy & paste any of the examples from office documents, please be aware that the quotes used here will may not be understood as quotes in the formula evaluator, so you may need to replace them with the double quote character from your keyboard.
   Example: “Abc.de” is not going to work, so you manually have to change it to "Abc.de".

The complete list of classes available in Formula builder

In addition to the generic ones listed below (part of .NET), there are 3 classes specially developed for IDE:

Sys:      Support for some safe string manipulations

Calendar: Provides some special methods for deriving dates

General: Not recommended to use as it is deprecated (added here for backward compatibility)

List of .NET classes available for making formulas (where the ones in bold may be of direct use):

Array 

ArrayList

bool

byte

char

Component

Convert

DateTime 

DateTimeOffset

DayOfWeek

decimal

Delegate

Dictionary

double 

Encoding

Enum

Enumerable

EventArgs

Exception

ExpandoObject Type

Expression

float

HashSet

Hashtable

ICollection

IEnumerable

IEnumerable KeyValuePair

int

Int32 

IQueryable TypeConverter

List

long

Math 

object

OverflowException

Queryable

Queue

RegexOptions   

sbyte

short

SortedDictionary

Stack

string 

StringBuilder

StringComparer

Regex 

TimeZoneInfo

uint

ulong

ushort

Differences between sending and receiving config formulas

As mentioned, most mappings will typically be made in a sending config, but if you decide that you need a mapping in a receiving config, there are some things that differ from a sending one.

Below, each numbered explanation is illustrated by corresponding "red number" in screenshot:

1. Sending config has a host field selector whereas receiving config does not....

2. ... Instead the ILAP term will be used to populate the "[VALUE]" placeholder (see 2 below).

3. The receiving host field needs to be specified for a receiving config, so that we know which field to affect at download. NOTE: On the other hand, a sending config may have blank host field in case there is an agreement with receiver that they will do a hard coding of the value in their config (in which case there is no need to specify a source field).

Sending config has a host field selector dropdown

Receiving config has no host field selector, but uses the ILAP terms as the "source"

Each mapping row in receiving config need to have a specified host field to update at download