Many to one relationship? Staff training base

[Leslie_Rimmer]

I’m very new to AirTable and am trying to build a base to track employee trainings. One table has employees and the other has events. Each event table record uses a multi-select field for selecting the staff who took the training. Now I want to produce a “report” for each employee. I’ve linked the events in the employee table but can’t figure out how to create a view that gives me info in a format that I can share with each staff member. Any input would be appreciated!

[Jeremy_Oglesby]

I would create your views in the “events” table, rather than the “employees” table.

Create a view that filters to show only “events” where “Employee” = “Some Name”. Then you can share that view with that employee via a shared view link, and that employee will be able to see only their own training events.

Is that what you are talking about doing?

[Leslie_Rimmer]

That works well - great idea! So if I create a grid view and filter on a name in the attendee field I can see what trainings they did, and if I hide the attendee field they don’t have to see all the others who were there. Is there a way I can somehow pull their name into the report or as a title or do I have to create a view for each employee? There are about 40 of us. Thanks for your help with this!

[Leslie_Rimmer]

Another issue I’m running into involves attachments. In the events table I’m using attachments for training announcements and/or materials. However, if an employee receives a certificate, I need to attach that to their specific record. Does this point to having a 3rd table that is a training log with one record per event taken?

[Jeremy_Oglesby]

Leslie_Rimmer:

Does this point to having a 3rd table that is a training log with one record per event taken?

Yes, it does!

Table 1 = “Events”; 1 record for each Event held with all the information specific to that event

Table 2 = “Employees”; 1 record for each Employee with all the information specific to that employee

Table 3 = a join table where Employees meet Events – “Attendance Records”; 1 record for each instance of an Employee attending an Event with all the information specific to that employee-attendance (such as the certificate)

Leslie_Rimmer:

Is there a way I can somehow pull their name into the report or as a title or do I have to create a view for each employee?

While 40 may sound like a lot of views to make, it really isn’t that bad. Create your first view (which, if you use this 3 table route, will be in the “Attendance Records” table) with your filter for a person, and hiding any fields you don’t want visible; then use the 3 dot menu for a view to “Duplicate” that view – all you need to do is change the filter and the name.

If navigating all those views sounds like a nightmare, consider that you can simply type ctrl\⌘ + shift + K to open the search bar for views in a table and start typing a name – once the name you want is filtered down/selected, hit enter, and you will be taken to that view.

So if you have all these views named for the employee, you can then (if you need to) share an employees view with them, and as the title they will simply see their own name (the name of the view, not the name of the entire base). Here’s an example of a shared gallery view – the title of the entire base is “Assessment & Accreditation”, but all the external viewers of my shared view see is the title of the view I’m sharing:

[Leslie_Rimmer]

Hi Jeremy, Thank you for your helpful responses. I like the views you suggest - they help me display the information I want to share. I have the Events table linked to the Training Log table. I’d like to have a view in the Training Log that groups by Supervisor but that is in the Employee table. Is it possible to link more than one table? Thanks again - getting closer!!

[Jeremy_Oglesby]

Yes, it is possible to link as many tables as you want.

But in your situation, I think you just need a lookup field.

Your Training Log table is, presumably, linked to the Employees table. This means that the Training Log table has an avenue to access the data in the Employees table. So create a “Lookup” field in the Training Log table that points at the Employees Table --> Supervisor field. Now you’ve got your supervisors listed in the Training Logs table, and you can group by Supervisor.

[Leslie_Rimmer]

That makes sense but I can’t seem to link it. I create a new field, select “link to another record” and select the Employee table. Then when I do the lookup function all I can select is the Event table. I can’t quite figure out what I’m doing wrong.

[Jeremy_Oglesby]

Are you trying to do you lookup inside the employees table or the training log table?

[Leslie_Rimmer]

From the Training Log table I’m trying to link to the Employee table by creating a new field in the Training Log table, then --> customize field type --> link to another record --> select Employee table. Then when I go to customize field type --> look up --> configuration, only Events is an option, no Employee. I really appreciate your help with Jeremy!