TreeTABLE Help

All you need to know about TreeTABLE

TreeTABLE

Introduction

TreeTABLE is an Add-in for Microsoft Office Excel with Relation Table creating and editting functions, after you install it on your computer and open Excel, it will show up on ribbon as TreeTABLE tab.

word-image

Back To Top

How To Install

To install TreeTABLE on computer, please downloads it from iQASystem website: www.iqasystem.com/treetable-download. TreeTABLE Add-in supports both version of Microsoft Excel 2010, 2013 or 2016: 32bit and 64bit. Please select the right version for you.

Make sure you have Microsoft Excel on your system and also have Administrator right to install a software. Decompress .zip file that you downloaded, then open Setup.exe, and then follow the instruction of Setup.

Back To Top

How To Activate

Get A Serial Number

Before activate the software, you need a Serial Number, depend on the edition you want to use: Trial Edition or Pro Edition, you have 2 ways to get a serial number.

Trial Edition’s Serial Number

Submit your name and email to our request form at www.iqasystem.com/request-trial-license, we will send a Trial Edition’s Serial Number to your email address for free.

Pro Edition’s Serial Number

After you complete payment at www.iqasystem.com/tretable-pricing, we will send Pro Edition’s Serial Number to your email address.

Online Activation

If you have an internet connection from the computer you install TreeTABLE, you should use Online Activation because it’s very quick. To activate TreeTABLE online:

  • Copy the Serial Number that you received from support@iqasystem.com after purchase it, and paste in TreeTABLE Activator – Serial Number text box
  • Then click Active, if your Serial Number is correct and you still have remaining license, it will be active

image011

Note:

TreeTABLE Activator will connect to internet to complete activation, so if you have firewall, you should open your firewall for Microsoft Office Excel when you are activating the product. If you use a proxy for internet connection, you must specify your proxy for TreeTABLE Activator.

 

Email Activation

If you don’t have an internet connection from the computer you install TreeTABLE, you should use Email Activation. However this may take hours to a day to complete process. To activate TreeTABLE by email:

  • Click Activate in TreeTABLE tab. When TreeTABLE Activator open, select Email Activation and then copy the Hardware ID.
  • Send Hardware ID and Serial Number an email to: support@iqasystem.com. To know you Serial Number, check it on the email you receive from support@iqasystem.com . If you did not receive that email when you complete purchase, that will be a big problem with us, please inform to iQASystem Support team at support@iqasystem.com, iQASystem support team will help you. Check our more information about purchase process at www.iqasystem.com/treetable-pricing
  • When we received your Activation Request email, we will need maximum 16hours to process it (Except weekend), after process it successfully, we will send you an email which attaches a Pro License file (*.lic). Open Activator window from TreeTABLE ribbon, select Email Activation and click Active. You will be requested to specify the Pro License file to complete activation.

 

Back To Top

Relation Table

Relation Table

Relation Table  (or Relation Structure Table) is a “big table” which combines 2 or more sub related (data) tables. Each sub table links to other by a relation (1 to many) between columns, so data displays as a tree in table format. In many case, Relation Table make data easy to read because it not only display data but also express relations in one big picture.  It even became industrial standard like Control Plan, FMEA, DRBFM, AMDEC, FMECA.

Note:

Relation Table has other names as Relation Table, Hierarchical Table, Merge Table or Grid.

Below is an example of Relation Table which combines 3 sub tables:

tree-table-example

As above example Relation Table, there are 3 sub tables: Grandfather Table, Father Table and Child Table. Each item in Grandfather Table has one or many items in Father Table, and each item in Father Table has one or many items in Child Table.

Back To Top

Relation Table Solution

With a short Relation Table, Microsoft Office Excel or any spreadsheet software can be a solution. They can create a Relation Table with inserting, merging and changing row height several times.

How about a long one? You have to not only Insert, merge and resize row height over and over again but also keep in mind all the time to maintain relation structure of the table. And it so waste of time and make you lost the focus of content you’re developing. That is because spreadsheet software is designed to handle flat table, not relation table. TreeTABLE Add-in for Excel comes in to play to change it, provide capability to handle Relation Table without a pain.

Back To Top

Structure of Relation Table

General Structure

Relation Table is built of columns and column’s relationships. The Relation Table below has 4 columns and 3 relationships between them.

relation-table-structure

Back To Top

Relation Table’s Column

Relation Table’s Column is part of an Excel’s column. Each column has 1 Header and Items below its Header. Header or Items can be single cell or merge range which has many cells.

relation-table-column

With above Relation Table:

Column 1 has Header 1 and items: Item 1.1, Item 1.2;

Column 2 has Header 2 and items: Item 2.1, Item 2.2,.. Item 2.5.

Column 2’ has Header 2’ and items: Item 2’.1, Item 2’.2,… Item 2’.5.

Column 3 has Header 3 and items: Item 3.1, Item 3.2,… Item 3.10.

Note:

Not all range can be Header of a Relation Table Column. Please follow below requirement of a Header Prerequisite when you setup column relation for your Relation Table (Setup Column Relation)

