Follow-Up to this topic regarding dynamic link formulas. That has been closed, so here is my follow-up as a different thread. I developed my own script in a different direction, and wanted to share it in case it is useful for someone.
@bennhatton and @rdb, sorry for the late reply, and thanks for the great scripts. After the initial hint towards scripting, I needed some weekend time to come up to my own solution. Doing so, I realized that @bennhatton 's script was not what I had in mind. Here’s my version, and below, my explanation.
const aname = 'Table A';
const bname = 'Table B';
const afield = 'Date';
const bfieldstart = 'Collect Table A records from';
const bfieldend = 'Collect Table A records to';
const atable = await base.getTableByName(aname);
const aviewrows = await base.getRows(atable,'Script view');
const btable = await base.getTableByName(bname);
const bviewrows = await base.getRows(btable,'Script view');
const blinkname = 'Linked Table A records in Table B';
const blinkid = base.getColumnLinkId(bname, blinkname);
const alist = new Map();
// Table A: collect record IDs and the filter field(s) in a Map
aviewrows.forEach(function(row){
if ('undefined' != typeof row[afield]) alist.set(row['_id'],Date.parse(row[afield]));
});
// Go through all records in Table B
bviewrows.forEach(function(row){
// Trigger date interval from record in Table B
let bvalstart = Date.parse(row[bfieldstart]);
let bvalend = Date.parse(row[bfieldend]);
// Does the date field of the records in A lie within the date interval? Collect them
const llist = new Array();
alist.forEach(function(avalue,akey){
if (avalue <= bvalend && avalue >= bvalstart) {
llist.push(akey);
}
});
base.updateLinks(blinkid,bname,aname,row['_id'],llist);
});
This is what the base looks like: Table A
And Table B after the script has run:
You’ll notice a few different things:
- The script itself does not build the sum, but only links all the records from A that match a certain filter criterium in a record in Table B (a date interval, in this case).
- The sum (and an average as well, for good measure) is then created using the normal link formula mechanism. This is fully dynamic if the values in A are changed. Which they do a lot
- The script only has to run if dates change, or records are added to A or B.
- My intentions:
- Let normal users do adjustments (add lines in Table B and A, change values and critera) without having to change the script.
- Therefore, use Seatable’s full capacity of linked tables.
- Minimize the number of script runs.
To be clear: Table B’s sole purpose is to enable users to create records that summarize selected (=linked) records from Table A. Kind of dynamic reports, if you like. Yes, I (or the users) just could create filtered views in Table A, and look at the column sums - but those sums can’t be used anywhere else. They can not automatically be compared to those from other filtered views, put togther into a diagram etc.
The linking could be done manually, but: If table A has thousands of records, manually linking a subset of them to a record in B would be impractical. Thus my initial thought with dynamic filters: The record in B contains filter criteria, and the link formula would pick only a subset to summarize. Come to think of it, that would be tedious, too, because thousands of links (all records in A) would have to be created first, and then the filter could act.
My script works fine for me - I hope that someone can use it, too.
If you think about adding features, adding this to automations would problably serve the purpose as well, doing the work of the script:
BTW, found this thread which covers the same topic, more or less.
