Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Sep 24, 2024 02:52 PM - edited Sep 24, 2024 02:54 PM
I'm a total novice with scripting and automation, so I had ChatGPT write a script. It looks right to me (I've double-checked the strings) and does not throw out any errors, but it is not having the desired effect on my base.
Would love some pointers... Thanks in advance!
// Define the table and view
let table = base.getTable("Transactions");
let view = table.getView("All Transactions");
// Fetch records from the view
let query = await view.selectRecordsAsync();
// Get the current date
let currentDate = new Date();
let currentYear = currentDate.getFullYear();
let currentMonth = currentDate.getMonth();
let currentDay = currentDate.getDate();
// Helper function to determine if a date is within the current month
function isWithinCurrentMonth(date) {
return date.getFullYear() === currentYear && date.getMonth() === currentMonth;
}
// Helper function to determine if a date is within seven days of the current date
function isWithinSevenDays(date) {
let diff = Math.abs(currentDate - date);
let daysDiff = diff / (1000 * 60 * 60 * 24);
return daysDiff <= 7;
}
// Loop through each record
for (let record of query.records) {
let status = record.getCellValue("Status");
let transactionDate = new Date(record.getCellValue("**Date"));
// Skip if the status is "Pending"
if (status === "Pending") continue;
// Determine the new status
let newStatus = status;
if (!isWithinCurrentMonth(transactionDate) && !isWithinSevenDays(transactionDate)) {
if (status === "Cleared") {
newStatus = `Archive ${transactionDate.getFullYear()}`;
} else {
newStatus = `${status} ${transactionDate.getFullYear()}`;
}
}
// Ensure the new status is a valid single-select option
let validStatuses = [
"Autopay", "Planned", "Cleared", "Pending",
"Archive 2024", "Archive 2023", "Archive 2022", "Archive 2021",
"Autopay 2024", "Autopay 2025", "Autopay 2026",
"Planned 2024"
];
if (!validStatuses.includes(newStatus)) {
continue;
}
// Update the record if the status has changed
if (newStatus !== status) {
await table.updateRecordAsync(record.id, {
"Status": { name: newStatus }
});
}
}
Sep 24, 2024 07:06 PM
Hmm, this seems like something you should be able to do with an automation, may I know what issues you faced with that?
If you could provide a screenshot of some data and show what would and wouldn't be included I could see whether I can help!
Sep 24, 2024 08:22 PM
Thanks for the response. You got me thinking and I realized I'm overengineering this. can just use a view to show the items that match the timeframe I'm wanting rather than trying to update the fields.