Header Prerequisite:

  • Header of Relation Table does not take more than 1 column of Worksheet: As below example, Header 1 occupies 1 column of Worksheet, so it can be a Header of Relation Table; Header 2 occupied 2 columns of Worksheet, so it cannot be a Header of Relation Table.

image007

  • A Header cannot be an unmerged range; it must be a single cell or merge range: As below example, Header 1 is a merge range, so it can be a Header of Relation Table; Header 2 is not merge range, so it cannot be a Header of a Relation Table.

image006

 

Back To Top

Column Relation

There are 2 types of column relationship.

  • 1-n relationship: (also called Parent – Child) is 1 to many relationship, means 1 item of Parent Column has many items of Child Column. Both Parent Column and Child Column in a relationship are Master Columns.
  • 1-1 relationship: (also called Master – Sub) is 1-1 relationship, means 1 item of Master Column has 1 item of Sub Column. Each Master Column can have many Sub Columns, Master Column and its Sub Columns generate a flat table.column-relation

Back To Top

Column Type

Base on the relationship between columns, there’re 3 type of Column in a Relation Table:

  • Root Column: The highest level in hierarchical tree of column relationships
  • Master Column: is column involved in a 1-n relationship (or left column in a 1-n relationship)
  • Sub Column: is right column in a 1-1 relationship.

Note:

  • If the Relation Table has more than 1 column, the Root Column is also a Master Column.
  • You must set Master column first before set it Sub column. A Master column is child of other Master column or Root column.

Back To Top

Tree Sheet

Tree Sheet is a Worksheet that has Relation Table on it. Each Tree Sheet has only one Relation Table.

Back To Top

Creating Relation Tables

Convert To Tree Sheet

Before create a Relation Table on specified Worksheet, you must convert the Worksheet to Tree Sheet first. To convert a normal Worksheet to a Tree Sheet:

  • Open the Worksheet you want to convert (this is not necessary a blank Worksheet), this Worksheet must not be a Tree Sheet.
  • Click “Convert To Tree Sheet”.

image013

Note:

Each Tree Sheet has only one Relation Table.

Back To Top

Set Root Column

TreeTABLE allows you to setup column relations. When you create relation between columns, TreeTABLE actually create relation between Headers of them. Before you setup column relation of a Relation Table in a Worksheet, make sure you already convert the Worksheet to Tree Sheet. Follow the below steps to complete setup column relation of your Relation Table, remember you only have to do this one time.

Set the Root Column of Relation Table, please follow the steps below:

  • Select the Header of the column you want to set as Root Column.
  • On the TreeTABLE tab, in the Edit Relation Table group then click Set Root Column.

image014

Note:

Each Relation Table has only 1 Root Column.

You also can use Column Relation Manager to set Root Column with below steps:

  • Open Column Relation Manager by click Column Relation Manager in Relation Table Setting of TreeTABLE tab.

image015

  • When Column Relation Manager appears, click Set Root Column.

image016

  • When Input Box appears, select the Header of Root Column you want to set and click OK. Please take note that the Header you select must satisfy Header Prerequisite (described in Relation Table’s Column) and it has not been used for other Column Header.

image017

  • This is the result, Column Relation Manager will show up again with the Root Column in the tree view.

image018

Back To Top

Add Child Column

To add Child Columns to an existing Master Column, please follow the steps below:

  • Open the Column Relation Manager by click Column Relation Manager in Relation Table Setting of TreeTABLE tab.
  • From Column Relation Manager, select Header of Master Column you want to add Child Column to it and click Add Child button.
  • An Input Box appears, select the Header of Child Column you want to add and click OK. Please take note that the Header you select must satisfy Header Prerequisite (described in Relation Table’s Column) and it has not been used for other Column Header.
  • If not thing go wrong, Column Relation Manager will show up again with update new Child Column

image020

Note:

In order to prevent complicated structure of a Relation Table, please try to keep each Master Column has only 1 Child Column.

Back To Top

Add Sub Column

Sub Columns to an existing Master Column, please follow the steps below:

  • Open the Column Relation Manager by click Column Relation Manager in Relation Table Setting of TreeTABLE tab.
  • From Column Relation Manager, select Header of Master Column you want to add Sub Column to it and click Add Sub button,
  • An Input Box appears, select the Header of Sub Column you want to add and click OK. Please take note that the Header you select must satisfy Header Prerequisite (described in Relation Table’s Column) and it has not been used for other Column Header.

Back To Top

Exchange Column

Sometime you want to exchange the position of a column in relationship by another column. When you exchange the column by another column, TreeTABLE actually remove current column out of relationship and replace it with a new one.

To exchange column, please follow the steps below:

  • Open the Column Relation Manager by click Column Relation Manager in Relation Table Setting of TreeTABLE tab.
  • From Column Relation Manager, select Header of the Column you want to add Sub Column to it and click Add Sub button,
  • When Input Box appears, select the Header of Sub Column you want to add and click OK. Please take note that the Header you select must satisfy Header Prerequisite (described in Relation Table’s Column) and it has not been used for other Column Header.

Back To Top

Unlink Column

