Working with Formulas

Custom Mapping Formulas as expressions, which you can use to manipulate values from the form with a use of custom functions. Formulas let you enhance the text captured, validate it, link with existing Dynamics data and more.

How to construct a formula

Formula relies on constants, variables, operations and functions. Each of those have related data type, which can be changed only by using conversion functions. Good example of data type are Text and Number variables. If you create formula which uses + operator between different variable types, the result will be different.

Example:

  • Text variables - "1" + "1" will return Text variable equal "11"
  • Number variables - 1 + 1 will return Number variable equal 2

The simplest formula is by inserting just one variable or constant. For example when your form has a field named firstname you can insert just firstname into formula box and it will dynamically replace this with text inserted into firstname field. If you want to use constant value, you may insert just a text surrounded with double quotes "John" and this text will always be sent to the target field.

However you may have two fields in your form - firstname and lastname and you want to push single concatenated value to your Dynamics. You can create formula firstname + " " + lastname, which will append last name to first name with space in between.

You have lots of functions at your disposal - simple ones like text converion or concatenation and more complicated one like querying your Dynamics and getting dynamically values form there. See bleo for more details.

Supported data types

  • Number/decimal
    • Example: 1.0
  • Boolean
    • Supports constants 'true' and 'false'
    • Example: true <> false
  • Array
    • Can contain all data types
    • Data types can be mixed in the same array
    • Example: Array(1, 2, 3, 4)
  • Text
    • Delimited by " or ' characters
    • Exampe: 'ABC'
  • Date/Time
    • Surrounded by '#' characters
    • Example: #2000-01-30 12:30:03#

Supported Operations

  • + - addition (numbers, date/time + number, string concatenation)
  • - - subtraction (numbers, date/time - number)
  • / - divide
  • *- multiply
  • = - equal
  • <> - not equal to
  • < - less than
  • > - greater than
  • >= - greater than or equal to
  • <= - less than or equal to
  • OR - logical or
  • AND - logical and

Supported functions

Form Variables

Each form field's value can be accessed in the formula directly by the field name. So if you added field named lastname you can insert this value to your formula by typing lastname.

System Variables

System variables are special variables, which give you access to current session and member details.

  • Current Member Id - MEMBER_DYNAMICS_CONTACT_ID - This variable gets replaced at runtime with currently logged-in member's linked Dynamics Contact record GUID.
  • Current Member Email - MEMBER_EMAIL - description: "This variable gets replaced at runtime with currently logged-in member's email address defined in the portal."
  • Current Member Username - MEMBER_USERNAME - This variable gets replaced at runtime with currently logged-in member's username at portal
  • Session Id - SESSION_ID - This variable gets replaced at runtime with current session id. When you submit form and assign value of SESSIONID to record in Dynamics, you can refer to this number for example in further process steps. When users submit the form they will be redirected to the defined page with SESSIONID page parameter set. You can use this value to query records from Dynamics created in previous steps.

DateTime Functions

  • Today - TODAY() - Gives today's date
  • Add Days - ADDDAYS(<starting date>, <number>) - Returns a Date value containing a date and time value to which days has been added.
  • Add Hours - ADDHOURS(<starting date>, <number>) - Returns a Date value containing a date and time value to which hours has been added.
  • Add Minutes - ADDMINUTES(<starting date>, <number>) - Returns a Date value containing a date and time value to which minutes has been added.
  • Add Months - ADDMONTHS(<starting date>, <number>) - Returns a Date value containing a date and time value to which months has been added.
  • Add Seconds - ADDSECONDS(<starting date>, <number>) - Returns a Date value containing a date and time value to which seconds has been added.
  • Add years - ADDYEARS(<starting date>, <number>) - Returns a Date value containing a date and time value to which years has been added.
  • Difference between dates in Days - DATEDIFFDAYS(<starting date>, <end date>, <allow fractions>)
  • Difference between dates in Hours - DATEDIFFHOURS(<starting date>, <end date>, <allow fractions>)
  • Difference between dates in Minutes - DATEDIFFMINUTES(<starting date>, <end date>, <allow fractions>)
  • Difference between dates in Seconds - DATEDIFFSECONDS(<starting date>, <end date>, <allow fractions>)
  • Now - NOW() - Returns current datetime in utc.
  • Second - SECOND(<datetime>) - Returns an Integer value from 0 through 59 representing the second of the minute.
  • Minute - MINUTE(<datetime>) - Returns an Integer value from 0 through 59 representing the minute of the hour.
  • Day - DAY(<datetime>) - Returns an Integer value from 1 through 31 representing the day of the month.
  • Hour - HOUR(<datetime>) - Returns an Integer value from 0 through 23 representing the hour of the day.
  • Month - MONTH(<datetime>) - Returns an Integer value from 1 through 12 representing the month of the year.
  • Year - YEAR(<datetime>) - Returns an Integer value representing the year of the datetime.

Math

  • Count - COUNT(<array>) - Returns count value from passed array of numbers.
  • Sum - SUM(<array>) - Returns sum value from passed array of numbers.
  • Absolute Value - ABS(<value>) - Returns absolute value of number value passed.
  • Average - AVG(<array>) - Returns average value from passed array of numbers.
  • Ceiling - CEILING(<number>) - Returns the smallest integer greater than or equal to the specified double-precision floating-point number.
  • Exponential - EXP(<number>) - Exponential function - returns e raised to the power of number. The constant e equals 2.71828182845904, the base of the natural logarithm..
  • Max - MAX(<array>) - Returns the maximum numeric value from an array of numbers.
  • Min - MIN(<array>) - Returns the minimum numeric value from an array of numbers.
  • Logarithm - LOG(<number>) - Returns the natural (base e) logarithm of a specified number.
  • Floor - FLOOR(<number>) - Return the integer portion of a number.
  • Power - POW(<number>, <number>) - Returns a specified number raised to the specified power.
  • Random number - RND() - Returns a random number
  • Round - ROUND(<number>) - Rounds a double-precision floating-point value to the nearest integer.
  • Squere Root - SQRT(<number>) - Returns the square root of a specified number.

