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.
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.
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) !
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});
}
