I am using Airtable as a finance base that enables us to create a financial forecast for the current year.
I want to be able to correctly predict all salaries that are going to be paid out. All compensation changes for each employee are located in the "Compensation" table. Compensation can be changed on a quarterly basis for each employee. When I enter new compensation for an employee, I need to run a script that would find the last compensation for that particular employee and within the last compensation record enter CompensationEndDate which should be the day before the new compensation 'CompensationEffectiveDate'. I have tried to write a script using AI, however, I continuously get this error message:
"TypeError: Cannot read properties of undefined (reading 'getCellValueAsString')
"TypeError: Cannot read properties of undefined (reading 'getCellValueAsString')
at updatePreviousCompensation on line 13 at main on line 33"
// Fetch the table and sort it by CompensationEffectiveDate in descending order
const table = base.getTable('Compensation');
const sortField = table.getField('CompensationEffectiveDate (date)');
const queryResult = await table.selectRecordsAsync({ sorts: [{ field: sortField, direction: 'desc' }] });
const records = queryResult.records;
async function updatePreviousCompensation() {
// Get the new compensation record
const newCompensationRecord = input.record;
// Find the previous compensation record for the same employee
const employeeField = table.getField('Employee');
const employeeCellValue = newCompensationRecord.getCellValueAsString(employeeField);
const previousCompensation = records.find(record => {
const employeeValue = record.getCellValueAsString(employeeField);
return employeeValue && employeeValue === employeeCellValue && record.id !== newCompensationRecord.id;
});
if (previousCompensation) {
// Calculate the end date for the previous compensation
const newCompensationStartDate = newCompensationRecord.getCellValueAsString('CompensationEffectiveDate (date)');
const previousCompensationEndDate = new Date(newCompensationStartDate);
previousCompensationEndDate.setDate(previousCompensationEndDate.getDate() - 1);
// Update the previous compensation record with the new end date
await table.updateRecordAsync(previousCompensation, {
'CompensationEndDate (date)': previousCompensationEndDate,
});
}
}
await updatePreviousCompensation();