Interacting with related items (ie. other items than the one that triggers the formula)

You are not limited to using the current item's columns in your formulas. Nor are you limited to targeting columns of the current item.

With the Advanced Formula Booster, you can interact with a number of items besides the one triggering the formula which we refer to as the Current item.

In fact, you may read colums or modify columns of the following items:

  • The Previous item
  • The Next item
  • The Parent item (if the Current item is a sub-item)
  • The Sub-Items (if the Current item has sub-items)
  • The Predecessor Items (if the Current item has dependencies)
  • The items in the Same Group
  • The items in the Same Board
  • Items in Other Boards

Which item is the previous or next item?

The previous item refers to the item directly above the current one, if any. Similarly, the next item is the item directly below the current one, if present, provided that these items are in the same group as the current item.

So, in case an item is the last one of its group, it has no Next item even if there are more groups below.

How to insert columns from these related items in a formula

We tried to make it as simple as possible. When you type an opening curly brace in the Syntax editor, the Column Selector pops up. Notice the tabs at the top. You will always see Current, Previous, Next, Board, Board2, Board3. Depending on the board, you may see Group if the board is not a sub-item board, Parent if the board is a sub-item board, Sub if the board has sub-items, Pred if the board contains a dependency column.

If you select the Previous tab as shown above, you will get the list of columns prefixed with "Previous".

It works the same way as for the Current tab. In other words, you can read any column from the previous item and write to it as well. Same with the next item, the parent item, the sub-items, the group items and the board items.

If the current item is the first of its group (ie. it has no previous item), any reference to a column of the previous item will return an empty result. Same for the next item if the current item is the last of its group.

If the current item has no sub-items or dependencies, any reference to sub-items or predecessor items will return an empty string.

Selecting and renaming Board2 and Board3

To read/update columns from boards other than the current board, you need to make use of the Board2 and Board3 tabs. To select which board should be Board2 or Board3, click the Select button under the corresponding tab.

It may be useful for clarity purposes to change the alias of your Board2 and Board3 boards. To do so, click the default Alias and type a new one (6 characters max.)

Once changed, all the column placeholders are renamed accordingly.

Handling references to multiple items

The previous, next and parent item are unique, but the other related items are generally multiple. How does that work?

Writing to multiple items (sub-items or group items or board items)

If you target a sub-item, group items or board items column, all items will be modified. For instance, this instruction will set the Date column of all sub-items, all items in the same group and all items in the board to today's date.

1:{Sub.Date}=TODAY()

2:{GroupItems.Date}=TODAY()

3:{BoardItems.Date}=TODAY()

In theory, the app supports up to 500 related items. However, this number may be high when, for instance, writing to 500 Long Text columns. It could potentially result in an error or degraded performance.

Reading data from multiple items

When using a sub-item, group, board column on the right side of the formula, you will get all the values of the corresponding items in a list, i.e. in a concatenated string of all elements, separated with our list separator (| also called Pipe). Here is an example:

1:[Dates]={GroupItems.Date}-> 2024-01-01|2023-12-17|2024-02-28

These lists can easily be manipulated: for more on this, see Manipulating lists.

Same note as above. In theory, the app supports up to 500 sub-items. Use common sense and run tests!