Assets/Inventory Management

Topic Labels: Base design
1742 3
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer


I am trying to set up a base to track asset/inventory quantities, costs, assigned employees, and locations, but am fairly new to databases and couldn’t find anything in existing topics that helped me beyond probably needing a junction table?

I currently have three tables:

  • one showing the item, type, total quantity, and cost per item
  • one showing the employees that currently has a column showing links to the items
  • one showing the locations

I want to be able to designate how many of each item each employee has been assigned, and where the item currently is. For example say there are 5 laptops worth $500 each, John has 2 (1 at home and 1 at Office 1), Mary has 2 (1 at home and 1 at Office 2), and the last is currently unassigned but is at Office 1. This situation is then repeated with various other items with varying values.

From that, I want to be able to see at a glance that John has $2,500 worth of items and a breakdown of what those items are, while having some kind of limit that tells me, “there aren’t any more of item X available” if I try to assign units beyond what is available.

Thank you for any help.

3 Replies 3

I made a custom app with this sort of setup in mind:

If you don’t want the app, watch the video anyway to give you an idea of what you’ll need for a fourth “Log” table so you can store the information you want for current “assignment” details

That does give me a better idea of what I’m looking for, and looks really useful. However a lot of the items I have aren’t split into separate units, and are currently just one line records with “item name” columns and “quantity” columns whereas your units are all distinct though categorized into like kind groups (chairs, desks, etc.).

Is the only way to effectively handle tracking to split everything out like that?

I have definitely bookmarked your app to poke around at though, thanks!

Sure. Your log could just link to the master “item”, and have a quantity field. Then you could use a conditional Lookup or Rollup to determine if an item has any available units by subtracting the total “out” quantities from the overall count.