Can a filter in Link Formula / formula use a value from the current record (line)?

Hi,

a question that drives me mad, but possibly I’m just not seeing the obvious.

Assume this:

  • I have 2 tables, linked togethe* r: A => B
  • Both tables contain a number column (“Number A”, “Number B”)
  • Let’s say that one line in A is linked to 20 record in B

Now I want to filter those links in A to create a sum, based on the value in “Number B”:

  • Using a constant value (let’s use 3) is no problem: I set a filter in a Link Formula column in A with the filter criterium “Number B > 3”. Or, as a formula in A: rollup("Link to B", "Number B", "sum", "{Number B} > 3")
  • But I can’t find a way to use the value from the column “Number A” for the filter to make it dynamic.

Is that even possible?

Thanks!

You are seeing quite well and spotted a feature improvement.

We are aware of the need to make filters/conditions dynamic - in link formula columns, as you pointed out, but also for conditional formatting.

For now, the only way to achieve your goal is a script.

Thanks for the feedback! Such a feature would be great!

For the time being, I can work around this by creating views with static filters, and use the view’s calculations lines (sums etc.) to get my numbers (like sums, averages etc.) in table A. And / or just manually link the items in B that need to go into the calculations in A. Which is tedious.

Dynamic filters in (link) formulas would give me the opportunity to create (sort of) “one-line-reports” in A that immediately use the parameters to filter and calculate records from B. Neat!

But a script … haven’t considered this yet, but a script might be a intermediate solution, and shouldn’t be too hard.

The script shouldn’t be too complicated indeed and when you run periodically with a automation, you are pretty close to what you want/need.

As a consequence, the priority for this feature extension is not so high. Sorry.

Hi @abaer , if every line of table B is linked to one single line in table A here is another workaround, a bit complex but that works:

  • in table B, let’s create a “link formula” column to create a lookup of Number A (we’ll call it “Number A from table A” to keep as clear as possible)
  • then, in table B again, create a “formula” column to compare “Number B” and “Number A from table A” : {Number B}>{Number A from table A}. We’ll call it “Test”
  • in table A, you can create a rollup, using either a “link formula” column type, or a simple “formula” column type with the following formula, as you did in your example : rollup("Link to B", "Number B", "sum", "{Test}")
    and… Voilà !

Why it only works if every line in table B is linked to one single line in table A:

  • Consider you have two rows in table A : A1 and A2. A1’s “Number A” = 2 and A2’s “Number A” = 6
  • Consider they are both linked to B1 row of table B, with B1’s “Number B” = 5
  • If you look at B1’s “Link to A” column, you’ll see both A1 and A2. “Number A from table A” column will also contains both 2 and 5
  • If you look now at B1’s “Test”, you should see True, as B1’s “Number B” 5 is actually greater than the first element of B1’s “Number A from table A” column (2). The problem is that it’s not actually true for A2 (5 ≯ 6) !

Bests,
Benjamin

1 Like

Hi again, here are two scripts that can match your needs :

First one, uploading the current row, needs to be launched with a button (from a button type column)

const tableA = base.getTableByName('Table A');
const tableB = base.getTableByName('Table B');
const rowA = base.context.currentRow;
const numberA = rowA['Number A'];
let sum = 0;
for(const link of rowA['Link to B']) {
    const Brow = base.getRow(tableB,link);
    if(Brow['Number B']>numberA) { // considers that your filter is Number B > Number A
        sum += Brow['Number B']; // considers that you want to sum Number B of every table B lines linked in your table A line that meets the above condition
    }
}
base.updateRow(tableA, rowA, {'Result': sum});

Second one, uploading every rows of table A

const tableA = base.getTableByName('Table A');
const tableB = base.getTableByName('Table B');
const rows = base.getRows(tableA, 'Default View');
for(const rowA of rows) {
  const numberA = rowA['Number A'];
  let sum = 0
  for(const link of rowA['Link to B']) {
    const Brow = base.getRow(tableB,link);
    if(Brow['Number B']>numberA) {
        sum += Brow['Number B'];
    }
  }
  base.updateRow(tableA, rowA, {'Result': sum});
}

Bests,
Benjamin

1 Like

Thanks for that! Amazing community effort!

@abaer I think we can close this topic :wink:

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.

An alternative solution was offered in this post: [Tutorial] Dynamic conditions for link formulas