Scripting: Creating backlog of records: Attendance tracker

[sophia-take2]

We are moving student metrics to Airtable and I am trying to create a backlog of attendance records for our students since they started. (we have two campus's with seperate start dates). I have an automation working fine for populating records with every new day but what i need help with is the back log script. Currently, I am trying to create a record for every student for everyday they have attended class thus far. 

Can someone help debug why this is not working? Nothing happens when I run the script and I do not get an error in the console, meaning the script is killed?

let studentTable = base.getTable("Participant Core Database");
let attendanceTable = base.getTable("Attendance");
let inTraining = studentTable.getView("Training View")
let currentStudents = await inTraining.selectRecordsAsync({
    fields: ["Full Name", "Training Facility"],
      sorts: [
       {field: "Training Facility"}]
})


//gets an array of all the dates the student has attended class since they started (before airtable)
/**
* @param {string} s
* @param {string} e
*/
function getDaysArray(s,e) {
    for(var arr=[], date = new Date(s); date <= new Date(e); date.setDate(date.getDate()+1)){ 
        const day = date.getDay()
        // only adds weekdays 
        day !== 0 && day !== 6 && arr.push(new Date(date).toLocaleDateString());
        }
    return arr
}
currentStudents.records.map(async (record) => {
    const campus = record.getCellValueAsString("Training Facility")
    let newDates = []
    //each campus has different start dates
    campus === "ASFC" ? newDates = getDaysArray("2023-02-07", "2023-03-16") : newDates = getDaysArray("2023-01-16", "2023-03-16")
    newDates.map(async (date) => {
        try {
            await attendanceTable.createRecordAsync(
            {
                    "Attendance Date": date,
                    "Student": [{id: record.id}],
                    "Attended": [{name: "Attended"}],
                    "Campus": [{ name: campus}]
            })
        } catch (Error) {
            console.error(Error.message)
        }
        
    })
    
 
})

[Ben_Young1]

Hey @sophia-take2, 

This was an interesting script.

Here's the pattern I would use to approach this problem:

const studentTable = base.getTable("Participant Core Database");
const attendanceTable = base.getTable("Attendance");
const inTrainingView = studentTable.getView("Training View");

let currentStudents = await inTrainingView
	.selectRecordsAsync({
		fields: ["Full Name", "Training Facility"],
		sorts: [{ field: "Training Facility" }]
	})
	.then(query => query.records);

const getDaysArray = (startDateString, endDateString) => {
	startDate = new Date(startDateString);
	endDate = new Date(endDateString);
	var datesArray = [];

	function pushDate(startDate) {
		startDate !== 0 && startDate !== 6 && datesArray.push(startDate.toLocaleDateString());
		startDate.setDate(date.getDate() + 1);
	}

	while (startDate <= endDate) {
		pushDate(startDate);
	}

	return datesArray;
};

let recordsToCreate = currentStudents
    .map(studentRecord => {
        const campus = record.getCellValueAsString("Training Facility");
        let attendanceDates = [];
        campus === "ASFC"
            ? (attendanceDates = getDaysArray("2023-02-07", "2023-03-16"))
            : (attendanceDates = getDaysArray("2023-01-16", "2023-03-16"));
        return attendanceDates, studentRecord, campus;
    })
    .map((date, studentRecord, campus) => ({
        "Attendance Date": date,
        "Student": [{ id: studentRecord.id }],
        "Attended": [{ name: "Attended" }],
        "Campus": [{ name: campus }]
    }));

if (recordsToCreate) {
    while (recordsToCreate.length > 50) {
        await attendanceTable.createRecordsAsync(recordsToCreate.splice(0, 50));
        recordsToCreate = recordsToCreate.splice(50);
    }
    await attendanceTable.createRecordsAsync(recordsToCreate);
};

I didn't test this script in Airtable, so they are probably a couple of things that will need to be tweaked, but the idea remains the same.

Here's a summary of my changes:

1. I have the view.selectRecordsAsync() method return the records property (which is an array of record objects) rather than the entire query.
2. I've refactored the getDaysArray function:
   - Renamed parameters for readability.
   - Created pushDate function to abstract the array creation.
   - This function still returns an array of date objects for each day of attendance.
3. Added recordsToCreate variable:
   - This variable stores an array of records objects that we will create in bulk.
4. Added bulk record creation handling at the bottom of the script.

[sophia-take2 · Accepted solution]

Thanks, this was very helpful there were a few adjustments I had to make but this approach got me there, so again thanks! 

const studentTable = base.getTable("Participant Core Database");
const attendanceTable = base.getTable("Attendance");
const inTrainingView = studentTable.getView("Training View");

let currentStudents = await inTrainingView
	.selectRecordsAsync({
		fields: ["Full Name", "Training Facility"],
		sorts: [{ field: "Training Facility" }]
	})
	.then(query => query.records);

function getDaysArray(s,e) {
    for(var arr=[], date = new Date(s); date <= new Date(e); date.setDate(date.getDate()+1)){ 
        const day = date.getDay()
        // only adds weekdays 
        day !== 0 && day !== 6 && arr.push(new Date(date));
        }
    return arr
}


let batchRecords = []
currentStudents.forEach(studentRecord => {
        let attendanceDates = []
        const campus = studentRecord.getCellValueAsString("Training Facility");
        campus === "ASFC"
            ? (attendanceDates = getDaysArray("2023-02-07", "2023-03-16"))
            : (attendanceDates = getDaysArray("2023-01-16", "2023-03-16"));

        batchRecords = [ ...batchRecords, attendanceDates.map((date)=> {
            return { 
                fields: {
                    "Attendance Date": date,
                    "Student": [{ id: studentRecord.id }],
                    "Attended": [{ name: "Attended" }],
                    "Campus": [{ name: campus }]
                }
            }
        })]
   
})

if (batchRecords) {
    batchRecords = batchRecords.flat()
    while (batchRecords.length > 50) {
        let records = [...batchRecords]
        await attendanceTable.createRecordsAsync(records.splice(0, 50));
        batchRecords = batchRecords.splice(50);
    }
    await attendanceTable.createRecordsAsync(batchRecords);
    output.text('records have been added')
};

[Anthony_Fransel]

@Ben I have a similar issue with just the very first part pulling records in the first place. Essentially I created a view with the current month NPS survey results and want to do some things to it in my script, but it never makes it past the record gathering to the console log. Am I being obtuse and missing something obvious here? Thanks!

async function processAirtableRecords() {
    let NPSSourceTable = base.getTable("NPS Feedback");
    let NPSSourceView = NPSSourceTable.getView("Current Month");
    let NPSrecords = await NPSSourceView.selectRecordsAsync({fields: ["Comment", "Product"]});
    let currentNPSRecords = NPSrecords.records;
    console.log("records pulled");

[Ben_Young1]

Hey @Anthony_Fransel!

Here are a couple of ways to accomplish what you're looking for based on what I'm seeing in your script:
This first method doesn't require a function. Instead, it simply returns the results of the Table.selectRecordsAsync method to the npsRecords variable.

const npsSourceTable = base.getTable("NPS Feedback");
const npsSourceView = npsSourceTable.getView("Current Month");

let npsRecords = await npsSourceTable.selectRecordsAsync({ fields: ["Comment", "Product"] })
    .then(query => {
        console.log(`${query.records.length} Record(s) Pulled`);
        return query.records;
    });

This method preserves the console.log that you have that returns a message on the records being returned from the query.

If you don't need the console.log, then it can be slimmed down to this:

const npsSourceTable = base.getTable("NPS Feedback");
const npsSourceView = npsSourceTable.getView("Current Month");

let npsRecords = await npsSourceTable.selectRecordsAsync({ fields: ["Comment", "Product"] })
    .then(query => query.records);

If you still want to abstract the record query inside a function, then this function will return the same thing as the npsRecords variable in those previous snippets:

const npsSourceTable = base.getTable("NPS Feedback");
const npsSourceView = npsSourceTable.getView("Current Month");

const getViewRecords = async view => view.selectRecordsAsync({ fields: ["Comment", "Product"] })
    .then(query => query.records);

let npsRecords = await getViewRecords(npsSourceView);

This is a good solution if you are going to be grabbing records from multiple views, as the function accepts a view object as a parameter. You can just feed it any view object and it will return those records.

If you're only going to be querying a single view, then it doesn't make sense to put that inside a dedicated function. You'd be better off using one of the first two snippets.

I don't recommend instantiating new Table objects outside of global scope, but if you don't mind them being tossed by the garbage collector, then here's what it looks like when its fully abstracted:

const getRecords = async () => {
    return base
        .getTable("NPS Feedback")
        .getView("Current Month")
        .selectRecordsAsync({ fields: ["Comment", "Product"] })
            .then(query => query.records);
}

let npsRecords = await getRecords();

This is just a brain dump and I don't have a ton of details about your use case. Nonetheless, no matter what method you decide to use, you'll always get an array of record objects.

[Anthony_Fransel]

Wonderful Ben, that was very thorough and extremely helpful! Thank you, I now have it working properly. 🙂