Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Help with Script Automation

777 2
cancel
Showing results for 
Search instead for 
Did you mean: 
dw
4 - Data Explorer
4 - Data Explorer

I have an automation to run script when a unit enters a view. The script is working (in general) but not returning the value it should. The script looks at an occupied unit from one table and using a set of pre-defined parameters, makes a suggestion for a vacant unit from another table that it could be assigned to. If one is not available it returns a value  of 'No suitable vacant unit found. Unit needed.'

Here is my script:

 

// Define the names of your tables and views
const OCCUPIED_TABLE_NAME = 'Unit & Relo Data';
const VACANT_TABLE_NAME = 'Available Units';
const OCCUPIED_VIEW_NAME = 'Unit Search'; // The view that filters occupied units

// Function to fetch records from a specified table and view
async function fetchRecords(tableName, viewName = null) {
    const table = base.getTable(tableName);
    const queryOptions = viewName ? { view: viewName } : {};
    const records = await table.selectRecordsAsync(queryOptions);
    return records.records;
}

// Function to suggest a vacant unit based on specified criteria
function suggestVacantUnit(occupiedRecord, vacantRecords) {
    const desiredBedrooms = occupiedRecord.getCellValue('Beds');
    const desiredZipCode = occupiedRecord.getCellValue('Zip Code');
    const needsAccessibility = occupiedRecord.getCellValue('Needs Accessible Unit?');
    const petsAllowed = occupiedRecord.getCellValue('Pets Allowed Unit');
    const maxBudget = occupiedRecord.getCellValue('Budget');
    const budgetTolerance = maxBudget * 0.1; // 10% of the budget

    return vacantRecords.find(record => {
        const unitBudget = record.getCellValue('Lease Amount');
        const withinBudget = unitBudget >= (maxBudget - budgetTolerance) && unitBudget <= (maxBudget + budgetTolerance);
        const accessibilityMatch = !needsAccessibility || record.getCellValue('Accessible Unit');
        const petsMatch = petsAllowed === record.getCellValue('Allows Pets');

        return record.getCellValue('# Bedrooms') === desiredBedrooms &&
            record.getCellValue('Zip code') === desiredZipCode &&
            withinBudget &&
            accessibilityMatch &&
            petsMatch;
    });
}

// Main function to execute the script logic
async function main() {
    try {
        const occupiedRecords = await fetchRecords(OCCUPIED_TABLE_NAME, OCCUPIED_VIEW_NAME);
        if (occupiedRecords.length === 0) {
            console.log('No occupied units found.');
            return;
        }
        const occupiedRecord = occupiedRecords[0];

        const vacantRecords = await fetchRecords(VACANT_TABLE_NAME);
        const suggestedUnit = suggestVacantUnit(occupiedRecord, vacantRecords);

        if (suggestedUnit) {
            await base.getTable(OCCUPIED_TABLE_NAME).updateRecordAsync(occupiedRecord.id, {
                'Suggested Unit': suggestedUnit.getCellValue('Name')
            });
            console.log(`Suggested unit updated in the record: ${suggestedUnit.getCellValue('Name')}`);
        } else {
            console.log('No suitable vacant unit found. Unit needed.');
        }
    } catch (error) {
        console.error('An error occurred:', error);
    }
}

main();

 

I don't know much about Java Scripting in Airtable but the code itself works but is not returning a value, even though I made sure to test it using a vacant unit that matches an occupied unit exactly, so it should be returning that vacant unit as a suggestion in the field "Suggested Unit". I'd be happy to share a link to my base if someone could help. 
2 Replies 2

If you DM me an invite to your base I could take a look at this for you!

Paulette3318
4 - Data Explorer
4 - Data Explorer

@dw wrote:

I have an automation to run script when a unit enters a view. The script is working (in general) but not returning the value it should. The script looks at an occupied unit from one table and using a set of pre-defined parameters, makes a suggestion for a vacant unit from another table that it could be assigned to. If one is not available it returns a value  of 'No suitable vacant unit found. Unit needed.'

Here is my script:

 

// Define the names of your tables and views
const OCCUPIED_TABLE_NAME = 'Unit & Relo Data';
const VACANT_TABLE_NAME = 'Available Units';
const OCCUPIED_VIEW_NAME = 'Unit Search'; // The view that filters occupied units

// Function to fetch records from a specified table and view LaSRS Login
async function fetchRecords(tableName, viewName = null) {
    const table = base.getTable(tableName);
    const queryOptions = viewName ? { view: viewName } : {};
    const records = await table.selectRecordsAsync(queryOptions);
    return records.records;
}

// Function to suggest a vacant unit based on specified criteria
function suggestVacantUnit(occupiedRecord, vacantRecords) {
    const desiredBedrooms = occupiedRecord.getCellValue('Beds');
    const desiredZipCode = occupiedRecord.getCellValue('Zip Code');
    const needsAccessibility = occupiedRecord.getCellValue('Needs Accessible Unit?');
    const petsAllowed = occupiedRecord.getCellValue('Pets Allowed Unit');
    const maxBudget = occupiedRecord.getCellValue('Budget');
    const budgetTolerance = maxBudget * 0.1; // 10% of the budget

    return vacantRecords.find(record => {
        const unitBudget = record.getCellValue('Lease Amount');
        const withinBudget = unitBudget >= (maxBudget - budgetTolerance) && unitBudget <= (maxBudget + budgetTolerance);
        const accessibilityMatch = !needsAccessibility || record.getCellValue('Accessible Unit');
        const petsMatch = petsAllowed === record.getCellValue('Allows Pets');

        return record.getCellValue('# Bedrooms') === desiredBedrooms &&
            record.getCellValue('Zip code') === desiredZipCode &&
            withinBudget &&
            accessibilityMatch &&
            petsMatch;
    });
}

// Main function to execute the script logic
async function main() {
    try {
        const occupiedRecords = await fetchRecords(OCCUPIED_TABLE_NAME, OCCUPIED_VIEW_NAME);
        if (occupiedRecords.length === 0) {
            console.log('No occupied units found.');
            return;
        }
        const occupiedRecord = occupiedRecords[0];

        const vacantRecords = await fetchRecords(VACANT_TABLE_NAME);
        const suggestedUnit = suggestVacantUnit(occupiedRecord, vacantRecords);

        if (suggestedUnit) {
            await base.getTable(OCCUPIED_TABLE_NAME).updateRecordAsync(occupiedRecord.id, {
                'Suggested Unit': suggestedUnit.getCellValue('Name')
            });
            console.log(`Suggested unit updated in the record: ${suggestedUnit.getCellValue('Name')}`);
        } else {
            console.log('No suitable vacant unit found. Unit needed.');
        }
    } catch (error) {
        console.error('An error occurred:', error);
    }
}

main();

 

I don't know much about Java Scripting in Airtable but the code itself works but is not returning a value, even though I made sure to test it using a vacant unit that matches an occupied unit exactly, so it should be returning that vacant unit as a suggestion in the field "Suggested Unit". I'd be happy to share a link to my base if someone could help. 

I'm not able to access your Airtable base, but I can try to help you troubleshoot the code. First, let's make sure that the `suggestVacantUnit` function is working properly. You can do this by passing in a hard-coded occupied record and a hard-coded list of vacant records and seeing if the function returns the correct suggested unit. If the `suggestVacantUnit` function is working properly, then the issue is most likely in the `main` function. Make sure that you are calling the `suggestVacantUnit` function correctly and that you are updating the correct field in the occupied record.