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 items in the Same Group
- The items in the Same Board
Which item is the previous or next item?
The previous item is the item immediately above the current item IN THE SAME GROUP.
The next item is the item immediately below the current item IN THE SAME GROUP.
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 and Board. 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 or Sub if the board has sub-items.
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 and the current item is the last of its group.
What about multiple items? There may be multiple sub-items, group items, board items...
Indeed! The previous, next and parent item are unique, but the other related items are generally multiple.
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 separated with our list separator (| also called Pipe). Here is an example:
1:[Dates]={GroupItems.Date}
You can easily manipulate these lists: see Manipulating lists for more on this.
Same note as above. In theory, the app supports up to 500 sub-items. Use common sense and run tests!