• If You Are Having Trouble Logging In with Your Old Username and Password, Please use this Forgot Your Password link to get re-established.
  • Hey! Be sure to login or register!

Parts List in a Spreadsheet

I am currently putting the Piper Parts Catalog for the PA18-150 on to an Access database. The objective is to make it simple to locate a part and accurately print out the order with all the required information. I was also planning to try and synch it to the Univair Catalog as well. Perhaps a few members might like to act as consultants to get the design right.
Kind regards
Stew
 
Last edited:
We can sure setup a sharable sheet here for lookup purposes also.

sj
 
Steve, it would be cool to create a simple DDBB with parts list, description, alternate part numbers, and pictures. It could even be a cummunity deriven and become a beginning for social content on this site. :wink:
 
Steve, it would be cool to create a simple DDBB with parts list, description, alternate part numbers, and pictures. It could even be a cummunity deriven and become a beginning for social content on this site. :wink:

Actually this is something I have considered for a number of years. Not difficult to create, but ideally the data would come from the manufacturers and multiple manufactures/sources could be cross referenced by part number. I can see if Univair, Cubcrafters, etc, might share the pertinent data with me - part number, aircraft model, alternate numbers.

sj
 
You guys are over my head with all these acronyms. However, I would love to integrate my hand written notes and hardware call outs into an Illustrated Parts Catalog for the Super Cub. I have such a manual which is all written into my parts manual but I am too computer illiterate to make it digital. Comes in real handy when I need to know exactly what hardware I need to install something or notes to guide me not to make the same installation mistake etc.
 
I like the idea of having notes and comments on each part - like help installing, etc. What other data points would be helpful for a part?

sj
 
I was in the mood to do some mindless work today and got a good chunk of the parts manual digitized using Abbyy Finereader OCR software. Here's one of the better pages that doesn't need much cleanup:

https://googledrive.com/host/0B7F_O5yTWZiBRDZycmdZUjhWblE/pa18.html

Once it's all converted, I can use it to put together a database. Rough list of all the ideas so far:

user interface options:
parts catalog format (looks like the original manual, but with clickable links, notes, photos, etc.)
simple spreadsheet view
searchable database

features:
"shopping list" - save/print/email a list of parts
part number to supplier cross-reference with links and prices (univair, spruce, cubcrafters, bushwheel, etc.)
links to drawings and/or suppliers of drawings for each part (supercubproject, northland, cub club)
user contributed notes and photographs (hardware, individual part weight & dimension)
information on modifications and STCs should be available somehow

Anything else? This could be a good winter project.
 
I have already put a few parts pages into Access 2007, a relational database that is pretty entry level but has a good report writing function and is available to download free in a run time version for any user if the data is supplied.

fancypants has outlined almost exactly the way I see it going and I also liked the idea of adding the weight of each item as in some cases this may possibly vary with supplier

The obvious reference is by Piper Part Number but it would be technically better to create a unique reference number for each part, a Part ID.

If you have no idea about databases, a short explanation

You create a series of tables (building blocks) containing hard data eg

In the case of the Piper Parts Catalog

Part ID
Figure No
Index No
Piper Part No
Code
Nomeclature
No. Required
Serial Numbers Affected

If you now consider the same part supplied by Univair, you would add their table (building block)

Part ID
Univair Part No
Nomeclature

Once you have all your "parts" tables you can go on to add other tables such as Steve Pierce suggests:

Part ID
Hand written Notes
Hardware Callouts


Once one has all the building blocks, it is relatively straightforward to bring it all together and create virtually any reference system you want to achieve exactly what you want.

One objective is to keep the database as "light" as possible and that is where database designers try to stop the database becoming "flat" and adopt the relational method. There may be 1000 Piper Parts but Steve may only have notes for 200 of them. It is inefficient to have 800 blank spaces in a flat spreadsheet type database so Steve's table would only have 200 entries.

In a good database design, every bit of information is referenced around one common identity, in my example "Part ID". Part ID will also have a Piper Part No or a Univair Part No, it might be a different spec from various suppliers but the important thing is that it has one specific identity that we enter it in to the database with. The Part ID can be invisible but it is the hinge around which the whole system operates.

Perhaps the most important thing is to co-ordinate the effort and agree a structure. Once the structure is agreed then any individual can do work in specific areas and the whole thing easily brought together at the end, bit like building a Boeing 747.

