I love Microsoft products. Here is an example of why. Working for Microsoft I get to play around with them for free. This Blog is an example of what I am talking about.
A couple of years back my mother started documenting our entire extended family. ( which is actually four different trees spanning at least 6 generations. After splitting the largest tree there were at least 75+ names left on a single sheet of paper.
The best my mother could do was glue two or more large sheets of Foolscap paper together to write this down. When I saw this I immediately thought of digitizing it and thought it will be a weekend activity.
Circa 2005; Internet to the rescue ! And after exploring many propriety software available on the internet I had to rule them all out one by one. Reasons varied from un-friendly interface to propriety methods of storage. ( considering that I would be sharing this out to the extended family for possible updates or simply viewing )
Finally after many hours spent exploring and ruling out options I zeroed on Visio after seeing how somebody else had done it. ( Website no longer available. )
I found this options scalable and somewhat convenient, but it was not to be.
In Hindsight I believe my approach needed some fine tuning. I built my own templates for the common shapes like male, female, married, child, etc. ,etc.
I got started assembling the bricks, and that’s when I realized how dam difficult aligning and sizing the different shapes is. And I had not even started putting details. Picture how aligning all the above shapes into the below would be like.
Each shape is in fact a simple grouping of discrete lines. With no other choice I carried on the cumbersome task to putting all the objects together. Not regularly but when ever I had some free time on a weekend.
All This happened sometime in 2005 and till December 2008 I was barely 25 % done with the entire tree in spite of spending a lot of time on this. I did think of Exploring AutoCAD but it was not to be. ( remembering my AutoCAD days in 1998-99 )
In December 2008 when I was somewhere 25% done with the tree I met a colleague who was the Expert on Visio & who’s job profile was to demonstrate & sell Visio to customers ( he actually sat right next to me for a very long time). In some discussion on Visio I mentioned to him I was in progress of creating this family tree and was having a tough time. That’s when he suggested why don’t I type the names in Excel File and simply import them into a Visio file.
How ingenious ! But I dint know how that could be done. A couple of minutes later he was showing me the organizational chart feature in Visio.
Here is what he did.
File > New > Organizational Chart.
Created a Couple of Objects, then exported the file to excel.
Looking at the excel file I immediately understood how Visio built the hierarchy. Just 3 columns matter and additional 2 columns for the name and title.
Each person must have a unique ID
Which should have the Unique_ID of the parent
For the identifying shape from the library.
In Short, we reverse Engineered the Visio file into Excel to understand the Required fields.
What I got :
- Ability to build a diagram from scratch with virtually no skillset in Visio.
- No messy aligning , shapes, grouping & un-grouping. ( Trust me this can be Really Messy )
- Just Type all the details in Excel & simply import them into Visio.
So, Put the Data into Excel & it take less then a minute for importing. ( compared to many many many hours doing it the old way. )
I can also simply e-mail either the Visio or a Jpeg file to the extended family. Or if I need some help in filling out the details I simply e-mail the excel file to relatives & ask them to fill it up.
( every one has Microsoft excel )
In hindsight I feel like an idiot for having spent so many hours attempting to do it using my own template 🙁
Some problems I faced on the way & how I resolved them:
- Every Spelling update made resulted in the Visio needing a re-import.
Thus I needed to make the Visio dynamically linked to the excel file. If the excel file is updated it should reflect in the Visio without re-importing it.
Done. It was a simple option in Visio.
- I need to figure out how to maintain the order when the file is imported into Visio.
i.e. Elder Siblings should be to the left of their younger sibling.
I put an additional field which had a number. So after importing the excel file, I displayed the number to sort out siblings. Then hide the number.
- Printing Across multiple Pages. ( 2 Problems here )
- Margin the Printer leaves
I knew that all printers have a limitation to printing right to the edge. But dint know how much I could push the printer I was using. After wasting many sheets of paper I decided not to bother. Simply chop off the extra paper with a blade.
- Overlap Printed ( a couple of mm )
Dint bother fixing this. Simply cut a strip off along with solution of point a.
Going ahead I need to solve the following challenges:
- Differentiate Male & female.
I have used two master Shapes ( manager & delegate ) mapping to a Descendant & His / Her Spouse.
If I need to Differentiate between Male & Female, I would need four master Shapes
(Male Manager, Female manager, Male Delegate & Female Delegate )
Given that there are no more shapes to be used I might need to create some custom shapes. ( Non-Standard – not going to attempt it just yet )
What I lost:
- Unique shapes male and female. ( I Think i can workaround that by creating a custom shape but not going to attempt it just yet )
- A lot of time I spent on the old method.
4 Family trees – 1 for each Grand Parent. ( 1 Tree split into 2 due to size, so 5 Trees )
390 different persons.
Largest tree of 135 Individuals
Up to 6 generations Documented.
13 A3 sheets of Paper for printing in landscape mode.
Many Hours to put it all together.
Unfortunate that i cannot upload the tree over here.
Adding further value:
- Put this up on a website. ( Ahh…. That’s another Blog topic )
- Capture as much information as possible in the excel file and look for ways to put it up on the Visio.
( DOB, DOD, Photo, Etc )
Credit also to a friend of mine who tickled me into moving forward.
– Sachin Filinto