Struggling with Structure

[Raj_Bharij]

Hi, I am evaluating airtable for use in our not for profit orgainisation and I’m struggling with the correct structure to start.

I have three tables:

Table Name: Trainee
Field: Trainee ID (Primary Key), Name, Address

Table Name: Courses
Field: Course ID (Primary Key), Trainee ID (Foreign Key to Trainee ID ), Course name, Course Passed

Table Name: Work History
Fields: Work ID, Trainee ID (Foreign Key to Trainee ID Table), Employment Gained, Start Date

I want to be able to use the linking to run reports such as number of trainees who have passed or even of the trainees who passed how many went on to gain employment. I don’t seem to be able to link specific columns in tables. Also, if you create a form, can you have columns on the form from across different tables?

[Nathalie_Collin]

Hi @Raj_Bharij,

I think what you’re looking for is a LOOKUP column. You’ve already linked you’re columns (aka “Foreign Key”). Once you’ve linked them, you now have the ability to pull in other information via the LOOKUP column type.

Hope this helps,
Nathalie

[Justin_Barrett]

Raj_Bharij:

Also, if you create a form, can you have columns on the form from across different tables?

Yes and no. You can’t directly pull fields from other tables into a form, but if you’ve used links or lookups (as @Nathalie_Collins suggested) to echo those cross-table fields in the table with the form, they can be included.

[Raj_Bharij]

Hi, so I tried to do a lookup of a field in another table but I get no data returned. In the work history table I created a new column and made this a lookup of Course table to look at Course Passed. I have a Yes/No there but no data is returned. Am I doing something wrong?

[Justin_Barrett]

You can’t do a lookup straight to a field in another table without a link between the tables first, and your table descriptions above didn’t indicate that you’d done this. If you did, please describe in detail how this is set up. If you can include screenshots of your tables and the details of the lookup fields you made, that would also help.