Filtering view from script

Welcome Thomas!

Would you consider adding two new fields to make this work? One field would be a number field written by the script, the second field would be a formula checking if the two match. Then the view could be filtered by the formula field instead of the month field directly.

Does that make sense? If not I can try to break it down step by step.

Hey Paul,

I’ve implemented it starting from the example for find/replace, which gets the job done.

======

See script below:

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 = 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 replacements = [];
for (let record of result.records) {
    let originalValue = record.getCellValue(field);

    if (originalValue !== inputValue) {
        replacements.push({
            record,
            before: originalValue,
            after: parseInt(inputValue),
        });
    }
}

if (!replacements.length) {
    output.text("You're already filtering by that month");
} else {
   let updates = replacements.map(replacement => ({
            id: replacement.record.id,
            fields: {
                [field.id]: replacement.after,
            }
        }));
        // Only up to 50 updates are allowed at one time, so do it in batches
        while (updates.length > 0) {
            await table.updateRecordsAsync(updates.slice(0, 50));
            updates = updates.slice(50);
        }
        output.markdown('Done');
}

=====

However, it takes ages to run, to the point where it would be easier to go and do this task manually.
I’m using this approach on our time-tracking table and as you can imagine we have quite a lot of records (+8000), which are only going to increase in the upcoming months.

The big limitation seems to be the slicing in batches of 50, which is forcing the script to slow down the processing.

I’ve tried removing the await directive to make use of the async nature of the function, but the block ends up crashing :frowning:

Do you have any other suggestions or is this the best shot we’ve got?

Thanks!

Hi Justin,

I’m afraid that defeats the purpose of the question.

I could achieve that result by simply using the filter in the view, but I would like to filter the blocks directly in the dashboard, and the only way to achieve that (as far as I know) is by using a script, as the blocks don’t present a global filtering option.

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');
}

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');
}