Build my own CMMS! Production Equipment and Building Maintenance Use Case


#1

Hello Airtable Community,

I am a technically trained serviceman, who is currently employed as a maintenance guy with a myriad of assets that require on going responsibilities. I have a fuel inside of me to get all the scattered and tacit knowledge shoved into a database so I can have more time to spend on projects I enjoy.

The Access relationship picture supplied was a work in progress. I was having a hard time with balancing certain things. I created 3 levels of “assets” Items, Assemblies and Parts. This is because I have equipment that has assemblies that can be replaced or repaired externally (OEM) but can be repaired in-house on a part level. Also, those assemblies can be taken off of one machine, repaired, then put back onto a different machine. The assembly level is also where any prototyping would occur when designing something with my 3d printer and trying it out. So any information about length of time in service would be important for a specific identifiable assembly. Parts level assets would be not unique but inventoried.

I was uncertain how to bring it all into user friendly presentation and usability in access. I was certainly willing to err on the side of more tables than not.

Use Case examples for the different needs of this single asset table include:

Ex, Production machines are important to see the last few records back 6 months, on a specific machine, as well as the last time usable parts were replaced (4 cd drives in each),
HVAC equipment just needs to see when the belts and filters were replaced and any past notes, like drip pan look clogged, drip pan unclogged/fixed,
Emergency lighting/signs need to have the battery changes out on a 5 year schedule
etc.

One thing I am worried about with Airtable is having an excess of views.

One thing I think Airtable will excel at will be replacing some of my tables with drop down selects. Location, Area, and Equipment Type tables can all be replaced with drop downs.

I also think Equipment Model will be deleted.

As far as the Item>Assembly>Part hierarchy. I wonder if I could go with two tables, Assets and Parts, and if need be use an Asset to Asset table to link “Assembly” level Assets to “Item” level assets.

Any help or contributions toward the project are more than welcome!


#2

I agree with your thoughts on replacing the Location, Area and Equipment Type tables with drop-downs. That makes sense.

As for views, you could go one of two ways:

  1. Create a filtered view for each of your use cases. You’ll wind up with quite a few views.
  2. Don’t create any views. Just change the filter as necessary. You’ll keep the number of views down, but entering the same filter over and over again will likely get old.

You might want to start out with #2, then permanently define views with filters you run frequently.