Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jan 30, 2023 09:09 AM
Hello
I have a task I'm trying to achieve with a script in an automation. It's an automation to run every 3rd week of a month (ideally), and to trigger a script which will find all the Wednesdays in the next month and create certain records on those dates. That part I have sussed out, but what I can't find out how to do would be to grab the data/title from the last record of the current month the automation runs on so that I can make sure the new records it creates in the following month flow on correctly.
Eg:
Automation runs on 16th Jan, triggers a script to create records on every Wednesday in February, starting with a specific title relative to the last Wednesday record in January.
The script needs to find out the title of record on the last wednesday of January (which would have been the 25th) and pass that variable into some logic I'll create to determine the titles of the February entries it automates.
Hopefully this makes sense! Thanks
Solved! Go to Solution.
Jan 31, 2023 08:35 AM
Using your code example as a base, I managed to crack what I wanted to achieve 🙂 Here's the final code in case it helps anyone else to modify etc to their needs:
function firstOrLastDayOfMonth(isFirstDay) {
let date = new Date();
return isFirstDay
? new Date(date.getFullYear(), date.getMonth(), 1)
: new Date(date.getFullYear(), date.getMonth() + 1, 0);
}
let start = firstOrLastDayOfMonth(true);
let end = firstOrLastDayOfMonth(false);
let dates = [],
recordDateObject = [],
options = { weekday: 'long' };
for (let r of query.records) {
let date = new Date(r.getCellValue("Pub Date"));
let dayName = date.toLocaleDateString('en-US', options);
//Get all Wednesday records
if (date >= start && date <= end && dayName === "Wednesday") {
dates.push(date);
}
recordDateObject[date.toDateString()] = r.id
}
let maxDate = new Date(Math.max.apply(null, dates));
let latestRecord = await table.selectRecordAsync(recordDateObject[maxDate.toDateString()]);
console.log(latestRecord); //Final record of the last Wednesday of the current month
Jan 30, 2023 11:58 PM
If you're open to creating a view that only shows the Wednesday records, you could try using this?
let query = await table.selectRecordsAsync()
for (let r of query.records){
let date = new Date(r.getCellValue(field))
dates.push(date)
recordDateObject[date.toDateString()] = r.id
}
let maxDate=new Date(Math.max.apply(null,dates));
let latestRecord = await table.selectRecordAsync(recordDateObject[maxDate.toDateString()])
This script will just grab the record with the latest date for you, so if, when you run it on the 16th you're certain there's a record in the view for the 25th, it'll grab that one. So just manually set it up the first month, and once you have the automation running it should be fine
It'll break if the view you're using ever contains a record that isn't from a Wednesday though
Jan 31, 2023 02:41 AM
Thanks for this, there are other records in the view, but there will always be something on Wednesdays as it's a recurring schedule I'm trying to automate the creation of
Jan 31, 2023 03:17 AM
Ah, any chance you could make a view that only contains Wednesdays? Use a `Weekday()` to check whether it's Wednesday and filter on that
Jan 31, 2023 03:42 AM
Not in this case as it's a client managed table for various schedules, but there's just a recurring event every Wednesday they don't want to have to manually input all the time
Jan 31, 2023 08:35 AM
Using your code example as a base, I managed to crack what I wanted to achieve 🙂 Here's the final code in case it helps anyone else to modify etc to their needs:
function firstOrLastDayOfMonth(isFirstDay) {
let date = new Date();
return isFirstDay
? new Date(date.getFullYear(), date.getMonth(), 1)
: new Date(date.getFullYear(), date.getMonth() + 1, 0);
}
let start = firstOrLastDayOfMonth(true);
let end = firstOrLastDayOfMonth(false);
let dates = [],
recordDateObject = [],
options = { weekday: 'long' };
for (let r of query.records) {
let date = new Date(r.getCellValue("Pub Date"));
let dayName = date.toLocaleDateString('en-US', options);
//Get all Wednesday records
if (date >= start && date <= end && dayName === "Wednesday") {
dates.push(date);
}
recordDateObject[date.toDateString()] = r.id
}
let maxDate = new Date(Math.max.apply(null, dates));
let latestRecord = await table.selectRecordAsync(recordDateObject[maxDate.toDateString()]);
console.log(latestRecord); //Final record of the last Wednesday of the current month