Look up alphabetic shelf height in second table

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?

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.

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.