Sachin Filinto Blog Solving The Family Tree Challenge With Visio - Random Technical Artices By Sachin Filinto - Site Home - TechNet Blogs

Random Technical Artices By Sachin Filinto

Random Technical Artices By Sachin Filinto

Solving The Family Tree Challenge With Visio

Solving The Family Tree Challenge With Visio

  • Comments 14
  • Likes

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.

 

  • Unique_ID

Each person must have a unique ID

 

  • Reports_To

Which should have the Unique_ID of the parent

 

  • Master_Shape

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:

 

  1. 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.

 

  1. 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.

 

  1. Printing Across multiple Pages. ( 2 Problems here )
    1. 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.

  1. 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:

 

  1. 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.

 

 

==========================================

Some Statistics:

  • 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:

  1. Put this up on a website. ( Ahh…. That’s another Blog topic )
  1. 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

 

Comments
  • www.geni.com does the same job (family tree creation) very neatly.

  • Hey good work sachin, this helps me to prove for an case study of building the family tree as well..apart from regular demoes

  • @ ganesh, I did not explore this product. seems like a pure web based solution.

    @ Srikanth, without your help i would never have known what Visio is capable of.

  • @ ganesh, Nice to know this geni tool, but who wants to do ever and ever the same instead of importing an Excel file? I like this importing function of Visio.

    @ sachin If you add a DataGraphic based on the Visio ShapeData you can use the color by value function you can color the gender info - If this is not enough you can create your own DataGraphics on Visiotoolbox.com - if you like, you can enhance your map with an e-mail link to stay in contact with your family, just add a ShapeData field with a hyperlink like mailto:xxxx@blabla.com

  • awesome work. just the info I was looking for. bravo. shall re-visit to see how you've gone.

    cheers,

  • hi, i'm trying to build family tree too, i have some questions:

    1 reports_to allows u to connect  just to one shape, how do u connect sons to both mother and father?

    2 how did u connect wife and husband, that is not a hierarchical connection? u can't use reports_to...

    thx

  • hello,

    was going thru all internet articles for family tree in visio

    would like to know 2 things in visio 2007

    how do you link " wife" in org data in excel so it comes in the chart in correct position

    how do you make the org wizard pick a specific shape for the record in excel

  • This Visio relationship topic is exactly what I needed particular help with and I think that a good way for me to reserve intermediate and extended family gatherings. We are going to have a family reunion in my great grand parents preserved favorites. Very well to long lost Elizabeth.  

  • Sydney Aust 6/7(July)2013

    Looks great so far. I have a family trees going back to early 1700's in  proprietary (Win 3.1 dB. With your ideas I will export to Excel and follow your ideas.

    Thanks

    Geoff

  • Can you share your excel file? i would like to use your model,  as i cant figure out how to make 1 person report to both of the parents mom and dad.

    Glad if you can share

    mgc@terra.com.br

  • @  MArcel

    a screen shot of what the excel file looks like is in the blog post. there is nothing other then the 5 columns.

  • Hou do you represent a marriage and how you represent a child born from a union? In other words, you can not put two unique ID into a report_to field?

  • Hou do you represent a marriage and how you represent a child born from a union? In other words, you can not put two unique ID into a report_to field?

  • check the first image to see the representation of a marriage. Not had the other scenario in my family. ( sorry its not common in this part of the world )

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment