Help

Help with Script Automation

397 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.