Equations, Rules, and Inheritance

InfoQube supports equations for computing the values of fields. Equations can pull values from other fields on the same item, or from parents and children (column inheritance and equations).

There are two types of row equations and two types of column equations:
  • Row Equation: Updates the current field’s value when other field values change
  • Auto-assignment Rule (or Push Equations): Updates other fields when the current field is changed
  • Column Inheritance: Children inherit the parent field value
  • Column Equation: Parent field value is calculated from the children
Equations can use built-in functions and user-defined functions (see 7. Visual Basic Editor for details)

1. Row Equations

Equations compute a value from the values of other fields, when used in an equation we call these field arguments. As an example, the following equation is equal to the value of the SubTotal field times 0.08.
0.08 * [SubTotal]

In this example SubTotal is considered a field argument. Anytime that field’s value is changed on an item, this equations value will change as well.

Depending on the type of equation that will result in another field’s value being updated. You use a field argument by just listing the field name in an equation inside square brackets (note that the square brackets are actually optional, but recommended in case you field name matches a function name or reserved word).

If you are familiar with Excel the idea is the same, you can write an equation in one cell that depends on other cell arguments. When those cells are changed, then the first cell’s value is updated. One difference though is that equations only need to be defined once at the field level, then they will apply for that field for all items (without having to copy the same equation to every row like you do in Excel).

Another big difference is that equations only update field values, which means you can manually change them to something else if you like (if the equation fires again you will lose the change though).

Equations can work with all the field types (e.g. text, dates, numbers and yes/no), and can use any operator/function that Microsoft VBScript supports. You can also define your own User Defined Functions to use in your equations as described below.

Some other things to keep in mind about equations:

  • All types of values are supported (e.g. text, dates, numbers and yes/no).
  • Fields can be made read-only (using the Field Management Dialog), which means the only way to set their value is through equations (e.g. a "Total" field)

A Row Equation updates the current field’s value when other field values change. It’s the one that is most similar to what you see in Excel.
Let’s start with an example. The following image shows the Equations section of the Field Management Dialog for the field Taxes. The Taxes field is in the Sample data file if you want to follow along.

In this example, the current field Taxes will be set to 0.08 times the value of the field argument SubTotal. This Row Equations will apply to the Taxes field for all items.
If you update the SubTotal field of any item, then the Taxes field will be updated automatically using the equation.
Another difference from Excel is that Row Equations can be circular.
You could have two Row Equations like we show below, one on the StartDate field and one on the EndDate field. If you manually edit the EndDate then the StartDate gets updated. If you manually edit the StartDate then the EndDate gets updated.
  • StartDate’s Row Equation: =[EndDate] - [Duration]
  • EndDate’s Row Equation:  =[StartDate] + [Duration]
