Setting up junction table for maintenance parts tracking

[warren_wu]

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.

Airtable

Maintenance - Airtable

Explore the "Maintenance" base on Airtable.

[ScottWorld]

Welcome to the community, @warren_wu!

You’ve already got your junction table setup properly.

In your junction table, each record would have ONE part, ONE work order, and ONE quantity used. You’ve already got this setup correctly.

In your parts table, you need to change your rollup field to pull from the Parts table instead of the work orders table. That will give you the total quantity used for each part.

Once you have that summary number, you can create other formulas in your parts table that use that rollup field.

[warren_wu]

Got it. That makes sense but I do have some usability concern with this set up.

With this setup, they would have to go to the junction table and pick the parts used, quantity, and attach it to a work order.

The easiest way I was thinking was to have my team pick the parts and quantity within the work order table without them having to go to other tables. Is this set up possible?

[ScottWorld]

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.