Look up alphabetic shelf height in second table

Ask the community Formulas
#1

My company has a warehouse full of shelves with differing heights. Rows are lettered, individual racks are numbered (rolling over into letters), horizontal locations on the shelves are numbered 1-4, and shelf height is lettered there is also sometimes a bin number at the end between 1 and 20).
Examples: “M74M 15”, “Q11B”, “ZQ3A”, etc.

The shelves have different vertical spacing depending on the parts, so it can be hard to tell at a glance whether the part you’re picking can be grabbed without getting a ladder or lift vehicle. Rack H4 has small spacing, so you can easily pick anything up to shelf G, but H5 has larger parts, so anything above C needs a ladder or vehicle.

My current solution: searching for the rack number of each part in a spreadsheet that indexes it with the max shelf you can reach. I have a field for the location of each part in our Airtable Database. I’m using formulas to extract the rack number…
LEFT({WH Location} , 2)
…and the shelf height…
RIGHT(LEFT({WH Location} , 4) , 1)
…from that field, and I’m using those to group/priortize my picking in another view.

What I want to do

  • Include my rack number/shelf height index as another table in my base.
  • use a formula in my parts table to compare the rack number and height extracted from the location of the part against that rack in the lookup index, then output a yes or no of whether that location needs a vehicle or not
  • use this information to filter parts by “ones that don’t need a vehicle to pick”

Problems:

  • I’m new to this platform and I’m not sure how to determine if B is greater than A in a formula. (I assume I’ll need to make another table that puts numbers next to every letter of the alphabet, then reference that?)
  • I’m coming from Google Sheets, mostly, and want to be able to use a column from another table as a selector and a value. Is that possible? I want to be able to write something like:
- choose between these options based on the {RackNumber} field in the "Parts" table:
- find the matching {RackNumber} value in the "Index" Table
- compare the {ShelfHeight} fields in both tables where the {Racknumber} fields match
- - - OR lookup the {ShelfHeight} letter of both in the "LetterIndex" table to determine which is greater
- if the one in the "Parts" table is less than or equal to the one in the "Index" table, return "walkable"
- else, return "needs WAV"

Is this possible?

#2

update:

I found a work-around. Currently I have a formula pulling the rack number (2 digits) out of the manually-entered part location field (4-7 digits). I have an automation set up:

  • Whenever the part location field is updated for that record
  • take the linked record field going to the rack height index table
  • update it to match the rack number formula field

I then use that link as a lookup for the for the max-reachable rack height for light and heavy parts.

I use the formula IF( {Height}<={HeavyMax} , "yes" , "needs WAV") to help me priortize picking.

I hope to add the feature of making it give different outputs depending on if the part is marked “heavy” or not.

Does anyone know of a way to do this without the automation? I want the link field to always match the output of that formula.