We have a similar issue with comment notifications when someone is mentioned in an interface they don't have access to.
We have interface-only sales reps who do all of their work in a rep interface. The admin team is also interface-only and makes comments on orders in an admin interface. Often times, the admin team will make a comment in their interface, mentioning the rep but the rep never receives a notification because they don't have access to the admin interface.
Because this level of collaboration is crucial for orders to flow properly, I've been trying to figure out a way to force some level of notifications so the rep knows to go in and check on an order. What would be most ideal is if Airtable would allow some level of trigger to fire when a comment is made. To my knowledge, nothing (including modified time fields) fire when a comment is made.
The below is a pseudo-workaround that I haven't been able to get working due to time constraints for scripts. If anyone has advice on how to better execute this and get it working, please let me know.
1. The first automation runs on a time-based trigger and runs the below script to check the API to see if comments are present
let apiKey = 'xxxxxxxxxx'; // Replace with your API key/token
let endpoint = 'https://api.airtable.com/v0/appXXX/tblXXX?recordMetadata=commentCount';
let table = base.getTable('Orders'); // Replace with your table name
// Function to make a GET request using fetch
async function fetchData() {
let response = await fetch(endpoint, {
method: 'GET',
headers: {
Authorization: `Bearer ${apiKey}`,
},
});
if (response.ok) {
let data = await response.json();
let dataArray = [];
if (data) {
for (var i in data) {
dataArray.push([i, data [i]]);
}
}
let offset = dataArray[1][1];
console.log(offset);
if (offset !== undefined) {
do {
const api1 = await fetch(endpoint + "&offset=" + offset, {
method: 'GET',
headers: {
Authorization: `Bearer ${apiKey}`,
},
});
const response = await api1.json();
for (var j in response) {
dataArray.push([j, data [j]]);
}
offset = dataArray[1][1];
} while (offset !== undefined);
}
for (var k in dataArray[0][1]) {
const commentCount = dataArray[0][1][k]["commentCount"];
const recordId = dataArray[0][1][k]["id"];
await table.updateRecordAsync(recordId, {
"Comment Count": commentCount,
},
)}
} else {
console.error('Error fetching data:', response.statusText);
}
}
// Call the function to fetch data and update records
fetchData();
2. The second automation runs every 2 hours and checks for records with a comment count greater than 0. If there are comments, it checks a latest comment date field and sees if there is something newer. If so, it updates the date, comment text, and mentioned fields with fetched API data
const inputConfig = input.config();
const recordIds = inputConfig.recordIds; // Replace with the actual record ID you want to monitor
console.log(recordIds.length);
const token = 'XXXX';
const baseId = 'appXXXX';
const tableId = 'tblXXXX';
const table = base.getTable("Orders");
const headers = {
Authorization: `Bearer ${token}`,
'Content-Type': 'application/json',
};
for (var i in recordIds) {
const commentsEndpoint = `https://api.airtable.com/v0/${baseId}/${tableId}/${recordIds}/comments`;
let response = await fetch(commentsEndpoint, { headers });
let dataArray = [];
if (response) {
for(var i in response) {
dataArray.push([i, response [i]]);
}
console.log(dataArray);
}
}
try {
let response = await fetch(commentsEndpoint, { headers });
if (!response.ok) {
throw new Error(`Request failed with status: ${response.status}`);
}
let data = await response.json();
let dataArray = [];
if (data) {
for(var i in data) {
dataArray.push([i, data [i]]);
}
console.log(dataArray);
let commentDate = dataArray[0][1][0].createdTime; //gets the date of the latest comment
let commentText = dataArray[0][1][0].text; //gets the text of the latest comment
let mentioned = dataArray[0][1][0].mentioned;
await table.updateRecordAsync(recordId, {
"Last Comment Date": commentDate,
"Last Comment Text": commentText,
},
)}
} catch (error) {
console.error('Error:', error.message);
}
3. The third automation runs on a record updated trigger that watches the latest comment date field for changes and sends the comment text to the rep saying there has been comment activity on their order.
As mentioned, this is a long workaround (not working at the moment) to what seems like a relatively easy fix from Airtable to fire an event of some sort when a comment is made.
