Setting up a complex base

[Sigurdur_Herman]

TL;DR: I've come up with a Rube Goldberg machine of a base. Is there a better way to organise this? 

I'm managing a small school. I'm using Airtable for organisation, Mailerlite for emailing, Zapier to connect the two, and Fillout for sign-up forms. Probably this doesn't count as complex for most of you wizards, but I'm having a hard time finding an efficient workflow!

Currently, I've got this going:

• A form table (Fillout integrates with Airtable and links to this table). Each record is a form submission, so some records may reflect the same student, perhaps returning for an advanced course. An Airtable automation checks if the course a student wants to join is full, and if it is full, updates the record to join a waiting list instead. Zapier checks a view here to add or remove students from the corresponding group on Mailerlite.
• A courses table, and a Mailerlite group table. Each course is connected to a Mailerlite group, but not every Mailerlite group is connected to a course. For instance, an 'interested' mailing list.
• Since courses and Mailerlite groups are separate things, the form table and students table each have distinct 'linked records' fields for 'join course', 'leave course', 'join Mailerlite group', and 'leave Mailerlite group'. This is the beginning of my troubles, I think.
• A students table. When a record is created in the form table, an automation checks if the email already exists in the students table. If it doesn't, the automation makes a new record for that student. If it does, the automation updates the old student to join the relevant course or Mailerlite group.

I had a moment of "there has got to be a better way" when I was setting up an Airtable automation with 8 if-conditions. To update a pre-existing student, the automation has to check if they're joining a course, joining a Mailerlite group, leaving a course, or leaving a Mailerlite group. Then it has to check if the relevant field in the students table is empty or not, so that it doesn't overwrite the content if it isn't empty.

I feel like I've created a Rube Goldberg machine of a base. Is there a better way?

[TheTimeSavingCo]

That all sounds pretty good actually.  The one thing I'm wondering about is the "Join course", "Leave Course", "Join Mailerlite Group", "Leave Mailerlite Group" bits, could you talk about that a bit more?

In my head, I was thinking that the form submission would trigger an automation that would link or unlink a `Student` record from a `Course` record, right?  How does the "Join Course", "Leave Course" stuff come into play?

[Sigurdur_Herman]

That all sounds pretty good actually.  The one thing I'm wondering about is the "Join course", "Leave Course", "Join Mailerlite Group", "Leave Mailerlite Group" bits, could you talk about that a bit more?

---

I've got a Zap watching 'join course' to 'create or update a Mailerlite subscriber'. I've got another Zap watching 'leave course' to 'remove a subscriber from a group'. Then two more Zaps doing the same things for the fields 'join Mailerlite group' and 'leave Mailerlite group'. I suppose I could figure out a way to make one Zap add a subscriber to a course AND/OR a Mailerlite group, but since I've got a 'courses' table and a 'Mailerlite groups' table, they're separate linked fields in the 'students' table, as well.

In my head, I was thinking that the form submission would trigger an automation that would link or unlink a `Student` record from a `Course` record, right? How does the "Join Course", "Leave Course" stuff come into play?

---

I'd like to be able to look at old courses and see who was in them. Unlinking a 'student' record from a 'course' record entirely would preclude that (it would, wouldn't it? The things I don't know about Airtable could fill a book).

Thanks for the reply, @TheTimeSavingCo! 

---

[TheTimeSavingCo]

Ahh, yes, thanks for explaining all of that.  And yeap, removing the links would prevent you from seeing who was in the course

Hmm, I think that I would try to create a bigger distinction between fields that exist to trigger Zaps, fields that display current data, and fields that display historical data

Like, the linked fields "Join Course", "Leave Course", "Join Mailerlite Group", "Leave Mailerlite Group" shouldn't be used for anything except triggering the zaps; there should be clear links to indicate which courses / mailerlite groups each student is already in without checking those linked fields

Similiarly for the old courses information, if you're looking to keep in view what students were previously in a course, you could technically do that via the form submissions page, or perhaps have another automation that exists solely to consolidate historical data. 

It feels like once those systems are in place, it would feel less rube goldbergy because the links between each of those systems are less tenuous; they're all built for a specific thing if that makes sense?  (All very subjective though!)
---
When you say "look at old courses and see who was in them", does that mean you're unlinking all the students from a course manually once it's over, and then reusing the record when the course begins again or something?

[Sigurdur_Herman]

Hmm, I think that I would try to create a bigger distinction between fields that exist to trigger Zaps, fields that display current data, and fields that display historical data

