Many to one relationship? Staff training base

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?

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!

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?

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)

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:

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!!

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.

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.

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

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!

ah, ok… from your description, it sounds like you maybe are trying to combine the “link to another record” and “lookup” functionality into a single field. That’s not quite how it works.

First, you need to set up the link to the Employees table. You need that link as a pathway for a Training Log Record to access the data in an Employee Record. Then, link an Employee in there.

Next, create another field that is a Lookup field, that points at your Employee linked record field, and the “Supervisor” field from that table.

I’m definitely missing something here. How exactly do I make the link? I’ve done it successfully in Training Log table with the Events table. So when I add an observation to the Training Log table and select the event, it looks up all the other details, like date, duration, etc. I just can’t seem to make it work for bringing in data from the Employee table.

You set up the link by creating a linked record field that points to the Employees table. And then you link to an employee by selecting the employee you want to link to for that Training Log record in that linked record field.

Can you help me with the steps because it’s still not working for me? The employee name is in the primary column of Training Log. The Supervisor is in the Employee table.

1. insert new field in Training Log --> name it Supervisor
2. For new field Supervisor --> Customize Field Type/Link to Another Record–>Employees–>Save
3. For same new field Supervisor --> Customize Field Type/Lookup --> Employee table is not an option

I must be going wrong somewhere. Right?

So back from the beginning, my understanding is that you should have 3 TABLES right now:

1. Events (1 record for each Event)
2. Employees (1 record for each Employee)
3. Training Logs (1 record for each instance of an Employee attending an Event)

Your Training Logs table should be serving as a JOIN TABLE For Events and Employees – it’s where an Employee meets an Event (in terms of the records).

This means you need to have two separate linked record fields (columns) in your Training Logs table – one that links to Events, and one that links to Employees.

Once you have that field set up that Links to employees (not just typing in an employees name in a text field, but actually selecting an Employee from a list of records that exist in the Employees table), then you will be able to create an additional field to LOOKUP the Supervisor of the Employee that you linked.

Got it! In the Training Log table, I created a field that links to the employee table then selected the employees from the list of records, as you said. Then I created a Supervisor field that is a lookup and it works.

I made the primary field equal to the EmployeeName field (don’t know what else it should be) and the weird thing is that some of the results have quotation marks and some don’t. Do you know why that would be? It doesn’t seem to be an issue but I’m trying to understand it.

The quotes appear because whatever data its reporting contains a comma. AirTable puts quotes so the field isn’t read as an array.