Formula Builder

The Formula Builder gives you a way to have report columns automatically populate with data. Both standard and unique data can be included by creating a formula using existing project information stored in e-Builder. Formulas can range in complexity. The most straightforward formulas might include functions that automatically insert the current date and time into a report column. More complex formulas might include mathematical operations that provide the average square footage of X-ray rooms or school gyms. Another example might be the sum of the current contract values on all commitment and commitment changes.

Setting up these data fields is optional, but they can reduce the time it takes you to fill-out data fields and make information readily available without having to search through the system for it.

Below are detailed descriptions of the fields located on this page.

Field

Description

Label

Enter a label for this column.

Decimal Places

Click to select a preferred decimal placement for integer values.

Use Parentheses for Negative Values

Yes: Select this option to use parentheses around negative values. This is the default selection.

No: Select this option if you do not want negative values to be denoted by parentheses or distinguished at all.

Formula

Select Field

The selections in this drop-down arrow will also vary depending on the fields configured. Moreover, if the workflow is for a cost-related process, it will contain many more fields, specific to cost.

Operators

Operators are used to build formulas between data fields.

Alphanumeric fields such as an address and strictly alphabetical fields such as the project names, cannot be combined with mathematical operators (+-/*) to build a formula, otherwise an error will occur. When using mathematical operators, only numeric fields can be used.

Functions

Functions are predefined expressions that allow you to input data to complete assignment expressions. The options in this drop-down arrow appear by default. Below is an explanation of each:

Now() Returns current date and time in GMT:

  1. Select this function.
  2. Click Insert.
  3. Select a field from the Assign to Field drop-down arrow.
  4. Click Save.

Today() Returns current date and time in your time zone (The run as user):

  1. Select this function.
  2. Click Insert.
  3. Select a field from the Assign to Field drop-down arrow.
  4. Click Save.

MinDate(value1, value2) Minimum date (applies to date fields only): Returns the smaller of two specified date values. Value1 is the first of the two numeric values to compare. Value2 is the second of the two numeric values to compare.

MaxDate(value1, value2) Maximum date (applies to date fields only): Returns the larger of two specified numeric values. Value1 is the first of the two numeric values to compare. Value2 is the second of the two numeric values to compare.

DateDiff(datepart, start, end) Returns a value for the difference between a start and end date.

The datepart, start and end parameters should be enclosed with double quotes if you are not using data fields, as outlined in the steps below.

Start and end are expressions that should be resolved to a date, using any valid date formats, such yyyy-mm-dd, mm/dd/yyyy, etc.

The table below lists all valid datepart arguments and the abbreviations that can be used to replace them.

datepart

datepart

Abbreviations

year

yy, yyyy

quarter

qq, q

month

mm, m

dayofyear

dy, y

day

dd, d

week

wk, ww

hour

hh

minute

mi, n

second

ss, s

  1. Select the DateDiff(datepart, start, end) function and click Insert.
  2. The function displays as DateDiff( , , ).

  3. Place your cursor before the first comma.
  4. Enter double quotes + [valid datepart or abbreviation] + double quotes.
  5. So far, the formula should look similar to: DateDiff("year", ,)

  6. Place your cursor before the second comma.
  1. To enter a data field, click the Select Field drop-down arrow and make a selection.
  2. —Or—

    To enter a date, enter the [Date (value) function located in the Functions drop-down menu].

    For the (value) enter the end date value. For information on the [Date (value)] function, see Date(value) below.

  3. Place your cursor after the second comma.
  4. To enter a data field, click the Select Field drop-down arrow and make a selection.
  5. —Or—

    To enter a date, enter the [Date (value) function located in the Functions drop-down menu].

    For the (value) enter the end date value. For information on the [Date (value)] function, see Date(value) below.

  6. Click Done.

DateAdd(datepart, number, date) Returns a date with the given amount added to it.

The datepart, number and date parameters should be enclosed with double quotes if you are not using data fields, as outlined in the steps below.

Number is an expression that can be resolved to an integer that is added to a datepart of date. If you specify a value with a decimal fraction, the fraction is truncated and not rounded.

Date is an expression that should be resolved to a date, using any valid date formats, such yyyy-mm-dd, mm/dd/yyy, etc.

The table below lists all valid datepart arguments and the abbreviations that can be used to replace them.

datepart

datepart

Abbreviations

year

yy, yyyy

quarter

qq, q

month

mm, m

dayofyear

dy, y

day

dd, d

week

wk, ww

hour

hh

minute

mi, n

second

ss, s

  1. Select the DateAdd(datepart, number, end) function and click Insert. The function displays as DateAdd( , , ).
  2. Place your cursor before the first comma.
  3. Enter double quotes + [valid datepart or abbreviation] + double quotes. So far, the formula should look similar to: DateAdd("year", ,).
  4. Place your cursor before the second comma.
  1. To enter a data field, click the Select Field drop-down arrow and make a selection.
  2. —Or—

    Enter a numeric value.

  3. Place your cursor after the second comma.
  4. To enter a data field, click the Select Field drop-down arrow and make a selection.
  5. —Or—

    To enter a date, enter the [Date (value) function located in the Functions drop-down menu].

    For the (value) enter the end date value. For information on the [Date (value)] function, see Date(value) below.

  6. Click Done.

Date (value) - Allows you to convert a date in string form to a date type. Value is the string value.

Examples:

Formula

Sample Output

Date("12.10.2013")

12.10.2013

Date("12-10-2013")

12.10.2013

Date("12/10/2013")

12.10.2013

IIFDate( 1 > 2, Date("12.10.2013"), Date("12.12.2013"))

12.10.2013

MaxDate(Date("12.10.2013"), Date("12.12.2013"))

12.10.2013

IsNullDate(date): Allows you to determine whether or not a value exists for this date field. If it does, then it will be factored into the formula.

IsNullString(value): Allows you to determine whether or not a value exists for this string field. If it does, then it will be factored into the formula.

RemoveWhiteSpace(value): Returns the given value with extra spaces removed. This function removes both leading and trailing spaces from the text as well as converting blocks of multiple spaces within the text into a single space character. (e.g. ‘the   text’ becomes ‘the text'). “Value” is the text to remove extra spaces from.

Trim (value): Removes white space from the beginning and end of the given value. “Value” is the text to strip preceding and following white space from.

nd (similar to VB Instr): Finds a string within a string & returns the character number where the search string is found.

Left (source value, length number): Use to find a specified number of characters from the left (beginning) of a string.

Mid (source value, start position number, length number): Returns the middle x characters of a string, starting at position y within the string.

Right (source value, length number): Returns a specified number of characters from the right (end) of a string.

Substitute (source value, search value, substitution value): Changes characters within a string.

Replace(source value, start position number, length): Used to replace characters within a text.

Rev (source value): Returns a reversed string. For example, REV(Luke) returns ekuL.

Len (source value): Returns the length of the inputted variable

IIFString (Condition, true string, false string): If and only if (applies to text fields only):

Evaluates the given condition and returns the text for true string if the evaluation succeeds or the text for false string if the evaluation fails.

Condition: Boolean expression used to determine which text to return.

True String: If condition evaluates success then this text value is returned.

False string: If condition evaluates failure then this text value is returned.

  1. Select this function.
  2. Click Insert.
  3. Place your cursor before the first comma, enter the condition by selecting a data field from the Select Field drop-down arrow, and then click Insert.
  4. Place your cursor before the second comma. To use a data field for the if false value, make a selection from the Select Field drop-down menu and click Insert.
  5. —Or—

    To enter your own value, enter double quotes + [true string value] + double quotes.

  1. Place your cursor after the second comma.
  2. To use a data field for the if false value, make a selection from the Select Field drop-down menu and click Insert.

    —Or—

    To enter your own value, enter double quotes + [false string value] + double quotes.

  3. Click Done.

IIFNumber (Condition, true string, false string): If and only if (applies to number fields only):

Evaluates the given condition and returns the number in true number if the evaluation succeeds or the number in falsenumber if the evaluation fails.

Condition: Boolean expression used to determine which number to return.

True String: If condition evaluates success then this number value is returned.

False string: If condition evaluates failure then this number value is returned.

  1. Select this function.
  2. Click Insert.
  3. Place your cursor before the first comma, enter the condition by selecting a data field from the Select Field drop-down arrow (e.g. Contract Sum), and then click Insert.
  4. Place your cursor before the second comma. To use a data field for the if false value, make a selection from the Select Field drop-down menu and click Insert.
  5. —Or—

    To enter your own value, enter double quotes + [true number value] + double quotes.

  1. Place your cursor after the second comma.
  2. To use a data field for the if false value, make a selection from the Select Field drop-down menu and click Insert.

    —Or—

    To enter your own value, enter double quotes + [false number value] + double quotes.

  3. Click Done.

IIFDate(Condition, true string, false string): If and only if (applies to date fields only):

Evaluates the given condition and returns the date in true date if the evaluation succeeds or the date in false date if the evaluation fails.

Condition: Boolean expression used to determine which date to return.

True String: If condition evaluates success then this date value is returned.

False string: If condition evaluates failure then this date value is returned.

  1. Select this function.
  2. Click Insert.
  3. Place your cursor before the first comma, enter the condition by selecting a data field from the Select Field drop-down arrow (e.g. Contract Sum), and then click Insert.
  4. Place your cursor before the second comma.
  5. To use a data field for the if false value, make a selection from the Select Field drop-down menu and click Insert.

    —Or—

    To enter your own value, enter double quotes + [true string value] + double quotes.

  1. Place your cursor after the second comma.
  2. To use a data field for the if false value, make a selection from the Select Field drop-down menu and click Insert.

    —Or—

    To enter your own value, enter double quotes + [false string value] + double quotes.

  3. Click Done.

MinNumber(value1, value2) Minimum number: Returns the smaller of two specified numeric values. Value1 is the first of the two numeric values to compare. Value2 is the second of the two numeric values to compare.

MaxNumber(value1, value2) Maximum number: Returns the larger of two specified numeric values. Value1 is the first of the two numeric values to compare. Value2 is the second of the two numeric values to compare.

IsInList(list, value): Returns true if a value is contained in either (1) the selected values of a Multi Pick List Custom Field column, or (2) the values in a comma delimited string. IsInList() must be used as part of a condition in formulas like IIFString, IIFNumber, or IIFDate.

List is a Multi Pick List Custom Field column, or comma delimited string.

Value is the value to search for.

Examples:

Formula

Sample Output

IIFString(IsInList({CustomProject.ProjectMPLCustomField}, 2), 2 was in the list, 2 was not in the list)

2 was in the list

IIFString(IsInList(“one, two, three”, “two”), “two is in the list”, “two is not in the list”)

two is in the list

Str(value) Converts a value to a string. This function gives you the ability to output a string or empty value. Value is the value you want to convert to a string. conversion of date diff..works best for string or number

Formula

Sample Output

Str(1+2)

3

IIFString (1>2, "", Str(DateDiff("d", Today (), {Portals.CreateDate})))

5

Done

Click to save the formula. If the expression is invalid, an error message displays for you to make necessary corrections.

Cancel

Click to cancel the operation and close this window.