You can create calculated fields within a questionnaire to be used as answers to questions or applied as default. Please note, a good understanding of Excel formula and/or C# coding is required to use this functionality.
To create a calculated field, you must first reach stage 4 of a questionnaire. A button, to the left of Notes in the top right of the screen, will load a modal window when clicked. The button also displays the number of Calculated Fields associated with the questionnaire (it is grey when there are no Calculated Fields, but blue when there are one or more)
Calculated Fields Management modal
This screen contains a grid listing the Calculated Fields which have been created, with an edit button against each to edit. There is a '+ Calculated Field' button to create a new Calculated Field; clicking this button, or the Edit button in-line with an existing calculated field, opens the Add/Edit Calculated Field modal. If you want to delete a Calculated Field, you must click the Edit button.
Add/Edit Calculated Field modal
In this modal you are able to create or amend a Calculated Field. You will need to give your Calculated Field a name so that you can reference when answering a Questionnaire question or applying a default.
Calculated fields are created using expressions. The expressions you create can comprise of functions and operators (listed below) as well as references to Tide/Bordereau fields. When selecting the Fields option, the following fields are available for selection:
• Tide fields to which there is a Bordereau Column supplied.
• Bordereau Columns (with the format ‘Bordereau Column: [Column Name]’.
When you attempt to save the Calculated Field, Tide will validate the expression. If it is valid, the Calculated Field will be saved, but if not an error will be reported. You must correct the error before the expression can be successfully saved. It is possible to delete a calculated field if it has not been used as the answer to a Questionnaire question or default.
Available Functions
| Function | Description |
| concat(text1, text2, ...) | Concatenates up-to four specified String values into a single String. e.g. concat([FirstName], " ", [LastName]) - combines the values in [FirstName] and [LastName] with a space in between. |
| substring(text, startIndex, length) | Extracts a substring from the current String (text) that begins at a specified character position (startIndex) and ends after a defined number of characters (length). The starting character position is a zero-based and therefore the first character in the string is at index 0, not index 1. e.g. substring([Field1], 0, 5) - extracts the first 5 characters of the value in [Field1]. |
| replace(text, oldText, newText) | Returns a new String in which all occurrences of a specified String (oldText) in the current String (text) are replaced with another specified String (newText). e.g. replace([Field1], "£", "") - replaces all instances of '£' in [Field1] with blank text, thus removing it from the value. |
| toLower(text) | Converts the current String (text) to lower case. e.g. toLower([Field1]) |
| toUpper(text) | Converts the current String (text) to upper case. e.g. toUpper([Field1]) |
| indexOf(text, searchText) | Returns the zero-based index of the first occurrence of the specified String (seachText) in the current String (text), starting from position 0 and searching from left to right. e.g. indexOf([Field1], "hello") - returns the start position of the String "hello" in [Field1]. |
| lastIndexOf(text, searchText) | Returns the zero-based index of the first occurrence of the specified String (seachText) in the current String (text), starting from the last character in the current String and searching from right to left. e.g. lastIndexOf([Field1], "hello") - returns the start position of the String "hello" in [Field1]. |
| startsWith(text, searchText) | Determines whether the beginning of the current String (text) matches the specified String (searchText). e.g. startsWith([Field1], "hello") - returns True if the value in [Field1] starts with "hello", otherwise False is returned. |
| endsWith(text, searchText) | Determines whether the end of the current String (text) matches the specified String (searchText). e.g. endsWith([Field1], "hello") - returns True if the value in [Field1] ends with "hello", otherwise False is returned. |
| trim(text) | Removes all leading and trailing white-space characters from the current String (text). e.g. trim([Field1]) |
| length(text) | Returns the number of characters in the current String (text). e.g. length([Field1]) |
| if(expression, valueIfTrue, valueIfFalse) | Returns a value based upon an Expression (expression) being True or False. If the Expression is True, the valueIfTrue value is returned, otherwise the valueIfFalse is. e.g. if(equals([Field1], [Field2]), [Field1], [Field2]) - this would return the value of [Field1] if [Field1] = [Field2], otherwise the value of [Field2] would be returned. |
| equals(value1, value2) | Returns a Boolean value based upon the comparison of the supplied Values. If both values are the same, True is returned otherwise False. e.g. equals([Field1], "hello") - this would return True if [Field1] = "hello". |
| and(expression1, expression2) | Returns a Boolean value based upon the comparison of the supplied expressions. If both expressions are True, True is returned otherwise False. e.g. and(equals([Field1], "hello"), equals([Field2])) - this would return True if [Field1] = "hello" and [Field2] = "goodbye". |
| or(expression1, expression2) | Returns a Boolean value based upon the comparison of the supplied expressions. If one of the expressions is True, True is returned otherwise False. e.g. or(equals([Field1], "hello"), equals([Field2])) - this would return True if [Field1] = "hello" or [Field2] = "goodbye". |
| not(expression) | Returns a Boolean value based upon the supplied expression. If the expression is False, True is returned otherwise False. e.g. not(equals([Field1], "hello")) - this would return True if [Field1] is not equal to "hello". |
| int(value) | Converts the current Value (value) to the data type Integer. e.g. int([Field1]) |
| string(value) | Converts the current Value (value) to the data type String. e.g. string([Field1]) |
| float(value) | Converts the current Value (value) to the data type Float (this should be used to convert values to decimals). e.g. float([Field1]) |
| bool(value) | Converts the current Value (value) to the data type of Boolean (True or False). e.g. bool([Field 1]) |
| add(value1, value2) | Sums two specified Numeric (Float or Integer) values (value1 and value2). e.g. add(float([Field1]), float([Field2])) - sums the values in [Field1] and [Field2], it is necessary to convert the Fields to Float first to enable the calculation. |
| sub(value1, value2) | Subtracts two specified Numeric (Float or Integer) values (value2 is subtracted from value1). e.g. subtract(float([Field1]), float([Field2])) - subtracts the values in [Field2] from [Field1], it is necessary to convert the Fields to Float first to enable the calculation. |
| utcNow() | Returns the current Date/Time, expressed as the Coordinated Universal Time (UTC) timezone. e.g. utcNow() |
| addDays(dateTime, days) | Adds the specified number of days (days) to the current Date/Time (dateTime).Negative numbers can be used to subtract. e.g. addDays([Field1], -10) - subtracts 10 days from the value in [Field1]. |
Available Operators
| Operator | Description |
| + | Sums Numeric (Float or Integer) values. e.g. float([Field1]) + float([Field2]) + float([Field2]) |
| - | Subtracts Numeric (Float or Integer) values. e.g. float([Field1]) - float([Field2]) - float([Field2]) |
| / | Divides Numeric (Float or Integer) values. e.g. (float([Field1]) / float([Field2])) / float([Field2]) |
| * | Multiplies Numeric (Float or Integer) values. e.g. (float([Field1]) * float([Field2])) * float([Field2]) |