Inventory/storage location spreadsheet

[ErBrown]

Howdy Folks,

We're trying to get a handle on how to best keep track of and organize an inventory including storage locations. I've been scouring blogs for ideas but thought it would be worth posting something up here.

Anyone have a spreadsheet that works well for them, that they'd be willing to share. Any good apps out there?

Thanks again,
EB

[UNCIVILIZED]

Someone was kind enough to pass this one along to me yesterday. Though I've not had a chance to investigate it much as yet. But there's a free, downloadable spreadsheet along with instructions. https://theboatgalley.com/downloadab...g-spreadsheet/
What else have you tried to date?

Also, I posted that I needed some spreadsheet help last night, & folks have been sending me all sorts of info. Perhaps some of what's in the thread might help you. It's here --> http://www.cruisersforum.com/forums/...rs-177409.html

[StuM]

If you're trying to cross link stores and equipment in various categories over various locations, a simple database would be much better than a spreadsheet.

Who'd be interested in an MS Access one?

[ErBrown]

Originally Posted by StuM

If you're trying to cross link stores and equipment in various categories over various locations, a simple database would be much better than a spreadsheet.

Who'd be interested in an MS Access one?

I'd be very interested, never been much of a DBase guy, but it probably would be the best option. I'd be interested to see it, but as a Mac user, I've never used access.

Sounds like you're onto what we're looking for. An easy way to keep track of what we store where. Thinking more along the lines of spare parts and the types of things we don't use on a regular basis. I have seen the boat galley spreadsheet linked above and think it's great for shopping purposes, but we're really more looking for something to tell us which of the crap-ton of lockers has X-widget.

[scherzoja]

I think an inventory db would be a great idea. I'd like a copy. I see many spreadsheets for this, but they seem very cumbersome. I make simple MS Access databases for all sorts of things, but I can't seem to pull together the creativity to design one for stores inventory.

[StuM]

OK, version one is up. All comments/suggestions for improvement welcome.

http://www.camcopng.com/download/BoatInventory.zip

[sy_gilana]

Hi, several years ago we set up a system, actually in 1998. We put it on our blog, and it was adopted by the USN sailing squadron at Annapolis.

Spreadsheet is good enough, and it allows you to sort by name or location, also, if you know where something should be, on the SS you can see what else is in the compartment.

The naming conventions are important.

go to gilana.org and then on the left there is a button called "MORE" click that and then you will find the inventory system.

It has evolved since then, but there are now meds and personal items so I don't want to share the latest version.

OH in OpenCPN you can DL the PI called Logbook konni, it also has an inventory database, but as I said, its handy to look at the list, not as a report form or input form.

[StuM]

Just updated the database with an additional screen to view/sort/filter everything.

[scherzoja]

Thanks Stu,

I like this. I'll mess around with it.
Only problem I found so far was the right column of Inventory, when I leave a new field blank, I get error stating the primary key can't be null value.

I understand that the means and that it's totally user-error, but it's an easy error to make and only way to get out of the 'can't be null' error is to close the db.

One has to enter data in that field (circled in red in attached pic), then go down to specify where it's located, qty, and description. If you add that empty field, specify loc, qty, desc, then go back and try to enter what the item is, you'll get error.

Any way to make it so we can go back up to that primary key field and enter data?

It's not a big deal, but for anyone who does not understand what the error means, it'll be really frustrating.

[StuM]

Ok, first bit of "user proofing" done


(You didn't need to exit the application, just hitting Esc after acknowledging the error message should have done it)

Please download the new version from the link above.

[StuM]

Just realised that you will get a similar problem if you add a storage loc when there is no selected compartment and with item and no selected item type. (No error, just lost entries. I'll publish a fix shortly and advise when up.

Edit: OK, that one is resolved too. New version at link above.

[scherzoja]

That works better, thank you Stu; the error is more understandable.

How about an export function from the "List Contents" output screen to create shopping list (instead of revealing the List Contents query to export?

Export to *.csv or *.xls (preferred) to create a "shopping list"

Maybe export when qty < x amount? Where x is a min. qty threshold for restock. Thresholds *might* be different for food vs engine spares, vs other categories. I had food in mind with < x amount

It might be better to export without conditions, then in the SS, add a column for reorder.

In that column, add formula: =IF(C2<2,"Reorder","Full") to flag any qty < 2 with Reorder, the sort by Reorder to get my shopping list

I'm not sure which will better for the general user. I'll sleep on that.

Thanks again Stu, I think this will be very useful.

[StuM]

I've added the ability to select/deselect items in the List Contents screen and to export the selected items to a spreadsheet on the Desktop.

New version is up at the link above.

[sy_gilana]

Stu, that looks really good, but I won't be able to use it unfortunately, linux rules here.

One addition you could make is to create a + and - button when the individual item is listed to enable reducing or increasing inventory by one. With a SS our keystrokes are cumbersome...

Example: When we do a tomato pasta dish, we will get into locker xxx, and take one can of tomato paste. We open the SS, search for "tomato" find the item, select the qty cell, and subtract one, save and exit. (we do this for every item)

Another nice addition would be the standard EOQ and Reorder point formulas from production/inventory management science.

[StuM]

Originally Posted by sy_gilana

Stu, that looks really good, but I won't be able to use it unfortunately, linux rules here.

One addition you could make is to create a + and - button when the individual item is listed to enable reducing or increasing inventory by one. With a SS our keystrokes are cumbersome...

Example: When we do a tomato pasta dish, we will get into locker xxx, and take one can of tomato paste. We open the SS, search for "tomato" find the item, select the qty cell, and subtract one, save and exit. (we do this for every item)

Another nice addition would be the standard EOQ and Reorder point formulas from production/inventory management science.

Changed "List contents of all Storage Locs" to "List All Inventory Items" (previous version didn't show items in list if you didn't have them allocated to any storage location)

Added buttons to List screen to change sort order between "Locations" and "Items".

Added +/- buttons for each item storage loc.
Added "Min Qty" field for each item.
Added "Select Items Below Minimum Quantity" to List screen.
(I think EOQ and ReOrder point are a bit overkill for this simple application )

New version up at the link above.