Linking between tables and pulling info based on multiple select linked fields

[Alanjh1]

Hi, I'm pretty new to Airtable and currently just exploring if it will be viable for our company to use.

The set up I have is two tables, one for learners and one for workshop sessions. 

The sessions table has names etc and a field linked to the learners table that shows the attendees booked on the session (multiple select allowed).

The learners table has a similar field linked to the sessions table showing the sessions that they are booked on to (again multiple selections allowed).

What I would like to be able to do is have a new field on the sessions table that for example shows the attendees that did not show up (nothing fancy I would just use a linked field to the learners table and just input the learners manually, again multiple select allowed). But then I want a corresponding field on the learners table showing the names of sessions they have not attended, is there a way to populate this with the session name (stored on the sessions table) based on the did not attend field on the sessions table? I felt like I was a good way towards this using a rollup and IF function but then could not pull the session name through as it was not a recognised field (because it is stored in another table), plus I don't know if it would pull multiple entries through in any case.

So is this possible with formula/rollup or is automations the way to go, if the automations route can anyone point me in the right direction?

Thanks

[Megan_Bandy · Accepted solution]

Hey,

This is a pretty simple Airtable use case and would be well suited to the platform. I've set it up many times for this exact case.

The proper way to set this up is with 3 tables - Sessions, Learners and Attendances. You can then log who attended in the Attendances field and use lookup and/or rollup fields to feed that back to the sessions and/or learners. You'll find you'll need to 'predict' the unique ID for the Attendance Records table - This can be done either with a simple formula (Learner Name + Workshop session = Attendance Record ID) which is then manually pasted into the Linked Field, or you can automate this. It depends on what works best for you within your payment plan.

Let me know if you have any further questions

[Alanjh1]

Thanks for the speedy reply, I'm assuming there just isn't a way to do this without generating a unique record for each workshop slot? I think ultimately the extra records this will generate will make it untenable for us in the long run but thanks for explaining it.

[Megan_Bandy]

Theoretically you could probably do it with formulas but you'd be trying to replicate Excel inside Airtable which is the same as trying to build a spreadsheet inside microsoft word.

Airtable is effectively a database and databases vs spreadsheets allow you to create relationships between different 'things'. So to do it properly you need to put each 'thing' into its own table and that in your case is Workshop Sessions, Learners and Attendances. I'd go as far as suggesting putting the Workshop Names into a 4th table. By splitting them up, you can start to make Airtable much more powerful than Excel.

For example you are trying to manually populate who did / did not attend into the Workshop Session table. But if you entered it where it belongs in an attendances table you can automatically summarise:

Who attended each workshop
Who didn't attend each workshop
Total No. Attendees, Max, Min, Average
Identify most successful workshops
How many workshops a learner attended
How many workshops a learner missed
Who are your repeat offenders for booking and not turning up

Do you think the extra records would push you over the free limit?

[Megan_Bandy]

I've just rechecked the tables you've suggested in your jpeg and its definitely not the way to go. You'll end up with a table for both Bookings and Non Attendees. You just want 1 table (the attendances table) where you log bookings and if they turned up or not. If you already have a Bookings table this is all you need - consider it Workshops, Learners and Bookings tables. Then you can run the summary you want in the workshop table using a lookup.

[Alanjh1]

Yeah it's basically trying to work around the records restrictions to do what we want at a price they're willing to pay so I've been trying to look at what kind of workarounds there may be (which as you say is sacrificing some of the functionality/automation side of things), I think I'm back to having a conversation along of the lines of ultimately for the number of users we'd want and the amount of potential data we are just looking at higher monthly costs than the basic plans.