Formula Fields

One of the benefits of number fields in Centerbase is that you can set them as calculated Formula Fields.  These can automatically calculate values based on other fields either directly on the item, connected through a lookup, or roll-up values across linked items.

In the Centerbase Admin tool under Admin -> Item Manager, go to the fields list of the item you want to set a formula for.  Click on or add a Number field and you will have the option to add a formula.

Basic Formulas

A basic formula simply takes number fields and adds, subtracts, multiplies, and/or divides them.  The field can be directly on the item or connected through a lookup field.  Add a field by simply clicking the Fields button at the top.  This will show all valid number fields that can be added to this formula.  If you want to include a field that is connected through a lookup field, simply add that field to the data view for this item type and it will be available.  You can either select an operator by clicking the drop down or type it in.

Roll-up Formulas

A roll-up formula looks at items that are linked to this item and calculates an aggregate value.  This can take a simple count of the linked items or it can get a sum/min/max/average of a number field on the linked items.  To create a roll-up formula, go to the normal formula editor and select New Roll-up.  First choose what type of linked item you want to roll-up.  Next choose what type of roll-up you want.  If you choose Sum, Average, Max, or Min you must also choose what number field from the linked item to aggregate.  So for instance if you wanted the average of all the salaries of the contacts linked to a record, you would choose Contacts as the linked type, Average as the Roll-up type, and Salary as the Field to Aggregate.  You can also limit or expand the results by choosing a query.  This can allow you to control what linked items are part of the aggregate (such as only contacts living in Texas).  Formulas by default do not include Inactive items; however if you setup a query that does include inactive, then a formula with that query will include them as well.

Further Details, Limitations, and Workarounds 

Formulas can build upon one another.  So you can have (for example) a formula that is calculated based on two fields, then another formula that uses the first formula in its calculation, and a roll-up that aggregates the second formula.

Currently, many billing related functionality does not trigger a formula connected to it to recalculate.  For instance, if you had a formula that sums the balance of all invoices connected to a matter, receiving a payment will usually not cause this formula to be recalculated. 

One way to get around a formula being out of date is manually telling it to recalculate.  Simply go to the Item Manager, click the Edit Formula button, and hit Save without making any changes.  This will cause the formula field (and any formulas that depend on this field) to recalculate system wide.

Centerbase support can setup two types of date based formulas.  We can setup a number field to calculate the difference between two date fields in a unit of time (days, weeks, months, quarters, or years).  This CANNOT be used to calculate the difference between a date field and TODAY as formulas are not recalculated as time passes, only when changes are made.  The other type of date based formula can be setup on a date field by taking a date field and adding or subtracting a unit of time (days, weeks, months, quarters, or years).  The number of days (for example) that are added or subtracted can either be hard-coded (such as Date + 3 days) or can be pulled from a number field (such as Date + Payment Terms).  Once again, the date that is used cannot be TODAY, it would have to pull from a date field, though you can have the date field default to Today when a record is created.

The same "today" limitation applies to queries on formulas.  So you can't setup a formula that (for example) showed the sum of all bills issued to a client in the last 3 months.  As this formula would need to update as days passed, it would not stay up to date without triggering a manual recalculation when needed.