Solving The Family Tree Challenge With Visio


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 (18)

  1. Anonymous says:

    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?

  2. Anonymous says:

    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 )

  3. Anonymous says:

    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?

  4. Ganesh Palaniappan says:

    http://www.geni.com does the same job (family tree creation) very neatly.

  5. Srikanth, Visio SSP says:

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

  6. @ 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.

  7. cquast says:

    @ 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

  8. nebish says:

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

    cheers,

  9. Luca says:

    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

  10. skdas says:

    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

  11. Adelle Bradley says:

    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.  

  12. Geoff Raebel says:

    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

  13. MArcel Guigou Castro says:

    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

  14. @  MArcel

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

  15. Hussain says:

    Im looking for Visio Stencils for family chart like the one you created,where i can find a free template.

  16. alex says:

    Hi. This article is superb for what I would like to do, however I am stuck on one thing: How do you create the Object in the first image and allow it to use the data fields from your excel spread sheet. I.E. an Object with wife and husband. And where do
    you place the Object once it is imported into excel. any help would be greatly appreciated. Thanks

  17. Daniel says:

    It’s been a while since you posted this, I hope you’re still around. I think I share the same question with a few other commenters (Marcel, Luca, Anonymous, and skdas). Even though I think you misunderstood Anonymous’ comments.

    I guess the question is how do you represent a marriage and the children from this marriage. In the very first picture, you have "husband" and "wife" having "child 1" and "child 2". Essentially, it’s like the children "reports_to" the marriage itself, not to
    either husband or wife. How did you manage to represent that in Visio/Excel?

  18. Owain says:

    I’m having a similar problem. I am familiar with creating org charts in Visio but when it comes to Families there seems to be a restriction on adding siblings. The Visio org chart rules is limiting the structure such that a Parent can only "report to one
    child