Below is a modified script. With this update, there’s no need for a formula field to compare anything and drive the view filters. Before I get into that, though, here’s a rundown of how this improves over the previous version.
With the old script, every single record had a number put into that {Script Input} field. If you enter 8 when running the script, all 8000+ records have 8 put into {Script Input}. If you change to 9, all 8000+ records must be changed to 9.
With the new version, only the records that change are updated. If you start with {Script Input} completely empty, and you enter 8, maybe there are only 400 records that are in August, so the script puts an 8 in those 400 records and leaves the rest alone. Next month you switch to 9 for September, and maybe there are another 400 of those records. Those 400 records are marked with a 9, the previous 400 that had the 8 are blanked out, and the rest don’t get processed. The script only updates 800 records vs all 8000.
Because the number you enter only goes into the matching records, you can set the view filters to only show records where {Script Input} is not empty. Any records that were matched previously are blanked out, and the rest are just left alone.
If you apply this to your current setup, the script will take the longest the first time you run it because it has to blank out all of the non-matching records. After that, each subsequent change should go more quickly. If you manually erase the {Script Input} field’s contents before running the script the first time, you’ll see the benefit right away.
One more note: the new script also reads the formula field you built that outputs the month for that record’s entry. Replace “Month” in record.getCellValue("Month") with the name of your formula field.
output.markdown('## Dashboard filter');
output.markdown('### Input the number for the month you want to filter by');
let table = base.getTable("Time Tracking reporting");
let field = table.getField("Script Input");
let inputValue = parseInt(await input.textAsync('Enter the month to filter by'));
// Load all of the records in the table
let result = await table.selectRecordsAsync();
// Find every record we need to update
let updates = [];
for (let record of result.records) {
let originalValue = record.getCellValue(field);
let recordValue = record.getCellValue("Month");
if (recordValue == inputValue && originalValue != inputValue) {
updates.push({
id: record.id,
fields: {
[field.id]: inputValue,
}
});
} else if (originalValue && originalValue != inputValue) {
updates.push({
id: record.id,
fields: {
[field.id]: null,
}
});
}
}
if (!updates.length) {
output.text("You're already filtering by that month");
} else {
// Only up to 50 updates are allowed at one time, so do it in batches
output.markdown(`Updating ${updates.length} records...`)
while (updates.length > 0) {
await table.updateRecordsAsync(updates.slice(0, 50));
updates = updates.slice(50);
}
output.markdown('Done');
}
