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.'
// 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.
