Keeping an Inventory of your Trains

@lionelflyer posted:

Hi RJR,

Someone on this forum, sorry I don't remember his name, quite a while ago used the DBF file I sent him and converted it to an Access database.  I'm pretty good at keeping it updated when I acquire new items as there are over 1000 line items.  If you can still open the DBF file, I think you can convert it to something like a comma delimited file and then up load that in to Excel or Access.

This website seems to have some information on doing the conversions: https://www.lifewire.com/dbf-f...w-to-open-dbf-files, but I haven't tried doing that as I have the files in Access now.  I'm sure there is someone on this forum with much more knowledge on how to do that than I have and may be able to help you.

For the reasons stated above, Insurance, Heirs, not winding up with duplicates, I like having the data base available.  Just yesterday I was looking in an antique store and they had a Lionel 3461 Green Log dump car for $38 with 30% off and I couldn't remember if I had one or not and quickly found that I did.

It is especially helpful since I have almost everything stored in 56 quart plastic containers and have them numbered so I know about where I can find something.  I think I'm up to about 80 containers so knowing where something is can be a great timesaver when i want to run it.

In the 1980's, I used to do a LOT of dBase/Clipper database development. Clipper was great because you could develop free-standing database applications. It kind of faded away as Windows matured. That link was very helpful as I hadn't followed the xBase standard when I started using Access (now I'm curious).

The one problem I had with the dBase III/VI table format was the use of .DBT files for memo fields. The major issue was the DBT file link with the DBF becoming separated (this goes back to the DOS days). Because Clipper could handle large text fields (65535 characters), I developed a function that would split text "fields" into smaller blocks which reduced wasted space.

A good xBase structured file set (properly normalized) coupled with a nice portable front end would be great for an inventory program across multiple platforms.

Hello All, I have been an Apple products user since the days of the Apple IIc desktop. The first database that I used for my model trains was the database that came with AppleWorks. Since it is no longer supported by Apple, I have converted to iDatabase and was able to convert my self-made database to iDatabase which is still a supported product.

Here's my Inventory:

    Locomotives #1 through #5.

    Traditional cars #1 through #12

    Tinplate cars #1 through #8.

Hope I didn't leave anything out.  Oh yea, . . . the Z1000.   :-)

Mannyrock

Having served as Executor of decedents' estates several times, having accumulated a large collection of trains and associated paraphenalia (e.g., tracks, transformers, DCS eqpt, operating accessories, etc.), I can tell you why I have an inventory.  Some day one of my sons will be in a position of having to dispose of these treasures.  While all of may grandchildren (youngest now 15) relate their fond memories of operating my layout, I am sure that not all of the equipment will find a home in the family.  Having an inventory in hand will make it easier for whichever son draws the short straw to be able to communicate with someone like Mr. Muffin to discuss finding a good homer for someone seeking to adopt.

Hmmm...........if you really need inventory software or a database to track what you have in trains, you probably are the point where you need to sell some off.

@Mannyrock posted:

Here's my Inventory:

    Locomotives #1 through #5.

    Traditional cars #1 through #12

    Tinplate cars #1 through #8.

Hope I didn't leave anything out.  Oh yea, . . . the Z1000.   :-)

Mannyrock

That's fantastic.  Now the OGR forum search engine can be your inventory software.

Jon

I made a Welcome to Papa's Trains photo album. The album is not only a record of all my trains but it is also a history of mt trains and layout. My trains are all listed with a description of the engines, operating cars, stock and passenger cars and all my accessories. I have photos of all my scratch made buildings and how I created some of them and where I got some of my ideas. I have been published in some magazines and those articles are included in the album. This 60 page album is not only a record of my Train Collection but it is also some that will be passed on to my family.

That’s very cool!

@Ross posted:

Mark, I use Excel, and it is possible to put a link to a photo in the spreadsheet.  I have a folder that has my photos and my spreadsheet.  When you are ready to add the link right click on the box, and select Link.

That will open a window that allows you to go the photo file and select that.

I edit the Text To Display down to just be the car initials and number, then select OK.

You will end up with a hyperlink that when selected will open a picture.

Hope this helps, Merry Christmas!

Ross

That is a great idea! I use an excel template to track all my trains saved on Onedrive so I can access it from any device and it is safely backed up. I'm going to go back and add photo links when I get time.

Confession time.  In my last post I came across somewhat cavalier by not showing much concern about my inventory until just now.   I was proven to be unwise by not having recorded my inventory movements.

It is not unusual for me to have a locomotive stored away in some hidden staging track and have it slip from my mind.  This a.m. I spent about an hour looking everywhere for a favorite SS  Q-1.  As an extreme reach I put a shout out to Gunner to see if it was in a stash of my stock to be serviced.  He noted that it was not there??  That confirmed I gotta exercise better stewardship.

In my rush to search closets and attic I failed to scroll to the bottom of John's email where he said "I'm kidding, it is here".

Just wait until the next addition to his layout!!  I'll do it when he is away on vacation... 8<(

An Excel .xlsx stored in Google Docs, that way I can edit from any computer or my phone. Will, more like view it from my phone. Then every so often I sync Google Drive to my hard drive. I don't know how to edit a .gsheets file on Windows 10. I also don't know if LibreOffice can open .xlsx files.

Oh, the diversity!! But trying to choose an inventory system that has universal appeal is a lot like trying to get the temperature right for everybody in the house - ain't happening!

I too, use Excel, but do not keep separate pages for box cars, flats, etc. When I search for stuff, the old CTFL-F works great, especially when keywords pop into mind. If my search isn't yielding the results (or more commonly, too many results), I simply filter on my CATEGORY, TYPE and CONFIG columns to narrow the field, then can more easily do a manual search. Actually, all 21 of my columns have filters set, plus I can sort on any of them.  

To avoid battery damage in the PW engines, I have a column just for battery installation dates, and will periodically sort on that to find what needs to be done.

That's another 2 cents thrown into the pot!

George

I just have a list, alphabetical, as an MS Word document, of my locomotives. Don't have anything on my other cars, just a dozen or so. Still dealing with health issues that raised themselves suddenly in September. I am amazed how my passion for acquiring more streamlined locomotives has abruptly become dormant, as if my brain threw a switch. Still have 3 locomotives still in their shipping boxes, unopened, which I really must get around to. But maybe tomorrow, or...

@Power Poynt posted:

Howdy, I've used Excel for years and am very pleased - completely customizable, easy to update, inexpensive, and compatible with nearly any other computer that may need to be used to view the inventory. I track anything of value related to the layout/collection (trains, vehicles, signals, structures, power systems, etc). The one thing I feel my Excel inventory system is missing is the ability to easily add photos. Can you describe what you don't like about Excel? Perhaps I or others can find you a solution that has the features you desire.

You can insert pictures into Excel. Just click on the cell wherever you want the picture to appear. Click on Insert and then Picture. Browse to where the picture is and click on it. You can resize it to fit the cell or leave it in it's original size.

@RJR posted:

Never mind, lionelflyer.  I found I could get Excel to open DB3 files.  Now if I could get it to open old 1-2-3 files.

Save the 1-2-3 file as a comma separate value (csv) file. Excel can open csv files.

I ended up creating an Access database program for my inventory. I tried several inventory programs but most didn't contain everything I wanted. So I took the things (fields) from each database/inventory program and put them into my own Access program.

I originally started with a spreadsheet and am slowly inputting all my spreadsheet entries into Access. It's not the best relational database, but it works for me!

The thing I like about any database program is you create any type of report you need. I don't print the reports, but occasionally run them and print them to PDFs.

Here's me spreadsheet columns:

Seq #
Cat No
Unq ID (if I have more than one item)
MA (use this column to indicate which items are in the Access database)
Description
Color(s)
Series / Set
Manufacturer
Scale
Group (my own way of categorizing my items)
Road Name Code
Road Name Description
Road Logo
Reporting Mark
Category (from Yard Office inventory program)
Type (from Yard Office inventory program)
Min Curve
Length
Built
Status (from Yard Office inventory program)
Config (from Yard Office inventory program)
Years Cataloged
MSRP
Purch Date
Sold by
Purch Price
S & H / Tax
Calc. Total
% MSRP Purch Price
Item Cond
Best Value
% MSRP Best Value
CY Greenberg Value
% MSRP CY Greenburg Value
MITrains Value Date
MITrains Value Price
% MSRP MITrains Value Price
Qty
Box Type
Box Cond
Images (up to 8)
Notes

@hokie71 posted:

@AGHRMatt, I have not linked pictures in access, is it similar to excel as Described by @Ross above.

Unless i missed it, seeing multiple item pictures simultaneously in excel or access is not easily done. Seeing a picture of the item, and all similar items simultaneously, has always been very important for me. Easy and quick to see what I have and don't have. My inventory is not huge and these basics are what keep me under control and avoid too many duplicated items. For picture ease, I pretty much migrated from excel and now rely on word files. I keep them by item category files: 40 ft boxcar, 50 ft boxcar, steam locos, etc. The issue with pictures in word docs like I use is files get big quickly, even with cropped pictures.

I initially linked pictures, but where that becomes a problem is that if you move the database file set, the links don't move with it. I imbed the photos as objects and reduce the size to 640x400, but they do cause the file to grow. The other way I looked at was linking to web-based images, but those potentially disappear if the site disappears or the photo is taken down. Don't quote me on this as my legal opinion and $3.00 will get you a cup of coffee, but I believe it's covered under "Fair Use".

By the way, the FRA reporting mark list is out there on the web and I added a table for the reporting marks with the ability to add others for those fictional situations like "7-up", etc.

@AGHRMatt posted:

I initially linked pictures, but where that becomes a problem is that if you move the database file set, the links don't move with it. I imbed the photos as objects and reduce the size to 640x400, but they do cause the file to grow. The other way I looked at was linking to web-based images, but those potentially disappear if the site disappears or the photo is taken down. Don't quote me on this as my legal opinion and $3.00 will get you a cup of coffee, but I believe it's covered under "Fair Use".

By the way, the FRA reporting mark list is out there on the web and I added a table for the reporting marks with the ability to add others for those fictional situations like "7-up", etc.

I wouldn't worry about "Fair Use" as long as you are not selling those pictures. Heck, if you own the item you have a right to have a picture of it.

Rather than modifying my existing database, I've decided to rewrite it from scratch with modified table structures. Fortunately, I can import the data. Wish me luck.

Good Luck Matt!

I like using Excel for my inventory. I use Excel in my professional and appreciate its flexibility. I use pivot tables to create reports.  I think its important to keep records of valuable items that you own.  I have about 40 fields, including product numbers, a basic description, catalog year,  purchase price, catalog price, where I bought it, etc.

I also took pictures of everything,  including the boxes.  In the picture below,  the box  has a label with "65" on it. That number ties to the excel sheet and is a unique ID for the item.  I also put items back in their boxes, along with instructions. It took a ton of time but enjoyed looking at my trains and am thankful for such a great hobby.  Both my granddads worked for the Northern Pacific, so always feel a special connection to the NP and all of the railroads that came through the Pacific Northwest, including the Lionel Lines.

Like many others, I use Excel for inventory purposes.  Also, for the past 12 years, I have been recording when I have run my trains on an Excel spreadsheet.  I insert usage dates for all locomotives, including DCS scale mph where available, and rolling stock. It always surprises me what I run frequently and what I don't.  I record maintenance dates and types on the usage spreadsheet, too.  Sometimes, I use the usage records to determine what to sell.

I too use Excel for both my O Scale inventory and G Scale inventory.  I have 20 columns with equipment data, cost, where I purchased, comments on condition, upgrades, storage location, etc.

I also have tabs for wish lists, upgrade priority and upgrade details such as tether wiring color use, board wiring and electrical diagrams.

For the inventory I make use of filters, sorting and the "Find" functions.   When I was working I routinely used Access but I find that Excel let's me easily keep track of my inventories (200-300 pieces) and I can access Excel from my laptop, iPad and iPhone.

Instead of Microsoft Office I have been using the free open source LibreOffice for years which works very well and which can save it's documents in extensions that will open in Office.

The program includes the LibreOffice Calc spreadsheet program which is nearly identical to Excel.

John

@Craftech,

I have nothing but bad memories of LibreOffice, but that was on a Linux platform. Are you running it on Windows or MacOS?

@texgeekboy posted:

@Craftech,

I have nothing but bad memories of LibreOffice, but that was on a Linux platform. Are you running it on Windows or MacOS?

I run it on Windows 7.  They constantly update it.  I can even export the documents as a pdf file.

John

I also keep my layout data-fied in LibreOffice spreadsheets. I love it! I run it on Windows 11 for my main laptop and on Linux (Mint, I believe) for the laptop I keep near the train stuff.

I didn't think I would keep records until I got to 4 trains and couldn't remember my ID numbers haha

I have been using an OpenOffice spreadsheet for my inventory for about 16-17 years.  I initially went that route because I had much more experience in using and creating spreadsheets, and almost none in databases.  It still works for me, so I have little desire to switch.  If I could go back, I would probably use a database, and I recommend databases for anyone just starting an inventory.

A big drawback to using a spreadsheet is that it is easy to corrupt, delete or scramble the data if the user is careless.  I've seen it happen too many times.

One upside for me in using a spreadsheet is the ability to easily send it to my iPad and open it with Numbers.  That way I can take my inventory with me to train shows and meets.  It has kept me from buying duplicate items quite a few times.

Andy

Loong ago, back in the dark days of Win9x, I started using a freeware application called RRTrains 2000. It wasn't specifically geared to O-Gauge (being a general model RR database), but that was okay with me since I didn't see where obscure manufacturers were going to be represented in the other inventory programs that had you input from prepared drop-down lists, and I had oddballs that weren't going to be on these lists. The application is currently running on a WinXP netbook, but it runs on Win7 and 8, and will supposedly run on W10 and maybe even 11 (I haven't peeked at the authors' website recently to confirm).

Prior to this, I had paper records consisting of a spiral-bound notebook with the barest-possible detail as it was mostly to keep a running count of the entire collection rather than documenting what I had in any detail. When it came to entering those "records" into RRT2000, I at first crammed in a couple hundred placeholder entries figuring that I would update them later.

Yeah, that was a mistake. . Going forward I vowed to compile as complete a dataset as I could for each piece and add new equipment as I acquired it (Ha!), including photographing and weighing each piece, and lately tagging each piece's box with a Post-It denoting that the piece had been documented and photographed, but also its record number in the database so it could be quickly located on the "spreadsheet" view. So far, I'm up to 550-ish records and the program, old as it is isn't bogging down, even on such an old machine. Unfortunately I haven't updated the placeholders yet, and some pertinent bits of info that would have gone with them (such as purchase location and price) has been lost to history.

I only wish it could display photos in-app instead of depending on the system's designated image viewer. It'll let you search for a photo and store a link to it, but if you try to access the database on another computer (I keep mine on a flash drive for that purpose), the links may break since the drive letter is part of the link, and a flash drive won't always get the same drive letter on another machine.

---PCJ

@RailRide posted:

I only wish it could display photos in-app instead of depending on the system's designated image viewer. It'll let you search for a photo and store a link to it, but if you try to access the database on another computer (I keep mine on a flash drive for that purpose), the links may break since the drive letter is part of the link, and a flash drive won't always get the same drive letter on another machine.

---PCJ

There IS a way to ensure your flash drive ALWAYS has the same drive designation.

Insert the flash drive into your computer (desktop or laptop - doesn't matter).

Using "Control Panel - Computer Management - Disk Management" assign it a drive letter - maybe M or some other drive letter later in the alphabet.

Unfortunately this only works if you have assigned another drive (partition) higher so that's why I always assign my DVD drive as drive "Z". Then the flash drive will always have the same drive letter as long as the computer your insert it into has a drive letter higher than the one assigned to your flash drive.

"Keeping track of what you own."

You guys are so funny!

@paulp575 posted:

There IS a way to ensure your flash drive ALWAYS has the same drive designation.

Insert the flash drive into your computer (desktop or laptop - doesn't matter).

Using "Control Panel - Computer Management - Disk Management" assign it a drive letter - maybe M or some other drive letter later in the alphabet.

Unfortunately this only works if you have assigned another drive (partition) higher so that's why I always assign my DVD drive as drive "Z". Then the flash drive will always have the same drive letter as long as the computer your insert it into has a drive letter higher than the one assigned to your flash drive.

Assuming you are on windows, and you are asking about 1 flash drive on the same computer yes.

To clarify this will ensure that Flash Drive A, is connected to computer 1.  If you plug flash drive A into computer 2 it will have a different drive letter.

To lock a flash drive to a drive letter on a windows system do the following.

Connect the flash drive.

Start

Computer management

Disk Mangement

Right click on the drive you want to assign a letter to.

click on Change Drive Letter and Paths.

Click Change

Make sure radio button is on Assign the follow....

Use the drop down to pick your drive letter from the list.

Then click OK.

Your Device is now locked to that drive letter every time you connect it to the computer you set this on.

Meaning if I take the flash drive to another computer it will not use that drive letter, you need to perform the steps above for that to happen.

I use Excel to inventory my wireless engine control upgrades. I classify by product, wireless system (RF, Wi-Fi, Bluetooth),  features included (lights, sound, etc.) method of control and type of power.

I have have several bound journals full of hand written lists of items. Most of my trains are not displayed, packed for a cross country relocation that has been delayed. Much of it has been photographed also. I've been going through the trains, using the Ambrose-Greenberg guides to correctly identify variations, present condition and so forth. I'll eventually get back to working on the inventory spreadsheet I started 20 years ago. Since I travel/work 80-90 hours a week, I rather play  with the trains than enter data onto a database in the few spare hours I have each week.

I use Excel, in fact I have since the very first edition made for the Win 286/Win 386 versions of Windows 2.0, c. 1985 (remember those?).

Over the years I've taken pictures of most of items entered in my spreadsheet, and have always wanted to enter these pictures in the spreadsheet as well the text, but Excel hasn't cooperated with this.

Pictures in Excel don't stick to cells, they float.  This means that if you increase the height of a row above where you've placed a picture the spreadsheet will slide down to add the space for the text, but the picture won't follow suit.

Wonder of wonders.  I just heard last week that the next version will finally fix this, after nearly 40 years.

It's not only trains that get old and need repairs or updates.

Mike

Is anyone else using Google Sheets?  I'm using Google Sheets and my spreadsheet is always "safely" stored away in the cloud.  I think I mentioned this before, but one sheet has locomotives, another sheet for freight cars, another sheet for accessories, and the last sheet has all the invoice information including tax and shipping.  Each item references the corresponding invoice.  I only do this for O Scale right.  I plan to create a new spreadsheet for my growing HO Scale in the near future.

Also, when you sell an item, how do you all denote that in the spreadsheet?  I'm desperately wanting to thin down my O Scale herd.  Hopefully I'll get motivated to take photos this weekend.

Anthony

I try not to do anything Google, but sheepishly admit I use their maps stuff since I haven’t found a better mapping package. I do make sure all settings are as secure as possible though.

I also don’t do cloud. I make sure things are backed up on a flash drive regularly.

Just call me paranoid about big tech, and I’ve done software development since the late 70’s.

I hate fooling with computer programs!!!  I use a three ring binder. I have pages for each major manufacturer...headings for type of car/engine, model number/road name.  If I sell an item I erase it (pencil)...buy something new...added it to the appropriate page...(pencil). Done!

When the appropritate time comes, the binder goes to the estate executor and the heirs can sort it out.

@Rockyroad posted:

I hate fooling with computer programs!!!  I use a three ring binder. I have pages for each major manufacturer...headings for type of car/engine, model number/road name.  If I sell an item I erase it (pencil)...buy something new...added it to the appropriate page...(pencil). Done!

Rock,

For most people what you do is perfect.  I only fiddle with a computer program because a binder is too easy to lose, get stolen, or destroyed in a disaster (think tornado, hurricane, or fire).

My spreadsheet, and each and every other file I have generated, including pictures, is backed up offsite, but not in the cloud, in order to avoid these possibilities.

Now, if you said to me "Why go through all this trouble if your collection is housed in the same room as the binder? Both would be affected at the same time.", I would agree.

That's why a binder should be more than sufficient.

Mike

I created a database using LibreOffice Base with the embedded database engine.  The embedded engine has known problems with scaling, but my collection of stuff isn't large enough to hit those issues.  There are other free database engines available (such as MySQL for example) that can serve as a more robust back-end for the LO Base front-end.  A hardcopy binder or Excel spreadsheet would have served me equally as well - I did it more as an exercise than anything else.  But it does allow me to have a consistent manner to grade the condition of the different items, and I can also attach photos to the inventory items so anyone who needs to see the details has a visual reference to go by.  While it's possible to add the the photos as binary blobs to the database proper, this would quickly inflate the size of the DB files and impact performance.  Instead, the DB contains only references to file locations.  As was mentioned previously, this makes the system less portable, but I found by storing the DB and images files in a shared network folder, I can open LO Base on other computers on my home network and access the database from there.