Skip to main content

Hi Community,

New to Airtable and trying to put together an inventory tracking system.  I followed some video's on YouTube and created 4 separate tables.  Equipment (i.e. models), Received, Issued, and Disposition.  I have a form that will intake when we receive item.  The form logs each record as an individual item, so we can track serial numbers and other unique information to the device. 

Here is where I am wondering the art of the possible.  I can have the team fill out another form with the exact same information, but I was wondering if there is a way, in my Issued Form where I can look back at the inventory that has been received and provide a drop down for staff to select from.  That data would be submitted in the Issued table.  Assuming there would also need to be a field in the Received table for Available Yes/No.  After selection and submission of the form, it would change that Available field from Yes to No and remove it from the list, the next time it is submitted. 

Stretching my knowledge area a bit here, but I thought I would check with the Community to see if this is something that is even possible with the system and difficulty level to set something like that up.

Any help would be appreciated.

Thank you,
Keith

Yeap, this is possible (assuming I have the correct understanding of your base in my head).  Each form submission's linked to the appropriate record in "Equipment" right? 

If so, you could use rollup fields with the formula `MAX(values)` to identify the latest "Received" and "Issued" form submissions, and if the "Issued" date is later than the "Received" date, that means it hasn't been returned yet.  You'd then use a formula field to do this check for you, create a view based of the output of that formula field to only show equipment that's available, and then modify your form's linked fields to pull from that view only, ending up with something like this:

Link to base

All in all, probably take someone familiar with Airtable a half hour or less.  If you're not familiar with the rollups for `MAX(values)`, here's a link to a base I've set up that you can refer to.  These two examples together should be enough to get you up and running pretty quickly

Hi Adam,

Thank you for your response.  Sounds like I need to figure out rollup fields a bit more.  I was trying to look at the logic behind the information you linked, but it seems to be view only.  Your inventory processed until disposition was helpful because I think that is what I am looking for.  Looks like you have two separate grids, one containing the data and another with only the "available" items to select from.  Trying to replicate is the challenge at this point.  

I think something just dinged and I am hoping you can verify.  In your example, you are pulling date from Received and Issues and if there is no issued date, you are showing rolling up the equipment as available, correct?

Hi Adam,

Thank you for your response.  Sounds like I need to figure out rollup fields a bit more.  I was trying to look at the logic behind the information you linked, but it seems to be view only.  Your inventory processed until disposition was helpful because I think that is what I am looking for.  Looks like you have two separate grids, one containing the data and another with only the "available" items to select from.  Trying to replicate is the challenge at this point.  

I think something just dinged and I am hoping you can verify.  In your example, you are pulling date from Received and Issues and if there is no issued date, you are showing rolling up the equipment as available, correct?


> In your example, you are pulling date from Received and Issues and if there is no issued date, you are showing rolling up the equipment as available, correct?

Ah no, I'm showing "Available" if the "Received Date" is after the "Issued Date"

> I was trying to look at the logic behind the information you linked, but it seems to be view only. 
Yeap, once you duplicate the base you'll be able to view everything. 

You can duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button.

 

Reply


Terms & Conditions