Final thing, when I googled a Piper Parts Manual some time ago it became clear that every result reproduced a .pdf file of the exact same copy. The copy has black spots where the ring binder holes are.
On Page 38, Fig 9 Page IB 14 there is a exploded diagram with the index nos 43,45,47,48......one index no is blacked out and I am assuming it is 46, can anyone confirm this.

Kind regards
Stew
 
Stew,

Good points about databases. I made most of my career off of relation databases and systems design and development. In this case because the data set is small, it is not a huge problem if there are some flat aspects to it, but it does make it difficult when you want to make changes. One thing I would suggest, is not to have a table (database) that is manufacturer / distributor specific, but to include their information in a separate table with a reference to "vendor number" or "source". Then when a new source becomes available (lke Steve Pierce emptying out his hangars of treasure) they can simply be added to the source database, and that code placed in the source lookup table for that specific part number.

I have converted many many systems from Access to other platforms, recently, mostly some brand of SQL. Access is a great prototyping tool and I know tons of organizations still using it everyday to run their businesses. - in spite of its antiquated interface and inability to interact well with the on-line world.

One question I have, is how prevalent are alternative part numbers? If so, it would be good to have a part cross reference table.

I see a structure like this:
Master Part Reference (description, master part number, link to image of part, STC 337 info, etc) Master Part number is the primary key on record per part number
- Cross reference (master part number, alternative part number) - multiple records per part.
- Models (master part number, applicable aircraft models this part works for by serial number, etc) - multiple records per part
- Suppliers Parts (master part number, supplier part number, supplier number) - multiple per part
- Supplier Master (supplier number, contact information for suppliers, blah blah blah) one record per supplier
- Notes (master part number, user notes and links on using parts)

That's off the top of my head after one cup of coffee.... I'm sure I missed something!

sj
 
I think another useful reference would be links to comments concerning the individual part on this site. It could maybe be broken down further into:
"General Comments", "How to build part", "Tooling", "Installation" etc. I spend a lot of time in detailed searches that lead all over the place and it's useful the first time but when going back months or years later it's just reinventing the wheel. It would have to be user driven like (or actually be?) the tag function but specific to the part number and a part of the listing for it.

A "for instance" would be "inboard leading edge, part #XXX,blah blah, under tooling: "Steve leading edge tool" -- comment, link.
 
Many thanks SJ for the specific response.
I have just come in from plastering a ceiling and will sit down later and sketch something out along the lines that you have detailed.
I have used older versions of Access right back to Access 95 and have got pretty familiar with it and it is great for prototyping an idea.
It is heartening to see the response from qsmx440, it just illustrates that whatever requirement is perceived, it can be provided and easily bolted on to the original database.
I think that we are agreed that we need to hang our hat on a "master part number" for the whole project
One historical point of note, I agree that flat databases are not such a problem, computers are so powerful now that database design can be pretty inefficient as the modern fast processor can handle it without any visible delay for the user.

Final thing is just to say how impressed I am by all you chaps in the US and how great your SuperCubs are turned out. My UK registered PA18-150 needs a lot of tlc and I am just planning the future. We do not have the freedom to personally work on our aircraft here, it all has to be done by a qualified person and duly signed off.

I was down at a strip in East Sussex on Wednesday and met up with a few friends there. It is called Deanland and was an Advanced Landing Site for the D-Day Invasion in 1944. The chaps there operate from a fraction of the original aerodrome but there is still 450M

Try entering "Deanland" in Google, from the results select one that is an airfield in the UK

Great to chat to you chaps as there is such a wealth of information out there in the US and have to say that we feel a little like a lost outpost here in the UK w=hen it comes to SuperCubs.

Kind regards

Stew
 
Stew we are doing our best to catch up to the UK on regulation of everything. The sport pilot and experimental stuff seems to be a bright spot for us. The basically unregulated supercub clone probably doesn't have any worse aircraft safety record than the certified unit with all the paper work, regulation, certificates, STC's liability insurance etc. I am leaving "the annual" out of this as that is an example of a good regulation based on the things the mechanics talk about finding during the process. The rest of it is probably just expensive labor and waste.

In the line of safety, this process you all are contemplating could really be so much better than all the huge (obsolete now that we have the internet) system now in place. If you also include a safety section for posting known problems then when an annual is done and some part is somehow suspect looking up the part number could also unleash all of the problems and solutions for that part. How about a section with recommended parts to examine based on time and failure. The data base should eventually turn up life span on stuff.

