I’m using airtable to track maintenance work orders. The issue I’m currently running into is tracking parts quantity used in a work order.
A work order is a task that I assign to my technician for them to complete. When they’re assigned a work order, they’ll have to attach the parts they use to complete the work order. Right now I have a 1-1 relationship with the parts which works well when they select one part, but when they select two parts, the quantity in the Parts table is thrown off.
I’ve read about junction tables and many-to-many relationships, and I’m struggling on how to get this set up. I want my technicians to still select the parts and quantity they used in a work order, and then I want my parts to get updated.
For example -
Work Order - Test
Parts Used - Filter, Screw
Quantities Used - 1, 5
This should deduct 1 filter and 5 screws from my parts table.
Yes, but Airtable doesn’t make it super intuitive to do data entry for linked records, particularly when you’re working with many-to-many relationships. Your users will have to jump through a few hoops.
From the work orders table, your users can click in the Parts Used column, then click on the plus sign, and then add a new linked record by clicking on the “Add New Record” button at the bottom. For each new record they add, they would then specify both the part and quantity.
When they first click on the plus sign, they’re going to see all the records in the junction table, which is what makes Airtable very cluttered and highly unintuitive. Since they’ll probably only be adding new records, you can make it slightly more user-friendly by having Airtable show you no records at all after they click on the plus sign.
You can do this by creating a new view in your junction table which is filtered to show you no records at all, and then limiting the linked record field (back in the work order table) to only show you records from that new view that you created in the junction table.