Like, the linked fields "Join Course", "Leave Course", "Join Mailerlite Group", "Leave Mailerlite Group" shouldn't be used for anything except triggering the zaps; there should be clear links to indicate which courses / mailerlite groups each student is already in without checking those linked fields

Similiarly for the old courses information, if you're looking to keep in view what students were previously in a course, you could technically do that via the form submissions page, or perhaps have another automation that exists solely to consolidate historical data. 

It feels like once those systems are in place, it would feel less rube goldbergy because the links between each of those systems are less tenuous; they're all built for a specific thing if that makes sense?  (All very subjective though!)

I really like the idea of this, but that's also where my ability for data organisation breaks down. Do you have a suggestion for how to clearly indicate which courses/mailerlite groups each student is already in without checking zap-only fields? Or how to consolidate historical data? 

---

When you say "look at old courses and see who was in them", does that mean you're unlinking all the students from a course manually once it's over, and then reusing the record when the course begins again or something?

---

For clarity, I'll use the term 'course run' to indicate a single instance of a course (like a 4 week period where that course is taking place). A separate 'course run' would then begin some time after a 'course run' ends, though it's the same type of course.

I'm looking into making an automation which triggers when the 'end date' field of a course run is 'today'. The automation would change the Mailerlite group associated with the course run, and all old course runs would link to a 'former students' Mailerlite group. That way, all former students join the same Mailerlite group, which is all that's relevant for emails, but I can still check WHICH course they took in the past, if necessary.

Maybe I'll run into technical issues when making the automation and be forced to change some technicalities, but basically it will automatically add former students to a 'former students' Mailerlite group while preserving information about which course the former student was in.

[TheTimeSavingCo]

>  Do you have a suggestion for how to clearly indicate which courses/mailerlite groups each student is already in without checking zap-only fields?

Hmm, this is all powered by form submissions right?  And the form submission indicates which student it is, as well as whether they're joining or leaving a course? 

If so, I'd just have all my automations / Zaps trigger from there.  When a form gets submitted for a student joining a course, an automation would run to look for that student record and link it to the specific course.  When a form gets submitted for a student leaving, then an automation would run to unlink it. 

These same form submissions would also trigger the Zaps to add / remove from the mailerlite groups

> Or how to consolidate historical data? 

Hmm, I think I'd potentially just create a new text field or something to the "Courses" table and just add names to it.  If I really wanted to I could just make it a linked field to "Students" that I always add to and never remove stuff from, and it'd be in the same automation mentioned above for adding students to courses

> For clarity, I'll use the term 'course run' to indicate a single instance of a course (like a 4 week period where that course is taking place). A separate 'course run' would then begin some time after a 'course run' ends, though it's the same type of course.

And each course run is a single record, right?  Yeah your "end date" automation idea to alter the Mailerlite group sounds good

[Sigurdur_Herman]

When a form gets submitted for a student joining a course, an automation would run to look for that student record and link it to the specific course.  When a form gets submitted for a student leaving, then an automation would run to unlink it. 

---

I looked into that, but I can't find a way to have Airtable automations simply unlink specific records in a multi-linked-record field without using scripts, and coding scripts is...intimidating. The best I can do is having an Airtable automation overwrite the field entirely.

That's how I ended up working on a ridiculously long if-conditional automation. IF the student is joining a course AND their "courses" field is empty, add their chosen course; IF it isn't empty, add the old value and the new course, comma separated. IF the student is leaving a course, AND their 'former courses' field is empty, add the course they're leaving; if it isn't empty, add the old value and the course they're leaving, comma separated. And repeat for Mailerlite groups. It'll work, it just...felt inelegant. That's when I figured somebody on the forums would have a better solution xD

Hmm, I think I'd potentially just create a new text field or something to the "Courses" table and just add names to it.  If I really wanted to I could just make it a linked field to "Students" that I always add to and never remove stuff from, and it'd be in the same automation mentioned above for adding students to courses

Hmm...I could probably just rename my 'leave course' field in the 'students' table to 'former courses' and that'd be pretty much the same thing. I'd obviously still keep 'leave course' in the 'form input' table, linked to Zapier. Thanks 😃

[TheTimeSavingCo · Accepted solution]

> I looked into that, but I can't find a way to have Airtable automations simply unlink specific records in a multi-linked-record field without using scripts, and coding scripts is...intimidating. The best I can do is having an Airtable automation overwrite the field entirely.

Hm, I don't think you'll need scripting for this actually.  Just set up an automation that'll look for all the courses that the student is signed up for AND the course name doesn't match the course they're leaving:

And then update the Student record's "Courses" linked field with the found value

[Sigurdur_Herman]

Well I'll be. That is a fantastic solution; thanks, Adam!