Vlokkup workaround

[Paul_Thompson]

I have two columns in a sort of library system. One is a list of about 100 members, and the other a list of items. A third (junction) column forms a historical list of loans, combining an item, a member and a time stamp. I use rollup with max(values) to flag the latest loan of an item.
In the items table, I need to find a way of looking up the latest loan of an item and return its current holder, without having to upgrade to use apps.
Can this be done in Airtable?

[kuovonne · Accepted solution]

It is a bit hard to tell your exact base setup from your post.

I’m assuming that you have three tables: items, members, and loans (the junction table). The loans table has linked record to both items and members. You already identify the time of the latest loan of an item by using a rollup field in the items table. Now you want the member of the latest loan to also show up in the items table.

1. In the loans table, get the time of the latest loan from the items table using a lookup field.

2. In the loans table, use a formula field that checks if the time of the latest loan is the time of the current record, and sets a flag if it is.

3. In the items table, use a conditional lookup that shows the member (from the loans table) for only records where the flag is set.

[Paul_Thompson]

Many thanks Kouvonne. That’s exactly what I’ve been trying to achieve. Your step 3 is my stumbling Block: how to apply such a conditional lookup. I’m struggling to find a solution other than a hidden column for each member and a very long compounded IF or Swap statement.

[Paul_Thompson]

I think I’ve got it! After two months of pondering over this, it’s simple! (I hope). Restrict the lookup to a view that filters to where the latest member field is not empty!! Thanks for your inspiration - at least I think that’s what you meant.

[kuovonne]

Yes, that’s it. The support webpage on conditional lookups has more info. The exact condition will depend on the formula that you use.

[Paul_Thompson]

I’ve just managed it without the need for the filtered view, and discovered how to do a conditional lookup. Thanks so much! Problem solved!