Help

Creating a field to show the most recent person who checked something out

Topic Labels: Base design
1439 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Pamela_Weiner
4 - Data Explorer
4 - Data Explorer

Hi everyone! I’m basically brand new to using airtable to do anything exciting. I am working with a group to set up an inventory rental tracking base for our gear and pretty much recreated the base found here:

The pertinent tables for this are the equipment (our inventory) and equipment checkouts (who took what out)
equipment
equipment checkouts

It’s working really, really well (thanks, @Ben.Young!).

What I’d like to be able to do now, though, is create a field that shows who currently has the gear out.
I was hoping to find something similar to the request here - Comparing two arrays and remove values that appear in both - utilizing the checked in gear and checked out gear fields, but I’m pretty sure the values in those wouldn’t count as an array, and it doesn’t seem to be a thing yet anyway.

I was thinking maybe I could do something with comparing the check out value in the equipment table (uses a unix number created by the date of new records in the equipment checkout table and does a max return) to the records in the equipment checkout table and returning the name from that back into the equipment table (apologies if that was unclear), but I’ve tried putting in link fields and lookup fields and somehow nothing returns at all. Maybe I’m doing it wrong?

I’ve been searching through a ton of topics and finding similar things, but couldn’t get any of the solutions to work for me. I feel like this should be a lot easier to figure out… is there something I’m overlooking? Is this doable?

Thank you!

3 Replies 3

I don’t understand your intended approach as written, but you can have a Lookup field in the Equipment table that shows the {Submitted By} field of linked records (from the {Checked Out Gear} link) of the most recent record.

The Equipment Checkouts table has a {Created} field. So The Equipment table could have a Rollup field that returns the MAX(values) of {Created} so now each equipment item shows the most recent time it was checked out. To get the Person associated with that time, follow along in the example from S08E12 of the BuiltOnAir show, where I go over exactly how to do that.

ayeeeeee! It works!

Dope, so for this problem, I recommend an automation.

I actually just used the example base I provided in the post you probably saw and built this new automation in there.

Here’s the link to it:

I recommend you copy the base and then look at the existing automation.
Regardless, I’ll explain it here as well.


Where’d We Leave Off?

For anyone that isn’t familiar with the original post that this comes from, you can find it here

So we have our base, and we can determine everything we’d like to thus far.
Now we want to see the isolated name of the person who last checked out a piece of equipment.

image

Now, I will create a linked record field from the Equipment table to the Team Members table.
I’ll call this field “Last Checkout By.”

Why Not A Formula?

Now that we have the linked record field, we’ll be able to associate a team member with a piece of equipment directly.

We could do it via formulas, but there are some downsides.

  1. You’d need to create additional fields. This isn’t the worst tradeoff, but it contributes to field clutter, which makes everyone sad.
  2. You’d have to start working with a formula that hits a level of complexity and nesting that quickly becomes really difficult to maintain. It also makes me sad.

The Automation!

I will create an automation whose trigger is based on any update to the given record.

image

Now, I know in your post that you want to see the person that last checked something out specifically.
To provide you with some flexibility, this automation will watch for any new activity.

If you’d like to have it only fire for last checkouts exclusively, then set the trigger to watch for updates to the “Check Outs” linked record field.

Action One: Find Records

We’ll want to find the actual checkout that triggered the change for this action.
So we’ll select a Find Records action and point it to look at the Equipment Checkouts table.

Next, you’ll want to have it search for a condition that looks at:

  1. Where Checked-in Gear contains Gear Tag.
    OR
  2. Where Checked Out Gear contains Gear Tag.

If you’re confused as to how to configure the variables, here’s a step-by-step:


First, you’ll want to set the condition to allow us to select our variable from a previous step.

image

From there, you’ll see the field display a blue +.
Click into it, and a dialog will appear.

Since we want to use data from a previous step, we will click on the data from the first step.
This allows us to plug in data that came from the record that is responsible for the automation’s trigger.

image

From there, we can find the field on the record that contains the data we want to use.
In this case, we want to search for the check-in | check-out record that is linked to our piece of equipment.

This is kinda hard to explain and can be rather dense. I won’t dump time into talking through all the nuances.

We’re going to select the data we’ll search for and insert it into the automation.

image

Now the automation knows that we want to search for the record in the table that is linked to the equipment.

Run the test and it should return one record.
This is the check-out or check-in that was just created for the gear.

image

Action Two: Update Record

Now that we have our information. We now need to link the person that checked out/in the gear, directly to the equipment.

Things will look a bit different in this action step.
You’ll need to tell Airtable which record it should update.

That information will live where it is asking for the Record ID.
Click that blue button again. You’ll now also see the data from the Find Records action.

All you need to do is click back into the first section like we did before.

image

image

Click into the data, and you’ll see the Airtable record ID field.
Hover over it as you did before and select the Insert button.

Now, for the fields to update, you’ll want to select that linked record field we created at the very start; I named mine “Last Checkout By.”

You’ll insert data again, but this time go to the Find Record action and navigate through and find the Name option. Insert that into the automation.

image

Go to run the preview/test, whichever you prefer.
image

You can see that the Last Checkout By field is now linked to the team member record!

Once you activate the automation, anytime someone logs a check-in/out for equipment, it will automatically override the last person and only display the most recent!


My write-ups can be dense (obviously).
But I highly recommend that you copy the base I linked and poke around.
It would probably help open up the automation and read along with the steps I just wrote out to understand better what is going on.

It’s also totally possible that you know how to build automations and that I’ve just been assuming wrong, lol.


A Quick Note…

You’ll want to probably to adjust the permissions on the field so that nobody can change the value besides the automation we just built.

image

There’s probably something I missed, and there’s perhaps something I misunderstood lol.

If you have any questions or need more guidance, don’t hesitate to toss em at me!

There are people way smarter in this community who can also supplement what I posted and support if I can’t answer anything.

Pamela_Weiner
4 - Data Explorer
4 - Data Explorer

Oh, wow, you are absolutely amazing and I truly appreciate you taking the time to do this!! I’m trying to step through this but I am a COMPLETE n00b with this stuff. Usually pretty good with following directions, but I don’t have the option for gear tag under equipment checkout - it is only on the equipment table. Is there some other way to get it over? I tried linking the field on the checkout page, but it isn’t showing up as the formula.