Linking to multiple tables with same primary key

[Adrienne_Luczko]

Hello, I’m looking for advice on a specific use case: my organization provides training and we need to track when a participant joins a program, when they leave a program, and their attendance at various events. Ideally, this information would be filled out by the participant themselves or a coach via a form, but not all of the information would be known at the same time. However, I would like all of the information to be linked, so that a joining, leaving, and attendance record for the same participant would recognized as the same participant. I believe that this would mean that the primary key for each of these tables should be the participant’s name or a unique id, but I’m not sure how to link multiple tables with the same primary key.

Ideally when filling out an attendance or leaving form, it would be possible to search the list of participants who have joined, rather than manually entering the name (perhaps leading to inexact matches). Is there a way to do this?

[Christina_McGui]

Hi,

I’m a bit of an Airtable newbie, and you may have solved this already(?), anyway…

What’s the structure you’ve set up to support this? (Bases/tables)

Airtable doesn’t link on primary keys - this is where it differs from a database - you need to create a linked record field in order to link records in different tables (and it can’t be on the primary field), and then use Lookup fields as needed.

So, say you have a table of participants, you could link them to programmes. With a linked record in place you can then use a Lookup field to assign a programme to a participant or vice versa.

You also need a programme attendance table as well. Use Linked Record fields to link to the particpant, and to the programme, and record their start and end date on that programme in the programme attendance table.

You need another table for programme events, and this needs to link to the programme table. (Assuming the events are related to programmes.)

Tracking the events the participants attend - again, a linked record in the participant table to the event table. The nuances of this will depend on whether you need to record the date of attendance for each event, etc.

Filling in the information (at different time points) would be (realtively) easily accomplished providing the people who input the data are Airtable collaborators. if you are looking to do this via a form so that those who input don’t need an Airtable account then I think this will be much more complex to achieve. Perhaps in the first phase of development you could set that requirement aside and eg gather info via a spreadhseet for those with collaborator permissions to add?

I haven’t got my head around updating existing records via a form, and I don’t ‘think’ that’s actually what you want. it seems that you want to add a new record eg a new event, new programme with their start date. Though I can see that end date is effectively an edit…

Anyway, this is the limit of my knowledge - I hope it’s of some use, and am sure if I’ve stated anything incorrect someone will come along and correct it :upside_down_face: