LInking to two different fields in a table, including the primary field

[SHC-Admins]

I have been setting up Airtable for the records of our large chorale singing group about 6 months. A lot is great, but there are still some things I am trying to figure out.
I have a Members table in which the primary field is the member name. Among a number of other tables, I one called Ticket Sales to track the number of tickets sold by each member. I set up the link from Ticket Sales to Members and successfully created the Lookup so that I can bring in the Member Name to Ticket Sales. However, I also want to bring in the Active Status information for each member, which is a field in the the Members table (people who are on leave aren’t assigned tickets for an upcoming concert). But even though in the Lookup field I choose the Member table, and Active Status field, nothing is returned. What am I doing wrong?

[Andy_Lin1]

If you were able to look up the ticket sales, you should also be able to look up the active status. (In fact, you shouldn’t really need to set up a separate lookup field to display the Member name in the Ticket Sales table – the link field should take care of that.) Without being able to see your table, I can’t say for sure what the problem is.

I’m not quite sure how you’ve set up your base, but this is how I might set it up based on your description.

Airtable

Choir Ticket Sales - Airtable

Explore the "Choir Ticket Sales" base on Airtable.

I’ve created a third table, Concerts, to pull in the sales transactions from the Ticket Sales table; I think this is where my base differs from yours. But hopefully, this gives you an idea of how to proceed.

[Nathalie_Collin]

SHC-Admins:

I also want to bring in the Active Status information for each member

Hi @SHC-Admins - I think what you’re missing is the field type. You’ve already linked the two sheets and now you need to create a LOOKUP field to lookup the value in the ACTIVE STATUS field.

So from the TICKET SALES sheet, you can now see that it’s pulling in the checkmark from the Member sheet. (if no checkmark is found though, it will return blank)

I hope this makes sense.

[SHC-Admins]

Thanks for responding Nathalie! Active status is contained in a single select drop down list that contains Active, Inactive, Non-singing and Resigned. Do you think my problem might be that this value is in a drop-down list not a check box as your show?

[SHC-Admins]

Thanks so much Andy for getting back to me. I see that both you and Nathalie used a check box to indicate active status. It makes me wonder if my problem is that the Active Status field is a single select drop down field with possible choices of Active, Inactive, Resigned or Non-singing. Do you think it is a problem with Airtable not picking up that value if it is in a drop down list?

[Andy_Lin1]

Airtable will pick up drop-down lists in lookup and rollup fields (although it does lose the colour coding), so that shouldn’t be a problem.

Does my model base correspond to yours? As in, are the fields linked in the same way? In your original post, I got the impression that you were tracking ticket sales differently from how I did it, and that could be where it goes awry.

[Nathalie_Collin]

@SHC-Admins - Drop down lists are not an issue. I’m thinking that your field is in the opposite sheet that it should be. Can you share your base with me (nathalie@vacepro.com). I think this would be quicker at fixing the problem.

[SHC-Admins]

Ok I finally had some time to sit down and play with this and found that I needed to reset the Member Name link and now the Lookup function is working exactly as I need! Thank you very much for your input. Being able to look at your model helped me to compare what I was doing right versus wrong. I’m back on track.

[SHC-Admins · Accepted solution]

Andy,

Thanks so much for taking the time to set up this test table for me. By tracking through how you set it up and comparing it to mine I was able to troubleshoot and see where things were correct. It was helpful to know that it is not a problem to generate values from a drop down list. I finally ended up redoing the link to my Member table, redid the look up and got the list I was looking for. Thanks!