Quickly improve the coordination of your activities using Airtable and no-code

Our development team faced the need to find a no-code or low-code solution that would help minimize risks and optimize the process of scheduling speakers in an EdTech company. We found that standard tools and management methods are not always capable of handling the task, especially when it comes to the complexity and dynamism of changes in event schedules. This led to the development of our own solution, based on the no/low-code platform Airtable and JavaScript programming.

In this article, we will share our experience in solving the issue of overlapping speaker schedules, discuss the challenges we encountered, and describe how a low-code technological approach allowed us to create an efficient system for event management. We will also explain how to use Airtable for this task, comparing its scripts and automation features.

Improving Event Coordination: Automating Speaker Schedules with Airtable and JavaScript

The curators of our courses periodically faced situations where the same speaker was scheduled for lectures or events on the same day. Often, this problem was only discovered at the last minute, forcing immediate rescheduling of the events.

The root of the problem was that when planning events, we did not account for the possibility of a single speaker participating in different sessions that were scheduled at the same time. This meant that without proper control and coordination, we risked finding ourselves in a situation where a speaker physically could not be in two places at once. In practice, this led to the need to change the schedule at the last moment, which could negatively impact the participants' experience and the overall perception of the event's organization.

#1. Main task – avoiding speaker schedule conflicts

The solution to the problem required us to implement a no-code system that would prevent such scheduling overlaps. We understood that effective management of speakers' time necessitated a comprehensive approach, which included careful planning as well as the ability to respond flexibly to schedule changes.

#2. Developing the Solution: Automation with Airtable and Custom Scripts, i.e., No-Code + Low-Code

To address the issue of scheduling conflicts, we identified that the key to success lay in automating the management processes for speakers' time. The starting point of our journey towards an effective solution was a database hosted on Airtable. Airtable provided us with a convenient platform for storing and managing event data, including information about speakers and their schedules. This system was chosen for its flexibility and its ability to integrate with various automated processes.

However, it soon became clear that the standard no-code automation tools available in Airtable were not able to fully solve our task. They could not provide sufficient depth of analysis and lacked the necessary flexibility to handle the specific scenarios we encountered. As a result, we decided to develop a custom script that would be specifically tailored to our unique requirements.

The script we developed uses JavaScript and interacts directly with the database in Airtable. It is automatically triggered whenever speaker information is updated, conducting a detailed analysis of upcoming events.

const table = await input.tableAsync('Pick a table');
const timeField =  await input.fieldAsync("Pick a date/time field", table);
const linkField =  await input.fieldAsync("Pick a linked records field", table);
const issueTableName = "Errors log";
let issuesTable;
const issueFieldName = "Error text";
const issueLinkedName = table.name;
const dateOptions = {
  year: 'numeric',
  month: 'numeric',
  day: 'numeric',
};
const issuesForDeletingArr=[];
const issuesForCreatingArr = [];
function dataFormating(dataString){
    const oldDate = new Date(dataString);
    const newDateWithoutTime = new Date(oldDate.getFullYear(),oldDate.getMonth(),oldDate.getDate());
    return newDateWithoutTime;
}
const dateNow = new Date();
// @ts-ignore
const isIssuesTableInclude = base.tables.filter(table => table.name === issueTableName);
if(isIssuesTableInclude.length===0){
    
    const issuesTableID = await base.createTableAsync(issueTableName, [{name: issueFieldName, type: "singleLineText"}]);
    issuesTable = base.getTable(issuesTableID);
    await issuesTable.createFieldAsync(issueLinkedName, "multipleRecordLinks", {linkedTableId: table.id});
} else {
    issuesTable = base.getTable(issueTableName);
}
const allEventsFetch = await table.selectRecordsAsync({fields: [timeField.name, linkField.name, issueTableName]});
const allEvents = allEventsFetch.records.map(evt=>({
    id: evt.id,
    start:dataFormating(evt.getCellValue(timeField.name)),
    linkedRecord: evt.getCellValueAsString(linkField.name).split(', '),
    issues: evt.getCellValue(issueTableName)
}))
.filter(evt=>evt.start.getTime()>dateNow.getTime());
const issueText = "❌ The linked record already has an event scheduled for the same date";
const issuesFetch = await issuesTable.selectRecordsAsync({fields: issuesTable.fields});
let issuesArr = issuesFetch.records.map(rec=>({
    id: rec.id,
    name: rec.getCellValueAsString(issueFieldName),
    event: rec.getCellValueAsString(issueLinkedName)
}));
function deleteIssues(arrIssuesForDeleting){
    const newArr = arrIssuesForDeleting.filter(rec=>rec!==null).flat().map(rec=>rec.id);
    issuesTable.deleteRecordsAsync(newArr);
}
async function createIssue(issuesArr){
    const newIssue = await issuesTable.createRecordsAsync(issuesArr);
                return newIssue;
}
//foreach all events
allEvents.forEach(event=>{
    //push all issues with the conflict in arr
    issuesForDeletingArr.push(event.issues);
    //filter all events with each linked rcords of the event and same date
    event.linkedRecord.forEach(linkedRec=>{
        // @ts-ignore
        let filteredEvents = allEvents.filter(rec=>event.start.getTime()===rec.start.getTime()&&rec.linkedRecord.includes(linkedRec)&&rec.id!==event.id&&!rec.linkedRecord.includes(''));
        if(filteredEvents.length!==0){
            issuesForCreatingArr.push({
                    fields: {
                        [issueFieldName]: `${issueText} filter events by ${linkedRec} linked record and date for details`,
                        [issueLinkedName]: [{ id: event.id }]
                    },
                });
        } else {
        }
    })
})
deleteIssues(issuesForDeletingArr);
createIssue(issuesForCreatingArr);
output.text('The script has finished working. If errors were found, you will find them in the Errors log table or in the linked records of event. If the errors were corrected, the error records were deleted.')]

