So full disclosure I do not have a ton of experience scripting, but I have built quite a bit of stuff in office script with the assistance of chatgpt and a lot of follow-up questions/research to understand what CGPT did and why it did or did not work.
Did the same here trying for my first scripting automation but I am kind of stuck on an issue early in the script. The intent is that whenever a record is created or updated, the script should grab ALL records in the table that have the same values in two fields as the new/updated record. IE if a record with training type "CPR" and employee "Steve" (both are linked record fields to other tables) is created or updated, then the script should retrieve ALL records in the table that have CPR and Steve in the respective fields. It should then look through those found matching records and find the one that has the maximum (most recent) date in the date field, and checkmark that record. If another record previously had the checkmark, that checkmark should be removed.
This is to flag multiple training instances over time for which one is "current" IE if steve has taken CPR every 2 years for the last 10 years, the record we add tomorrow for training on 3/14/2024 would get flagged as most recent. If we had to backdate anything and also put in a record from say 2019, it would not get the checkmark since although it is the most recent record added, it is not the most recent date of a training.
I'm reading through the documentation on input.config (which I still think is the right place to start) but am kind of stumped. The console is returning an empty array which leads me to think that I'm obviously not retrieving the "created/updated" record which triggered the automation correctly. The next bit in the console is a type error, so it doesn't even make it to the next console.log lines 4 and 5 rows down, so it must be getting a type error in the linkedEmployees linkedTrainingType lines.
Same with office script, figuring out this "type" stuff has been the biggest hurdle so far as I can usually handle and work out the logic bits. Whether or not its written correctly I am on board with the process of:
1) get trigger record
2) get filtering field values from trigger record
3) retrieve all records and filter based on 2 filtering fields
4) Loop through filtered records checking the date, if the date is greater than the reference date or the previously stored recent date, update that date to the most recent
5) grab the record that matches that date and checkmark the current field
6) un-mark all other records in the filtered group
// Define the main function
async function main() {
try {
// Retrieve the newly created or updated record
let currentRecord = input.config();
console.log("Current Record:", currentRecord); // Log the current record
// Extract the linked employee and training type from the current record
let linkedEmployee = currentRecord.fields['Employee'] ? currentRecord.fields['Employee'][0] : null;
let linkedTrainingType = currentRecord.fields['Training Type'] ? currentRecord.fields['Training Type'][0] : null;
console.log("Linked Employee:", linkedEmployee); // Log the linked employee
console.log("Linked Training Type:", linkedTrainingType); // Log the linked training type
if (linkedEmployee && linkedTrainingType) {
// Query records from the 'Employee-Trainings' table
let records = await base.getTable('Employee-Trainings').selectRecordsAsync({
filterByFormula: `AND({Employee} = '${linkedEmployee}', {Training Type} = '${linkedTrainingType}')`
});
console.log("Filtered Records:", records); // Log the filtered records
// Find the record with the maximum date
let maxDateRecord = null;
let maxDate = new Date(0); // Initialize with a very old date
// Iterate through the filtered records to find the maximum date
for (let record of records.records) {
let recordDate = new Date(record.fields['Date']);
if (recordDate > maxDate) {
maxDate = recordDate;
maxDateRecord = record;
}
}
console.log("Max Date Record:", maxDateRecord); // Log the record with the maximum date
// Update the 'Current' checkbox field based on the record with the maximum date
for (let record of records.records) {
if (record === maxDateRecord) {
// Check the 'Current' checkbox for the record with the maximum date
await base.getTable('Employee-Trainings').updateRecordAsync(record, {
'Current': true
});
} else {
// Uncheck the 'Current' checkbox for other records
await base.getTable('Employee-Trainings').updateRecordAsync(record, {
'Current': false
});
}
}
} else {
console.log("Linked Employee or Training Type is missing.");
}
} catch (error) {
console.error(error);
}
}
// Call the main function
main();
