Tracking Uniforms

I am building a database for employees and want to also keep track of the uniforms we have given them/they return as well as the amount of uniforms we have left to distribute in house.

I figured out a way to link the people to the uniform sizes so i can easily control whats in house and who has which size shirt by linking the employee names to the uniform table and then counting the names to see how many shirts are out. But i cant figure out a way for it to account for multiple quantities of shirts - that’s the roadblock i am stuck at. It wont let me assign someone’s name to a field more than once, so it only subtracts 1 shirt from inventory (1 shirt per name), but most employees have multiple uniforms. How can i get it to account for different quantities for different people?

Any ideas?

Hi @Jennifer_Fierro,

Welcome to Airtable Community! :smiley:

The way I would do that is by having 3 tables, Employees, Inventory, Uniforms Issued

You would create an inventory of the available sizes and quantities, then you would create a table with the employees names, then a table for the issuing.

In the issuing table, link the Inventory and the Employees table. Then you can Rollup in the Inventory table the amounts issued, subtract from the available, now you have the balance.

While in the employees table, you can rollup the qty of uniforms you have issued/returned over The period of time.

BR,
Mo

Does your inventory contain one row for each physical uniform, or one row for each uniform size?

If you have one row per uniform size, then you need a junction table as Mohamed suggests.

If you have one row per physical uniform, you do not need the junction table. One employee can be linked to multiple uniforms. An added benefit of this method is that you can track not only how many uniforms each person has, but also track which uniforms they have. (I have built out a costume database using this model.)

I appreciate the help!

What should I use for the primary column on the issuing table?

If you use the junction table, I recommend having the primary field be a formula field that concatenates the other three fields (employee name, uniform size, and quantity).

Ahh sorry, I had only seen MS response and didnt realize i had replied to yours!

I have men’s and women’s sizes, multiples sizes, one row for each - but not per uniform. I will look into this!

Thank you!