The script checks for overlaps in the schedules and, if any are found, records the data for further adjustment. This approach allowed us not only to promptly identify potential scheduling conflicts but also to significantly simplify the process of resolving them.

The script, written in JavaScript, automatically triggers no-code automation whenever the information in a speaker's field is updated.

The built-in automation was not suitable because it has limited capabilities. For example, we could either only perform an if-else check or iterate through records, but it’s impossible to do both simultaneously within the automation. That's why we turned our attention to scripts as the next step in transitioning from a completely no-code solution to a low-code approach.

#3. Operating Principle of the Solution: Detailed Mechanism

Let's take a closer look at the operating principle of the script:

1. Event Analysis and Filtering

Our system starts by analyzing upcoming events. The primary focus is on event dates, allowing us to concentrate exclusively on future events and avoid unnecessary processing of past events. This significantly speeds up the script's operation, which is critically important given the 30-second execution time limit in Airtable.

2. Speaker Schedule Verification

Next, the system checks the schedules of the speakers. We take information about each speaker assigned to an event and verify whether there are any other events scheduled for the same day involving that speaker. If there are multiple speakers, the process is repeated for each one, ensuring thorough verification and preventing potential schedule conflicts.

3. Error Handling and Conflict Notifications

A key aspect of our solution is not only detecting schedule conflicts but also effectively managing errors. Errors are recorded in a dedicated field within the no-code database, making them easier to visualize and resolve. When an error related to a specific speaker and event is corrected, the error information is removed. This ensures that the entire team has access to the current status of tasks and can promptly respond to any issues that arise.

4. Improvements and Prospects

Our solution is actively used by the team, and to effectively recognize the types of conflicts, they have also customized a color-coding system using Airtable's no-code functions. This allows for quick identification and resolution of arising issues, significantly simplifying the process of event and speaker schedule management.

#4. What Helped Develop the Script: Useful Resources

During the project, knowledge related to working with objects in Airtable and applying JavaScript methods for iterating and filtering data proved especially valuable. The use of methods like map and filter allowed us to efficiently process large amounts of information, simplifying and speeding up the script's operation. This approach demonstrates the power of programming and automation in solving specific management and planning tasks.

For the development and optimization of our solution, we relied on various information sources, including documentation and educational materials on Airtable and JavaScript. Articles and guides, such as "Working with Objects," "Airtable Basics," and resources on JavaScript methods, were invaluable in the learning and development process of our script.

The Airtable editor itself includes examples of object code:

#5. Problem-Solving Philosophy

An important aspect of our approach to problem-solving is the focus on preventing issues from recurring. This philosophy helps us stay focused on continuous improvement of processes and systems and reminds us that there is always a solution to any problem.

This idea is echoed in the words of Albert Einstein:

"If I had only one hour to save the world, I would spend fifty-five minutes defining the problem, and only five minutes finding the solution."

This principle reminds us of the importance of deeply understanding a problem before attempting to solve it, especially when deciding whether to take the no-code or low-code path, as every wrong decision costs you time.

CONCLUSIONS

In summary, during our work on addressing schedule conflicts for lecturers, we found that standard tools were not suitable for solving the problem. The most appropriate tool turned out to be the Airtable platform.

To simplify the handling of overlapping events, we created our own script, which not only made the team's work easier but also proved to be adaptable for further improvements and could be used in other projects and products for our clients. We needed to find a solution that would help minimize risks and optimize the process of scheduling speakers in an EdTech company.

We discovered that standard tools and management methods are not always capable of handling the task, especially when it comes to the complexity and dynamism of event schedule changes. This led to the development of our own solution, based on Airtable technology and low-code programming with JavaScript.