Help

Re: Filtering view from script

Solved
Jump to Solution
2388 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Thomas_La_Licat
5 - Automation Enthusiast
5 - Automation Enthusiast

I have built a dashboard pulling in data from 2 different views, both of them filtered by a formula field that prints out the month.
For instance, if it’s August the formula returns 8 and I manually filter both views by number.
Once that’s done my blocks get updated and I can see the monthly dashboard.

I would like to achieve this dynamically from the dashboard by having an input field/dropdown, in which I would just type in the number to filter the view, rather than having to do this manually in the view itself.

I’ve looked at the Views API but I can’t find anything that would the job.

Is this achievable?

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

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

See Solution in Thread

10 Replies 10
Paul_Warren
8 - Airtable Astronomer
8 - Airtable Astronomer

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,

That sounds good!

I’ll give it a shot and post the final script/solution when done.

Thomas_La_Licat
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

You have exited the realm of my expertise! :persevere:

Hopefully someone else can hop in here with the coding experience.

I will confirm the experience that scripts can take absurdly long to run…

There’s no need to use a script for this. You already have a formula field that generates the number of the current month (8 for August in this example). I’ll refer to this field as {Current Month}. Make another formula field named something like {View Month}, and for its formula, enter the number of the month you want to see. It may seem odd to have a “formula” that’s just a number, but it works, and that number will fill into every record automatically. Now make a third formula field named something like {Month Match}, using the formula:

{View Month} = {Current Month}

That formula will output a 1 where the month values match, and a 0 where they don’t. Filter your views by that formula to only show records where there’s a 1.

To change the month you wish to see, change the number in the {View Month} formula field. Both views will update instantly.

Thomas_La_Licat
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

True, but you’d have to change each view’s filter separately. I can appreciate your desire to do this from the dashboard, but my suggestion takes about the same time to execute as running a dashboard script that requires you to input a number. If both views operate using the same filter setup that I suggested above, changing the numerically-driven formula field updates both views at the same time, and thousands of records update instantly vs waiting for a script to process them in batches of 50.

That said, there may be a way to optimize the setup so you can use the dashboard. I’m tied up for the next couple of hours, but I can revisit this after that.

Justin_Barrett
18 - Pluto
18 - Pluto

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

Hi Justin,

Thanks for this, it’s a solid improvement of the previous iteration.

I’ve added some additional input checks and refined the update to display the percentage completion to make it more user-friendly, but other than that I think we’re good!

@Justin_Barrett to the rescue again!