Apr 27, 2022 12:43 PM
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)
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!
Apr 27, 2022 01:31 PM
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.
Apr 27, 2022 02:03 PM
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.
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.
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.”
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.
I will create an automation whose trigger is based on any update to the given record.
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.
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:
Checked-in Gear
contains Gear Tag.
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.
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.
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.
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.
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.
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.
Go to run the preview/test, whichever you prefer.
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.
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.
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.
Apr 28, 2022 09:17 AM
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.