Formulas are equations that can be used to perform calculations, manipulate characters and dates, return information, or test data. A formula can contain any or all of the following: fields, constants (numbers, text, dates), operators, and functions. In Designer Studio you can create formulas in the Formula Designer.
In this section:
• Formula Designer
• Parts of a formula
• Create a formula
• Add fields to a formula
• Add constants to a formula
• Add operators to a formula
• Add functions to a formula
• Using operators
• Formula properties
• Round a number
• Test a formula
• Open and edit a formula
CHAPTER PART 4: FORMULAS
Formula Designer
Formula Designer is a tabbed pane that allows you to construct and
manage the formulas contained in your module. When you open a formula you are opening an instance of the Formula Designer and you will
automatically see a contextual tab in the ribbon that shows options specifically related to formulas.
A key feature of Formula Designer is the ability to incorporate fields in formulas. This allows you to use any information that is stored in the ALB database. To achieve this, Formula Designer contains a field picker. From here you can quickly and efficiently find fields and add them to your formula.
Formula Designer also includes a validation mechanism. Based on a formula's properties, it instantly knows what the expected output should be and it also knows how valid formulas should be constructed. As you build a formula, it automatically validates as you go and indicates if there are any errors. It is also possible to test formulas against live matters from directly within Formula Designer. This is very useful, as it allows you to make sure your formula is working as intended.
Fig. Formula Designer
A. Validation box B. Formula pane C. Operator buttons D. Operand tabs E. Test pane F. Field picker
• Formula Designer is only displayed when you open a formula.
• The validation box is expandable and shows whether a formula is valid or not.
• The formula pane displays the formula currently being designed.
• Operator buttons; mathematical, comparison, logical, and concatenation.
• Brackets allow you to set the operator precedence.
• Operand tabs include access to fields, constant values (number, text, and date), and pre-defined constants.
• The field picker (Field tab) provides access to database fields.
• The test pane is only available once a valid formula has been constructed.
Parts of a formula
A formula can contain any or all of the following: fields, constants (numbers, text, dates), operators, and functions.
Fig. Parts of a formula
1. Fields. The value of a data field stored in the database.
CHAPTER PART 4: FORMULAS
4. Functions. Pre-written formulas that take a value, perform an operation, and return a value.
Create a formula
You can create a simple formula by using constants and operators. For example, the formula 5+2*3 multiplies two numbers and then adds a number to the result. You can also create more complex formulas using fields from the database to return information, combine text, calculate interest, etc. Depending on the type of formula that you create, a formula can contain any or all of the following: fields, constants (numbers, text, dates), operators, and functions.
To create a formula:
1. On the Home tab, under Components, click Create.
2. From the drop-down menu, click Formula and then Formula again on the submenu.
3. When the Properties pane opens, complete the following:
• Formula name. Specifies a name for the formula.
• Expected type. Specifies the output type.
• Entity type. Specifies the owning entity.
• Rounding. (Number only) Specifies how the rounding of decimal places is handled.
• Decimal places. (Number only) Specifies the number of decimal places.
• Treat empty numeric values as zero? Specifies that any empty numeric values are treated as zeros.
4. Once you have completed the properties, click Designer and Formula Designer automatically opens in Design mode ready for you to build your formula.
• Add operators
• Add functions
• If a formula is invalid, then the Validation box will give you an indication of why and you are prevented from saving it until you have corrected the error(s). Once you have corrected the errors, the Validation box will read "Formula is valid".
• To test the formula, click Test Formula at the bottom of Formula Designer and enter a matter, client, or contact reference.
6. Click Save.
NOTES
• Invalid formulas cannot be saved. The Save button is unavailable.
• You can enter as many constants and operators in a formula as you need.
• For more about setting the above properties, see Formula properties.
Add fields to a formula
When building a formula you will probably want to use data that is already stored in the ALB database. This allows you to extract information and use it in your formula results. For example, you may want to check a client's credit limit. Adding a field from the database uses the Field Picker.
To add a field:
1. Click the Field tab to display the Field Picker.
2. In Get Fields From, select the desired entity, e.g. matter. This
defaults to the entity associated with the formula when it was created.
However, you can easily pull in fields from other entities by changing it here.
• NOTE When a formula is set to Client, Get Fields From defaults to Primary Client. However, because ALB allows multiple clients to be attached to a matter, it is possible to choose other clients here as well.
CHAPTER PART 4: FORMULAS
keyword in the Find Field box. The Field Picker will then be filtered based on what you typed.
4. Select the field that you want to use and click Add field or double-click. The field is added to the Formula pane.
Add constants to a formula
A constant is a value that is not calculated—it always stays the same. For example, the number 220, the text "Credit Limit", and the date
21/06/2010 are all constants. In Formula Designer, you can insert number, text, and date constants using the associated tabs. These allow you to enter literal values for each type. A number of pre-defined
constants are also available for adding things like a new line, a comma, etc.
To add a number to a formula:
Use this procedure to add a fixed number to your formula. For example, maybe you want to subtract a number of days from a date in order to calculate a new date.
1. Click the Number tab.
2. Type the number you want to use into the box provided and click Add Number. The number is added to the Formula pane.
1000.00
To add text to a formula:
Use this procedure to add fixed text to your formula. For example, let's say you needed to combine a text value with a database field in order to display a single piece of text.
To add a date to a formula:
Use this procedure to add a fixed date to your formula. For example, perhaps you want to calculate the amount of interest incurred between two dates.
1. Click the Date tab.
2. Type or lookup the date you want to use, in the box provided, and click Add Date. The date is added to the Formula pane.
09/11/2015
To add a pre-defined constant to a formula:
Use this procedure to add a pre-defined constant into your formula. For example, you may want to insert a new line in the formula or a comma.
1. Click the Constants tab.
2. Select the desired pre-defined constant from the drop-down list.
You can choose from:
• Empty string
• New line
• Logical literal: True
• Logical literal: False
• Function syntax: Comma
3. Click Add Constant. The value is added to the Formula pane.
TRUE
Add operators to a formula
Operators can be included in formulas. They let you define the type of calculation that you want to perform. For example, using the Plus sign ("+") you can combine a text value with a number or a database variable.
CHAPTER PART 4: FORMULAS
2. Use the Operator buttons to insert the desired operator or manually type using the keyboard, e.g. the Plus sign. You can:
• Perform basic mathematical operations:
• Add
• Divide
• Multiply
• Subtract
• Compare two values to return a logical answer:
• AND
• Equal to
• Greater than
• Greater than or equal to
• Less than
• Less than or equal to
• Not
• Not equal to
• Or
• XOR
• Combine one or more text strings by using +.
Add functions to a formula
Functions can be included in formulas. They are pre-written formulas that take a value, perform an operation, and then return a value. For example, using the Split function you could take the value of a postcode from a client's address and extract the first or second part of it.
To add a function:
1. In the Formula pane, place your cursor at the point you want to add the function.
• Split
• Substring
3. Click the Add button to insert the function.
4. Construct the function.
To add an AddWorkingDays function:
Use this procedure to calculate a future date based on a start date plus a number of days. If the calculated date falls on a weekend, the date will be pushed forward to the following Monday.
Syntax:
AddWorkingDays(<date or date field>,<Value>)
This syntax has these parts:
Part Description
Date or DateField
Required. The start date or the name of the field in the database that contains the start date for the calculation.
Value Required. The number of days in the future.
1. In the Formula pane, place your cursor at the point you want to add the function.
2. From the Functions drop-down, select AddWorkingDays.
3. Click the Add button to insert the function.
4. Construct the AddWorkingDays function. Example:
Use the tools provided to specify the 2 parts of an AddWorkingDays function; Date or DateField and Value. For example, to calculate the date 10 days after the last billing date, <DateField> is LastBillingDate and <Value> is 10
Function AddWorkingDays(LastBillingDate,10) would return the date 10 days after the last billing date unless it falls over a weekend in
CHAPTER PART 4: FORMULAS
To add a Contains function:
Use this procedure to check for the presence of a text value in a larger piece of text. It evaluates to TRUE when the text value is present. This is particularly useful if it is contained within a logical test.
Syntax:
CONTAINS(<TextField>,<Value>)
This syntax has these parts:
Part Description
TextField Required. The name of the field in the database that contains the value for which you want to check.
Value Required. The text value for which you want to check.
1. In the Formula pane, place your cursor at the point you want to add the function.
2. From the Functions drop-down, select Contains.
3. Click the Add button to insert the function.
4. Construct the Contains function. Example:
Use the tools provided to specify the 2 parts of a Contains function;
TextField and Value. For example, to check if a matter is a property purchase transaction, where <TextField> is Matter.Description and
<Value> is Purchase:
Function CONTAINS(Matter.Description,Purchase) would return TRUE if the value Purchase was present
To add an If function:
This syntax has these parts:
Part Description
LogicalTest Required. Any value or expression that can be evaluated to TRUE or FALSE.
TrueValue Required. The value that you want to be returned if the logical test is found to be TRUE.
FalseValue Required. The value that you want to be returned if the logical test is found to be FALSE.
1. In the Formula pane, place your cursor at the point you want to add the function.
2. From the Functions drop-down, select If.
3. Click the Add button to insert the function.
4. Construct the If function. Example:
Use the tools provided to specify the 3 parts of an If function;
LogicalTest, TrueValue, and FalseValue. For example, to merge the appropriate salutation into a document based on whether the
Salutation Envelope field is completed, where <LogicalTest> is TRUE and its value is Peter Parker:
Function
IF(SalutationEnvelope<>Empty,SalutationEnvelope,Title+Foren ame+Surname) would return Peter Parker
To add an In function:
Use this procedure to check for the presence of a value in a list of values.
It evaluates to TRUE when the value is present in the list. This is particularly useful if it is contained within a logical test.
Syntax:
<SearchValue>IN(<Value1>,<Value2>,...,<ValueN>)
This syntax has these parts:
CHAPTER PART 4: FORMULAS
Part Description
SearchValue Required. The value for which you want to check.
Value Required. The list of values that you want to check.
1. In the Formula pane, place your cursor at the point you want to add the function.
2. From the Functions drop-down, select In.
3. Click the Add button to insert the function.
4. Construct the In function. Example:
Use the tools provided to specify the 2 parts of an In function;
SearchValue and Value. For example, to check if the matter branch is either Hull, Leeds, or Manchester, where <SearchValue> is
Matter.Branch.Name and <Value> is Hull , Leeds, and Manchester:
Function Matter.Branch.Name IN(Hull,Leeds,Manchester) would return TRUE if the value any of the specified values were present
To add an IsEmpty function:
Use this procedure to check whether a value has not been captured for a field. It evaluates to TRUE when the field value is "Not set". This is particularly useful if it is contained within a logical test.
Syntax:
IsEmpty(<field>)
This syntax has these parts:
Part Description
Field Required. The field whose value you want to check.
4. Construct the IsEmpty function. Example:
Use the tools provided to specify the parts of an IsEmpty function;
Field. For example, to return 90% of the value of a quote, where
<Field> is Matter.Quote and a value has been provided, otherwise return 0.00:
Function IF(IsEmpty(Matter.Quote),0.00,0.9*Matter.Quote) would return 90% if a quote was provided and 0:00 if a quote had not been provided.
To add a Substring function:
Use this procedure to break a text string into pieces where the character positions are constant for that type of field.
Syntax:
SUBSTRING(<TextField>,<StartPosition>,<NoOfCharacters>)
This syntax has these parts:
Part Description
TextField Required. The name of the field in the database that contains the value you want to break down.
StartPosition Required. The position (or offset) in the value from which to start. Numeric.
NoOfCharacters Required. The number of characters to return.
Numeric.
1. In the Formula pane, place your cursor at the point you want to add the function.
2. From the Functions drop-down, select Substring.
3. Click the Add button to insert the function.
4. Construct the Substring function. Example:
Use the tools provided to specify the 3 parts of a Substring function;
CHAPTER PART 4: FORMULAS
Function SUBSTRING(NINo,1,2) would return AB Function SUBSTRING(NINo,3,2) would return 12 Function SUBSTRING(NINo,5,2) would return 34 Function SUBSTRING(NINo,7,2) would return 56 Function SUBSTRING(NINo,9,1) would return C
To add a Split function:
Use this procedure to break a text string into pieces where the string is essentially a list of parts with a separator.
Syntax:
SPLIT(<TextField>,<ListItem>,<ListSeparator>)
This syntax has these parts:
Part Description
TextField Required. The name of the field in the database that contains the value you want to split.
ListItem Required. The part of the value to extract. Numeric.
ListSeparator Required. The character that indicates the separation point, e.g. a space, comma, forward slash, etc.
Alphanumeric.
1. In the Formula pane, place your cursor at the point you want to add the function.
2. From the Functions drop-down, select Split.
3. Click the Add button to insert the function.
4. Construct the Split function. Example:
Use the tools provided to specify the 3 parts of a Split function;
TextField, ListItem, and ListSeparator. For example, to extract the first
Formula properties
When you create a formula you are prompted to enter a number of properties to identify it in Designer Studio and to set certain behaviours.
This information is stored in the Properties view for each formula.
Formula Properties
Property Description Options
Formula name Specifies a name for the formula Expected type Specifies the data type that the
formula will evaluate to
String Number Date Logical Entity type Specifies that the formula is to be
driven by data from a particular entity. This drives the Field Picker
Client Contact Matter Rounding Specifies how the rounding of
decimal places is handled. Only applies to formulas of data type Number Decimal places Specifies the number of decimal
places. Only applies to formulas of data type Number
0 to 9
Treat empty numeric values as zero?
(Number formulas only) Specifies that any empty numeric values are treated as zeros. For example, if you add numbers together and there are some empty values, then ticking this option flags that you are happy to count them as zero, not Not set.
NOTE This is for numeric formulas
CHAPTER PART 4: FORMULAS
NOTE
• Only the Rounding and Decimal places properties can be edited once a formula has been created.
Round a number
You may want to round a number to the nearest whole number. For example, decimal values are not significant to you or there is a legislative reason to always apply a specific rounding. The following options are available.
To round a number:
Use this procedure to specify how the rounding of decimal places is handled.
1. Open the Properties view for the desired formula.
2. From the Rounding drop-down, select one of the following:
• Always round up. Specifies that decimal places always round up.
• Always round down. Specifies that decimal places always round down.
• Round 5 and above up. Specifies that decimal places above 5 round up.
• Round 5 and below down. Specifies decimal places below 5 round down.
• Rounding examples:
The following are based on the formula 20+2.5173 which returns 22.5173.
Decimal places 0 and Always round up would return 23 Decimal places 1 and Always round up would return 22.6 Decimal places 2 and Always round up would return 22.52
To change the number of decimal places:
Use this procedure to specify the number of decimal places.
1. Open the Properties view for the desired formula.
2. From the Decimal places drop-down, select the number of decimal places; from 0 to 9.
3. Click Save on the ribbon.
NOTES
• Rounding is based on the number of decimal places that have been specified.
• Rounding only applies to formulas of data type Number.
• Rounding only occurs when the result of a formula has more decimal places than the field that stores the result.
Test a formula
When you have created a formula it is possible to test that it works. This is extremely useful, as it allows you to make sure your formula is outputting the correct information before it is used, from directly within the design environment. This is achieved by entering the code of a live client, matter, or contact.
To test a formula:
1. Create your formula.
2. Next, at the bottom of Formula Designer, expand the Test Formula pane.
3. Type a valid matter number code into Entity Ref/ID. This allows the formula to be tested against a live client, matter, or contact.
4. Click Test Formula. The result is shown in the grey box.
• If the output is not as expected, amend the formula and click Test Formula again.
CHAPTER PART 4: FORMULAS
Open and edit a formula
Some of the information relating to formulas can be changed after they
Some of the information relating to formulas can be changed after they