Creating a field that returns all occurrences of a value in another table

[Koby_Kenney]

Hello,

I am developing a dock check-in and punchlist combination tool for some of our employees. The gist is that of the primary tables, one (let’s call it Dock Check) is to be a list of each item, summarizing their quantities and listing every room in which the specific item is to be placed. The second of the primary tables (let’s call it Punch List) is organized by room, and lists each item in the room.

I want a field in Dock List to return every room in which the item is listed on the Punch List. I can’t determine if I’m just having trouble with the logic or if this isn’t a possibility in Airtable.

The only supplemental table presently is a rooms table, which just lists the room #, room name, and department.

Thank you in advance for your help!

[Zollie]

If you have a table full of Items, and you’d like to associate it with a table of Rooms containing those items, then you should be using linked records. Once the linked records are in-place you can also take advantage of some additional field types that can help you summarize data and reduce data redundancy.

But if I’m taking your title literally, then no, that field type doesn’t exist. Not sure what the technical limitations are for Airtable, but for client-side tools (scripting, integrations), that sounds like a difficult ask.

[Koby_Kenney]

Hey Zollie,

Thanks for responding. I have in mind a way of linking the Dock List to the Punch List, even through an intermediary table, where I could link the the Punch List and use a Lookup field to read the rooms back off of that Linked field. I just can’t straighten out the hoops I’d have to jump through. Right now, Punch List is set up with the key field just being autonumbered; Dock List is set up to have the item as the key field, but I am considering shifting the key to autonumber, as well.

I don’t know if that helps shed light on a feasible solution, but I thought I would add it in, just in case.

[Zollie]

If every row in Dock Check represents a unique item, and every row in Punch List represents a unique room, then linked records should make this easy. Here’s a small example:

Items (Dock Check)
Name (single line text)
Item1
Item2
Item3

Related Rooms (linked record)
Room1, Room2
Room3, Room1
Room2

Rooms (Punch List)
Name (single line text)
Room1
Room2
Room3

Related Items (linked records)
Item1, Item2
Item1, Item3
Item2