Some other things to keep in mind about Row Equations:
  • Row Equations can cascade. So if field X has a Row Equation that depends on Y, and Y has an equation that depends on Z (X <- Y <- Z). Then editing Z will update Y which will update X.
  • Equations cannot update the original field you edited though. So if field X has a Row Equation that depends on Y, and Y has an equation that depends on X (X <- Y <- X). Then editing X will update Y, but your original edit to X will not be overwritten.
  • When adding a new row equation or when changing the equation parameters, it is necessary to recalculate dependencies. Click the ... menu (just right of the F1 link and select Recalculate Dependencies

Here's another example from the default New IQBase (with sample data)
 
Duration field is a number field.
It's row equation reads:
WorkTime([StartDate], [EndDate], 8) - ( [NonBillable] )
  • to figure out a period of time you need to use the WorkTime(X, Y) equation, where X & Y are date fields (X is the beginning of the time period)
  • The number 8 allocates eight hours per day when appropriate - you can change this number as you wish -  if not included, the day is treated as 24 hours.
  • In this example of the Duration field, we are working towards calculating a billable time period so we subtract the field NonBillable (also a number field) at the end of the equation.
  • Note that once the Duration field has content, it will stay the same unless modified manually. Changing the StartDate will cause the EndDate to change - Duration will remain the same. Likewise adding a number to the NonBillable field will cause the EndDate to change - so again, Duration will remain the same. For more info about the way this can work see this post Duration field ~ some queries
  • Re the Autoupdate option: it's only important to tick if the value of the field would change with each passing day (i.e. current date is a parameter in the calculation). See also next paragraph.

Recalculating

Instead of waiting for a field argument to change, you can force a Row Equation to recalculate. You can do this by selecting the field and pressing Shift-F9. This will cause the Row Equation for that field to immediately execute.

You can also recalculate a field for all items by clicking the recalculate button next to the Row Equation’s text field in the Field Management Dialog.

You also have the option to have a field’s Row Equation recalculate for all items daily. If you check the Auto-update checkbox  above the Row Equation’s text field, then InfoQube will recalculate the field for all items on each change of day (at midnight or the next time you start InfoQube). Note, Auto-updating can be a lengthy process, so use it only when required.

NOTE, when recalculating Row Equations will only fire if they have a field argument that has a value. If none of the field arguments have a value then the Row Equation will not fire. That means Row Equations with no field arguments will never fire (e.g. =Now()).

2. Auto-assignment Rules

While Row Equations update the current field when other fields change, Auto-assignment Rules actually do the reverse. They update other fields (called target fields) when the current field is changed. Their syntax is:
<Flags>:<TargetField> = <Equation>

Because Auto-assignment Rules update other fields, you have to list what target field to update. You also need to provide a flag that indicates when that field should be updated. For example, you may only want to update it when the current field is first added, but not when it’s modified. There are three flags you can use (separately or together):

  • A:  Update target field when the current field is added (initially no value in the current field)
  • M:  Update target field when the current field is modified (initially there is a value in the current field)
  • E:  Update target field when the current field is erased (the current field had a value and you deleted it)
    (Flag D: for 'Delete' is synonymous to E:)
  • Z or anything else: Use this flag to disable a rule. Only A, M, E, D will trigger, all other flags will not trigger

You can have as many auto-assign rules as you wish separated by vertical bars (or a new line), they'll be executed in the sequence that they are entered.

Here is a simple example from the sample file on the field Archived. When you first add a value to the Archived field, that item’s Notes field will be set to “Archived”. If you later on erase the Archive field for that item, then it will have its Notes field changed to “not archived”.

Some other things to keep in mind about Auto-assignment Rules:

  • They are actually more efficient than Pull equations, since it is the current field that triggers the equation. Plus they allow many equations to be triggered by a single field change.
  • Equations can cascade. So if A has a rule that updates B, and B has a rule that updates C (A -> B -> C); then editing A will update B which will update C.
  • Rules cannot change the original field you edited though. So if A has rule that updates B, and B has a rule that updates A (A -> B -> A); then editing A will update B, but your original edit to A will not be overwritten.
  • Auto-assign rules can also add / remove tags:
    • Add a Tag: Use #TagName or #TagName=-1 or #TagName=1
    • Remove a Tag: Use #TagName= or #TagName=0
See Examples section below !
 

3. Column Inheritance

  • New children inherit the parent's value
  • Any changes to the parent item updates children (that have the same value as the parent)
  • Inherited value is not read-only, so it can be changed. If the child value is different than the parent, it will no longer inherit the parent's value
To use Inheritance:
  • Set the Inheritance button ON
    (Column inheritance and equation (below) are mutually exclusive)
  • Can delete on item move: When a sub-item is moved away from a parent, from which it inherited a field value, should the inherited value be deleted or not
     

4. Column Equations

Column Equations work with the outlining capabilities of InfoQube. Say you are a consultant and need to write a proposal. You can use InfoQube to describe the project and break it down into tasks and sub-tasks, etc. If you set-up a Duration column to perform Column level sum, the project duration and task duration will be automatically calculated from the sub-tasks. No need to set-up complicated equations to total each group of cells, that bringing that up to the next level, etc as you would need to do in Excel.

The equation is defined once at the field level:
Parent = SUM (children)

That's it! Make any changes to a sub-sub-task. The project duration is automatically updated. Same for project cost. A great time saver
Hierarchy Equations can also work in reverse - children's field values can be computed based on their parent's field value.

Hierarchy Equations use User Defined Functions (see below for more information on Functions) to define how to compute their values. The User Defined Function takes as its argument an array of the sub-item field values and returns the computed value for the parent item's field. For example:
 

Function Sum(x) ' Calculates the sum of the array x

  • dim d, i
  • d=ubound(x)
  • for i=0 to d
    • Sum=Sum+NZ(x(i))
  • Next

End Function
 
The following basic functions are already included:

  • Sum
  • Max
  • Min
  • Avg

Hierarchy Equations with Parameters

It is possible to have Hierarchy equations based on many parameters. The syntax is: Parent = YourVBScriptFunction(Field1, Field2, ...
Typically, this can be used to have conditional hierarchy sums (sums of only some of the children).  For example if the hierarchy equation is: Parent = mysum(Billable, Children)  where mySum is a VBScript function:
Function mySum(IncludeIt, x)  ' Calculates the sum of the array x if IncludeIt is true
 dim d, i, OK
 ok=false
 d=ubound(x)
 for i=0 to d
  if IncludeIt(i)=true then 
   mySum = mySum+NZ(x(i))
   ok=true
  end if
 next
 if ok=false then mysum=null
end Function
In this case, the parent will take the sum of all children that have the field Billable checked.

5. Getting Row and Hierarchy Equations to work together

When a cell value (i.e. an item field value) is governed by both a row and a hierarchy equation, the value will be:
  1. The Hierarchy equation returned value if not null
  2. The Row equation otherwise
     

6. User-defined Functions

See 7. Visual Basic Editor for details
 

7. Examples

Row Equation Example 1

 Here is an example of using Auto-update and using a User Defined Field. For the Urgency field below, is required, is the Urgency field, as the CalcUrgency function uses the current date (see "now" below) to get an Urgency number (>0 is late):

Function CalcUrgency(Due, Duration, PDone, Done)   ' calculates an Urgency figure for tasks >0=late or will be late based on Duration and PercentDone
  if isnull(Done) and not isnull( Due ) then
     CalcUrgency = int( int(now) - int(cdate( nz( due ) )) + (nz(Duration) * (100-nz(PDone))/100 * 7/5) )
     if CalcUrgency<-60 then CalcUrgency=null
  else
     CalcUrgency = null
  end if
End Function

Auto-assignment Example 1

Here is an example that calculates the color when a field is added or edited and clears the ItemColor when the field is erased:
AM: ItemColor=CalcColor ([DurationHr] - [BudgetHrs]) | E: [ItemColor]=

Auto-assignment Example 2

Here's a rule, written in the "Auto-Assign Rules" of the item field :

AM:Food=HasKeyword([Item],"Breakfast",-1)|E:Food=

This first part of the rule will check the Food target field if the field Item has the keyword "Breakfast". For yes/no fields -1 means enabled and 0 means empty.
The second part means that when the item field gets emptied, the food field is unchecked.

Auto-assignment Example 3

You want the Today field to turn on automatically when the Urgency field gets to zero. If Urgency exceeds zero (you are late!), the Today field is not changed. This uses the iif statement (more info):

M:Today=iif([Urgency]<0,0,iif([Urgency]=0,-1,[Today]))

Auto-assignment Example 4

If an item is marked pending, then the {pndg} wikitag is added automatically:

A:WikiTag=AddKeyWord([WikiTag],"{pndg}") | E:WikiTag=RemoveKeyWord([WikiTag],"{pndg}")

Auto-assignment Example 5

Strike an item when marked done. For the Done field:

AM:[ItemFont]=SetFontAttrib([ItemFont],"S",-1) | D:[ItemFont]=SetFontAttrib([ItemFont],"S",0)

Last Name and First Name Auto-fill Example

How to automatically fill the item field with the "last name" and "first name" of a contact ?

 

7. Errata/References

The following are outstanding questions or issues with this page - feel free to fix if you know the answer!