Conversion

  • Convert to DateTime - DATETIME('2016/31/1 01:00', 'yyyy/d/M HH:mm') - Used for converting Text to Number. You can use basic function or with format extension - DATETIME('2016-10-12 01:00') OR DATETIME('2016/31/1 01:00', 'yyyy/d/M HH:mm') - format is in standard c# convention
  • Convert Text to Number - NUMBER(<value>) - Used for converting Text to Number
  • Convert Number to Text - TEXT(<number>) - Used for converting Number to Text
  • Convert to Boolean - BOOLEAN(<value>) - Used for converting Text and Number to Boolean" }

Dynamics-specific

  • Create Entity Reference - CREATEENTITYREFERENCE(<entity name>, <record guid>) - Returns serialized EntityReference object. Use output of this function to pass value to Lookup fields in Dynamics.
  • Create Party List - CREATEPARTYLIST(<array of entity references>) - Returns the serialized ActivityParty object. Use output of this function to pass it to To/Cc/Bcc fields in email activity in Dynamics. It is a list of lookup values.
  • Get Guid - GETGUID(<EntityReference>) - Returns guid retrieved from LOOKUP value (EntityReference).
  • Creates new Guid - NEWGUID() - Returns new random guid.
  • Lookup record in Dynamics - LOOKUP(<search entity>, <search field>, <result field>, <search value>) - Lookup function is used for querying data in Dynamics and getting single value from one record found. You can use it for example to get Account Email address if you have just Account GUID.
  • XLOOKUP - Check if only one record satisfies a condition - XLOOKUP(<search entity>, <search field>, <search value>) - XLOOKUP function is used to query Dynamics by single attribute and check if just one record is found. It is usually used in combinatation with LOOKUP function to check if looking up data will give any result of not and acting differently depending on the result.

Arrays

  • Create Array - ARRAY(<item1>, <item2>, ...) - Returns an array from a list of items.

Operators

  • Add - + - Adds two numeric values, text or a date and numeric value (adds days). Example: 1 + 2 | #2000-01-01# + 1 | 'ab' + 'c'
  • Subtract - - - Subtracts one numeric value from another or the number of days from a datetime value, or the difference between two dates. Examples: 1 - 2 | #2000-01-01# - 1 | #2000-01-02# - #2000-01-01#
  • Multiply - * - Multiplies two numeric values. Example: 1 * **2
  • Divide - / - Divides numeric values. Example: 1 / 2
  • Equals - = - Returns boolean. Compares two numeric, text, boolean or datetime values. Examples: 1 = 2 | true = false | 'a' = 'b' | #2000-1-1# = #2000-1-2#
  • NotEqual - <> - Returns boolean. Compares two numeric, text, boolean or datetime values. Examples: 1 <> 2 | true <> false | 'a' <> 'b' | #2000-1-1# <> #2000-1-2#
  • And - AND - Returns boolean. Logical And operator. Example: true AND false
  • OR - OR - Returns boolean. Logical Or operator. Examples: true OR false
  • Greater Than - > - Returns boolean. Compares two numeric or datetime values. 1>2
  • Greater Than Or Equal - >= - Returns boolean. Compares two numeric or datetime values. Example: 1>=2
  • Less Than - < - Returns boolean. Compares two numeric or datetime values. Example: 1<2
  • Less Than Or Equal - <= - Returns boolean. Compares two numeric or datetime values. Example: 1<=2
  • Modulus - % - Returns the modulus from two numeric values. Example: 1 % 2

Text

  • Format Number - FORMAT(<number>, <format mask>) - The format for numeric values utilises the standard or custom numeric string formats. If format is omitted then the value is converted to the most appropriate string representation.
  • Get Character By Code - CHR(<character>) - Returns the character associated with the specified character code.
  • Left - LEFT(<value>, <number of characters>) - Returns a string containing a specified number of characters from the left side of a text.
  • Right - RIGHT(<value>, <number of characters>) - Returns a string containing a specified number of characters from the right side of a text.
  • Length - LEN('abc') - Accepts one argument of type string for which the length is returned. Example: Len('abc') -- returns 3
  • Replace - REPLACE(<original text>, <text to be replaced>, <text to replace>) - Returns a string in which a specified substring has been replaced with another substring
  • Uppercase - UPPERCASE(<text>) - Returns a string or character containing the specified string converted to uppercase.
  • Lowercase - LOWERCASE(<text>) - Returns a string or character containing the specified string converted to lowercase
  • Trim - TRIM(<text>) - Returns a string containing a copy of a specified string with no leading and trailing spaces.
  • NULL - NULL() - Returns NULL

Checks

  • If Condition - IF(condition, trueResult, falseResult) - Based on the condition / expression returns the true or false result.
  • Check if array - ISARRAY(<value>) - Returns true if passed value is an array
  • Check if datetime - ISDATETIME(<value>) - Returns true is passed value is a DateTime
  • Check if NULL - ISNULL(<value>) - Used for checking if parameter is NULL
  • Check if not NULL - ISNOTNULL(<value>) - Used for checking if parameter is not NULL
  • Check if number - ISNUMBER(<value>) - Returns true if passed value is a number
  • Check if text - ISTEXT(<value>) - Returns true is passed value is a text
← How Forms WorkExample - Create a Form →