Calculated fields allow you to extend the data model based on data that has been configured or captured. For example, you may wish to conditionally populate the data in calculated fields based on the data captured in other fields.
Calculated fields allow you to write short scripts to extract any ShareDo data. This is ideal for document generation or template administration. As well as extracting the data, you can format it and perform basic operations on it, like comparing data and returning a value based on the comparison. For example, return only female witnesses.
Creating a Calculated Field
Calculated fields can be created in any data composer explorer. For work types e.g. legal matter go to Modeller > Work Types > {Your WorkType} > Data Composer to access the data composer. For a full list of where to find the Data Composer, see the article Data Composer.
When creating your calculated field, you should consider at which level you want to add it to your data model. Remember your work types are hierarchical. If you have a parent work type of 'matter' and you want your field to apply to all child types under matter you should add the calculated field at the matter level in the work type modeller. If you want it to be specialised for a specific work type - add it directly onto the work type.
| Example | |
|---|---|
|
Click on the + button at the top of the required area in Data Composer to add your field. For example, if you want to add a calculated field to a work type, select the work type, select Data Composer > Work Item, then click the + button as shown in this image. |
 |
| The calculated field blade opens. |
|
| Enter the name, display name, and description for your calculated field, then enter your expression (see below for help on operators and keywords). | |
Operators & Keywords
The following is a list of the operators and keywords you can use when creating calculated fields.
| Operator / Keyword | Description |
|---|---|
| + | Add numeric values or concatenate strings e.g. firstname + ' ' + lastname |
| - | Subtract numeric values. |
| * | Multiply numeric values. |
| / | Divide numeric values. |
| = | Equals. |
| <> | Not equal to, e.g. workitem.roles.client.ods.person.lastName <> 'Fred' |
| > | Greater than. |
| >= | Greater than or equal to. |
| < | Less than. |
| <= | Less than or equal to. |
| and | And. |
| or | Or. |
| true | True. |
| false | False. |
| null | Null. |
Functions
The following is a list of commonly used functions that you can use to create calculated fields. In the table below, my_field refers to the field you are running the function against.
| Function | Description |
|---|---|
| isNullOrEmpty(my_field) | Returns true if the field's value is null or an empty string. |
| startsWith(my_field, 'string to check') | Returns true if the string starts with the characters provided. |
| startsWithIgnoreCase (my_field, 'string to check') |
Returns true if the string starts with the characters provided, regardless of case. E.g., startsWithIgnoreCase(workitem.reference, 'ReF') returns true. |
| endsWith(my_field, 'string to check') | Returns true if the string ends with the characters provided. |
| endsWithIgnoreCase(my_field, 'string to check') | Returns true if the string ends with the characters provided, regardless of case. |
| contains(my_field, 'string to check') | Returns true if the field contains the string specified. |
| containsIgnoreCase(my_field, 'string to check') | Returns true if the field contains the string specified, regardless of case. |
| toUpper(my_field) |
Changes the case to upper case. E.g., toUpper(workitem.roles.client.ods.person.lastName) changes Smith to SMITH. |
| toLower(my_field) |
Changes the case to lowercase. E.g., toLower(workitem.roles.client.ods.person.lastName) changes SMITH to smith. |
| toTitleCase(my_field) |
Changes to title case (first letter upper case). E.g., ToTitle(workitem.roles.client.ods.person.lastName) changes smith to Smith. |
| truncate(my_field, number to truncate) |
Will truncate the input string to the number of characters entered. E.g. truncate(workitem.roles.client.ods.person.firstName, 1) changes James to J. |
| count(my_list) |
Counts the number of items in a list. E.g., count(workitem.roles.witness) returns the number of witnesses. |
| elemantAt(list, index) |
Looks up the specified index number in a list. For example, find the 3rd item in the list. E.g., elemantAt(workitem.roles.witness, 2) finds the 2nd witness in the list. |
| if(condition, true, false) |
If statement that will check a condition and then return specified values for true and then false. E.g., if(count(workitem.roles.witness) > 0, 'Has witnesses', 'Does not have witnesses') checks if there are one or more witnesses; if so, it returns ‘Has Witnesses'. If there are none, it returns 'Does not have witnesses'. |
| dateDiff | Return the difference between two dates. |
| dateAdd |
Add a number of years (y), days (d), or months (m) to a date. E.g., dateAdd('d', 3, '01/01/2000 12:00:00.100') adds 3 days to the inputted date. |
| format |
Format a date field. E.g., format(dateAdd('d', 3, '01/01/2000 12:00:00.100'), 'dd/MM/yyyy hh:mm:ss.fff') adds 3 days to the inputted date and formats it as dd/MM/yyyy. |
| format | Format a number by using the following formatters together with a precision e.g. 'n', 'n2' to control decimal places. 'n' - number. E.g., format(12345,'n2') returns 12,345.00 'c' - currency. E.g., format(12345,'c') returns £12,345.00 'p' - percentage. E.g., format (0.39755, 'p2') returns 39.75% |
| numberToWords |
Returns a number in words. E.g., numberToWordsWithCase(4134) returns “four thousand one hundred and thirty four”. |
| numberToWordsWithCase |
Returns a number in words in the specified case (upper, title or default lower). E.g., numberToWordsWithCase(4134, 'title') returns “Four Thousand One Hundred and Thirty Four”. |
| currencyAmountToWords |
Returns a currency in words. E.g., currencyAmountToWords(my_currency_field) returns “four thousand one hundred and thirty four dollars”. |
| currencyAmountToWordsWithCase |
Returns currency in words in the specified case. E.g., currencyAmountToWordsWithCase (my_currency_field, 'usd', 'upper') returns “FOUR THOUSAND ONE HUNDRED AND THIRTY FOUR DOLLARS”. |
Calculated Field Examples
| Calculated field example | Description and usage |
|---|---|
| if(count(workitem.roles.witness) > 0, 'Has witnesses', ‘Does not have witnesses’) | Counts the number of participants holding the witness role for a work item. If there are one or more, it returns ‘Has Witnesses’. If there are none, it returns 'Does not have witnesses'. Used in documents to change the text. |
| toUpper(workitem.roles.client.ods.person.lastname) + ' ' + toUpper(truncate(workitem.roles.client.ods.person.firstName, 1)) | For ‘John Smith’, will return ‘SMITH J’. This kind of function can be used to build up names in the format required by specific documents. You can add a calculated field to the ODS entity (e.g. person) so that every person would have this name format. |
| numberToWordsWithCase(4134, ‘title’) | Returns the number in title case e.g. Four Thousand and Thirty Four. Case options are 'title' or 'upper'. If omitted, the calculation returns the number in the default lowercase. You can use this in documents where numbers must be written as words. |
| format('12/01/2018', 'dddos MMMM yyyy') |
Format a date using the ShareDo specific 'dos' component to produce the day ordinal suffix: 2nd May 2023. |
| format(dateAdd('d',3,'01/01/2000 12:00:00.100'), 'dd/MM/yyyy hh:mm:ss.fff') |
Add / Subtract dates using the dateAdd function. |
| my_interger_field * my_other_intergerfield | Perform mathematical calculations with fields that are integers. |
Data Composer Tags in Expressions
When using tags in an expression, you can use the 'local' names for things if you wish.
For example, if I add a new calculated field for the phase of a work type, I can refer to the phase name as either workitem.phase.name or just name.
When using data composer tags in expressions, you can move up and down the work type hierarchy:
| Operator / Keyword | Description |
|---|---|
| $root | Takes you to the base work item. |
| $parent | The parent within the context of type. |
Adding a Calculated Field to a List
You are able to add a calculated field to a list by clicking on the + button underneath the filter for the list. One use for this is adding in calculated values to a list or the display of parent information in a list. For more information, see the article Calculated Lists.
