Auto link all rows from another table that contain X

Hi there,

I’m working on a database to manage shots in a video piece. In the most basic form, Table1 has a list of every unique shot with timings and other info. Eg.as below (the actual tables have far more fields).

Table1: SHOT __ TIMING

1 __ 01:00
2 __ 01:05
3 __ 01:14
4 __ 01:22

My 2nd table relates to a series of notes, and each single note relates to a single shot from the 1st table, but a shot can have multiple notes. E.g.

Table2: SHOT __ NOTES
1 __ Low Quality
1 __ Not cleared yet
3 __ Fixed
4 __ Cleared
4 __ Low Quality
4 __ Awaiting Update

So using a Look-up on Table1 and Roll-up I can manually add and display all these, but I’d like the Look-up to auto-complete / auto-populate with all the entries on Table 2 that contain the same numbers Eg…

1 __ 01:00 __ Low Quality , Not cleared yet
2 __ 01:05
3 __ 01:14 __ Fixed
4 __ 01:22 __ Cleared, Low Quality, Awaiting Update

Am I missing a trick on how to do this? Shot no. 1 and 4 having multiple records makes this more complex, too. Any help please would be super-duper amazing!

Many thanks,
Tom.

Hi @EastEndTom,

I think what you need is the CONCATENATE formula. For example;

CONCATENATE(ID," __ ",Time," __ ",Notes)

Also, you could consider setting up your base a little differently, in that table 2 would only list the various possible “notes”
image

Hope this helps,
Nathalie

Thank you Nathalie,

That’s not quite what I meant but actually your example base has been super useful for me to think around the problem and how I need to structure the data. Thanks so much! I really appreciate you taking the time.

Tom.

Glad it served useful. :slightly_smiling_face: