Help

Trouble with Scripts Filtering by a Multi-Select field.

Topic Labels: Extensions
311 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Cole_Wedemeier
6 - Interface Innovator
6 - Interface Innovator

I have a script that looks at a Festivals table and based on a date input prompt in the script it returns any festivals that are occurring during the date input by the user by looking at the start and end date fields. I'd like to further modify the script to allow the user to select 1, or more, genres of music and any festival that lists any one of the selected genres is then returned. 

I can get the first portion based on date to work, but I've tried several failed scripts in the attempt to further filter by genre. The script clearly finds a festival that has a genre included but still returns "No festivals found" message when run. Any help would be greatly appreciated if I'm merely creating the script wrong, or if it's just simply not possible. 

SCRIPT FOR ONLY THE DATE FILTER (WORKS)

// Get the table "FESTIVALS 1.0"
let table = base.getTable("FESTIVALS 1.0");

// Prompt the user to input a date
let inputDate = await input.textAsync("Enter a date in YYYY-MM-DD format");
let dateObject = new Date(inputDate);

// Validate the date
if (isNaN(dateObject)) {
    output.text("Invalid date. Please run the script again and enter a valid date.");
    return;
}

// Query the records in the table
let query = await table.selectRecordsAsync();

// Initialize an array to store the records that include the input date
let recordsIncluding = [];

for (let record of query.records) {
    let startDate = record.getCellValue("Start date");
    let endDate = record.getCellValue("End date");

    let startDateObject = new Date(startDate);
    let endDateObject = new Date(endDate);

    if (startDateObject <= dateObject && endDateObject >= dateObject) {
        recordsIncluding.push(record);
    }
}

// Output the filtered records
if (recordsIncluding.length > 0) {
    output.markdown("### Festivals Including " + inputDate);
    for (let record of recordsIncluding) {
        output.text(record.getCellValueAsString("Festival") + " - " + record.getCellValueAsString("Start date") + " to " + record.getCellValueAsString("End date"));
    }
} else {
    output.markdown("### No Festivals Including " + inputDate);
}


SCRIPT FOR FURTHER FILTERING BY GENRES FROM A HARD-CODED LIST
(I did this as having it provide me genre's from the field in the table to choose from would not return options of genres)

// Get the table "FESTIVALS 1.0"
let festivalsTable = base.getTable("FESTIVALS 1.0");

// Prompt the user to input a date
let inputDate = await input.textAsync("Enter a date in YYYY-MM-DD format");
let dateObject = new Date(inputDate);

// Validate the date
if (isNaN(dateObject)) {
    output.text("Invalid date. Please run the script again and enter a valid date.");
    return;
}

// Query the records in the festival table
let festivalsQuery = await festivalsTable.selectRecordsAsync();

// Initialize an array to store the festivals that include the input date
let matchingFestivals = [];

// Filter festivals where the input date falls between "Start date" and "End date"
for (let record of festivalsQuery.records) {
    let startDate = record.getCellValue("Start date");
    let endDate = record.getCellValue("End date");

    let startDateObject = new Date(startDate);
    let endDateObject = new Date(endDate);

    if (startDateObject <= dateObject && endDateObject >= dateObject) {
        matchingFestivals.push(record);
    }
}

// Hard-coded list of genres
let genreList = [
    "ROCK & POP",
    "ELECTRONIC",
    "HIP HOP",
    "SHOWCASE",
    "SPECIALIST",
    "ALL NONE TRAD",
    "TRAD",
    "CLUB DJ",
    "EXPERIMENTAL",
    "FOLK",
    "GLOBAL",
    "JAZZ",
    "CLASSICAL",
    "AMBIENT",
    "IMPROV"
];

// Prompt the user to select genres
let selectedGenres = await input.buttonsAsync('Select Genres', genreList);

// Filter the matching festivals based on genres
let genreMatchingFestivals = [];

for (let festival of matchingFestivals) {
    let festivalGenres = festival.getCellValue("GENRE");
    if (festivalGenres) {
        for (let genre of festivalGenres) {
            if (selectedGenres.includes(genre)) {
                genreMatchingFestivals.push(festival);
                break;
            }
        }
    }
}

// Output the filtered records
if (genreMatchingFestivals.length > 0) {
    output.markdown("### Festivals Including " + inputDate + " and Matching Selected Genres");
    for (let record of genreMatchingFestivals) {
        output.text(record.getCellValueAsString("Festival") + " - " + record.getCellValueAsString("Start date") + " to " + record.getCellValueAsString("End date"));
    }
} else {
    output.markdown("### No Matching Festivals for " + inputDate);
}

With the above, the output is the following:

Cole_Wedemeier_0-1694559670979.png

However, as 1 example, the following record meets the filter criteria, and yet isn't found:

Cole_Wedemeier_1-1694559778197.png

So then I modified the script to show me the festivals and then ask for the genre(s) I wanted to filter by, so I know it's finding the festivals OK, it's something with the genre field that I'm not writing properly.

Greatly appreciate your help!

0 Replies 0