Skip to main content

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

K

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!

    3 replies

    Z
    Forum|alt.badge.img+18
    • Zollie
    • Inspiring
    • 254 replies
    • April 30, 2020

    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.

    K
    • Koby_Kenney
    • Author
    • New Participant
    • 2 replies
    • April 30, 2020
    Zollie wrote:

    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.


    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.

    Z
    Forum|alt.badge.img+18
    • Zollie
    • Inspiring
    • 254 replies
    • April 30, 2020
    Koby_Kenney wrote:

    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.


    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

    Reply


    Most helpful members this week

    Terms & Conditions