Sometime you may want to unlink a column out of columns relationship (for example: when you set something wrong). When you unlink a column out of column relationship, all relations that have involvement of that column will be deleted, that mean its Child Columns, Sub Columns will be also unlinked.

To unlink a column, please follow the steps below:

  • Open the Column Relation Manager by click Column Relation Manager in Relation Table Setting of TreeTABLE tab.
  • From Column Relation Manager, select Header of the Column you want to unlink and click Unlink button,

Note:

Do not delete any range (row, column, cell) that include Header of a Column that has relation, please Unlink it out of Column Relation Manager before delete it.

Back To Top

Convert To Worksheet

Sometime, you might want to convert your Tree Sheet to normal Worksheet (example: just to restart creating Relation Table), TreeTABLE allow you to do it with a click. When you convert Tree Sheet to normal Worksheet, it does not delete Relation Table column but it will remove all relationships between columns.

To convert Tree Sheet to normal Worksheet:

  • Open Tree Sheet that you want to convert
  • On the TreeTABLE tab, in the Relation Table Setting group then click Convert To Normal Sheet.

image021

Back To Top

Importance Note

TreeTABLE uses Names of Excel to define its columns’ Headers; these Names start with “QATLN” and set hidden to user in default. You should not unhide or change it and define any new Name that starts with “QATLN”, if you do that, your Relation Table sheet will be corrupted. Also before you start convert your Excel Worksheet to a Relation Table, please make sure that there are no exist Name in your sheet that has name starts with “QATLN”.

TreeTABLE uses Custom Properties of Excel to store it setting data. These Custom Properties has name start with “QAT”, those are set hidden to user in default and not effect to your normal use. So please don’t change or delete them.

Back To Top

Editing Relation Tables

Insert Item

You can insert a blank item below the active item on a Relation Table. When you insert blank item, TreeTABLE will create a new row for new item but keep all other non-related item. To insert a blank item:

  • Select the item where you want to insert the new blank item and
  • On the TreeTABLE tab, in the Edit Relation Table group then click Insert Item.

image022

  • Tip: You can also right the selected item and then click TreeTABLE: Insert Item

image023

Tip:

For quick inserting in action: In the add-in ribbon’s Edit Option group, select “Insert by Key”. Select the item where you want to insert the new blank item and press INSERT in the keyboard.

Note:

Please note that TreeTABLE will not insert new item when Excel in Edit Mode. (Example: when you are typing cell content).

Before you start editing a Relation Table, you need to set up column relation for it (Setup Column Relation).

 

Back To Top

Delete Item

You can delete an active item on a Relation Table. When you delete item, TreeTABLE will delete the item you selected and its master item. Note that when a master item is deleted, it’s all child items and sub items also are deleted. The non-related items in Relation Table will not be affected. To delete an item:

  • Select the item where you want to delete.
  • On the TreeTABLE tab, in the Edit Relation Table group then click Delete Item.

image025

Tip:

You can also right the selected item and then click TreeTABLE: Delete Itemimage026

Note:

Pressing DELETE only deletes selected item’s contents, does not delete the item itself.

Back To Top

Fit Content Of Item

TreeTABLE allows you to fit the contents of the selected item. When you fit item, TreeTABLE will change the row height of occupied row to fit not only the content of selected item but also contents of related items. Does not like Excel, this function works with merged cells item. To fit content of an item:

  • Select the item which you want to fit content.
  • On the TreeTABLE tab, in the Edit Relation Table group then click Fit Item.

image027

Tip:

You can also item content fit whenever you change it by select “Auto Fit” in the add-in ribbon’s Edit Option group.

You can also right click the selected item and then click TreeTABLE: Fit Item.

image029

Back To Top

Fit Content Of Items

TreeTABLE allows you to fit the contents of the selected items. When you fit items, TreeTABLE will change the row height of occupied rows to fit contents of selected items. To fit contents of items:

  • Select the items that you want fit contents.
  • On the TreeTABLE tab, in the Edit Relation Table group then click Fit Items.

image030

Back To Top

Copy Formula In A Column

TreeTABLE allows copy down the formula of top up item in the table to all other item below it in the same column. When you fit item, TreeTABLE will change the row height of occupied row to fit not only the content of selected item but also contents of related items.

To copy formula in a column:

  • Select the first item in the column. This item should have a formula and the formula should have precedents which are its father items or father of father items.
  • On the Relation Table tab, in the Edit Relation Table group then click Copy Formula.

image031

Note:

The first item in a column is item just below the Header of the column.

Back To Top

Search Item

TreeTABLE allows search and show only the qualified items in a column. When you search items, TreeTABLE will hide the item that does not include the text you search, and unhide all items that does include the text you search. To search item in a column:

  • Select the Header of the column you want to search.
  • On the TreeTABLE tab, in the Edit Relation Table group then click Search.

image032

  • An Input Box open and request you to in put a search text, please type the search text, then click OK.

image033

Tip:

To clear the search, click Clear Search beside Search button on TreeTABLE tab.

image034

To show item that is partly hide by Search function, you can right click the item and then click TreeTABLE: Show Item.

Back To Top