Find previous record and update date

[daria_zagar_DCD]

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')

    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();

[ag314]

Kudos for your willingness to go the code route! Unfortunately AI isn't great when it comes to coding. Actually, let me rephrase that: AI can be awesome when it comes to coding - so long as you understand its limitations. One of them being that it is often wrong. Another being that it is very confident in its wrong answers.

Nonetheless, it will improve over time and become an invaluable tool for developers: beginners and expert alike.

Now, to your particular use case, there are a few issues with the code you are trying. And rather than trying to fix the AI code, if you explain in plain English EXACTLY what you are trying to achieve, including table names and fields names, and ideally some screenshots of your tables (WITHOUT any salary information of course) - then I'm happy to write the script for you - and explain what each line does. That way you'll be able to make any tweaks to it over time.