Hello everyone,
I’m fairly new to Airtable, and I’m unsure how to accomplish the query(ies) I’m performing in a simpler manner using Airtable commands.
The Base
I have an API with two tables: Classes and Students. The Classes table consists of classes and all the students enrolled in each class. The Students table consists of students and all the classes each student is enrolled in.
The Task
A user searches for a specific student on the front end. Using Airtable formulae, I need to query the Students table for the classes that student is taking. (NOTE: I need to return the class names, not the record IDs/codes for those classes, which are unintelligible for the user.) Using those class names, I then need to ask the Classes table for the students associated with each class, again returning the students’ actual names, not their record IDs/codes. Finally, I need to return the original student’s classes to the front end, along with the other student names associated to those classes. I would think I need to run a window function or something, but I’m unsure how to do that with Airtable.
For clarity, here is the code I’m using so far. I realize it’s very clunky, but I haven’t been able to find much documentation online.
router.get('/:name', async (req, res) => {
try {
// Retrieve submitted student
res.setHeader('Access-Control-Allow-Origin', '*');
const URL = 'http://api.airtable.com/v0/app8ZbcPx7dkpOnP0/students';
const query = '?filterByFormula=';
const filterBy = `SEARCH("${req.params.name}", {Name} )`;
const link = `${URL}${query}${filterBy}`;
const KEY = process.env.AIRTABLE_API_KEY;
const headers = {
headers: {
Authorization: `Bearer ${KEY}`
}
}
const { data } = await axios.get(`${link}`, headers);
const records = data.records;
console.log("Object by record:", records);
const classes = records[0].fields['Classes'];
console.log("Classes by record:", classes);
let classNames = [];
let studentIds = [];
let otherStudents = [];
let zippedData = [];
let resultData;
// Swap class codes for class names
for (let i = 0; i < classes.length; i++) {
base('Classes').find(classes[i], function (err, record) {
if (err) { console.error(err); return; }
classNames.push(record.fields.Name);
});
}
// Retrieve other student codes per class associated to chosen student
for (let i = 0; i < classes.length; i++) {
base('Classes').find(classes[i], function (err, record) {
if (err) { console.error(err); return; }
studentIds.push(record.fields['Students']);
});
}
// Associate other student codes to associated student names. Zip data to send to Reducer.
setTimeout(async function () {
for (let i = 0; i < studentIds.length; i++) {
let newTempArr = [];
for (let j = 0; j < studentIds[i].length; j++) {
base('Students').find(studentIds[i][j], function (err, record) {
if (err) { console.error(err); return; }
newTempArr.push(record.fields.Name);
});
}
otherStudents.push(newTempArr);
}
}, 500);
setTimeout(function () {
for (let i = 0; i < classNames.length; i++) {
let newTempArr = [];
newTempArr.push(classNames[i]);
for (let j = 0; j < otherStudents[i].length; j++) {
newTempArr.push(otherStudents[i][j] + ", ");
}
zippedData.push(newTempArr);
}
}, 750);
setTimeout(function () {
console.log("Class names: ", classNames);
console.log("Records of other students by class:", studentIds);
console.log("Records of other student names:", otherStudents);
console.log("Zipped array:", zippedData);
resultData = { zippedData: zippedData, name: req.params.name.toString(), loading: false };
console.log(resultData);
res.send(resultData);
}, 1000);
} catch (err) {
return res.send({ zippedData: [["Name/class do not exist in Airtable database", "Associated students do not exist in Airtable database"]], name: "Error", loading: false });
}
})
I’m also having issues figuring out how to properly implement asynchronicity in Airtable, but I’ve posted my questions for that topic in another thread.
Even incomplete solutions will be very helpful, so that I can better understand which direction to take in order to cut down on the verbosity of this beast.
Thank you in advance for any insights!
-Justin
