Posted 1/18/2023 19:23 (#10046993 - in reply to #10045690) Subject: RE: Farm Filter database
I've done it and it has taken me a decade or more now to fine tune it to where I am satisfied enough that it works well enough for me.
Ordering filters "used" to take 2 very frustrating days, and I would always either miss filters I need or order too many of a particular filter. Now it takes about an hour, unless I need to add or subtract a new or sold unit, that takes a little longer, but it's not bad.
This is a very detailed reply, sorry for being long winded, but you DID ask... LOL
This workbook is a lot more difficult to add machinery than my Machinery Database I've shared with several other NAT'rs, but if you're proficient with Excel it's doable.
I have over 300 different filter numbers I track. I only stock maybe half those numbers. Many are duplicate filters across several different units.
I have a for lack of a better word, a Database that lists every filter number for EVERY unit on the farm. I have it set up as a table so I can sort it alphabetically, though Excel sorts weird, I have learned to go with it over the years.
It lists the filter number, the quantity needed for stock, how many needed for each individual unit, the filter type of filter, populated by a selectable drop down list, and finally the manufacturer.
For ease I try to get just as many filters through Baldwin as I can, though more and more of our newer units need OEM filters of some sort. A pain.
If there is more than one unit that takes a particular filter, I have the first unit in the list have the total numbers of that particular filter as it's total, then every other unit listed as zero for stock needed. I had to do this because this database page populates my other two tables on other pages of my workbook.
Trying to get Excel to find every filter of a particular number, then add each one, THEN populate other sheets was beyond what I wanted to learn to do. I taught myself how to use Excel, and didn't want to invest that much time in it. I've got probably over 100 hours designing my Filter and Machinery databases over the years.
The next sheet I have is a printable table listing of every filter (in the same order I have them on my shelves) I stock. I simply sort that page by "Quantity Needed for stock" and it becomes a printable (after I fiddle with print areas some) checklist that I can print, go to the shelves, inventory, and write down my stock of each filter on hand. This page has the filter number, quantity needed on the shelves, a blank box to fill in each filter on hand, the type of filter, manufacturer, and one unit that filter fits. This usually jogs my memory enough that I know what other units take that filter if I question it.
Once I have taken inventory, I go back to my computer, and fill in the data I have written down on my next sheet, and quantities needed are being populated on my next sheet as I am entering the data.
This is where I enter Stock On Hand. It's a similar sheet (also a table like the others) to the Print Stock Sheet, but this sheet does the math of what filter numbers and quantities I need. If a filter quantity is zero or a positive number (say I have extra stock I'm using up) it fills the "Needed for Stock" column to "Not Needed". When I am done all of the data entry, I merely sort out the "Not Needed" filters and I have a list to order. I copy THIS filtered list to a new sheet (seperate and different every year based on needs) in the workbook for a little more editing. I change the quantities if I need, or change the print area/ page breaks for printing if I desire.
In the last several years, I just export my edited order sheet to a PDF and email it to my supplier so he can stock order my Baldwin filters for me. He prints my PDF and uses it to pull stock and make notes for his ordering filters for me.
Other brands I sort out, write down and order separately.
The last sheet I have is a "Wall List" where I have the units we have and filters needed. This is a printable sheet with EVERY unit, EVERY filter number, and other substitutions I have found over the years, as well as notes for each unit I need.
I print the Wall List out an place it in a 3 ring folder next to my filter shelves so any of us can just glance at the page with the unit we are working on, and every filter it needs is listed, as well as whether or not it should be on the shelves.
I wish I was a little better at Excel to be able to better handle this data, but it works well enough for me and I can add and subtract equipment as they are bought and sold pretty easily now, I have been working with it for long enough now. 300 different part numbers is a LOT of data to manage.
I have toyed with adding filters to my Machinery Database workbook, but I feel this separate workbook is easier to sort and keep track of just filters, but I have thought about it, I just haven't figured out a good enough way to do all I want with ONE workbook.
Yes, it's complicated enough I have a sheet dedicated for notes so that I can remember year to year how to use the Database.
I hope this gives you some ideas. Below are images of parts of each of my sheets for examples, including an order sheet I email to my supplier.