Setting up junction table for maintenance parts tracking

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.

I also discuss junction tables in greater depth in this Airtable podcast episode.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld 

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?

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.

2024 Update: 

Now that Airtable has added their interfaces feature, this user experience can be made slightly easier with interfaces.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld