Formula Fields

Purpose: To have custom fields within LegalServer that are automatically calculated based on the values in other fields. 

Cost: TBD. This feature is in Beta.

Formula fields are available on a limited set of modules within LegalServer and a limited array of formulas. The resulting field can be displayed on Forms, Profiles, Reports, or API calls.

Formula field values are never stored. The value is derived (recalculated/reevaluated) each time the formula field is displayed.

• Creating a New Formula Field
  • Formula Editor
• Using Formula Fields
• Examples
  • Adding Two Numbers
  • Multiplying Two Numbers and then Subtracting a Third
  • Date Comparison Resulting in Different Text Strings
  • In the Last 90 Days
  • Concatenating a String to Link to a LegalServer Process from a Report
• Planned Expansions/Known Bugs

Creating a New Formula Field

Go to Admin -> Custom Field Management. There is a tab for Formula Fields.

You can use the Plus in the corner of that list view to add a new formula field or you can go to Actions -> Create Formula Field.

Formula fields are available on certain modules. Currently that includes:

• Activity (note that while this is included in the dropdown, there are no dynamic forms for Activities, so this may not be very useful)
• Case/Matter
• Calendar Event
• Organization
• Organization Phase
• Outreach
• Project
• Service
• Timekeeping
• User

The fields below the Formula Editor are treated the same as with any custom fields in LegalServer.

Formula Editor

There are five aspects to the Formula Editor:

• Operators - These identify what you want to do with the Operands, Groups, or Branches. When you add one of those you also have to select the Operator you want performed on the subject you select. Selecting the Operator means that the fields available will be filtered based on the type. The available options are:
  • Logical Operators
    • All of These are True
    • Some of These are True
    • None of These are True
    • One or More of These are Not True
  • Mathematical Operators
    • Addition
    • Subtraction
    • Multiplication
    • Division
  • String
    • Concatenation
  • Date Operators - the first operand is the date to be tested, the second (which appears after an "IS ON", " IS AFTER", "IS ON OR AFTER", "IS BEFORE", or "IS ON OR BEFORE" header) is the controlled date. If you add an additional (third or more) operand, that also becomes a requirement - "IS ALSO ON", etc.
    • Date Equal to
    • Date Greater Than
    • Date Greater or Equal to
    • Date Less Than
    • Date Less Than or Equal to

• Operands - These are fields combined with a potential function. 

  

• Fields - These are the fields that you want to modify. 
• Functions - These are the filters/functions that you want to do on a specific field. Functions do not need to be selected if you do not want to use one of them. They are type specific. Meaning if you pick a text field, you will see a different set of options than if you pick a number. Options include:
  • lookup field options
    • any of
    • none of
    • empty
    • not empty
  • Text field options
    • trim - this removes spaces at the beginning or end of the text. 
    • lowercase - this transforms everything to lowercase
    • uppercase - this transforms everything to uppercase
    • left (Note that this does not currently work)
    • right (Note that this does not currently work)
    • replace - this offers two 
  • number field options
    • absolute value 
    • ceiling - this rounds the number up to the next whole number
    • floor - this rounds the number down to the next whole number
    • round - this offers a second field to indicate how many decimal places to truncate this number to
    • truncate - this offers a second field to indicate how many decimal places to truncate this number to
  • date field options
    • add days
    • subtract days
    • remove timestamp
    • set timezone
    • When used for Logic Options, there are other functions availabile:
      • date difference (days) - from a given date
      • date difference (string - from a given date
      • day
      • month

      • year

        Groups - These are groups of Operands. 

        

        Groups are were the formulas really come into play. Operands represent individual fields. Groups allow you to combine those fields in different ways. When trying to parse the order of operations, consider Groups to be the equivalent of parentheses. The available operators here are the same as with the general formula editor:

  • All of These are True
  • Some of These are True
  • None of These are True
  • One or More of These are Not True
  • Date Equal to
  • Date Greater Than
  • Date Greater or Equal to
  • Date Less Than
  • Date Less Than or Equal to
  • Addition
  • Subtraction
  • Multiplication
  • Division
  • Concatenation

• Branches - These represent IF/THEN/ELSE statements. Within the IF section, you can have a logical determination to see if the values are true. If the values are true, then it will use the THEN section. If they are false, it will use the ELSE section. You can nest these branches and use groups and other values within this logic framework. If the IF section, it will require the use of the logical operators.

  

By default, you start with a group waiting for either Operands or Groups to be added. In each Group, you have to identify the operation you intend to use. Once you have the operation, you can then add the Operands (fields) you want to do the calculation with.

To use the Operand, you select the field and then identify a function if you also need it. In each Group, you have to identify the Operation that is happening within that Group. It does not have to be the same Operation that is happening in the overall field.

Once a field is saved, the formula can be edited again in the future. This will change the output values displayed in LegalServer.

Formula field values are never stored. The value is derived (recalculated/reevaluated) each time the formula field is displayed.

Using Formula Fields

Formula Fields can be put on Forms or Profiles. They are not editable.

They can be accessed in Reports. When they are returned in reports, they will not appear in the custom module subtable. They will appear with the system fields for that particular module.

They can be used in New Style Document Templates.

They can be returned via an API call.

Formula Fields can be electronically transferred to other sites that have Formula Fields. Formula Fields transferred to sites that do not have Formula Fields will not be editable on the receiving site.

Examples

Adding Two Numbers

Of course that example is rather illogical.

Multiplying Two Numbers and then Subtracting a Third

Concatenating two Strings and a Literal Value

Date Comparison Resulting in Different Text Strings

Identifying Time Records from the Last 90 Days

Concatenating a String to Link to a LegalServer Process from a Report

This allows you to show a column in a report that links directly to a process for that specific record. This allows you to open

This requires a few steps.

You need to know the URL of the process you want to link to. This is best found by navigating to the page from the user interface. An example might be:

https://michael-demo.legalserver.org/matter/process/dynamic_16/form_loader:39/edit/94

or

https://michael-demo.legalserver.org/phase/process/dynamic_172/form_loader:487/edit/295393

In both cases, the important thing to recognize is that the integer at the end is the Database ID for the related record. In the top example, it is on the matter record. In the bottom example it is the Service/Phase Database ID. This Database ID is available in the formula field and can be used to concatenate a string to open the page.

The concatenated string will look like this:

The first literal value is typically something like:

<a href="/matter/process/dynamic_16/form_loader:39/edit/

Note that this aligns with the URL from the first example, except that the domain ("https://michael-demo.legalserver.org") is missing. By not explicitly stating the domain, the formula field will work whether it is on a Live or Demo site and can be copied back and forth during a Copy Live to Demo or a GoLive for a new Client.

The second operand is the relevant Database ID. In this case, it is the matter's Database ID.

The third operand is a second literal value:

 " target="_blank">Add Case Note</a>

Note that it starts with the closing quote that matches the sole quote in the first literal. The target="_blank" is optional, but this will force the link to open in a new tab as opposed to the default location of the current tab. The text between > and < will be what users see and that can be defined as needed for clarity.

When adding this field to a report, the next step is to then change the format of the column. It needs to be set as Unescaped (Raw HTML) .

This will then have the column show up as proper HTML links.

Known Bugs

• Formula Fields with Logical Operators display as N/A or 1.
• Multiselect fields are not available in Concatenation operations.
• Custom Field usage in Formula Fields can be problematic and break Guided Navigation
• Special Fields available on the Matter are not available via Formula Fields. These include things like the Client's Primary Address — fields stored in different tables that are then linked to the Matter. These may be added on a by request basis in the future, but cannot be added by default.