This is really a beautiful thing if you think about it.
 
I think you guys touched on it,

that one original part may be made by many different suppliers and might have different numbers.... univair, dakota cub, airframes AK, Steves, wag... etc......

and may have an improved or different version than stock parts..... think fuel valves, gascolators, wheels, brakes, landing gear, lights/LED/HID lights....
 
Morning everyone

There would appear to be many positive arguments for getting this done. It is taking about 5 minutes to convert a .pdf page from the Piper parts catalog to an Exel spreadsheet file, there are approx 40 pages so not a problem. It will just need tidying up and checking and then I can suck it in to one table. This is my original plan but very keen to join forces and really make something useful to everyone in the SuperCub community.

One project I did was to put all the former pupils (alumni?) of my old school in to a database, it was about 6000 people. It all worked out well and went on to be developed based on feedback much as this proposed project would. There was some comment that not everyone was computer literate so we published a comprehensive Who's Who that listed everyone with all the details we had about them (with their permission). The data from the Access database was used to create a report and we printed it to a .postscript file which the printers were able to accept directly in to their press. We did some short print runs of 200 copies.

In my notes on the project I have the following comments plus the structure suggestions from SJ

Master Part Reference (description, master part number, link to image of part, STC 337 info, etc) Master Part number is the primary key on record per part number
It is clear that a new "master part number" is required as explained above and a multiple digit number would be ideal.

Actual weights of parts

One original part may be made by many different suppliers and might have different numbers.... univair, dakota cub, airframes AK, Steves, wag... etc......

There may be an improved or different version than stock parts..... think fuel valves, gascolators, wheels, brakes, landing gear, lights/LED/HID lights....

Safety section for posting known problems with a part

Looking up the part number to unleash all of the problems and solutions for that part

Links to comments concerning the individual part. It could maybe be broken down further into:
"General Comments", "How to build part", "Tooling", "Installation" etc.

A section with recommended parts to examine based on time and failure.

Look to produce a paper copy of all the collected data


It is a clear Sunday morning in Sussex. Could get the SuperCub out but the field is very soft and wet, also the wind is about 15kts so will get on with some other things in the garage.

Kind regards

Stew
 
Man, my brain is dead but this all sounds awesome. Let me know how I can help.
I'd be willing to come down just to help get your knowledge and notes from that parts manual for everyone to read. Maybe people would stop calling and interrupting you so much ... oh wait.
 
I now have now completed the transfer of the Piper PA18-150 parts manual on to an Excel spreadsheet and Access database. Just working on the diagrams so am nearly there.

Question is, where do we go from here?

My thoughts are to get to work on the UniVair catalog and synch the two.

Kind reagrds

Stew
 
Stew, What do you want to import from the Univair catalog? Availability? My thoughts were to include more detail, hardware and installation notes.
 
Awesome work, anything I can do to help, seeing as I started this thread? I'd like to be able to filter the data by eliminating the 18a pages, then the 18-95 to 134 models and the early 18-150 models.

That will reduce the number of pages, which I will use as worksheets for my rebuilds.

regards, Don
 
Hi Steve and Aerodon

I have just converted the stock PA18-150 parts manual to a spreadsheet and database. I am planning to develop/create a master part number and synch the offerings from all the other manufactures/suppliers with the Piper Part Number.

It was interesting to have the 1339 individual parts in the file and sort them in various ways. I am thinking of assigning a global description to each part such as NUTS, WASHERS, TUBE, GROMMET, SPRING, SCREW the list goes on.

Have to say that now we have the parts database we can add anything to each part number by way of installation notes, weight, availability....absolutely anything really

Just need a bit of input from you chaps to get an end result that is of some use to everyone.

Kind regards

Stew
 
I just stumbled across this interactive parts catalog on Univair's website. It looks like they reconstructed the original parts catalog and added links straight to the product page for those parts that Univair stocks. There are a lot more pictures on Univair's site now, too. Heck of a lot easier now than it was just a few years ago. It's available to download as a PDF for use offline as well.

http://www.univair.com/content/partcatalog/18PM-flipbook/18pm.html

I think it would still be a worthwhile endeavor to create a more generic interactive parts catalog with some of the features described earlier in this thread. Unfortunately, I don't have any spare time to devote to it.
 
Back
Top