Skip to main content

Hardware inventory

  • July 8, 2020
  • 5 replies
  • 61 views
M
Forum|alt.badge.img+2

I’m trying to create an inventory of our IT hardware, using computer names and the users associated with those names. I have access to the model number of the computers, but I want to be able to convert that model number into an easier identity that laymen would understand at a glance.

Here’s an example:
Name, Computer Name, Model #, Model Name
Michael, PC01, 10HY002AUS, ThinkCentre M700 Tiny

This last column is what I need to generate, based on the “Model #” - I know I can do this with a VLOOKUP in Excel, is it possible to do in AirTable?

5 replies

Justin_Barrett
Forum|alt.badge.img+21

Welcome to the community, @Michael_Viselli! :grinning_face_with_big_eyes: Is this all in a single table? If so, I strongly suggest splitting things up into multiple tables. At the very least, I recommend a [Users] table and a [Computers] table. Link each user to a computer. In each computer record you can include a field with its “friendly” name, which can be looked up on the [Users] table via a lookup field. Does that make sense?

M
Forum|alt.badge.img+2

I’m not sure the user table is relevant.

The problem is that I’m importing the model information from a generated internal report. So adding it manually is tedious.

So, here’s a formula I have in Excel to do this:
=VLOOKUP(VLOOKUP($A14,Hardware!A:D,4,FALSE),‘New Notes’!E:F,2,FALSE)

The 2nd VLOOKUP is finding out the model # of the A14 cell, which is the computer name. The first VLOOKUP then uses that model # and finds out the “friendly” name. Does this make sense?

Justin_Barrett
Forum|alt.badge.img+21

I’m not sure the user table is relevant.

The problem is that I’m importing the model information from a generated internal report. So adding it manually is tedious.

So, here’s a formula I have in Excel to do this:
=VLOOKUP(VLOOKUP($A14,Hardware!A:D,4,FALSE),‘New Notes’!E:F,2,FALSE)

The 2nd VLOOKUP is finding out the model # of the A14 cell, which is the computer name. The first VLOOKUP then uses that model # and finds out the “friendly” name. Does this make sense?


Thanks for that info. Your original post didn’t mention that you were importing, which definitely changes things a bit.

Unfortunately, Airtable doesn’t have a direct equivalent to VLOOKUP because of how its data is stored and accessed, so we’ll need to look at other options. However, I’m not sure which way to go just yet because I’m still a little lost on how your base is structured and how that compares to the report you’re importing. That example you provided initially:

…is that what’s already in your base, or is that the format of the report that you’re importing?

On a side note, you might want to search the forum for other topics that touch on workarounds for VLOOKUP-style searching. There have been several in recent months if I recall correctly.

M
Forum|alt.badge.img+2

Thanks for that info. Your original post didn’t mention that you were importing, which definitely changes things a bit.

Unfortunately, Airtable doesn’t have a direct equivalent to VLOOKUP because of how its data is stored and accessed, so we’ll need to look at other options. However, I’m not sure which way to go just yet because I’m still a little lost on how your base is structured and how that compares to the report you’re importing. That example you provided initially:

…is that what’s already in your base, or is that the format of the report that you’re importing?

On a side note, you might want to search the forum for other topics that touch on workarounds for VLOOKUP-style searching. There have been several in recent months if I recall correctly.


I did look at many other threads, but couldn’t find one that applied to my situation. :frowning:
The format I provided was how I want it to look. Currently, I have User, PC Name, Model #.

I’m not sure if LOOKUP can do a A=B, B=C, therefore A=C type scenario, which is what the VLOOKUP provides.

Justin_Barrett
Forum|alt.badge.img+21

I did look at many other threads, but couldn’t find one that applied to my situation. :frowning:
The format I provided was how I want it to look. Currently, I have User, PC Name, Model #.

I’m not sure if LOOKUP can do a A=B, B=C, therefore A=C type scenario, which is what the VLOOKUP provides.


Airtable’s lookup field can pull any data you want from a linked record, but you have to make the link first, and it can only pull data from other fields in records that are directly linked. Airtable doesn’t auto-link things, and doesn’t provide any means of arbitrary access to data in other tables.

The best option I can think of is to use a script in the Scripting block, which can read the data you’re importing, find the matching computer record in your existing table, and output the friendly name. This script would have to be manually triggered after each import, though there’s a chance that a newly-announced beta feature might allow the script to be triggered upon import. I’ve asked to join the beta, but haven’t had a response yet, so this is pure speculation based on the description.

Terms & ConditionsAccessibility statement